Loading

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

Publiseringsdato: Apr 13, 2024
Oppgave

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

Trinn
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

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

001456719

 
Laster
Salesforce Help | Article