Grouping Dimensions By Rank At A Higher Level Of Granularity Than Used In The View

Published: 16 Jun 2020
Last Modified Date: 16 Jun 2020

Question

Grouping dimensions by rank at a higher level of granularity while showing most granular values. Example: Ranking Top 5 countries within a product while showing Sales at a monthly level

Environment

Tableau Desktop

Answer

Use a Fixed calculation to calculate Rank without taking view into consideration.

1. Calculate Highest sales with a formula like below:

{ FIXED [Product Id]: MAX({ FIXED [Country],[Product Id]:SUM([Sale])})}

2. Calculate second highest sales with a formula like below:

{ FIXED [Product Id]: MAX({ FIXED [Country],[Product Id]:if SUM([Sale]) < SUM([Highest sales])
then SUM([Sale]) end})}

3. Repeat step 2 for as many ranks you have to calculate

4. Create a calculated field called rank with below formula:

If ({ FIXED [Country],[Product Id]:SUM([Sale])}) = [Highest sales]
then 1
ELSEIF
{ FIXED [Country],[Product Id]:SUM([Sale])} = [Second highest sale]
then 2
end

5. Use the Rank field on row shelf to group dimension by rank and filter shelf to limit the results to 5

Additional Information

Calculating rank without table functions
Note: Using too many level of detail calculations can cause performance issues as LOD's are computationally expensive