Forum Discussion

BM4291's avatar
BM4291
Icon for Resolver I rankResolver 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 wh...
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

     

Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity 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
Icon for Resolver I rankResolver 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.