How to calculate Percent of total

I am trying to create a cohort analysis with the % retention, month after month.

I have created a cohort table with absolute numbers, but I cannot calculate a percentage of the total for each cohort.

For example: I am trying to calculate the numbers within the pivot table as a percentOfTotal, where the total should be the distinctCount of created_at for each month.

Does anyone have any ideas ?

Thanks!

Hello,
Could you please share a small sample of “fake” data replicating your dataset for this table (10-12 rows).
If you can also include an screenshot to understand on each cell what is the meaning of each value? E.g. crated_at=May/Send_date=Jul2021, what is the total at that point?

Hi @Srdan_Jovanovic, distinct count is not currently supported with the percentOfTotal function in QuickSight. You can workaround this by creating your calculation in SQL.

I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!

Peter is correct that percentOfTotal currently doesnt support distinct counts, however you could compute your own percent of total with your own numerator and denominator (where we will use an LAC-A function in the denominator to compute it at a different level).

distinct_count(field)/ sum(distinct_count(field, [created_at]))

Can you try that?

1 Like

@Jesse tried with a similar setup as reported by @Srdan_Jovanovic, but received this error: “Nesting of aggregate functions like / and COUNT(DISTINCT “Active Users (Last 90 Days)”) / NULLIF(SUM(COUNT(DISTINCT “Active Users (Last 90 Days)”)), 0) is not allowed.”

For background, here is what I am working with:

Active Users (Last 90 Days): ifelse(dateDiff({completion_date},now()) <=90,{user_id},NULL)

This calculated field aggregate is set to Count Distinct and it works well! When trying to calculate the number of active users over the total sum of users to return the percent of total during the same 90 day period, this is where I am getting the above error:

distinct_count({Active Users (Last 90 Days)})/ sum(distinct_count({Active Users (Last 90 Days)}))

Hi @jtuten - That error message is saying you cannot layer two aggregations on top of on another (e.g. sum of disctinct count). However when we write LAC calculations, the result is an un-aggregated field from quicksight’s perspective. In your denominator inside the distinct_count you are missing the comma and the field you want to group that by, which turns it into an LAC.

distinct_count(field , [created_at])

If you dont want to group by anything (analagous to a subtotal) and want the total across all the data, you can put those square brackets but leave it empty inside

distinct_count(field , [ ])

1 Like

@Jesse! You’re a wizard – thanks for the help

2 Likes

Thanks for letting us know this helped you @jtuten! Based on your reply, I am marking Jesse’s response as Solution.

1 Like