Average satisfaction by Manager if Manager exists in Manager 1 or Manager 2 columns

[shirane]

Hi,

I have a table of Employees Satisfaction. The table includes the employee name, satisfaction column (rating 1-5), Manager 1 (who is the employee's direct manager) and Manager 2 (Manager 1's manager). 

I need for each department manager (Manager 2) to calculate the average satisfaction in his/her department.

The problem is that it doesn't take into account the employees which are direct reports to that manager because he's their manager 1 and not their manager 2.

How can I solve this?

(hope my question is clear)

Shiran

[Greg_Deckler]

Probably something like:

Average Sat Measure = 
  VAR __Manager = MAX('Table'[Manager 2])
RETURN
  AVERAGEX(
    FILTER(
      ALL('Table'),
      [Manager 1] = __Manager ||
        [Manager 2] = __Manager
    ),
    [Rating]
  )

[shirane]

Hi, thanks, is this a new measure in my table? please explain what I should do with this function, I'm pretty new to PBI 🙂

thanks!

[Greg_Deckler]

Measures can live anywhere. Just right click your table and select "New Measure" and put the code in the formula bar. I used your sample data and created a PBIX for you.

[Anonymous]

We need to see the data model and some sample data. Also, please give us an example of a correct calculation that highlights the issue you're facing.

Thanks.

Best
D

[shirane]

OK, see example below:

Emp Name	Satisfaction	Manager 1	Manager 2
John	4	Amy	Michael
Tracy	3	Michael	Bob
Michael	5	Bob	Sammy
Neal	4	Bob	Sammy
David	4	Amy	Michael

If I want to calculate Bob's deparment average satisafction, I'll need to take into account Tracy's, Michael's, and Neal's records and get an average of 4.3, but currently I only get Tracy's record because I have a "Clustered Column Chart" in which my axis is Manager 2 and the value is Average of Satisfaction. In Manager 2  column I am also only filtering specific managers' names that I wish to see.