KNOWLEDGE BASE

Showing Top N Subcategories Within Each Top N Categories


Published: 28 Jun 2016
Last Modified Date: 09 Nov 2018

Issue

When multiple Top N filters are used in a view, the results are not as expected.

Environment

Tableau Desktop

Resolution

To control the order in which the Top N filters are executed, you can make use of:
  • Context filters
  • Level of detail expressions
  • Table calculations
The below example is based on Superstore sample data. 

Step 1: Create the View
  1. Place Region, Category and Sub-Category on Rows
  2. Place SUM(Sales) on Text
Step 2: Create Calculated Fields
  1. Select Analysis > Create Calculated Field. Name the field Rank of Sales, enter the following calculation, then click OK:
    RANK( SUM( [Sales] ) )
  2. Select Analysis > Create Calculated Field. Name the field Rank of Sales excluding Sub-Category, enter the following calculation, then click OK: 
     RANK_DENSE( SUM( { EXCLUDE [Sub-Category] : SUM( [Sales] ) } ) )

Step 3: Set up Filters

  1. Drag Rank of Sales excluding Sub-Category to Filters and click OK.
  2. Right-click Rank of Sales excluding Sub-Category on Filters and select Edit Table Calculation…
    • In the Table Calculation dialog, select Compute Using > Specific Dimensions.
    • Arrange Region, Category and Sub-Category in descending order, then select the check boxes next to all three fields.
    • Under Restarting every, select Region
  3. Right-click Rank of Sales Excluding Sub Category on the Filters shelf and select Edit Filter...
  4. In the Filter dialog, enter the desired limits. For example, enter a range of 1 to 2 to display the top two Categories in each Region.
  5. Drag Rank of Sales to Filters, and click OK.
  6. Right-click Rank of Sales on Filters and select Edit Table Calculation…
    • In the Table Calculation dialog, select Compute Using > Specific Dimensions.
    • Arrange Region, Category and Sub-Category in descending order, then select the check boxes next to all three fields.
    • Under Restarting every, select Category.
  7. Right-click Rank of Sales on Filters, and select Edit Filter
  8. Enter the desired limits.

Cause

The multiple Top N filters are executed together with the AND logical operator.
Did this article resolve the issue?