Average Score in Each Category

[paulvans182]

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('Player2'[Game Board]) RETURN

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

The variable H2HGamesPlayed, finds all the Game ID's where the Game Boards are involved.

The CALCULATE function calculates the average Total score for Player 1.  The total field exists in the Category column.  There are 11 categories.

The expression works perfectly.  The score I get on a card from this measure matches the data.

I want to create an additional expression where I remove the Filter for Category.  What I am expecting is to be able to create a bar chart where the axis will be the Category column - and the measure will calculate the average points for each of those categories.  Here is that expression:

Head2Head P1 Avg Points = 
VAR Number = 22 RETURN
VAR P1Board = SELECTEDVALUE('Player1'[Game Board]) RETURN
VAR P2Board = SELECTEDVALUE('Player2'[Game Board]) RETURN

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

This measure does not work. When I create a Bar Chart, it remains blank.

I am sure I must be missing something obvious, please can someone help me? 

I have attached the PowerBI file: Query Average by Category PB file 

Edit 21/07 13:51 GMT.

I removed each variable in my CALCULATE expression and hard-coded the values... I found that if I remove the H2HGamesPlayed variable, the correct output appears. 

So I guess that variable is not performing as I expect.  However, what I find odd is that it works for the Total Expression (expression 1).

Hope this helps anyone who is looking into this for me

Kind regards,

Paul

[sturlaws]

Hi @paulvans182 

why is the variable Number = 22? If you change it to 1 the  measure returns values for the categories.

Another small thing, you don't have to write RETURN after each variable, it is sufficient to write it after the last variable, like this:

Head2Head P1 Avg Points Total = 
VAR Number = 22 
VAR P1Board = SELECTEDVALUE('Player1'[Game Board]) 
VAR P2Board = SELECTEDVALUE('Player2'[Game Board]) 

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

Cheers,
Sturla

[paulvans182]

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 · Accepted solution]

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]

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