A question related to percentoftotal

Q&A
1

I was trying to add a calculated field in a table to calculate the percentage of customers in each division to the total number of customers.

My formula is
image

However, it gave me an error:
Nesting of aggregate functions like percentOfTotal and PERCENTOFTOTAL(SUM(COUNT(DISTINCT “BUS_PRTNR_NBR”))) is not allowed.

Could anyone help me?

2

I think there are two problems here:

  1. you are nesting two aggregate function (sum() and distinct_count())… in reality I think you meant percentOfTotal(distinct_count({BUS_PRTNR_NBR}))
  2. The above still does not work because percentOfTotal does not support distinct_count() aggregation yet. (see percentOfTotal - Amazon QuickSight)

But you can still achieve this by calculating the percentage directly by calculating the fraction of distinct customers in each category divided by total number of unique customers:

   distinct_count({BUS_PRTNR_NBR})
   /
   min(distinctCountOver({BUS_PRTNR_NBR}, [], PRE_AGG))

(Note: min() function in denominator is just to avoid mixing aggregated and non-aggregated terms… otherwise QuickSight gives an error)

Finally just change the format of this new calculated field to Percent.

1 Like
3

Hi darcoli,

Thank you for your answer, I can do it with the formula below:

distinct_count ({BUS_PRTNR_NBR}) / sumOver(distinct_count({BUS_PRTNR_NBR})) and the result is what I expected.

However, I am using the Pivot Table to show the results. When it comes to the row of subtotal, the formula does not work. That is why I have been seeking other solutions.

My dashboard was previously built in Power BI, which has a clear definition of “new column” and “new measure”. Therefore, the calculation can show up in the whole column including the subtotal line. However, In QuickSight, there is no definition between new columns and new measures. Instead, the calculated field is used for both of them. That has caused lots of troubles in Pivot table. Hopefully QuickSight can improve this in the future as businesses use lots of complicated pivot tables to show the results.