Forum Discussion
Average Score in Each Category
- 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 ) )
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
)
)
Hi sturlaws
Thank you so much. Not only does the solution work perfectly, but I am very grateful for the step by step explanation of the solution.
Kind regards,
Paul