Forum Discussion
Grand total wrong when using =IF formula
Hey guys. I’m new to Power BI and I’ve encountered a problem I can’t seem to fix. I’m not at liberty to post the actual numbers or a picture of the connections (everything should be connected though). I’ve got two different databases, which I’m pulling numbers from (see picture).
I got the following variables:
Tables:
Actual = revenue secured
Forecast = forecast
Measure:
Estimate = actual for the secured months and then forecast for the following months.
Formula for Estimate:
Estimate ($) = (if(sum(f_sales_forecast[Forecast])=0;sum(f_sales[Actual]);sum(f_sales_forecast[Forecast])))
The grand total only counts the forecasted period (49), and ignores the rest of the numbers that comes from Actual (100). What am I doing wrong?
Thanks for your help in advance
Hi Pastrupgaard
It seems you forgot a part in your formula. You wrote this one:
Estimate ($) = (if(sum(f_sales_forecast[Forecast])=0;sum(f_sales[Actual]);sum(f_sales_forecast[Forecast])))
But if you want the sum of Actual + Forcast, you should write something like:
Estimate ($) = (if(sum(f_sales_forecast[Forecast])=0;sum(f_sales[Actual]);sum(f_sales_forecast[Forecast])+sum(f_sales[Actual])))
and it will give you your Grand Total
6 Replies
Hi Pastrupgaard
It seems you forgot a part in your formula. You wrote this one:
Estimate ($) = (if(sum(f_sales_forecast[Forecast])=0;sum(f_sales[Actual]);sum(f_sales_forecast[Forecast])))
But if you want the sum of Actual + Forcast, you should write something like:
Estimate ($) = (if(sum(f_sales_forecast[Forecast])=0;sum(f_sales[Actual]);sum(f_sales_forecast[Forecast])+sum(f_sales[Actual])))
and it will give you your Grand Total
Hey GregoryMartin
You're totally right and that gets me a lot closer to the issue, but I forgot about the last part of the problem. So the Estimate should consist of the [actual] up until the [forcast] kicks in. What my formula does now, is take both the [actual] and the [forcast] for month 11 and 12, which gives me a wrong estimate.
Right now my grand total for Estimate is 155 and i would like it to give me 149 (10 months actual and 2 months forecast)
I hope that made sense.Br
Pastrupgaard
Yeap,
There weren't values on the first screen for [actual] on month 11 & 12. Now you've got some. I think, if you just want the [forecast] value in that case, you may create a new column in power BI using a formula looking like the first one you did.
NewColumn = if(f_sales_forecast[Forecast]=0;f_sales[Actual];f_sales_forecast[Forecast])
You'll have then a column with values for months 1 to 12 corresponding at your wish. Now you just need for your Grand Total cell to do a sum of this column. You should now have your 149 value.
Hello,
I recreated this using a simpler table, and i think you are not supposed to use sum on every step.
I got it working using the following formula:Estimate = IF(Table2[Forecast] = 0;Table2[Actual];Table2[Forecast])
Make sure you change the table names.
If it doesn't work for you, or if you have any questions please ask.
Good luck
