Currently, I am utilizing the following calculated field, **percentileRank([avg(queueduration) DESC], [truncDate('MM', LocalStartTime)])**, to derive P90 and P99 figures by employing the average queue duration from my phone call dataset.
However, I’ve encountered a challenge when attempting to display both the P90 and P99 measures together in the same pivot table visual side-by-side. It seems that the PercentileRank function operates primarily as a filter control, potentially restricting the entire table for a specific value.
Is there any workaround or alternative approach to achieve the simultaneous display of P90 and P99 in the same pivot table visual without encountering this limitation? Any insights would be greatly appreciated. Thanks!
Hello @manbig, so due to the fact that the percentileRank function is running these aggregations based on another field in a sort order, it will have an impact on how your data is displayed when they are being utilized. Have you considered utilizing the data returned from those functions in a different way, or possibly having a visual for each percentile rank measurement? Not all of your rows are going to return within a P90 or P99 measurement, so splitting them up may be for the best.
You could also consider running some ranking calculations in your custom SQL query for your dataset, to return a percentile value on all rows, then you could implement filters to display data that line up in the P90 and P99 groupings.
One more option would be switching to the percentileDisc function in QuickSight to just show the P90 and P99 on their own column, and that may provide you with a result that is closer to your expectation.
Let me know if any of those options lead you to the result you are expecting! Please mark my response as the solution, or let me know if you have any follow-up questions related to this issue.