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...
Need Help
  • 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
   )
)

     

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

Hi sturlaws 

Firstly, thanks for the info about RETURN.  I will amend my expressions.

 

Regarding the variable = 22.

 

The way that I had created the H2HGamesPlayed Variable was to first try and identify all Game ID's where both Player 1 and Player 2 boards were present.

 

I had the formula: 

 

Head2Head Games = 
VAR Number = 22 
VAR P1Board = SELECTEDVALUE('Player1'[Game Board]) 
VAR P2Board = SELECTEDVALUE('Player2'[Game Board]) 
RETURN
CALCULATE(COUNTROWS('Game Fact'),'Game Fact'[Game Board] IN {P1Board,P2Board})

 

When I created a table, I discovered the following (In this example, the player boards are "Alexandria" and "Roma"):

 

Any Game ID that had 11 rows (one for each individual category) included either one of the Boards, and any Game ID's where the count was 22 or greater meant that those two boards were in play.  Thus, I needed to ensure only those Game ID's where the row count was 22 or more were included in the larger expression.  As a result, my variable became:

 

VAR H2HGamesPlayed =
    FILTER (
        VALUES ('Game Fact'[Game ID]),
        CALCULATE(COUNTROWS('Game Fact'),'Game Fact'[Game Board] IN {P1Board,P2Board}) >= Number
    )
RETURN

 

Which I assumed returned a list/table of only those 4 Game ID's.

 

Does that make sense?  As I say, the Total Average points expression works correctly... but perhaps that is by chance and not by design?

 

I do know that if I change the variable to 1 as you suggested, the visual did show, however the averages points were incorrect as they included every single Game ID where that player's board was present, not just those games against the other player.

 

Thank you for helping me with this.  Any ideas how I can go forward from here?

sturlaws's avatar
sturlaws
Icon for Resident Rockstar rankResident Rockstar
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
   )
)

 

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

    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