Forum Discussion
Solved
Measures to calculate Ranking
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 |
- Ther's a one to many relationship between PlayersMaster[Player] and MatchScores[Player].
- I've slicers in dashboard for Match, Player & Team.
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?
Rank = RANKX(ALLSELECTED('PlayersMaster'),CALCULATE(AVERAGE('MatchScores'[Score])),,ASC,Dense)
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
RANK_Type = RANKX( ALLSELECTED( PlayersMaster[Player] ), [Total],,, DENSE )Rank_Overall = RANKX( ALL( PlayersMaster[Player] ), CALCULATE( [Total], ALLEXCEPT( PlayersMaster, PlayersMaster[Player] ) ), , , DENSE )
3 Replies
RANK_Type = RANKX( ALLSELECTED( PlayersMaster[Player] ), [Total],,, DENSE )Rank_Overall = RANKX( ALL( PlayersMaster[Player] ), CALCULATE( [Total], ALLEXCEPT( PlayersMaster, PlayersMaster[Player] ) ), , , DENSE )
