# How to Rank for each grouping sets

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)

2 Likes

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.