KNOWLEDGE BASE

## Sorting Dimensions by Calculated Fields That Use Table Calculations

Published: 03 Jun 2014

### Question

How to sort a dimension by a calculated field that uses a table calculation.

### Environment

Tableau Desktop

The ability to sort a dimension by a calculated field that uses a table calculation is currently not built into the product. However both the following workarounds can be reviewed in the workbook "Sorting by Rank CAGR" attached to this article (located on the right side of this page). The workbook details how to use table calculations and LOD to rank members of a dimension for a specific year.
CLICK TO EXPAND STEPS
##### Option 1 - Use expressions and Compute Using options
Use expressions and Compute Using options to sort by calculated field. For more information about Table calculations, see the on demand video Modifying Table Calculations.

### Step 1 - Create a Lookup calculated field

1. Select Analysis > Create Calculated Field...
2. In the Calculated Field dialog box that opens, do the following and then click OK:
1. Name the calculated field. In the example workbook, the calculated field is named !CAGR lookup.
2. In the formula field, create a calculated field similar to the following:
```lookup(POWER(ZN(SUM([Sales]))/
LOOKUP(ZN(SUM([Sales])), FIRST()),ZN(1/(INDEX()-1))) - 1,last()) ```

### Step 2 - Create a Rank calculated field

1. Select Analysis > Create Calculated Field...
2. In the Calculated Field dialog box that opens, do the following and then click OK:
1. Name the calculated field. In the example workbook, the calculated field is named !CAGR RANK.
2. In the formula field, create a calculated field similar to the following:
```RANK([!CAGR lookup])
```

### Step 3 - Create a Parameter

1. Click the arrow next to the Dimensions panel and select Create a parameter.
2. Name the parameter "TOP N".
3. In the Data Type, choose Float.
4. In Current Values, type 5.
5. Click OK.

### Step 4 - Create a Filtering calculated field

1. Select Analysis > Create Calculated Field...
2. In the Calculated Field dialog box that opens, do the following and then click OK:
1. Name the calculated field. In the example workbook, the calculated field is named !Top N filter.
2. In the formula field, create a calculated field similar to the following:
```IF [!CAGR RANK]<=[TOP N] THEN 'Include'
ELSE 'Exclude' END ```

### Step 5 - Build the View

1. Drag "Order Date" to Columns.
2. Drag "Sub-Category" to Rows.
3. Drag "Sales" to the Text Cards.
4. Right click "Sales" and select Compute Using > Compound Growth Rate.
5. Right click "Sales" and select Format > Percentage > 0 decimal.
6. Right click "!CAGR RANK" and select Convert to Discrete.
7. Place "!CAGR RANK" on Rows, to the left of "Sub-Category".
8. Right click "!CAGR RANK" in Rows and select Edit Table Calculation... and select Table(Down).
9. Drag "!Top N" filter to Rows.
10. Right click"!Top N", select Filter and choose Include to only select the TOP 5.
11. Right click "!TOP N" in the Parameter pane and select Show Parameter Control.
CLICK TO EXPAND STEPS
##### Option 2 - Use level of detail expressions in place of table functions
As an alternative workaround, modify the calculated field to use level of detail expressions in place of table functions. For more information, see Level of detail expressions

### Step 1 - Create the LOD calculated field

1. Select Analysis > Create Calculated Field...
2. In the Calculated Field dialog box that opens, do the following and then click OK:
1. Name the calculated field. In the example workbook, the calculated field is named !LOD.
2. In the formula field, create a calculated field similar to the following:
```power(
SUM([Sales])
/
min({ EXCLUDE [Order Date]:
(zn(sum(IF YEAR([Order Date])=
year({ EXCLUDE [Order Date]:MIN([Order Date])})
then [Sales] else 0 END)))})

,ZN(1/(YEAR(min([Order Date]))
-YEAR(min({ EXCLUDE [Order Date]:MIN([Order Date])}))))
)-1
```

### Step 2 - Build the View

1. Duplicate the Sheet from Option 1.
2. Replace "Sales" from the Text cards by the newly created field "!LOD".
3. Right click "!LOD" and select Format > Percentage > 0 decimal.

To view a Bottom N example, see Top and Bottom N within category at Tableau Community Forums.

Note that the ability to sort a dimension by a calculated field that uses a table calculation is currently not built into the product.

To view the above steps in action see the video below: