Forum Discussion

Pastrupgaard's avatar
Pastrupgaard
Icon for Helper I rankHelper I
8 years ago
Solved

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)...
  • GregoryMartin's avatar
    GregoryMartin
    8 years ago

    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

GregoryMartin's avatar
GregoryMartin
Frequent Visitor
8 years ago

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

Pastrupgaard's avatar
Pastrupgaard
Icon for Helper I rankHelper I
8 years ago

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