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

Published: 23 Mar 2016Last Modified Date: 08 Sep 2016

Issue

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

Environment

Tableau Desktop

Resolution

The following instructions can be reviewed in the attached workbook.

Create a calculated field with a name like "Sales per Sub-Category" with a calculation similar to the following:

{ EXCLUDE [State] : SUM( [Sales] ) }

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] ) )

Drag [RANK_DENSE + LOD] to the Measure Values card.

Right-click [RANK_DENSE + LOD] and select Edit Table Calculation…

In the Table Calculation dialog, for Compute using, select Advanced…

In the Advanced dialog, move each field from Partitioning to Addressing so that the fields are in the same order in Addressing as on the Rows shelf. In this example, the order should be: Region, Category, Sub-Category (group), State

Click OK to close the Advanced dialog

Optional: Select the desired dimension for Restarting every.

Click OK to close the Table Calculation dialog

Cause

RANK() cannot ignore a field in the view and therefore will always be calculated by the lowest level of granularity. 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.
Did this article resolve the issue?

Thank you for providing your feedback on the effectiveness of the article.