Finding the Top N Within a Category

Published: 07 Mar 2017
Last Modified Date: 31 Mar 2023

Question

How to find the top N within a category.

Environment

Tableau Desktop

Answer

To create the initial view, connect to Superstore sample data. Place Region and Sub-Category on Rows and SUM(Sales) on Columns.

Option 1: Use INDEX()

Select Analysis > Create Calculated Field.
Name the calculation (in this example, the calculation is named Index) enter the following formula, then click OK:
INDEX()
In the Measures pane, right-click Index and select Convert to Discrete.
Place Index on Rows, between Region and Sub-Category.
Right-click Index on Rows and select Edit Table Calculation.
Under Compute Using, select Specific Dimensions.
Under Restarting Every, select Region.
Under Sort Order, select Custom and enter the following options:
Sales Sum Descending
Ctrl+Click Index on Rows and place it on Filters. Click OK.
On the Filters shelf, right-click Index and select Continuous.
In the Filter dialog box, type the range of values for your top N, then click OK.

Option 2: Use RANK()

Rather than INDEX, you can use the following calculation:
RANK(SUM([Sales]))
This calculation has the sort order built in, so you can skip the Sort Order step above when using this method.