KNOWLEDGE BASE

Calculating Rank At a Level Other Than the Highest Level of Granularity


Published: 23 Mar 2016
Last Modified Date: 24 Jan 2018

Issue

How to calculate rank for a dimension on the Rows shelf that is not the last dimension.

Environment

Tableau Desktop

Resolution

Both options can be reviewed in the attached workbook.

Option 1: Use RANK()

  1. Create a calculated field with a name like "Sales per Sub-Category" with a calculation similar to the following:
    { EXCLUDE [State] : SUM( [Sales] ) }
  2. Create a calculated field with a name like "RANK_DENSE + LOD" with a calculation similar to the following:
    RANK_DENSE( SUM( [Sales per Sub-Category] ) )
  3. Drag [RANK_DENSE + LOD] to the Measure Values card.
  4. Right-click [RANK_DENSE + LOD] and select Edit Table Calculation…
  5. In the Table Calculation dialog, do the following and close the dialog:
    1. Select Specific Dimensions
    2. Check all dimensions in the list
    3. Ensure Region is the top dimension in the list
    4. For Restarting every, select Region from the dropdown

Option 2: Use INDEX()

  1. Create a calculated field with a name like "Sales per Sub-Category" with a calculation similar to the following:
    { EXCLUDE [State] : SUM( [Sales] ) }
  2. Create a calculated field with a name like "INDEX" with a calculation similar to the following:
    INDEX()
  3. Drag [INDEX] to the Measure Values card.
  4. Right-click [INDEX] and select Edit Table Calculation…
  5. In the Table Calculation dialog, do the following and close the dialog:
    1. Select Specific Dimensions
    2. Check all dimensions in the list
    3. Ensure Region is the top dimension in the list
    4. Ensure that Sub-Category is the second dimension in the list
    5. For Restarting every, select Region from the dropdown
    6. Click Automatic Sort to open the Sort dropdown menu
    7. Select Custom, Sales per Sub-Category, Sum, Descending
Note: The top dimension should always be the partition we are calculating rank within, and the second dimension should always be the dimension we want to calculate rank for. For example, I want to rank each sub-category within each Region.
 

Cause

Table calculations, like RANK() and INDEX() cannot ignore a field in the view and therefore will always be affected by the lowest level of granularity in the view. Using an LOD expression to aggregate the values that are being used to create the rank up to the desired level, Tableau Desktop calculates the same rank for every within the desired level.

Note: In Option 1, if two sub-categories have the same value of SUM(Sales) then they will be assigned the same rank. If this is not the desired behavior, please use option 2.
Did this article resolve the issue?