KNOWLEDGE BASE

Finding the Top N Within a Category


Published: 07 Mar 2017
Last Modified Date: 05 Oct 2017

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()

  1. Select Analysis > Create Calculated Field
  2. Name the calculation (in this example, the calculation is named Index) enter the following formula, then click OK:
    INDEX()
  3. In the Measures pane, right-click Index and select Convert to Discrete
  4. Place Index on Rows, between Region and Sub-Category
  5. Right-click Index on Rows and select Edit Table Calculation.
  6. Under Compute Using, select Specific Dimensions
  7. Under Restarting Every, select Region.
  8. Under Sort Order, select Custom and enter the following options: 
    Sales Sum Descending
    User-added image
  9. Ctrl+Click Index on Rows and place it on Filters. Click OK.
  10. On the Filters shelf, right-click Index and select Continuous
  11. 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.

Additional Information

To view these steps in action see the video below.
Note: video has no sound.
Did this article resolve the issue?