Forum Discussion

Jkh30's avatar
Jkh30
Icon for Helper I rankHelper I
2 years ago
Solved

RANKX with Multiple Criterias

Hi everyone,   I have a case that I need to rank clubs based on their points each week and if the point between each clubs are the same, it needs to compare based on Greater Goal deficit. Also if t...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    This can be solved with the RANK() function.  To receive further help, share the donwload link of the PBI file.  Is Greater Goal deficit the same as GD?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    Write this measure.  See the last column in the image.

    Measure = RANK(DENSE,FILTER(ALL(Club),Club[ClubID]<>BLANK()),orderby([Points],DESC,[Goal Deficit],desc,Club[Club Name],ASC))

    Hope this helps.

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi ALL,
Firstly  Ashish_Mathur and TomMartens thanks you for yours solutions!
And Jkh30 ,for your question, we can use rankx in the calculated column to accomplish your needs, we assign weights to Pts , GD and club name to make sure that they are guaranteed to be sorted in the order of Pts GD club name, I hope my method helps you!

Column = 
IF(
    ('club_data'[Club])="Crystal Palace",
    20,
    RANKX(
        ALL('club_data'),
        ('club_data'[Pts])*10000+('club_data'[GD])*1000+(1000-UNICODE(LEFT('club_data'[Club],1))),,DESC,Dense))

I hope my solution is helpful to you, if you have more recent questions, you can always contact me and I will reply as soon as I see your message!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

Jkh30's avatar
Jkh30
Icon for Helper I rankHelper I
2 years ago

Hello! Really appreciate your help on this matter. However, I kinda need this in a measure form not in calc table or column. Is it possible? Thank you!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Ashish_Mathur ,

    I'm sorry, due to environmental constraints, I can't type your pbix file, so I'm going to follow the example data you've shown, and here's the idea of calculating the columns along the lines of the DAX in the measure, which I hope will be helpful to you!

    Rank Measure = 
IF (
    SELECTEDVALUE('club_data'[Club]) = "Crystal Palace",
    20,
    RANKX (
        ALL('club_data'),
        CALCULATE(
            SUM('club_data'[Pts]) * 10000 +
            SUM('club_data'[GD]) * 1000 +
            1000 - UNICODE(LEFT(SELECTEDVALUE('club_data'[Club]), 1))
        ),
        ,
        DESC,
        Dense
    )
)

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Tom Shen

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.