Distinct count in pivot

Looker Q&A Looker
1

Here is a sample of data which shows applications and reasons for which applications are declined.

  • Total 3 distinct applications
  • One application can have multiple decline reasons

Screen Shot 2020-07-02 at 5.23.16 PM.png
Screen Shot 2020-07-02 at 5.23.16 PM.png541×227 7.12 KB

I have created an explore where I have month on x-axis, reason as pivot and I show count of applications declined for each reason

Screen Shot 2020-07-02 at 5.23.22 PM.png
Screen Shot 2020-07-02 at 5.23.22 PM.png754×114 4.23 KB

So far so good.

I want to create a visual that shows stacked bar chart which shows
% of applications declined because of “address not found” = 3/3 =100%
% of applications declined because of “user not authenticated” = 2/3 =67%
% of applications declined because of “user not authenticated” = 1/3 =33%

However, If I use the stacked % in Looker, the denominator is 3+2+1 = 6. I want the denominator to be 3
I tried pulling in count of distinct applications but the distinct count is same as pivot count

Any tips on how I can achieve what I am going for.

2

If you had the row total then it should show 3

Then from there you can create a table calculation that is the ratio between your count and the row total.

Hide the count from the visualization and only display that table calc (with format percent).