Hi all,
I have a Powerbi issue where I need some help. Consider the following data tables:
Data Table 1: Employee list
Name | ID | Manager ID | Job title |
John | 1 | | CEO |
Jane | 2 | 1 | CTO |
Elliot | 3 | 1 | CFO |
Elaine | 4 | 1 | COO |
Vicky | 5 | 2 | SW Engineer |
Data Table 2: Question List
Question |
Question 1 |
Question 2 |
Data Table 3: Survey Responses
Name | ID | Score | Question |
John | 1 | 1 | Question 1 |
Jane | 2 | -1 | Question 1 |
Elliot | 3 | 0 | Question 1 |
Elaine | 4 | -2 | Question 1 |
Vicky | 5 | 2 | Question 1 |
John | 1 | 0 | Question 2 |
Jane | 2 | 2 | Question 2 |
Elliot | 3 | 2 | Question 2 |
Elaine | 4 | 2 | Question 2 |
Vicky | 5 | -2 | Question 2 |
I already have a measure SURVEY SCORE that calculates the overall respondent survey score across questions, depending on the filter context:
SURVEY SCORE = AVERAGEX('Question List', average('Survey Responses'[Score]))
I now want to build a measure MANAGER SCORE that calculates for managers the average score of all their direct reports within a particular filter context. See below examples with a powerBI visual table
Filter context 1: Questions = all
Name | ID | MANAGER SCORE |
John | 1 | =[(-1+0-2)/3+(2+2+2)/3]/2 = 1/2 |
Jane | 2 | [2 + (-2)] / 2 = 0 |
Elliot | 3 | N/A |
Elaine | 4 | N/A |
Vicky | 5 | N/A |
Filter context 2: Questions = Question 1
Name | ID | MANAGER SCORE |
John | 1 | =(-1+0-2)/3 = -1 |
Jane | 2 | 2 |
Elliot | 3 | N/A |
Elaine | 4 | N/A |
Vicky | 5 | N/A |
My original thought was to write something like this:
MANAGER SCORE = calculate([SURVEY SCORE],FILTER('Employee List', 'Employee List'[Manager ID]= *employee ID fed within visual*))
However I have not found a way to write this formula. Would someone be kindly be able to help me?
PS:
I know I can write in a calculated column
MANAGER SCORE = calculate([SURVEY SCORE],FILTER('Employee List', 'Employee List'[Manager ID]= EARLIER('Employee List'[ID]))) but this does not adapt to filter context
