I have the “number of flights” (count of distinct mission IDs field) and the “number of flight interruptions” (count of distinct mission IDs with some filters applied). I also calculate the percentage of flight interruptions as follows:
“number of flight interruptions” / “number of flights.”
Now, I have a graph showing the reasons for flight interruptions. When I click on one reason, I want the percentage to show the number of flight interruptions for that reason divided by the total number of flights. However, the denominator is also influenced by the click, so it counts only the flights that have interruptions from that reason and shows 100%.
Additionally, you need to apply the filters for “flight interruptions” on all the shown visuals except “Number of Flights”.
Lastly, you can add a filter action on the “Flight Interruption by Reason” visual that filters the “Reason” on visual “Number of Flight Interruptions” (in case you want the total of interruptions also to update based on the selection) and “Percent of Flight Interruptions”.
The result would the look somehow similar to the following: Without selection:
It’s difficult to debug from here, but have you checked whether some additional filters have been applied? Otherwise, you could re-create your example with some dummy data within the QuickSight Arena here in the community and share it on this post. This will allow others in the community to copy that example and share a solution back to you.
Thanks. I think I understand what happened—I have a time range filter for the missions that I want to apply for all the sheet, but it’s not affecting the denominator of % Interruptions calculation (avg(distinctCountOver({mission_id},,PRE_FILTER))
), so the number isn’t accurate. Is there a way to yet apply this filter to that calculation?
Yes, in that case you can define an additional calculated field that contains only the time filtered Mission IDs. Instead of using the regular filters, you have to define the filter logic within the calculated field using a parameter to define for example the min date (and max date if needed). For a min date only filter, the calculated field expression would look as follows:
Afterwards, you simply leverage that newly defined filtered field within all three KPIs (for the percentage KPI, you will need to update the underlying calculated field). And make sure not to define any time filter in addition to the logic within the calculated field.
Did this answer your question? If so, please help the community out by marking this answer as "Solution!
