Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowGet inspired! Check out the entries from the Power BI DataViz World Championships preliminary rounds and give kudos to your favorites. View the vizzies.
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())
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code FABINSIDER for a $400 discount!
Check out the February 2025 Power BI update to learn about new features.
User | Count |
---|---|
85 | |
70 | |
68 | |
51 | |
32 |
User | Count |
---|---|
115 | |
100 | |
74 | |
65 | |
40 |