Rank AVG for the data subset

[kubaalbrecht]

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:

1. I am not able to define the condition to execute the ranking only within the single tournament, not across all the tournaments
2. 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

[v-kkf-msft]

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.

[kubaalbrecht]

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.

[v-kkf-msft · Accepted solution]

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.

[kubaalbrecht]

Hello, once more.

Sorry for continue asking. I though I will be able to continue on my own, but fortunatelly I am not.

Now I would like to add one more column defining the result of every single player in a every single tournament:

• value "winner" if the ranking of the player is the lowest ranking in the single tournament
• value "loser" if the ranking of the playes is the highest ranking in the single tournament
• value "middle" in other cases

Can you please help me in this excercise as well?

Thank you.

[v-kkf-msft]

Hi @kubaalbrecht ,

If you have the same number of people for each tournament, then you can create the following custom column.

= if [DiffRank] = 1 then "winner" else if [DiffRank] = 4 then  "loser" else "middle"

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

[kubaalbrecht]

Hello, thank you for you answer.

Unfortunally the task is not as easy because of the shared rankings and average rankings.

For example:

• winner:
  • if a single person has the lowest ranking (= the highest difference between sets won and sets lost), then winner's ranking = 1
  • if two persons have the lowest ranking, then both winners' ranking = 1.5
  • if three persons have the lowest ranking, then all the winners' ranking = 2
• the same way for losers:
  • a single loser: ranking 4
  • two shared losers: ranking 3.5
  • three shared losers: ranking 3

When you check rankings above, it is obvious that:

• ranking 2 may be winner or middle based on the ranking of other players in the same tournament
• ranking 3 may be loser or middle based on the ranking of other players in the same tournament

That is why a simple static condition considering only single player's ranking is not enough. Context of the ranking of other players within the same tournament must be considered.

So what I am asking for:

• "winner" is the player whose ranking equals the lowest ranking of the certain tournament
• "loser" is the player whose ranking equals the highest ranking of the certain tournament
• "middle" is the player who is not winner nor loser (= whose ranking does not equal lowest nor highest ranking of the certain tournament)

Thank you for your help.

[v-kkf-msft]

Hi @kubaalbrecht ,

Please try the custom column.

= let CurID = [Tournament ID], tab = Table.SelectRows(#"Added Custom1", each [Tournament ID] = CurID), minrank = List.Min(tab[Rank_avg]), maxrank = List.Max(tab[Rank_avg])
in if [Rank_avg] = minrank then "winner" else if [Rank_avg] = maxrank then "loser" else "middle"

Best Regards,
Winniz

[kubaalbrecht]

Thank you! Works as desired. Only performace is not as great but that is not crucial for me.

[kubaalbrecht]

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.

[Jos_Woolley]

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

[kubaalbrecht]

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.