Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Prahlad
Frequent Visitor

Dynamic Ranking with slicers without summation of values

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

It has the following columns:

MonthSegmentSUPPLIER CODESUPPLIER NAMEDOMAINOverall Score (Rounded Off)
Nov`23LCV,MHCV7200608ACCURATE PRODUCTS CORPN PVT LTDNON-METALLIC425
Nov`23E-H1,LCV,MHCV7200482ADHI PARASHAKTHI INDUSTRIESBAR MACHINING365
Nov`23MHCV7201111ADINATH FORGING P LIMITEDBAR MACHINING325
Nov`23LCV7206057Aditya Auto Products & Engg (iCHASSIS PROP375
Nov`23E-H1,MHCV7204097ADM JOINFLEX INDIASHEET METAL300
Nov`23E-H1,LCV,MHCV7202537ADVANTEK FUEL SYSTEMS PVT. LTD.,CHASSIS PROP340
Nov`23MHCV7202969AKAR AUTO INDUSTRIES LIMITEDFORGING300

 

 

In power BI I have made the following dashboard: 

 

Prahlad_1-1716533236131.png

 

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.
 
 
1 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:

_AAndrade_0-1716543451410.png_AAndrade_1-1716543475559.png

_AAndrade_2-1716543525546.png_AAndrade_3-1716543546879.png

 





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




View solution in original post

6 REPLIES 6
_AAndrade
Super User
Super User

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:

_AAndrade_0-1716540180572.png

 

_AAndrade_1-1716540191999.png

 







Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




Thank you @_AAndrade for the reply. 

Here is what I've got as output. 

Prahlad_0-1716540975533.png

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.

 

MonthSegmentSUPPLIER CODEDOMAINOverall Score (Rounded Off)
Nov`23MHCV7200980NON-METALLIC450
Nov`23MHCV7204096NON-METALLIC340
Nov`23MHCV7203771CHASSIS PROP425
Nov`23LCV7205672CHASSIS PROP383
Nov`23MHCV7200370CHASSIS PROP300
Nov`23MHCV7205371CHASSIS PROP340
Dec`23MHCV7200002BAR MACHINING375
Dec`23MHCV7200004NON-METALLIC407
Dec`23MHCV7200013ENGINE PROP450
Dec`23E-H1,MHCV7200015FORGING375
Dec`23E-H1,MHCV7200016CASTING275
Dec`23MHCV7200017ELECTRICAL425
Jan`24MHCV7200002BAR MACHINING343
Jan`24MHCV7200004NON-METALLIC408
Jan`24MHCV7200013ENGINE PROP500
Jan`24E-H1,MHCV7200015FORGING350
Jan`24E-H1,MHCV7200016CASTING347
Jan`24MHCV7200017ELECTRICAL412
Jan`24MHCV7200019ELECTRICAL275
Feb`24MHCV7200002BAR MACHINING369
Feb`24MHCV7200004NON-METALLIC409
Feb`24MHCV7200013ENGINE PROP500
Feb`24E-H1,MHCV7200015FORGING375
Feb`24E-H1,MHCV7200016CASTING337
Feb`24MHCV7200017ELECTRICAL455
Feb`24MHCV7200019ELECTRICAL300
Feb`24E-H1,MHCV7200021CHASSIS PROP475
Mar`24MHCV7200946RM375
Mar`24MHCV7200004NON-METALLIC409
Mar`24MHCV7200013ENGINE PROP500
Mar`24E-H1,MHCV7200015FORGING375
Mar`24E-H1,MHCV7200016CASTING337
Mar`24MHCV7200017ELECTRICAL455
Mar`24MHCV7200019ELECTRICAL300
Mar`24E-H1,MHCV7200021CHASSIS PROP475
Apr`24MHCV7200002BAR MACHINING322
Apr`24MHCV7200004NON-METALLIC410
Apr`24MHCV7200010CASTING475
Apr`24MHCV7200013ENGINE PROP500
Apr`24E-H1,MHCV7200015FORGING400
Apr`24E-H1,MHCV7200016CASTING336
Apr`24MHCV7200017ELECTRICAL455
Apr`24MHCV7200019ELECTRICAL300
Apr`24E-H1,MHCV7200021CHASSIS PROP475
Apr`24E-H1,LCV,MHCV7200023NON-METALLIC360

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:

_AAndrade_0-1716543451410.png_AAndrade_1-1716543475559.png

_AAndrade_2-1716543525546.png_AAndrade_3-1716543546879.png

 





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




It worked! Thanks alot

_AAndrade
Super User
Super User

Hi @Prahlad,

I'm attaching a pbix file with my solution.

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





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




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?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.