Calculate Sum Based on Multiple Criteria

[lencepeda]

I'm having some trouble calcuting Buget Revenue based on a filter from another column.  Please help.  Thank you

Currently, I have a table structured in this format:

Department  Commission   Fee1       Fee2

East                1,000               100       100

West               1,000               100       100

North             1,000                100       100

South              1,000                100      100

And existing Calculated Meassures

Revenue = SUMX(Table1, Table1[Commission] + Table1[Fee1] + Table1[Fee2])

RevenueLastYear = Calculate(Table1[Revenue], SAMEPERIODLASTYEAR (Datetable[Date]))

RevenueBudget = ?????????

Next, how do i calculate the Revenue Budget based on criteria from the Department column?

2% increase over same period last year for one department "East"

3% increase over same period last year for one department "West"

5% increase over same period last year for all other departments.

I am not sure if this should be present in a single measure or if i need to create a new budget table

Thanks again, any advice would be great.  

[Zubair_Muhammad · Accepted solution]

HI @lencepeda

Try this MEASURE

Revenue Budget =
SUMX (
    Table1,
    VAR increase =
        IF (
            Table1[Department] = "East",
            1.02,
            IF ( Table1[Department] = "West", 1.03, 1.05 )
        )
    RETURN
        ( Table1[Commission] + Table1[Fee1]
            + Table1[Fee2] )
            * increase
)

[lencepeda]

Zubair thank you so much for your reply.   I apologize for the extremely late response.  

This looks promising however the "RETURN" should be applied against LAST Years Revenue not this years.  

When i try to use REVENUE LAST YEAR i receive nothing.   No error No value.   Any thoughts?

Revenue Budget =
SUMX (
    Table1,
    VAR increase =
        IF (
            Table1[Department] = "East",
            1.02,
            IF ( Table1[Department] = "West", 1.03, 1.05 )
        )
    RETURN
        ( Table1[REVENUELASTYEAR] )
            * increase
)