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

[egrospe17]

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!

[egrospe17]

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

[v-shex-msft · Accepted solution]

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

[egrospe17]

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