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
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?