How to keep denominator fixed?

Hi all,

Anyone know how to fix the denominator for the percentage calculations?
Here’s the example:
I created a dashboard showing the yearly sales data.
There are 4 columns, Year, Category, Sales, and the percent of each category comparing to the year total sales.
I only need to show top 4 sales categories for each year.
The correct number should be in the screenshot.
However, when I created the same view in QuickSight, I think the denominator is automatically changed by the Year and Category column, so the Percentage are all 100%, since it shows the category sales/category sales, not category sales/yearly total sales.

Am I missing any settings? Or is there any trick like adding some calculated fields to solve this?

Thank you!

You need to use level-aware calculations with PRE_FILTER in your denominator.

Take a look at this post and let me know if it helps:


Thanks @David_Wong for sharing the details.

Regards - San

1 Like

Thank you very much @David_Wong
The link is very useful. Problem solved!