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

[jk8979356]

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])

[v-alq-msft]

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.

[lbendlin]

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.