Forum Discussion

paulvans182

Helper 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('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

Need Help

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
   )
)

4 Replies

sturlaws
Resident Rockstar
6 years ago
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
  Helper 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
    Resident 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 ) )

Forum Discussion

Average Score in Each Category

4 Replies

Related content

Average of categories over vendor

Calculation help - average score per category

Count of employee per Average score Category

DAX average score

AVERAGE BY CATEGORY