Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to Solution.
@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:
Hi @Rob_B ,
a measure like this should work:
@Rob_B , Create a new measure like
sumx(summarize(Table,table[Sector],table[Year],"_1",[Scenario Budget]),[_1])
@amitchandak thanks, this works! Now I'll have to study up on the Summarize function to understand why!
@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.
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?
User | Count |
---|---|
88 | |
72 | |
69 | |
65 | |
57 |
User | Count |
---|---|
95 | |
93 | |
91 | |
75 | |
69 |