Loading

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

게시 일자: Apr 13, 2024
과업

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

단계
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

 
추가 자원
Calculating rank without table functions 
Note: Using too many level of detail calculations can cause performance issues as LOD's are computationally expensive
Knowledge 기사 번호

001456719

 
로드 중
Salesforce Help | Article