Forum Discussion

BM4291's avatar
BM4291
Resolver I
7 years ago
Solved

Team Average at Individual Level - Granularity

Hi,

 

Having a bit of difficulty with the granularity of one of my measures; redacted data below.

 

What I'm trying to do is show individual totals against a team and area average to ascertain whether a user is above, below, or inline with their team/area.

 

When I view the data at team/area level I get exactly what I expect, however, when I introduce the user, the averages just revert to the user's own value.

 

Points Table

IDPoints
15
210
315
425

 

User Table

IDNameTeam
1John1
2Jim1
3Joe2
4James3

 

Team Table

IDNameArea
1Team 1Area 1
2Team 2Area 1
3Team 1Area 2

 

Users joins to Points by ID -> ID and Users joins to Team by Team -> ID.

 

Measures are:

totalPoints = SUM(Points[Points])
avgPoints = AVERAGE(Points[Points])
avgPointsALL = CALCULATE([avgPoints], ALL(Points))
avgPointsTeam = CALCULATE([avgPoints], ALLSELECTED(Team[ID]))
avgPointsArea = CALCULATE([avgPoints], ALLSELECTED(Team[Area]))

Below is what I would expect to see

NameTotalAverageTeam AverageArea Average
John513.757.510
Jim1013.757.510
Joe1513.751510
James2513.752525

 

Any help greatly appreciated.

 

Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi BM4291 

     

    Please see the below measures below.

    Area Average = 
IF ( 
    INT( ISEMPTY( Points) ) = 0,
    CALCULATE(
        AVERAGE( Points[Points] ),
        ALLSELECTED( ),
        VALUES( Team[Area] )
    ) 
)
    Average = 
IF( 
    INT( ISEMPTY( Points) ) = 0,
    CALCULATE( 
        AVERAGE( Points[Points] ), 
        ALLSELECTED() 
    )
) 
    Team Average = 
IF ( 
    INT( ISEMPTY( Points) ) = 0,
    CALCULATE(
        AVERAGE( Points[Points] ),
        ALLSELECTED( ),
        VALUES( Team[TeamId] )
    ) 
)

     

    Hope this helps.

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

2 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi BM4291 

     

    Please see the below measures below.

    Area Average = 
IF ( 
    INT( ISEMPTY( Points) ) = 0,
    CALCULATE(
        AVERAGE( Points[Points] ),
        ALLSELECTED( ),
        VALUES( Team[Area] )
    ) 
)
    Average = 
IF( 
    INT( ISEMPTY( Points) ) = 0,
    CALCULATE( 
        AVERAGE( Points[Points] ), 
        ALLSELECTED() 
    )
) 
    Team Average = 
IF ( 
    INT( ISEMPTY( Points) ) = 0,
    CALCULATE(
        AVERAGE( Points[Points] ),
        ALLSELECTED( ),
        VALUES( Team[TeamId] )
    ) 
)

     

    Hope this helps.

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

    • BM4291's avatar
      BM4291
      Resolver I
      7 years ago

      Thanks Mariusz ,

       

      Very close, the Team level measure is now working as expected but the area level is showing the same as team when I look at the user level; it works when I look at the team/area level, so very nearly there.

       

      Can you explain the ALLSELECTED() logic? I've never used it without specifying a table/column before, are you literally saying show the result for everything selected rather than specifying what to use?

       

      I'll keep plugging myself but this is a really handy tip.