Forum Discussion
Adjusting Multiple Visuals with One Slicer
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!
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)
4 Replies
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]))
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!
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)
