KNOWLEDGE BASE

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


Published: 29 Apr 2016
Last Modified Date: 17 Dec 2019

Question

How 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.
CLICK TO EXPAND STEPS
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.
CLICK TO EXPAND STEPS
Step 2: Create 4 new calculated fields
  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
    
  4. Select Analysis > Create Calculated Field 
  5. In the Create Calculated Field dialog box, do the following and click OK:
  6. 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
    
  7. Select Analysis > Create Calculated Field 
  8. In the Create Calculated Field dialog box, do the following and click OK:
  9. 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
    
  10. Select Analysis > Create Calculated Field 
  11. In the Create Calculated Field dialog box, do the following and click OK:
  12. 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
    
CLICK TO EXPAND SOLUTION
Step 3: Build the view
  1. Create a pie chart by selecting the Pie icon in the Marks card
  2. Drag 'Top N set' on Color and Label.
  3. Drag Sub-Category on Detail.
  4. Drag 'Sales (cheat)' on Angle and Label.
    1. To configure this field in Percentage, right-click it in the Marks card and select Format...
    2. In the Numbers section, click the numbers and select Percentage. 
    3. (Optional) Adjust the number of decimal places as desired.
  5. Drag '% of total (cheat)' on Label.
  6. Set all table calculation fields along 'Sub-Category'.
  7. Use the filter "Hide Duplicates" along 'Sub-Category' and filter on the 'True' value.
  8. Drag Sub-Category to the Filter shelf and select All
  9. Then right-click this Sub-category field and select Sort...
    1. In Sort by... select "Field"
    2. Choose to sort by Descending order
    3. In the Field Name, select Sales. 
    4. Close the Sort pop-up window

Additional Information

To view the steps showed in the below video, please expand all the above sections.
Note: the video has no sound. To view the video in higher quality, click the YouTube icon below to watch it on YouTube directly.


Discuss this article... Feedback Forum
Did this article resolve the issue?