Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I'm trying to create a measure that will ignore multiple filters.
I currently have this data structure:
Hierarchy Headcount
Dept A 25
Dept B 75
Dept C 100
Total Company 200
Dept A 12.5%
Dept B 37.5%
Dept C 50%
If I have a Dept. slicer and if I select Dept C, the % for that dept. as a % of the company total is calculated (50%) by using this measure:
Headcount | ||
Dept A | Team A | 12 |
Dept A | Team B | 13 |
Dept B | Team C | 25 |
Dept B | Team D | 50 |
Dept C | Team E | 75 |
Dept C | Team F | 25 |
Total employees | 200 |
Solved! Go to Solution.
Hi @Fusilier2 ,
You can modify your measure to ensure that it always calculates the percentage relative to the total company headcount, regardless of any filters applied to Hierarchy (Department) or Team. The key is to use the ALL function on both fields to remove filters in the denominator. Here’s the revised DAX measure:
% of total company =
DIVIDE(
SUM('HR data'[Headcount (Incl. Casuals)]),
CALCULATE(
SUM('HR data'[Headcount (Incl. Casuals)]),
ALL('HR data'[Hierarchy], 'HR data'[Team])
)
)
This measure calculates the sum of headcount within the current filter context for the numerator. In the denominator, it uses CALCULATE with ALL('HR data'[Hierarchy], 'HR data'[Team]) to remove filters applied by slicers on both the Hierarchy and Team columns. As a result, if no filters are applied, the measure returns 100% (200/200). If you select Dept B, it returns 75/200 = 37.5%. If you select Dept B and Team C, it returns 25/200 = 12.5%. This ensures that even when filtering by both department and team, the measure correctly calculates the percentage as a fraction of the total company headcount.
Best regards,
Hi @Fusilier2 ,
You can modify your measure to ensure that it always calculates the percentage relative to the total company headcount, regardless of any filters applied to Hierarchy (Department) or Team. The key is to use the ALL function on both fields to remove filters in the denominator. Here’s the revised DAX measure:
% of total company =
DIVIDE(
SUM('HR data'[Headcount (Incl. Casuals)]),
CALCULATE(
SUM('HR data'[Headcount (Incl. Casuals)]),
ALL('HR data'[Hierarchy], 'HR data'[Team])
)
)
This measure calculates the sum of headcount within the current filter context for the numerator. In the denominator, it uses CALCULATE with ALL('HR data'[Hierarchy], 'HR data'[Team]) to remove filters applied by slicers on both the Hierarchy and Team columns. As a result, if no filters are applied, the measure returns 100% (200/200). If you select Dept B, it returns 75/200 = 37.5%. If you select Dept B and Team C, it returns 25/200 = 12.5%. This ensures that even when filtering by both department and team, the measure correctly calculates the percentage as a fraction of the total company headcount.
Best regards,
Thank you so much!