DAX Formula To Rank Based On Multiple Values
Performing ranking in DAX is simple enough when you are considering just single numerical values.
I mean, for example, it’s easy to rank Products based on Total Sales.
But consider the scenario below. A classical problem in sporting league standings. Where there can be multiple ties on various values:
Table showing teams tied on several indicators
What’s happening there?
Assuming the rules for ranking teams are based on the following:
- Team with higher points is ranked higher.
- When 2 or more teams have the same points, the team with higher goals difference is ranked higher.
- However, if 2 or more teams have the same Points and Goals Difference, the team with the lower Goals Against is ranked higher.
- When 2 or more teams have the same Points, Goals Difference and Goals Against, they will be ranked in Alphabetical Orders.
Looking at the table above, all the above rules are in play. Here are some examples
- Manchester City and Everton have the same Points
- Brentford and West Ham have the same Points and Goals Difference
- Chelsea and Liverpool have the same Points, Goals Difference and Goals Against.
Wahala be like bicycle (Problem is like bicycle – Nigerian saying, meaning, like bicycle, problem is not a difficult thing to start).
Solving the problem mentally.
The first thing to solve any problem, including DAX is to break it down into smaller pieces.
- Rank all the teams based on points. If there are 20 teams, you will have ranking from 1 to 20.
- Some teams are going to have ties. So, another ranking will be required:
Isolate the teams based on ties from the above ranking and carry out another ranking based on Goals Difference.
Here for example, if there 3 teams tied on 1st position, the isolated ranking will have them ranked from 1 to 3.And if there is only 1 team on 4th position for example, the isolated ranking will have that team ranked as 1 since there is no competition there.
Table showing how to rank on multiple levels
- Rank again in a way that, the teams tied on 1st like in the previous point will now be separated into possible 1, 2, 3 by combining the initial rank with the isolated rank
- If there are still ties from the previous ranking, repeat steps 2 and 3 again for another ranking. And continue that loop till the end.
Solving the Problem with DAX
1. InitialRanking =
RANKX (
ALL ( ‘League Standing'[Club] ),
CALCULATE ( SUM ( ‘League Standing'[Points] ) ), ,
DESC,
SKIP
)
DAX Code and Table showing the initial ranking
2. Second Ranking =
VAR InitialRanking =
[InitialRanking]
VAR Ties =
FILTER (
ALL ( ‘League Standing'[Club] ),
[InitialRanking] = InitialRanking
)
RETURN
RANKX (
Ties,
CALCULATE (
SUM ( ‘League Standing'[Goals Difference] )
), ,
DESC,
SKIP
)
Dax code and Table showing the second ranking
3. Third Ranking =
[InitialRanking] + [Second Ranking] – 1
DAX code and Table showing the 3rd ranking
4. Fourth Ranking =
VAR ThirdRanking =
[Third Ranking]
VAR Ties =
FILTER (
ALL ( ‘League Standing'[Club] ),
[Third Ranking] = ThirdRanking
)
RETURN
RANKX (
Ties,
CALCULATE (
SUM ( ‘League Standing'[Goals Against] )
), ,
ASC,
SKIP
)
DAX code and Table showing the 4th ranking
5. Fifth Ranking =
[Third Ranking] + [Fourth Ranking] – 1
DAX code and Table showing the 5th ranking
6. Sixth Ranking =
VAR FifthRnking = [Fifth Ranking]
VAR Ties =
FILTER (
ALL ( ‘League Standing'[Club] ),
[Fifth Ranking] = FifthRnking
)
RETURN
RANKX (
Ties,
CALCULATE (
MIN ( ‘League Standing'[Club] )
), ,
ASC,
SKIP
)
DAX Code and Table showing the 6th ranking
7. Seventh Ranking =
[Fifth Ranking] + [Sixth Ranking] – 1]
DAX code and Table showing the final ranking
Conclusion
In the end, only the final ranking will be visualized.
Final Standings
Yeah, I know how complicated DAX can seem. But in the end, not giving up on mastering it is the right way to go.
You can check my YouTube Course on Mastering DAX Foundation to gain a solid foundation of the DAX Language. Please subscribe to the channel too.
