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

[Rob_B]

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')

Sector	Year	Budget
A	2020	100
A	2021	200
A	2022	300
A	2023	400
B	2020	1
B	2021	2
B	2022	3
B	2023	4
C	2020	1000
C	2021	2000
C	2022	3000
C	2023	4000

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!

[amitchandak · Accepted solution]

@Rob_B , Create a new measure like

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

[MarcoPessina · Accepted solution]

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

[amitchandak · Accepted solution]

@Rob_B , Create a new measure like

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

[Rob_B]

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

[amitchandak]

@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.

[Rob_B]

Thanks for explaining @amitchandak! @MarcoPessina's solution using SUMX with IF and MAX functions also works (had to remove the word "value" to refer to the what-if parameter column and not the measure). Is the a benefit to using summarize over the IF & MAX approach?