KNOWLEDGE BASE

Calculating Rank without Table Functions

Published: 16 Apr 2018

Question

How to compute rank without table functions. For example, if the end user can drill down from [Category] to [Subcategory] the ranks should not change.

Environment

Tableau Desktop

There are two possible workarounds:

Option 1: Use a level of detail expression inside of the RANK_DENSE() function

For detailed directions, please see Calculating Rank At a Level Other Than the Highest Level of Granularity
Note: with this option, categories that tie will always be assigned the same rank. Also, for some more complex views the table calculations may still become invalid when adding or removing dimensions from the view.

Option 2: Manually calculate the measure values for every ranking level

The attached modified workbook demonstrates the following directions:
1. Create a calculated field with a name like "Highest Sales Values" with a calculation similar to the following:
`{ FIXED : MAX( { INCLUDE [Segment], [Category] : SUM([Sales] ) } ) }`
2. Create a calculated field with a name like "2nd Highest Sales Value" with a calculation similar to the following:
```{ FIXED : MAX( { INCLUDE [Segment], [Category]:
IF SUM([Sales]) < SUM([Highest Sales Values])
THEN SUM([Sales])
END
})}```
3. Repeat step 2 for as many rank levels as desired, replacing [Highest Sales Values] with the lowest value above the current rank.
4. Create a calculated field with a name like "Rank (just LOD)" with a calculation similar to the following:
```IF { FIXED [Segment], [Category] : SUM([Sales])} = [Highest Sales Values]
THEN 1
ELSEIF { FIXED [Segment], [Category] : SUM([Sales])} = [2nd Highest Sales Value]
THEN 2
ELSEIF { FIXED [Segment], [Category] : SUM([Sales])} = [3rd Highest Sales Value]
THEN 3
END```