Forum Discussion

shaunguyver's avatar
shaunguyver
Helper III
7 years ago
Solved

DAX help

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]))
Need Help
Show and Tell
  • DaFloDo's avatar
    DaFloDo
    7 years ago

    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

1 Reply

  • DaFloDo's avatar
    DaFloDo
    Resolver I
    7 years ago

    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