Forum Discussion

paulvans182's avatar
paulvans182
Icon for Helper III rankHelper III
6 years ago
Solved

Average Score in Each Category

Good afternoon,   I have the following formula:     Head2Head P1 Avg Points Total = VAR Number = 22 RETURN VAR P1Board = SELECTEDVALUE('Player1'[Game Board]) RETURN VAR P2Board = SELECTEDVALUE...
  • sturlaws's avatar
    sturlaws
    6 years ago

    I think I would rewrite the measure to this:

     

    Head2Head P1 Avg Points = 
    VAR P1Board = SELECTEDVALUE('Player1'[Game Board]) 
    VAR P2Board = SELECTEDVALUE('Player2'[Game Board]) 
    
    VAR _GamesPlayed_P1Board =
        FILTER (
            VALUES ('Game Fact'[Game ID]),
            CALCULATE(COUNTROWS('Game Fact'),'Game Fact'[Game Board] IN {P1Board})>=1
        )
    
    VAR _GamesPlayed_P2Board =
        FILTER (
            VALUES ('Game Fact'[Game ID]),
            CALCULATE(COUNTROWS('Game Fact'),'Game Fact'[Game Board] IN {P2Board})>=1
        )    
    
    var _gamesPlayed_P1P2Boards =
    INTERSECT(_GamesPlayed_P1Board,_GamesPlayed_P2Board)
    
    RETURN
    
    CALCULATE(AVERAGE('Game Fact'[Points]),FILTER('Game Fact','Game Fact'[Game Board]=P1Board && 'Game Fact'[Game ID] in _gamesPlayed_P1P2Boards))

     

     

    This part returns all game ids where the game board is equal to Player 1 board

     

    VAR _GamesPlayed_P1Board =
        FILTER (
            VALUES ('Game Fact'[Game ID]),
            CALCULATE(COUNTROWS('Game Fact'),'Game Fact'[Game Board] IN {P1Board})>=1
        )
    

     

     

    and this returns all game ids where the game board is equal to Player 2 board:

     

    VAR _GamesPlayed_P2Board =
    FILTER (
    VALUES ('Game Fact'[Game ID]),
    CALCULATE(COUNTROWS('Game Fact'),'Game Fact'[Game Board] IN {P2Board})>=1
    )

     

     

    By using the intersect function, we get all game ids where the game has been between Player 1 board and Player 2 board:

     

    var _gamesPlayed_P1P2Boards =
    INTERSECT(_GamesPlayed_P1Board,_GamesPlayed_P2Board)

     

     

    Now use this in the main calculation:

     

    CALCULATE(
       AVERAGE('Game Fact'[Points]),
       FILTER(
          'Game Fact',
          'Game Fact'[Game Board]=P1Board &&
          'Game Fact'[Game ID] in _gamesPlayed_P1P2Boards
       )
    )