Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Comparing a category with the next ranked category

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 o...
Compare
DAX
  • tamerj1's avatar
    tamerj1
    4 years ago

    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
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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