KNOWLEDGE BASE

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

Published: 29 Apr 2016

### 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

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

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.