Adjusting Multiple Visuals with One Slicer

[BekahLoSurdo]

Hello,

I am trying to create a scorecard that shows a comparison between:

a) An employee's breakdown of hours by type of work

b) Breakdown of hours by type of work for that employee's entire division 

- if they do work for more than one division, this should show the type-of-work makeup for the cumulative hours of all applicable divisions

c) Breakdown of hours by type of work for the company as a whole

Here is some sample data / desired visual:

Person	Division	Project Type	Hours
A	1	Electrical	100
A	1	Software	200
A	1	Mechanical	400
A	1	Technical	60
B	1	Electrical	500
B	1	Software	200
B	2	Mechanical	300
B	2	Technical	800
B	3	Technical	600
C	2	Electrical	100
C	2	Software	200
C	2	Mechanical	400
C	1	Mechanical	60
C	2	Technical	500
D	3	Electrical	200
D	3	Software	300
D	2	Mechanical	800
D	2	Technical	600

I am able to easily present a) and c) but I am having trouble getting the slicer for a) to affect the visualization for b).

Switching my "Person" slicer around adds the appropriate options to my "Division" slicer (not shown above as it will be hidden in the final report) but it does not auto-check them which is what I need. For instance, switching from A to C results in:

Thank you in advance for any help!

[d_gosbell]

I'm assuming that you have 3 separate measure here to calculate the results for the different doughnut charts. For the middle chart I would use an expression like the following:

Hours by Division = CALCULATE(sum(Table1[Hours]), ALL(Table1),VALUES(Table1[Division]))

[BekahLoSurdo]

Hi @d_gosbell,

This is close but doesn't take into the account the fact that I need to include Project Type in the context as well or the following occurs:

I'm new to DAX so this may not be the best way to add to the context but going off of your code I changed it to:

Hours by Division = CALCULATE(SUM(Sheet1[Hours]), ALL(Sheet1), VALUES(Sheet1[Division]), VALUES(Sheet1[Project Type]))

This fixed the problem with the pre-filtered visual:

But now, because the measure is taking into account Division + Project Type, when I filter by (for instance) Person B, the visual is only showing the cominbations of Division + Project Type that Person B has:

Instead of this, what I need is: since Person B is in all 3 divisions, this should show the total of all Divisions (regardless of Project Type).

How do I include the granularity of Project Type for the sake of the legend but ignore it for the sake of the totals?

Thank you!

[d_gosbell · Accepted solution]

Oh, I see, so the current expression only get the divisions that exist for a given customer for a given type. So when you filter by B, it excludes division 1 from the type Technical as customer B does not have that combination.

The following code should fix this by getting all divisions for the current context, regardless of type and then passing that in when calculating the hours.

Hours by Division = 
Var _divisions = CALCULATETABLE( values(Table1[Division]), all(Table1[Project Type]))
var _types = VALUES(Table1[Project Type])
return CALCULATE(sum(Table1[Hours]), ALL(Table1),_types,_divisions)

[BekahLoSurdo]

@d_gosbell, yes that was exactly the issue and yes, that is the perfect solution. I was thinking that I had to create an intermediary table but doing so via a variable is so much more elegant and efficient. Thank you so much!

Here are the final results for the first 3 employees: