Forum Discussion
Rank AVG for the data subset
Hello,
I have data about the scores of players in beachvolleyball tournaments:
| Tournament ID | Player | Sets won | Sets lost | Sets diff | Rank AVG |
| 1 | A | 6 | 0 | 6 | 1 |
| 1 | B | 2 | 4 | -2 | 3 |
| 1 | C | 2 | 4 | -2 | 3 |
| 1 | D | 2 | 4 | -2 | 3 |
| 2 | A | 4 | 4 | 0 | 2 |
| 2 | B | 6 | 2 | 4 | 1 |
| 2 | C | 3 | 5 | -2 | 3,5 |
| 2 | D | 3 | 5 | -2 | 3,5 |
| 3 | A | 5 | 2 | 3 | 1 |
| 3 | B | 4 | 3 | 1 | 2,5 |
| 3 | C | 1 | 6 | -5 | 4 |
| 3 | D | 4 | 3 | 1 | 2,5 |
I am trying to achieve to calculated the rank of each player in each tournament (last column above) based on the diffence of the won - lost sets descending.
I am facing 2 problems:
- I am not able to define the condition to execute the ranking only within the single tournament, not across all the tournaments
- I am missing the function which equals to Excel's RANK.AVG as Power BI's RANKX equals to Excel's RANK.EQ/RANK. The difference is in the way how ranking for the same values are calculated: RANK.AVG calculates the average ranking of the same values while RANKX/RANK.EQ/RANK return the first ranking. Example: If 2nd and 3rd values are the same, RANK.AVG returns 2.5 while RANKX/RANK.EQ/RANK returns 2.
Please can you help mi solve the two problems above?
Thank you
Kind regards
Jakub Albrecht
Hi kubaalbrecht ,
You just need to change the code in step 2 to
= Table.AddIndexColumn(Table.Sort([allrows], {"Sets diff", Order.Descending}), "DiffRank",1)The other steps remain the same and get the same result.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
11 Replies
Hi,
Can you just clarify how you get your current expected results based on that dataset? Using Excel and calculating RANK.AVG for each of the values in the Sets diff column gives results widely different from those you give.
Regards
Hello,
I am trying to calculate the rank of every single player in a every single tournament regardless on the other tournaments. This means that I am not willing to calculate the rank accross all the tournaments, only within a single tournament. Cf. the problem Nr. 1 in my original post describing the same aspect as well.
I attach a screenshot containing the formula I would use in Excel. I need the same formula/function to covert to Power BI.
Screenshot from Excel describing the requested function to be converted to Power BI.
Further aggregations will be executed on top of this Ranking (e.g. "Which player has the smallest avarage ranking across all the tournaments?" as an simple average of the requested "Rank AVG tournaments ranking") but the is not the task and solving within this topic now.
Hello,
I am trying to calculate the rank of every single player in a every single tournament regardless on the other tournaments. This means that I am not willing to calculate the rank accross all the tournaments, only within a single tournament. Cf. the problem Nr. 1 in my original post describing the same aspect as well.
I attach a screenshot containing the formula I would use in Excel. I need the same formula/function to covert to Power BI.
Screenshot from Excel describing the requested function to be converted to Power BI.
Further aggregations will be executed on top of this Ranking (e.g. "Which player has the smallest avarage ranking across all the tournaments?" as an simple average of the requested "Rank AVG tournaments ranking") but the is not the task and solving within this topic now.
Hi kubaalbrecht ,
1. Grouping based on [Tournament ID] column.
2. Add custom column to calculate ranking.
= Table.AddRankColumn([allrows], "DiffRank", {"Sets diff", Order.Descending}, [RankKind = RankKind.Ordinal] )3. Delete other columns, keep the Custom column and expand it.
4. Add custom column to calculate average.
= let CurID = [Tournament ID], Curdiff = [Sets diff] in List.Average( Table.SelectRows(#"Expanded Custom", each [Tournament ID] = CurID and [Sets diff] = Curdiff)[DiffRank])If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hello.
Thank you very much for your help. Reading this seems clear a working solution BUT ...
On my working computer I have older version of Power BI with no rights to manage upgrade by my own, all the SW is managed centrally by IT department.
And in my current version of Power BI the function "Table.AddRankColumn" is missing. Do you have any idea how to solve the issue without this funtion?
Thank you once more for your help.
Hi kubaalbrecht ,
You just need to change the code in step 2 to
= Table.AddIndexColumn(Table.Sort([allrows], {"Sets diff", Order.Descending}), "DiffRank",1)The other steps remain the same and get the same result.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
