Forum Discussion

kubaalbrecht's avatar
kubaalbrecht
Regular Visitor
4 years ago
Solved

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...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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.

     

     

v-kkf-msft's avatar
v-kkf-msft
Community Support
4 years ago

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's avatar
kubaalbrecht
Regular Visitor
4 years ago

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's avatar
    v-kkf-msft
    Community Support
    4 years ago

    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's avatar
      kubaalbrecht
      Regular Visitor
      4 years ago

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

    • kubaalbrecht's avatar
      kubaalbrecht
      Regular Visitor
      4 years ago

      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's avatar
        v-kkf-msft
        Community Support
        4 years ago

        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