How to keep denominator fixed?

[danddong]

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!

[David_Wong]

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:

Divide 2 calculated fields in QuickSight derived from same column xyz

For this type of calculation usually you need to use LAC-W functions. You need to use PRE_AGG in the numerator and PRE_FILTER in the denominator. It’s covered in this video. I put the timestamp for you. [QuickSight Virtual Author Workshop Part 2] Another more recent video about level aware calculations: [2022 QuickSight Learning Series: Using Leverage Level Aware Calculations to work with data]

[Sanjeeb2022]

Thanks @David_Wong for sharing the details.

Regards - San

[danddong]

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