Forum Discussion
Grand total wrong when using =IF formula
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.
I've tried a lot of different things and the problem with creating a new column is that i have to pull data from two different databases, so the number in the column is the same all the way down through the rows. Any bright ideas :D?