Issue in Computing Percentages with repetitive data

Q&A
, , , ,
1

Hi Team,

I have a query on computing percentages in a bar chart.

I have certain products IDs categorized under certain Attributes. For example: sweet, sour, summer, winter, acidic. Now some product IDs can have multiple attributes amongst them, so if I create a bar chart out with keeping the Attributes on Y-axis, I’ll have repeated IDs in there.

My problem case is, I have a dynamic Bar Chart which is based on Dimensions (as Y axis), which has multiple select options and the above ‘Attribute’ is one of them. In the bars, I’m required to show the percentage of each variable based on the total count if distinct IDs.

For Example, I have a Dimension ‘Colours’ for which no ID can have more than one,

Dimension Variable: Colour Count of Distinct Product IDs Percentage of Total
Red 180 35.29
Orange 70 13.72
Green 120 23.52
Yellow 90 17.64
Dark 50 9.50
sumOver 510 100

And I can achieve the required percentage with this formula: distinct_count(product_id)/sumOver(distinct_count(product_id)

But in the case of Attribute as Dimension Variable, the formula wrongly calculates the percentage as the IDs are repeated for the attributes and sumOver ignores that and considers the overall sum (instead of 510). Like this:

|Dimension Variable:

Attributes Count of Distinct Product IDs Percentage of Total Correct Percentage(/510)
Sweet 300 30 58.82
Sour 120 12 23.52
Summer 250 25 49.01
Winter 260 26 50.98
Acidic 70 7 13.72
sumOver 1000 100 100

I want the formula to consider the distinct count of product IDs only here as well and give the correct %.

I tried to replace sumOver with DistinctCountOver in the formula, but in that case, it requires the Numerator as well to change to an LAC function, which when I do, again gives me error of mismatched aggregation, could be as I’ve used Dimensions as dependent variable in Nr, again a composited field?

distinctCountOver(product_ID,[Dimensions],PRE_AGG)/ distinctCountOver(product_ID,[],PRE_AGG)

And I’m not sure what else to use here, as none of it is giving me result

Can this be helped, I’m stuck with it.

Thanks in advance.

Regards,
Himani

2

Hi @HimaniS,
Apologies for the delayed response from the community on this thread. Trying to work through some ideas on this but I don’t have a great sample dataset that’s similar to run some testing on;
This may be easier to assist further if you are able to upload a sample dataset to the QuickSight Arena view:

3

Hi @HimaniS,

I hope this message finds you well! Just wanted to check in on if you saw Brett’s reply or found a workaround to your question in the meantime. If we do not hear back within the next 3 business days, I’ll close out of this topic.

Thank you!

4

Hi @HimaniS,

Since we have not 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.

Thank you!