Hi,
I need to create rank for each grouping sets and for selected filters.
For example for matrix report if I Place Customer then Region Then product
So Rank should evaluate on Customer then Customer, Region then Customer, Region and Product.
I tried some thing with isinscope but was not able to reach anywhere.
Added my practice of what I did to achieve same.
Will appreciate for further solutions or suggestion.
Thanks,
Harry
Practice.pbix (458.2 KB)
Hello @HarsimranjeetSingh,
Thank You for posting your query onto the Forum.
To achieve the results in the form of hierarchy, are always the trickiest one’s to achieve. So while writing the measure in order to evaluate the results, the order does matter i.e. how you’ve actually set-up your hierarchy. In the current scenario, you’ve set-up your hierarchy in the order as provided below -
Channel > Customer Names > City > Product Name
So now, the logic inside the formula goes like this -
1). First, ranking of the “Channels” will be evaluated.
2). Once that’s done and when you move down to next level of hierarchy i.e. towards “Customer Names” within each Channel, ranking of Customer Names will be evaluated. So for each Customer inside the specific Channel, ranking will be evaluated accordingly.
3). Now, when you move down to the “City” part or hierarchy. Inside each “Channel” and “Customer Names”, ranking of the City will be detemined specifically according to previous two criteria’s or hierarchy.
4). Lastly, by going through the above logic’s, finally ranking of the “Product Name” will be considered or evaluated accordingly.
So below is the measure alongwith the screenshot of the final results provided for the reference -
Ranking - Harsh =
VAR _Channel_Ranking =
CALCULATE(
RANKX( ALL( Sales_Data[Channel] ) , [TotalSales] , , DESC ) ,
ALL( Customer_Data[Customer Names] ) ,
ALL( Regions_Table[City] ) ,
ALL( Products_Data[Product Name] ) )
VAR _Customer_Ranking =
CALCULATE(
RANKX( ALL( Customer_Data[Customer Names] ) , [TotalSales] , , DESC ) ,
ALL( Regions_Table[City] ) ,
ALL( Products_Data[Product Name] ) ,
ALLSELECTED( Sales_Data[Channel] ) )
VAR _City_Ranking =
CALCULATE(
RANKX( ALL( Regions_Table[City] ) , [TotalSales] , , DESC ) ,
ALL( Products_Data[Product Name] ) ,
ALL( Customer_Data[Customer Names] ) ,
ALLSELECTED( Sales_Data[Channel] ) )
VAR _Product_Ranking =
CALCULATE(
RANKX( ALL( Products_Data[Product Name] ) , [TotalSales] , , DESC ) ,
ALL( Customer_Data[Customer Names] ) ,
ALLSELECTED( Regions_Table[City] ) ,
ALLSELECTED( Sales_Data[Channel] ) )
VAR _Results =
SWITCH( TRUE() ,
ISINSCOPE( Products_Data[Product Name] ) , _Product_Ranking ,
ISINSCOPE( Regions_Table[City] ) , _City_Ranking ,
ISINSCOPE( Customer_Data[Customer Names] ) , _Customer_Ranking ,
ISINSCOPE( Sales_Data[Channel] ) , _Channel_Ranking ,
BLANK() )
RETURN
_Results
I’m also attaching the working of the PBIX file for the reference purposes as well as providing few of the links below pertaining to the Ranking topics.
Hoping you find this useful and meets your requirements that you’ve been looking for. 
Thanks and Warm Regards,
Harsh
Ranking - Harsh.pbix (459.0 KB)
That was a great answer @Harsh, thank you for putting in lots of details and effort on @HarsimranjeetSingh’s inquiry.
We hope this helped you @HarsimranjeetSingh 
If you don’t have any further inquiry, kindly mark as solution the answer as solution.
Hi @HarsimranjeetSingh, we’ve noticed that no response has been received from you since October 27.
We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @HarsimranjeetSingh, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
