KNOWLEDGE BASE

Showing Top N subcategories within each Top N categories


Published: 28 Jun 2016
Last Modified Date: 23 Dec 2016

Issue

How to show top N subcategories within each of the top N categories.

Environment

Tableau Desktop

Resolution

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.
Did this article resolve the issue?