Hi ,
I am trying to do a rank for below scenario and it partially works but unable to get the correct result .
Have two levels in rows from same dimension and product with different levels in columns and rank column nested below it .
Expected ouput , rank agency within Regions .
Product 60-4- 00 Product 60-4 Product 60
RANK Rank Rank
Region1 Agency1 2 1 3
Agency2 1 2 1
Agency3 3 3 2
Region2 Agency4
Agency5
Region3 Agency8
Agency 9
I am using the below expression which works if all the products are from same level ,if the product has different levels as in column shown above it doesnot work .
rank([H0 Sales] for [Regions],currentMember[productHierarchy]).
I also tried
rank([H0Sales] within set (Regions]) - no result for rank all blank .
rank([H0Sales] within set([Agency]) - this works but gives the ranking for all agencies for all the regions .
Any help is appreciated to get the output as shown in the above table .