Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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())
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |