Best way to show Top N items while grouping remaining as 'Others' for Pivot in QuickSight?

Question & Answer
, , , ,
1

How can I display top 15 P Processors while grouping the rest as ‘All Other’ in a QuickSight Pivot Table?

Current setup:

Rows: Grouped Payer (Calculated Field), P Processor
Columns: Dates
Values: Units

Need to:

1. Show top 15 P Processors per category
2. Group remaining processors as 'All Other'
3. Maintain total units

Using standard top 15 filter removes others from the total. Looking for a solution that preserves the complete total while condensing the display.

2

Hello @kendesai Hope this message finds you well :smiley:

Here’s a suggestion for achieving this:

  1. Rank Processors:

    rank([Units], [Grouped Payer, P Processor], DESC)

  2. Group ‘All Other’:

    ifelse(rank([Units], [Grouped Payer, P Processor], DESC) <= 15, [P Processor], 'All Other')

  3. Apply in Pivot Table: Replace P Processor with the calculated field to show top 15 and group others as ‘All Other’. Total units will remain intact.

Hope this helps you!

3

Thanks for the suggestion. I’m getting a syntax error with the rank function. Could you share the exact QuickSight syntax that worked for you?

4

what type of error do you get?