KNOWLEDGE BASE

## Creating Dynamic Calculation to Find Top and Bottom N%

Published: 10 Mar 2014

### Question

How to create a dynamic calculation to find the top and bottom N% of values in a view.

### Environment

Tableau Desktop

The following instructions can be reviewed in the workbook attached below.

### Step 1: Create Calculated Fields

1. In Tableau Desktop, connect to Superstore sample data.
2. Select Analysis > Create Calculated Field.
3. In the Calculated Field dialog box:
• Name the calculated field. In the example workbook, the calculated field is named "Index".
• In the formula field, create a calculated field similar to the following:
`INDEX()`
4. Click OK.
5. Select Analysis > Create Calculated Field.
6. In the Calculated Field dialog box:
• Name the calculated field. In the example workbook, the calculated field is named "Size".
• In the formula field, create a calculated field similar to the following:
`SIZE()`
7. Click OK.
8. Select Analysis > Create Calculated Field.
9. In the Calculated Field dialog box:
• Name the calculated field. In the example workbook, the calculated field is named "Index/Size".
• In the formula field, create a calculated field similar to the following:
`[Index] / [Size]`
10. Click OK.
11. Select Analysis > Create Calculated Field.
12. In the Calculated Field dialog box:
• Name the calculated field. In the example workbook, the calculated field is named "Top 30% or Bottom 30%".
• In the formula field, create a calculated field similar to the following:
```IF [Index/Size] <= .3
THEN "Top 30%"
ELSEIF [Index/Size] >= .7
THEN "Bottom 30%"
END```
13. Click OK.

### Step 2: Create the View

1. Drag State to the Columns shelf.
2. Drag Category to the Rows shelf.
3. Drag Number of Records to Text on the Marks card.
4. Right-click and drag Number of Records to the Columns shelf, select MIN(Number of Records), and then click OK.
5. Double-click the x-axis to open the Edit Axis dialog box.
6. Select Fixed, starting at 0 and ending at 1, and then click OK.

### Step 3: Format the Top 30% or Bottom 30% Table Calculation

1. On Columns, right-click Number of Records, and clear Show Header.
2. Drag Top 30% or Bottom 30% to Color on the Marks card.
3. On Color, right-click Top 30% or Bottom 30% and select Edit Table Calculation.
4. In the Table Calculation dialog box, make the following selections:
• For Calculated Field, select Index.
• For Compute Using, select Advanced.
• In the Advanced dialog box, make the following selections:
• Move State and Category to Addressing, in that order.
• For Field, select Number of RecordsSum, and Descending.
• Click OK.
• For At the level, select State.
• For Restarting every, select None.
• Click OK.
5. On Color, right-click Top 30% or Bottom 30% and select Edit Table Calculation.
6. In the Table Calculation dialog box, make the following selections:
• For Calculated Field, select Size.
• For Compute Using, select Table (Across).
• Click OK.

### Step 4: Format the Index/Size Table Calculation

1. Drag Index/Size to Label.
2. On Label, right-click Index/Size, and then select Edit Table Calculation.
3. In the Table Calculation dialog box, make the following selections:
• For Calculated Field, select Index.
• For Compute Using, select Advanced.
• In the Advanced dialog box, make the following selections:
• Move State and Category to Addressing, in that order.
• For Field, select Number of RecordsSum, and Descending.
• Click OK.
• For At the level, select State.
• For Restarting every, select None.
• Click OK.
4. On Label, right-click Index/Size, and select Edit Table Calculation.
5. In the Table Calculation dialog box, make the following selections:
• For Calculated Field, select Size.
• For Compute Using, select Table (Across).
• Click OK.

### Step 5: Complete the View

1. In the Measures pane, right-click Index/Size, and select Default Properties > Number Format.
2. Select Percentage, and then click OK.