distinct_count/distinctCountOver alternative

[Bhavya_Jain]

Hi,
I have a customer level dataset. I wish to count distinct customer IDs (grouped by a few fields in my visual), and then add another field that would find the % of customer IDs in each of the groups. For this, I believe I need to do something like:

distinct_count({customer_id})/distinctCountOver({customer_id}, [{marketplace_name}, {program_id}, {campaign_id}], PRE_AGG)

Because distinctCountOver does not allow Post_Agg calculation, this formula is not working as distinct_count is aggregated field.

This is the error I keep getting:
Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination

I cannot do this calculation in my source query, to avoid double counting of customers when groupings change in the visuals. Is there any other way to achieve what I want? This is super frustrating as the same thing works absolutely fine with sum/sumOver

[Giridhar.Prabhu]

Hi @Bhavya_Jain

Please check this article. There is a section about handling Mismatched aggregation issues in there. See if you can resolve your issue using the details from this article.

Level-Aware Calculations: Tips and Best Practices

[Level-Aware Calculations in Amazon QuickSight] Level-Aware Calculations (LAC) are powerful functions that allow us to perform calculations at a granularity level which is different from the granularity of our datasets and/or visuals. The sample dataset referenced by this article is a dataset of product orders placed by customers. Each order can contain multiple products and each product in an order has a unique Line Item ID. Each row of our dataset represents one line item but we want to per…

[Brett]

Hi @Bhavya_Jain,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

[Brett]

Hi @Bhavya_Jain,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!