Percentage of total per distinct customer

Question & Answer
, , ,
1

Hi guys

I need to make a percentage calculation field of the total counting unique customers, but it’s not working.

percentageOfTotal(
distinctcount({id_customers}),
[truncDate(‘MM’, {date_ofe})]
)

How to do it?

2

@July , please refer community posts below regarding similar use case and let us know if this helps in resolving your use case.
1/Percent of Total for Distinct Count - #4 by duncan
2/ Getting percentages of total from a count distinct

Thanks

1 Like
3

Hi @DeepakS

I tried a few ways.

The command below executes, but shows all values ​​as 100% in the stacked bar chart.

percentOfTotal(avg(distinct Count Over({id customer},,PRE AGG))))

I need the total of all bar stacks to return a total of 100%

Tks

4

I haven’t resolved it yet.

5

@July, since percent of total for a distinct count not supported, you need to calculate numerator and denominator separately and then divide. Something like below -
distinctCountOver({customerID}, [truncDate(‘MM’, {date_ofe})], PRE_AGG)/distinctCountOver({customerID}, [], PRE_AGG)

1 Like
6

Hello @DeepakS

This doesn’t work:
distinctCountOver({customerID}, [truncDate(‘MM’, {date_ofe})], PRE_AGG)/distinctCountOver({customerID}, , PRE_AGG)

This shows all values ​​as 100% in the stacked bar chart:
avg(distinctCountOver({customerID}, [truncDate(‘MM’, {date_ofe})], PRE_AGG)/distinctCountOver({customerID}, , PRE_AGG) )

The two below are working, but I’m not sure what the difference is between them:
I don’t know if it’s a different count or not, both commands have the same result.

percentOfTotal(
count({customer_id}),
[truncDate(‘MM’, {date_ofe})]
)

percentOfTotal(
count(distinctCountOver({customer_id}, , PRE_AGG)),
[truncDate(‘MM’, {date_ofe})]
)

7

@July, you mentioned that last two expressions worked, so curious if you have validated the results and results meet expectations. if not, then may be you want to create an analysis with sample dataset using [QuickSight Arena.] and ask question (NEW - QuickSight Arena -- An embedded instance of QuickSight within the QuickSight Community)

1 Like