Comparing a category with the next ranked category

[Anonymous]

Aim:

To compare results of a player in one age group to the next age group up. 

Essentially, say I select player 001, I want to find out what the difference is between their score and the average of the players in the age group above them.

Data: 

Player ID	Team ID	Session Date	YoYo Score
001	3	01/04/2022	1,480
002	4	01/04/2022	1,880
003	4	01/04/2022	1,750
004	3	01/04/2022	1,500

I have thought about using RANKX but can't seem to get my head around it with something like IF TeamID is +1 of the SELECTEDVALUE then find difference. 

Ideally I would like to achieve this in a measure, rather than creating a calculated table with it all in.

Thanks in advance 😀 

[v-yetao1-msft]

Hi @Anonymous 

Has your problem been solved ? If it has been solved, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yetao1-msft]

Hi @Anonymous 

You need create a measure to count the average category by [Team ID] .

Ave = CALCULATE( AVERAGE('Table'[YoYo Score]), ALLEXCEPT('Table','Table'[Team ID]))

Then create a measure to count the difference of score between selected player and the average score of team id +1 .

Score Difference = SUM('Table'[YoYo Score]) - CALCULATE([Ave], FILTER(ALL('Table'), 'Table'[Team ID] = MAX('Table'[Team ID]) + 1))

Add a slicer with column [Player ID] .The fianl result is as shown below .

I have attached my pbix file , you can refer to it .

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1 · Accepted solution]

@Anonymous 

Please try

Score Difference =
VAR CurrentTeam =
    SELECTEDVALUE ( Table[Team ID] )
VAR CurrencyScore =
    SELECTEDVALUE ( Table[YOYO Score] )
VAR NextGroupAverage =
    CALCULATE (
        AVERAGE ( Table[YOYO Score] ),
        ALLEXCEPT ( Table, Table[Session Date] ),
        Table[Team ID] = CurrentTeam + 1
    )
RETURN
    CurrencyScore - NextGroupAverage

[Anonymous]

Thank you for your solution, I played around with it and split up the data into seperate tables and came out with the following which works: 

Team Up = 

Var AgeUp = SELECTEDVALUE(Joined[TeamCompOrder]) + 1

Var SelectedplayerScore = SELECTEDVALUE(Joined[Score])

Var SelectedPosition = SELECTEDVALUE(Joined[Position])

Var AvgPositionUp = CALCULATE(AVERAGE(Summary[Avg Score]),Summary[TeamCompOrder] = AgeUp && Summary[Position] = SelectedPosition)

RETURN

SelectedPlayerScore - AvgPositionUp

[tamerj1]

Hi @Anonymous 

please provide more details. Where is age group you've mentioned in your query? What is the average? The ranking is based on what?

[Anonymous]

The age group would be the TeamID. The average would be calculated in the measure I assume in a variable? In this instance if I were comparing player 001 then I would find the average of players in Team ID 4, as it's the next age group up, and then compare the results to that. 

Hope this helps?