I'm trying to make a report with a slicer that shows employee headcount plan for departments. There are many departments in different hierarchies.
My "Headcount Plan" table looks like this:
| DEPID | Department Name | Parent ID | Parent Path | Headcount Plan |
|---|---|---|---|---|
| 1 | IT | 1 | 1 | 15 |
| 2 | IT - Web Development | 1 | 1｜2 | 7 |
| 3 | Finance | 3 | 3 | 25 |
| 4 | HR | 4 | 4 | 17 |
| 5 | HR - Training | 4 | 4｜5 | 5 |
| 6 | HR - Strategy | 4 | 4｜6 | 12 |
The slicer I have shows departments in a hierarchical fashion. There are 4 levels in the hierarchy. I used a PATHITEM DAX measure to flatten the hierarchy, which I use for the slicer.
When I just sum all of the Headcount Plan using SUM(Table[Headcount Plan]), it sums child values on top of the parent values, leading to inaccuracy and added values. I want to show the sum as a card that changes its value when the slicer filter is applied.
How would one aggregate the Headcount Plan column without summing child values on top of parent values?