Team avg vs individual avg

[kkalyanrr]

Hello,

I'm trying to put an chart with Individual Avg , team average(minus individual avg) and State average (minus selected individual and region he belongs).

Please help.

[MFelix]

Hi @kkalyanrr ,

Create the following 3 measures:

Individual Average = CALCULATE(AVERAGE('Table'[Revenue]); ALLSELECTED('Table'[Emp ID]))

State Average = 
VAR temp_table =
    SUMMARIZE (
        ALLSELECTED ( 'Table'[Team]; 'Table'[Emp ID]; 'Table'[State] );
        'Table'[State];
        "@StateRevenue"; SUM ( 'Table'[Revenue] )
    )
RETURN
    AVERAGEX ( temp_table; [@StateRevenue] )

Team Average = 
VAR temp_table =
    SUMMARIZE (
        ALLSELECTED ( 'Table'[Team]; 'Table'[Emp ID]; 'Table'[State] );
        'Table'[Team];
        "@TeamRevenue"; SUM ( 'Table'[Revenue] )
    )
RETURN
    AVERAGEX ( temp_table; [@TeamRevenue] )

this will return the averages for Individual, team and State then you can use them to make the calculations you need.

Check PBIx file attach.

[kkalyanrr]

@MFelix  Thanks for the response.
I would like to see all the three lines in the same chart..

and if we select an individual id, we shld be able to see his performance,

his team performance(excluding his performace)

and his state performance(excluding his team performace).

Thanks.

[MFelix · Accepted solution]

Hello @kkalyanrr ,

Try the following steps:

State Average = 
VAR temp_table =
    SUMMARIZE (
        FILTER (
            ALLSELECTED( 'Table'[Emp ID]; 'Table'[Team]; 'Table'[State]; 'Table'[Revenue] );
            'Table'[Team] <> SELECTEDVALUE ( 'Table'[Team] )
                && 'Table'[State] = SELECTEDVALUE ( 'Table'[State] )
        );
        'Table'[State];
        "@RevenueTotal"; SUM ( 'Table'[Revenue] )
    )
RETURN
    AVERAGEX ( temp_table; [@RevenueTotal] )

Team Average = 
VAR temp_table =
    SUMMARIZE (
        FILTER (
            ALLSELECTED(  'Table'[Emp ID]; 'Table'[Team]; 'Table'[Revenue] );
            'Table'[Emp ID] <> SELECTEDVALUE ( 'Table'[Emp ID] )
                && [Team] = SELECTEDVALUE ( 'Table'[Team] )
        );
        'Table'[Team];
        'Table'[Revenue]
    )
RETURN
    AVERAGEX ( temp_table; 'Table'[Revenue] )

Check the result below and on the PBIX connection:

Since T8 to T10 are only on the computer in a state the state values are not presented.