Forum Discussion

Prahlad's avatar
Prahlad
Frequent Visitor
2 years ago
Solved

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: 

 

 

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.
 
 
Need Help
  • _AAndrade's avatar
    _AAndrade
    2 years ago

    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:

     

6 Replies

    • Prahlad's avatar
      Prahlad
      Frequent Visitor
      2 years ago

      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?

  • _AAndrade's avatar
    _AAndrade
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    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's avatar
      Prahlad
      Frequent Visitor
      2 years ago

      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.

       

      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
      • _AAndrade's avatar
        _AAndrade
        Icon for Resident Rockstar rankResident Rockstar
        2 years ago

        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: