Help with measure to ignore multiple filters

[Fusilier2]

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 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

 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.

[DataNinja777 · Accepted 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,

[Fusilier2]

Thank you so much!