Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, 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 it as a card that changes its value upon a filter is applied.
How would one aggregate the Headcount Plan column without summing child values on top of parent values?
Please let me know if clarifications are needed.
Thank you.
Solved! Go to Solution.
Hi, I understant you want to SUM the parent deparment without child deparment. And child departments have "|" in its parent path, so I will use it to remove child departments.
Measure = CALCULATE (SUM(Headcount plan),SEARCH("|",parent path column,1,blank())=BLANK())
Hi, I understant you want to SUM the parent deparment without child deparment. And child departments have "|" in its parent path, so I will use it to remove child departments.
Measure = CALCULATE (SUM(Headcount plan),SEARCH("|",parent path column,1,blank())=BLANK())
User | Count |
---|---|
98 | |
90 | |
83 | |
70 | |
67 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |