Calculating one average minus another for each value of a column

[Anonymous]

I have two tables of responses, one provided by an individual, one by their manager. Each looks like this:

Self Table

Person       Attribute         Value

A                 ST1                 6

B                 CB1                 4

C                SB2                   3

D                 SA1                  2

Manager Table

Person       Attribute         Value

A                 ST1                 3

B                 CB1                 6

C                SB2                   2

I want to calculate the gap between an individual's average responses for each item in the "attribute" column, and their manager's average responses for the same attribute. So I need an expression for average Value (Self) - average value (Manager) for each item in the Attribute column. Can anyone help?

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

Try to it.

Diff = CALCULATE(AVERAGE('self'[Value]),FILTER('self',[Attribute]=MAX('Table'[Attribute])))- CALCULATE(AVERAGE('Manager'[Value]),FILTER('Manager',[Attribute]=MAX('Table'[Attribute])))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Also just a quick clarification - I have a 3rd table which looks like this:

Attribute         Question

ST1                 What do you think of XYZ?

This table is linked 1-Many from the Attribute field to both the Self table and the Manager Table.

My ultimate desired output is a table of attribute, question, individual-manager difference which I can filter for each individual.