KNOWLEDGE BASE

## Finding the Dimension Member with the Highest Measure Value

Published: 10 Oct 2017

### Question

How to show a the dimension member with the highest measure value.

Tableau Desktop

### Option 1: Use Nested Level of Detail (LOD) Expressions

The attached example workbook uses the sample data set Superstore to demonstrate the following instructions:
1. Drag Regions to Rows
2. Drag Measure Names to Columns and Measure Values to Text
3. Select Analysis > Create calculated field
4. Name the calculated field "Maximum regional sales (LOD)", enter the following formula and click OK
`{FIXED : MAX( { FIXED [Regions] : SUM( [Sales] ) } ) }`
5. Add the calculated field to the Rows shelf
1. Right click the calculated field on Rows and convert it as Discrete
2. Right click the calculated field on Rows and select Format... to remove or customize decimals.
6. Select Analysis > Create calculated field
7. Name the calculated field "Region with highest sales Filter (LOD)"
8. Enter the following formula and click OK:
`{ FIXED [Regions] : SUM([Sales]) } = [Maximum regional sales (LOD)]`
9. Add the calculated field to the Rows shelf
10. Select Analysis > Create calculated field
11. Name the calculated field "Region with highest sales (LOD)", enter the following formula and click OK
```IF { FIXED [Regions] : SUM( [Sales] ) } = [Maximum regional sales (LOD)]
THEN [Regions]
END
```
12. Drag [Region with highest sales (LOD)] to the Rows shelf.

### Option 2: Use table calculations

The attached example workbook uses the sample data set Superstore to demonstrate the following instructions:
1. Drag Regions to Rows
2. Drag Measure Names to Columns and Measure Values to Text
3. Select Analysis > Create calculated field
4. Name the calculated field "Maximum Sales (table calc)", enter the following formula and click OK
`WINDOW_MAX( SUM( [Sales] ) )`
5. Add the calculated field to the Rows shelf
1. Right click the calculated field on Rows and convert it as Discrete
2. Right click the calculated field on Rows and select Format... to remove or customize decimals.
6. Select Analysis > Create calculated field
7. Name the calculated field "Region with highest sales (table calc)", enter the following formula and click OK
```IF SUM( [Sales] ) = [Maximum Sales (table calc)]
THEN MIN( [Regions] )
END
```
8. Drag [Region with highest sales (table calc)] to the Rows shelf.

Notes on Option 2
• Table calculations require that all dimensions needed to compute the calculations always be included in the view. In this case, since we want to find the region with the highest sales, [Regions] must be in the view.

To view the above steps in action, see the video below.
Note: the video has no sound.