KNOWLEDGE BASE

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

Published: 29 Apr 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

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.