topic Re: Comparing a category with the next ranked category in DAX Commands and Tips

Comparing a category with the next ranked category

Anonymous — Tue, 03 May 2022 09:54:51 GMT

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 😀

Re: Comparing a category with the next ranked category

tamerj1 — Tue, 03 May 2022 10:00:33 GMT

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?

Re: Comparing a category with the next ranked category

Anonymous — Tue, 03 May 2022 10:09:26 GMT

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?

Re: Comparing a category with the next ranked category

tamerj1 — Tue, 03 May 2022 10:26:33 GMT

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

Re: Comparing a category with the next ranked category

Anonymous — Fri, 06 May 2022 06:13:39 GMT

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.

Re: Comparing a category with the next ranked category

Anonymous — Mon, 09 May 2022 10:20:01 GMT

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

Re: Comparing a category with the next ranked category

Anonymous — Tue, 10 May 2022 02:57:50 GMT

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.