Forum Discussion

Rob_B's avatar
Rob_B
Helper I
6 years ago
Solved

Sum What-if parameter value with column value with IF function

I'm trying to create a measure that adds a what-if parameter ('PA Cost Adjustment'[PA Cost Adjustment Value])

to a column ('Forecast'[Budget]) only when another column is a certain value ('Forecast'[Sector]="B").

 

In Excel, I would use a formula like this:

 

=IF([@Sector]="B",[@Budget]+PA Cost Adjustment Value,[@Budget])

 

where PA Cost Adjustment Value is a named range.

 

I tried creating a measure with an IF statement

 

Scenario Budget = IF(Contains(Forecast|Forecast[Sector]|"B")|SUM(Forecast[Budget])+'PA Cost Adjustment'[PA Cost Adjustment Value]|SUM(Forecast[Budget]))

 

which will calculate the correct value for a single year, but if you sum up all years, the what-if parameter is only added once instead of 4 times.

 

Here's a sample of the table ('Forecast')

SectorYearBudget
A2020100
A2021200
A2022300
A2023400
B20201
B20212
B20223
B20234
C20201000
C20212000
C20223000
C20234000

 

I'm sure I'm making a rookie mistake but I despite hours of googling solutions and watching videos, I can't figure it out! Thanks in advance for your help!

  • Rob_B , Create a new measure like

    sumx(summarize(Table,table[Sector],table[Year],"_1",[Scenario Budget]),[_1])

  • Hi Rob_B ,

    a measure like this should work:

     

    Scenario Budget = CALCULATE(
    SUMX(MyBudgetTable, IF(MyBudgetTable[Sector]="B", MyBudgetTable[Budget] + max('PA Cost Adjustment Value'[PA Cost Adjustment Value]), MyBudgetTable[Budget])
    )
    )
     
    Hope it helps.
    Cheers,
    Marco

5 Replies

  • Rob_B , Create a new measure like

    sumx(summarize(Table,table[Sector],table[Year],"_1",[Scenario Budget]),[_1])

    • Rob_B's avatar
      Rob_B
      Helper I

      amitchandak thanks, this works! Now I'll have to study up on the Summarize function to understand why!

      • amitchandak's avatar
        amitchandak
        Super User

        Rob_B , It is because or Row Context. every measure recalculates the grand total. When we use if or some comparison, it actually does not have value for grand total, to Grand total is different. This formula(Summarize) force grand total to calculated from a row level.

         

  • Hi Rob_B ,

    a measure like this should work:

     

    Scenario Budget = CALCULATE(
    SUMX(MyBudgetTable, IF(MyBudgetTable[Sector]="B", MyBudgetTable[Budget] + max('PA Cost Adjustment Value'[PA Cost Adjustment Value]), MyBudgetTable[Budget])
    )
    )
     
    Hope it helps.
    Cheers,
    Marco