KNOWLEDGE BASE

Finding the Top N Within a Category


Product(s): Tableau Desktop, Tableau Public
Version(s): All
Last Modified Date: 16 Aug 2016

Article Note: This article is no longer actively maintained by Tableau. We continue to make it available because the information is still valuable, but some steps may vary due to product changes.


The Top tab in the Filter dialog box allows you to define a filter to show the Top N items based on a specific measure. For example, you can use this type of filter to show the top 5 categories by sales in each region.

This type of filter is computed across the entire data source. You can see that the same 5 categories are shown within each region because those are the top 5 selling categories sold across all regions. Instead of computing across the entire data source, you can calculate the Top N within a category by combining fields (i.e., creating a set) and then manually filtering the values. The following example below shows you how to create a calculation that acts as a filter to compute the top 5 selling categories within each region.

Create a nested sort

Follow the procedure outlined in the Nested Sorting article to create a nested sort that looks like the image below, or use the attached workbook.

Create a Calculation and Sort (Primary Example)

Step 1 

Select Analysis > Create Calculated Field.

Step 2 

In the Create Calculated Field dialog box, do the following:

  1. In the Name text box, type a name for the calculation. This example uses the default, Calculation1.
  2. In the Formula text box, type the following: INDEX()
  3. When finished, click OK.

Step 3 

In the Measures pane, right-click the Calculation1 field, and select Convert to Discrete.

Step 4 

Place the Calculation1 field onto the Rows shelf, in between the Region and Category & Region fields.

Step 5 

On the Rows shelf, right-click the Calculation1 field, and select Compute Using > Pane (Down).

Step 6 

Press the CTRL key, click the Calculation1 field, and place it on Detail.

Step 7 

Right-click the Calculation1 field and select Continuous, and move the field to the Filter shelf.

Step 8 

In the Filter dialog box, type 1 in the first text box and 5 in the second text box., and then click OK.

 

Now you can see the top 5 categories within each region.

Note: You can hide the values in the Calculation1 field by right-clicking the field in the Rows shelf, and select Show Header to remove the selection.

Use Advanced Options in the Calculation (Alternative Example)

Use this alternative example to show the Top N in a category using the advanced options in the calculation. Before beginning this example, ensure that you follow the procedure in the Nested Sorting article.

Step 1 

Remove the Region & Category field from the Rows shelf.

Step 2 

Select Analysis >Create Calculated Field.

Step 3 

In the Create Calculated Field dialog box, do the following:

  1. In the Name text box, type a name for the calculation. This example uses the default, Calculation 1.
  2. In the Formula text box, type the following: INDEX()
  3. When finished, click OK.

Step 4 

In the Measures pane, right-click the Calculation1 field, and select Convert to Discrete.

Step 5 

Place the Calculation1 field onto the Rows shelf, after the Region field.

Step 6 

From the Data window, place the Category field after the Calculation1 field.

Step 7 

Right-click the Calculation1 field, and then select Edit Table Calculation.

Step 8 

From the Compute using drop-down list, select Advanced.

Step 9 

Click Region, and then click the > button to move the Region field to the Addressing box. Repeat this step for Category.

Step 10 

Select Field, and then do the following:

  • From the first drop-down list, select Sales.
  • From the second drop-down list, select Sum.
  • Select Descending.

Click OK when finished.

Step 11 

Do the following:

  • From the At the level drop-down list, select Category.
  • From the Restarting every drop-down list, select Region.

Click OK when finished.

Step 12 

Press the CTRL key, click the Calculation1 field, and place it on the Filters shelf. The Filter dialog box displays. Click OK.

Step 13 

In the Filters shelf, right-click the Calculation1 field, and select Continuous.

Step 14 

In the Filter dialog box, type the range of values for your Top N in the respective text boxes. Click OK when finished.

Now you can see the top 5 categories within each region along with the original Category field.

 

Alternate Search Terms:top n, category, set, combined fields
Did this article resolve the issue?