I've below data set and need to identify the ranking of "Player" based on their "Score" average.
PlayersMaster
Player | Team | Type |
Player1 | Team1 | Local |
Player2 | Team1 | Overseas |
Player3 | Team2 | Local |
Player4 | Team2 | Local |
Player5 | Team2 | Overseas |
MatchScores
Match | Player | Score |
Match1 | Player1 | 25 |
Match1 | Player2 | 40 |
Match1 | Player3 | 84 |
Match1 | Player4 | 19 |
Match1 | Player5 | 43 |
Match2 | Player1 | 56 |
Match2 | Player2 | 36 |
Match2 | Player3 | 61 |
Match2 | Player4 | 58 |
Match2 | Player5 | 90 |
Match3 | Player1 | 26 |
Match2 | Player2 | 34 |
Match2 | Player3 | 31 |
Match2 | Player4 | 31 |
Match2 | Player5 | 56 |
Please support me in developing below measures.
1. Dynamic Ranking of Players (Top3) based on Average score. (Filtered upon slicer values)
I was able to identify the ranking using below measure. In visual, I just filtered the Top 3 players using a filter. Is there any other better way to achieve this?
2. Overall Ranking of Player, irrespective of slicer values.
Expected Result
Player | Rank | Type | Total |
Player5 | 1 | Overseas | 63 |
Player3 | 2 | Local | 58.67 |
Player2 | 3 | Overseas | 36.67 |
Player4 | 4 | Local | 36 |
Player1 | 5 | Local | 35.67 |
3. Overall Ranking of Player based on Type, irrespective of slicer values
Expected Result
Player | Rank | Type | Total |
Player5 | 1 | Overseas | 63 |
Player3 | 1 | Local | 58.67 |
Player2 | 2 | Overseas | 36.67 |
Player4 | 2 | Local | 36 |
Player1 | 3 | Local | 35.67 |
Thanks in advance
Solved! Go to Solution.
RANK_Type = RANKX( ALLSELECTED( PlayersMaster[Player] ), [Total],,, DENSE )
Rank_Overall =
RANKX(
ALL( PlayersMaster[Player] ),
CALCULATE( [Total], ALLEXCEPT( PlayersMaster, PlayersMaster[Player] ) ),
,
,
DENSE
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
RANK_Type = RANKX( ALLSELECTED( PlayersMaster[Player] ), [Total],,, DENSE )
Rank_Overall =
RANKX(
ALL( PlayersMaster[Player] ),
CALCULATE( [Total], ALLEXCEPT( PlayersMaster, PlayersMaster[Player] ) ),
,
,
DENSE
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thanks a lot @CNENFRNL for your reply. However, I'm not clear about the [Total] field that you've used for both measures.
In my expected results I've considered the Average Score for each player as the Total.
Please explain.
Thanks
Total = AVERAGE('MatchScores'[Score])
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
User | Count |
---|---|
142 | |
61 | |
59 | |
58 | |
47 |
User | Count |
---|---|
136 | |
72 | |
59 | |
56 | |
52 |