Forum Discussion
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')
| 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!
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
- amitchandakSuper User
Rob_B , Create a new measure like
sumx(summarize(Table,table[Sector],table[Year],"_1",[Scenario Budget]),[_1])
- Rob_BHelper I
amitchandak thanks, this works! Now I'll have to study up on the Summarize function to understand why!
- amitchandakSuper 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.
- MarcoPessinaResolver IV
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