KNOWLEDGE BASE

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

 
Did this article resolve the issue?