Team Average at Individual Level - Granularity

[BM4291]

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

ID	Points
1	5
2	10
3	15
4	25

User Table

ID	Name	Team
1	John	1
2	Jim	1
3	Joe	2
4	James	3

Team Table

ID	Name	Area
1	Team 1	Area 1
2	Team 2	Area 1
3	Team 1	Area 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

Name	Total	Average	Team Average	Area Average
John	5	13.75	7.5	10
Jim	10	13.75	7.5	10
Joe	15	13.75	15	10
James	25	13.75	25	25

Any help greatly appreciated.

[Mariusz · Accepted solution]

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.

[BM4291]

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.