I’m working on a vertical stacked bar chart in QuickSight where I want to show the percentage of exceptions out of the total.
Here’s how I have my calculations set up:
Exception Count: sum({IDs})
Total (denominator):sumOver({IDs}, [{run_date}, site], PRE_FILTER)
Percentage: sum({IDs}) / min({Total})
What I’m trying to achieve:
- I want the denominator (Total) to be fixed for all bars — aggregated only by the selected site(s) and date, not affected by other filters (like termination reason).
- The numerator (sum({IDs})) should respond to the filters, but the denominator should remain constant for the selected site(s).
- This setup works correctly when only one site is selected, but when I select multiple sites, the denominator doesn’t reflect the combined total correctly.
Additional context:
I have a termination reason filter as well, but the percentage should not change when different termination reasons are selected — it should still divide by the full site-level total (This is working fine if I select One site).
How can I calculate a fixed total that sums across all selected sites (one or multiple) while ignoring termination reason and other visual-level filters?
Thanks in advance!
This is how my data looks like
1 Like
Hello @Nitya_Goriparthi, I was able to build out a solution to this in QuickSight Arena:
Need help calculating percentage with fixed denominator across selected filter
My fields are going to look a bit different than yours, but the idea should be the same. Using the in() function to manage filtering for multi-value parameters in a nested ifelse statement, I can pick and choose which of the filters the denominator and the numerator will be required to follow depending on the user selections.
Then, we can apply PRE_FILTER as the aggregation level to ensure filters applied directly on the visual will not impact our calculation unless it is referenced in the calculated field.
One thing to note as well, I left a commented note in the calculated field, but in order to check for the Select All option in the dropdown, you have to check for the presence of a NULL value. That makes it so each parameter will need to be checked twice.
in(NULL, ${Parameter}) OR in({Value}, ${Parameter})
That functionality can be a bit tricky so I wanted to provide some more information here. Let me know if you have any questions!
Thanks so much, Dylan. This worked perfectly for my use case! I needed all filters applied to the numerator but not the denominator, and your calculation helped me get the right totals.
One more question: is there a way to sort the stacked bar chart by the displayed percentages? When I try to sort by percentage, it messes up the date field on the x-axis and still doesn’t sort the bar sizes correctly based on the percentage.
1 Like
Hello @Nitya_Goriparthi, unfortunately, this is a limitation to the visual. The bars are going to show the color by values in same order that you see in the legend. It won’t display the order differently per bar, because it is set to that constraint.
Now, in your visual, I notice that each grouping has relatively similar percentages across each individual bar. You could do something where you rank the returned values for each and replace your group by field with the rank field. This kind of messes up the legend, but you can at least replace the field that you show in the tooltip.
The first bar chart here has an example that you can see:
Percentage with Fixed Denominator
