topic Re: Calculate a value based on Department using a measure instead of calculated column in DAX Commands and Tips

Calculate a value based on Department using a measure instead of calculated column

egrospe17 — Wed, 08 May 2024 21:59:09 GMT

Hello,

I've been trying to search this and figure it out but I can't seem to find the correct answer. I have two tables.

(1) A list of Department

(2) A table of all of our budgeted hire and open requisitions by department

I need a count of Total Budget to Hire by department, as well as Total Open Requisition.

Total Budget to Hire is determined by the sum of "Budgeted Positions" and IF Req Type = "Budget".
Total Open Requisition is determined by the sum of "Number of Open Positions" and IF Req Type = "Open Requisition"

How do I accomplish this using a measure instead of a calculated column? The output I need is below

Thank you in advance!

Re: Calculate a value based on Department using a measure instead of calculated column

egrospe17 — Wed, 08 May 2024 22:03:19 GMT

EDIT: Sorry, the Total Open Requisition for Project/Program Management is 7, not 6.

Re: Calculate a value based on Department using a measure instead of calculated column

Anonymous — Thu, 09 May 2024 02:09:41 GMT

HI egrospe17,

You can try to use following measure formulas if they suitable for your requirement:

Total Budget = VAR list = VALUES ( Department[Department] ) RETURN CALCULATE ( SUM ( Table1[Budgeted Positions] ), FILTER ( ALLSELECTED ( Table1 ), [Department] IN list && [Req Type] = "Budget" ) ) Total Open Requisition = VAR list = VALUES ( Department[Department] ) RETURN CALCULATE ( SUM ( Table1[Number of Open Positions] ), FILTER ( ALLSELECTED ( Table1 ), [Department] IN list && [Req Type] = "Open Requisition" ) )

Regards,

Xiaoxin Sheng

Re: Calculate a value based on Department using a measure instead of calculated column

egrospe17 — Mon, 13 May 2024 17:28:23 GMT

This worked and exactly what I needed! Thanks so much!!