Forum Discussion

jk8979356's avatar
jk8979356
Regular Visitor
5 years ago

How do I nest an IF statement rather than create a new column for it?

I am using Power Query to take year to date sales data and compare it to our estimated total sales for the entire year. With this YTD vs full year comparison I am creating a "Forecast Factor" number that I then use to estimate the total item level sales for the full year. For example:

 

 

Year to date Sales: $900
Estimated total sales for year: $1000
Forecast factor: 900 / 1000 = 90%

Widget ABC YTD Sales: $10
Forecasted Widget ABC sales for full year: 10 / .90 = $11.11

 

 

 

Now where this formula breaks down is if my year to date sales exceeds my estimated total sales for year:

 

 

Year to date Sales: $1100
Estimated total sales for year: $1000
Forecast factor: 1100 / 1000 = 110%

Widget ABC YTD Sales: $10
Forecasted Widget ABC sales for full year: 10 / 1.1 = $9.09

 

 

 

So to account for this, I created an if statement that says if YTD sales are <= estimated total sales, then use (YTD sales / total estimated sales) to create a "Forecast Factor" that the Widget sales is then divided by (example 1 above). Else, return a value of 1 for the Forecast Factor.

 

 

= Table.AddColumn(#"Added Sales Forecast Column", "Forecast Factor", each if List.Sum(#"Changed Type2"[Amount]) <= #"2020forecast"{0}[Column2] then (List.Sum(#"Changed Type2"[Amount]) / #"2020forecast"{0}[Column2]) else 1)

 

 

 

I am currently putting this "Forecast Factor" in as a new column. My question is there anyway to skip adding the forecast factor as a new column and instead nest the IF statement directly into my Widget ABC forecasted sales column? Currently my Widget forecast code is. How would I change this code, to nest the Forecast Factor IF statement directly into it?

 

 

= Table.AddColumn(#"Added Qty Forecast Column", "Sales Forecast", each if [Amount] <= 0 then [Amount] else [Amount] / [Forecast Factor])

 

 

 

 

 

Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    You could do things like

     

    = Table.AddColumn(#"Added Qty Forecast Column", "Sales Forecast", each if [Amount] <= 0 then [Amount] else [Amount] / (if List.Sum(#"Changed Type2"[Amount]) <= #"2020forecast"{0}[Column2] then (List.Sum(#"Changed Type2"[Amount]) / #"2020forecast"{0}[Column2]) else 1))

     

    But what's the big issue here?  That additional column does no harm.  You can remove it again after the computation if you are concerned about memory. 

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, jk8979356 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a custom column with the following m codes.

    =let 
ytdsales = List.Sum(#"Changed Type"[YTD Sales]),
estimated = [Estimated total sales],
forecast = 
if ytdsales<=estimated 
then Value.Divide(ytdsales,estimated) else 1
in 
Value.Divide([Widget ABC YTD Sales],forecast)

     

    Result:

    1000>900: 10/0.9=11.11

    800<900: 20/1=20

    1200>900: 30/(900/1200)=40

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.