Dynamic Ranking with slicers without summation of values

[Prahlad]

I have a Table in excel by the name "Compiled" which I have loaded in Power BI.

It has the following columns:

Month	Segment	SUPPLIER CODE	SUPPLIER NAME	DOMAIN	Overall Score (Rounded Off)
Nov`23	LCV,MHCV	7200608	ACCURATE PRODUCTS CORPN PVT LTD	NON-METALLIC	425
Nov`23	E-H1,LCV,MHCV	7200482	ADHI PARASHAKTHI INDUSTRIES	BAR MACHINING	365
Nov`23	MHCV	7201111	ADINATH FORGING P LIMITED	BAR MACHINING	325
Nov`23	LCV	7206057	Aditya Auto Products & Engg (i	CHASSIS PROP	375
Nov`23	E-H1,MHCV	7204097	ADM JOINFLEX INDIA	SHEET METAL	300
Nov`23	E-H1,LCV,MHCV	7202537	ADVANTEK FUEL SYSTEMS PVT. LTD.,	CHASSIS PROP	340
Nov`23	MHCV	7202969	AKAR AUTO INDUSTRIES LIMITED	FORGING	300

In power BI I have made the following dashboard: 

I want to have the column "Rank" have its values change dynamically, when I click on Values of "DOMAIN" and Values of "Segment".

For this I tried using the following measure:

Rank Measure = Rankx(ALLSELECTED(Compiled),CALCULATE(SUM('Compiled'[Overall Score (Rounded Off)])),,desc,dense).

Basically I want the value in "Rank" to change and dynamically and provide rank only based on the new values in "Overall Score (Rounded Off)" column, which were filtered by "MHCV" in "DOMAIN column, for eg.

Similarly it must work for "Segment".

However, after trying the above measure, the ranks that was displayed were not accurate as per actual data. This is think is due to the SUM funtion used in the 2nd Arguement of Rankx.

Would be grateful if anyone could kindly resolve this and provide me the correct code for the achieving the above functionality.

@amitchandak @lbendlin @ryan_mayu @Greg_Deckler @Ritaf1983 

[_AAndrade]

Hi @Prahlad,

I used the data that you provided and used these DAX formulas:

Total Score = SUM(f_Data[Overall Score (Rounded Off)])

Rank Score = 
RANKX(
    ALLSELECTED(f_Data[DOMAIN],f_Data[SUPPLIER NAME], f_Data[Segment]),
    [Total Score],
    ,DESC
)

Final Results:

[Prahlad]

Thank you @_AAndrade for the reply. 

Here is what I've got as output. 

The thing is,  the values of "Overall Score (Rounded Off)" does not exceed 500 in the excel file. And the total score shown here is more than 500. I feel the ranking is based on the sum of values in "Overall Score (Rounded Off)" and not individual values. Could you please provide a code that Ranks based on the individual values in "Overall Score (Rounded Off)"? 

Also, the excel file data has duplicate Values in "Supplier Name" since this is like performance data for months Nov-Apr.

Please find below, the complete table.  I have removed the "Supplier Name" Column since that data is slightly confidential, if that's alright.

Month	Segment	SUPPLIER CODE	DOMAIN	Overall Score (Rounded Off)
Nov`23	MHCV	7200980	NON-METALLIC	450
Nov`23	MHCV	7204096	NON-METALLIC	340
Nov`23	MHCV	7203771	CHASSIS PROP	425
Nov`23	LCV	7205672	CHASSIS PROP	383
Nov`23	MHCV	7200370	CHASSIS PROP	300
Nov`23	MHCV	7205371	CHASSIS PROP	340
Dec`23	MHCV	7200002	BAR MACHINING	375
Dec`23	MHCV	7200004	NON-METALLIC	407
Dec`23	MHCV	7200013	ENGINE PROP	450
Dec`23	E-H1,MHCV	7200015	FORGING	375
Dec`23	E-H1,MHCV	7200016	CASTING	275
Dec`23	MHCV	7200017	ELECTRICAL	425
Jan`24	MHCV	7200002	BAR MACHINING	343
Jan`24	MHCV	7200004	NON-METALLIC	408
Jan`24	MHCV	7200013	ENGINE PROP	500
Jan`24	E-H1,MHCV	7200015	FORGING	350
Jan`24	E-H1,MHCV	7200016	CASTING	347
Jan`24	MHCV	7200017	ELECTRICAL	412
Jan`24	MHCV	7200019	ELECTRICAL	275
Feb`24	MHCV	7200002	BAR MACHINING	369
Feb`24	MHCV	7200004	NON-METALLIC	409
Feb`24	MHCV	7200013	ENGINE PROP	500
Feb`24	E-H1,MHCV	7200015	FORGING	375
Feb`24	E-H1,MHCV	7200016	CASTING	337
Feb`24	MHCV	7200017	ELECTRICAL	455
Feb`24	MHCV	7200019	ELECTRICAL	300
Feb`24	E-H1,MHCV	7200021	CHASSIS PROP	475
Mar`24	MHCV	7200946	RM	375
Mar`24	MHCV	7200004	NON-METALLIC	409
Mar`24	MHCV	7200013	ENGINE PROP	500
Mar`24	E-H1,MHCV	7200015	FORGING	375
Mar`24	E-H1,MHCV	7200016	CASTING	337
Mar`24	MHCV	7200017	ELECTRICAL	455
Mar`24	MHCV	7200019	ELECTRICAL	300
Mar`24	E-H1,MHCV	7200021	CHASSIS PROP	475
Apr`24	MHCV	7200002	BAR MACHINING	322
Apr`24	MHCV	7200004	NON-METALLIC	410
Apr`24	MHCV	7200010	CASTING	475
Apr`24	MHCV	7200013	ENGINE PROP	500
Apr`24	E-H1,MHCV	7200015	FORGING	400
Apr`24	E-H1,MHCV	7200016	CASTING	336
Apr`24	MHCV	7200017	ELECTRICAL	455
Apr`24	MHCV	7200019	ELECTRICAL	300
Apr`24	E-H1,MHCV	7200021	CHASSIS PROP	475
Apr`24	E-H1,LCV,MHCV	7200023	NON-METALLIC	360

[_AAndrade · Accepted solution]

I used another two DAX measures:

MAX(f_Data[Overall Score (Rounded Off)])

Rank Score base on Max = 
RANKX(
    ALLSELECTED(f_Data[DOMAIN],f_Data[SUPPLIER NAME], f_Data[Segment], f_Data[SUPPLIER CODE]),
    [MAX Score],
    ,DESC
)

The final output was this:

[Prahlad]

It worked! Thanks alot

[_AAndrade]

Hi @Prahlad,

I'm attaching a pbix file with my solution.

Please take a look and see if this could solve your issue.

[Prahlad]

Hi @_AAndrade . Thanks for replying. I however have Power Bi desktop version of 2021 and I'm unable to open files of newer versions. Could you please send me what changes have you done exactly or the code?