Dax measure - Broaden filter to include all the values within a peer group

[nogarr]

Hi there!

I have survey data for which each response is connected to an employee list which contains for each respondent its assigned manager, and hierarchical level and other data.

In my report, I have defined a dashboard grouping the information by manager (capturing the responses of all employees which have that specific manager) using a slicer.

I am able to define measures to calculate the average response by manager and slice that information further. However, my problem is that I am not able to define a metric to according to the manager defined in the slicer, calculates the average response for all the managers at his hierarchical level (and slice that average after further).

The two tables following show of how the data is structured (they are connected by the Employee ID):

Employee ID	Manager ID	Hierarchy level
1	6	3
2	6	3
3	5	3
4	5	3
5	7	2
6	7	2
7	na	1

Employee ID	Question 1	Question 2	Question 3
1	1	7	2
2	2	6	4
3	2	4	0
4	4	3	7
5	3	2	6
6	1	1	2
7	5	3	1

For this example I would have already calculated a metric that gives me the average across questions by manager (Manager average), but not the Hierarchy level average, whose values for this example can be seen in the following table:

Manager ID	Manager Average	Hierarchy level average
5	3.3	3.5
6	3.7	3.5
7	2.5	2.5

Any help would be welcome!

Thank you very much.

[OwenAuger · Accepted solution]

Hi @nogarr 

You need to create a measure that clears all Employee filters (using ALL or ALLSELECTED) except retains the values of Hierarchy level as a filter. Something like this worked for me in a test model:

Hierarchy level average = 
CALCULATE (
    [Manager Average],
    ALLSELECTED ( Employee ), // Could be ALL depending on requirements
    VALUES ( Employee[Hierarchy level] )
)

Here is a PBIX for reference.

I unpivoted the Survey results table, but that doesn't directly affect the above measure.

Regards,

Owen

[nogarr]

Hi @OwenAuger, thank you very much. At the end it was enough with using the same formula you did but with the hierarchy level that came by default in my tables, I just did not know about the "Values" formula.

Overall, your answer has helped me in three ways: by giving me a solution to my dashboard, by showcasing the use of the value formula and by showcasing the use of the path / pathlength formula. Thank you!