RANKX with Multiple Criterias

[Jkh30]

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 they have the same point and goal deficit, the rank needs to be sorted by Club name alphabeticaly. May anyone help me with this?

For example in the picture:

Crystal Palace should be at 20 rank and everything else should be 1, 2 ,3 and so on until 20. (not 1, 2 ,2 and so on)

Thank you everyone.

[Ashish_Mathur · Accepted solution]

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?

[v-xingshen-msft]

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]

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!

[v-xingshen-msft]

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.

[Ashish_Mathur · Accepted solution]

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?

[Jkh30]

Hi @Ashish_Mathur here is the url to download the PBIX and CSV files as the data source:

EPL Reports

Thank you for your help!

[Ashish_Mathur · Accepted solution]

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.

[Jkh30]

Splendid! Works beautifully!Thank you so much @Ashish_Mathur . I mark this as solution.

[Ashish_Mathur]

You are welcome.

[TomMartens]

Hey @Jkh30 ,

provide a pbix file containing sample data that still reflects your semantic model (tables, relationships, calculated columns, and measures), upload the file to OneDrive, Google Drive, or Dropbox and share the link. If you are importing the sample data from a spreadsheet instead of using the manual input method, share the spreadsheet as well. This is required to adapt the semantic model if necessary.

Do not forget to describe the expected result based on the sample data.

Regards,

Tom

[Jkh30]

Hi Tom, here is the url to download the PBIX and CSV file for datasource:

EPL Reports

Thank you for your help!