DAX help

[shaunguyver]

I have the following DAX formula that isn't working.

As you can see, I'm trying to sum a value in 'Headcount' by specifying corresponding values in neighbouring column 'BU'.

However, I also want to keep certain page-level filters.

Can anyone fix my formula? Thanks in advance

Total Headcount - Fixed = 
    CALCULATE(
	SUM(Occupancy[Headcount]),
		Occupancy[BU] IN {"Marketing", "Sales", "Head Office", "Support", "Ops", "IT", "Senior Team"}
) + 0
ALL('Real Estate'[Building]),
ALL('Real Estate'[City]),
ALL('Real Estate'[Country]),
ALL('Real Estate'[Region]))

[DaFloDo · Accepted solution]

hi @shaunguyver,

i am not sure, but you could try ALLEXCEPT within the CALCULATE

Total Headcount - Fixed = 
    CALCULATE(
		SUM(Occupancy[Headcount])
		, ALLEXCEPT('Real Estate', 'Real Estate'[Building],'Real Estate'[City], 'Real Estate'[Country],'Real Estate'[Region])
		, Occupancy[BU] IN {"Marketing", "Sales", "Head Office", "Support", "Ops", "IT", "Senior Team"}
) + 0

regards 

florian