Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Fusilier2
Helper V
Helper V

Help with measure to ignore multiple filters

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:

% of total company =
DIVIDE(
    SUM('HR data'[Headcount (Incl. Casuals)]),
    CALCULATE(SUM('HR data'[Headcount (Incl. Casuals)]),ALL('HR data'[Hierarchy])))
 
BUT I also have a 'team' sub hierarchy within each Dept. So, for example, Each department is further categorised by e.g. Team A, Team B......
I want to change the measure so that if I also have a Team slicer and say select Dept A and Team B it wil being back the % of Team B in Dept A as a % of the total company.
 
So, as an example using the data above:
  Headcount
Dept ATeam A12
Dept ATeam B13
Dept BTeam C25
Dept BTeam D50
Dept CTeam E75
Dept CTeam F25
Total employees200
 If I were to select total Dept B the measure would return 75/200 = 37.5%
If I were to filter Dept B and Team C I want the measure to return 25/200 = 12.5%
Can't work out how to get a measure to do this.
 
 
 
 

 

 

1 ACCEPTED SOLUTION
DataNinja777
Super User
Super User

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,

View solution in original post

2 REPLIES 2
DataNinja777
Super User
Super User

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!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors