KNOWLEDGE BASE

Finding the Dimension Member with the Highest Measure Value


Published: 10 Oct 2017
Last Modified Date: 26 Aug 2019

Question

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

Environment

Tableau Desktop

Answer

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.

Additional Information

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.
 
Discuss this article... Feedback Forum
Did this article resolve the issue?