I am trying to get the % of total calculated but it doesn’t appear to be working correctly. I have a measure (violations) and dimension (stores) and I want to show the % of total violations per store. I’ve tried percentOfTotal and a window function like I would do in Tableau, but it isn’t calculating appropriately.
Also, I would like to limit this to the top 10 stores. I want 1 calculation that provides 1 number that shows the percent of total violations that come from the top 10 stores.
Hi @rima.issa ,
We can calculate numerator as :
sumOver(violations,[stores],PRE_AGG) ,
This calculates sum of violations for each stores.
and denominator as:
sumOver(violations,, PRE_FILTER)
This calculates total violations.
Then, we can use another calculated field to calculate % of total :
Sum(numerator)/Avg(denominator)
However since we have used store as level to calculate numerator, the visual where you use these calculations will need to have reference of the stores field. Also based on your use case and data granularity, you may need to tweak the formula.
To filter top ten stores you can use stores filter by sum(violations) to filter top 10.
Hope this helps.
Thanks,
Prantika
Hi @prantika_sinha,
Is there a way to specify which filters we want to include in the denominator? The only filter I don’t want included is the top 10 filter.
If i understand correctly, you want all other filters to be considered but not the stores filter for top 10 stores. The calculation will need modification based on the fields you want to filter and the fields used in your visual.
Please share sample data for this use case to be able to identify the data granularity and other fields you would like to consider while calculating the denominator.