Forum Discussion
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 ...
v-alq-msft
Community Support
Community SupportHi, 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.