KNOWLEDGE BASE

Creating a Top N set That Retains Global % of Total Values


Published: 29 Apr 2016
Last Modified Date: 20 Oct 2016

Question

How can to create a view that shows the Top N of a dimension and classes the rest into the 'Other' group, while dynamically adjusting to filtering out dimension members and at the same time retaining global totals.

Environment

Tableau Desktop

Answer

In this specific example, we created a pie chart that shows the Top N for 'Sub-Category' and groups the rest as 'Other', the view adapts dynamically to de-selecting certain Sub-Categories in a filter, but retains the global % of total value.

Step 1: Create a Parameter

  1. In the Data window, click the drop-down arrow at the top right of Dimensions, and then select Create > Parameter
  2. In the Create Parameter dialog box, name the parameter. In the example workbook, the parameter is named Top N.
  3. Under Data Type, select Integer.
  4. Under Current Value, add "5".
  5. Click OK.

Step 2: Create a new calculated field

  1. Select Analysis > Create Calculated Field 
  2. In the Create Calculated Field dialog box, do the following and click OK:
  3. Name the field. In the example, this field is called "Top N set" and enter the following formula: 
    IF RANK_UNIQUE(SUM([Sales]))<=[Top N] THEN ATTR([Sub-Category]) ELSE 'Other' END
    

Step 3: Create a new calculated field

  1. Select Analysis > Create Calculated Field 
  2. In the Create Calculated Field dialog box, do the following and click OK:
  3. Name the field. In the example, this field is called "Sales (cheat)'" and enter the following formula: 
    IF RANK_UNIQUE(SUM([Sales]))<=[Top N] THEN
    SUM([Sales])
    ELSE
    WINDOW_SUM(SUM([Sales]),FIRST()+[Top N],LAST())
    END
    

Step 4: Create a new calculated field

  1. Select Analysis > Create Calculated Field 
  2. In the Create Calculated Field dialog box, do the following and click OK:
  3. Name the field. In the example, this field is called "% of total (cheat)'" and enter the following formula: 
    IF RANK_UNIQUE(SUM([Sales]))<= [Top N] THEN
    SUM([Sales])/SUM({ SUM([Sales]) })
    ELSE
    WINDOW_SUM(SUM([Sales]),FIRST()+[Top N],LAST())/SUM({ SUM([Sales]) })
    END
    

Step 5: Create a new calculated field

  1. Select Analysis > Create Calculated Field 
  2. In the Create Calculated Field dialog box, do the following and click OK:
  3. Name the field. In the example, this field is called "Hide Duplicates" and enter the following formula: 
    RANK_UNIQUE(SUM([Sales]))<=[Top N] OR LAST()=0
    

Step 6: Build the view

Create a pie chart using the newly created fields:
  1. Drag 'Top N set' on Color and Label.
  2. Drag Sub-Category on Detail.
  3. Drag 'Sales (cheat)' on Angle and Label.
  4. Drag '% of total (cheat)' on Label.
  5. Set all table calculation fields along 'Sub-Category'.
  6. Use the filter "Hide Duplicates" along 'Sub-Category' and filter on the 'True' value.
Did this article resolve the issue?