Forum Discussion
Divide formula in power query
Hello!
I am new to powerbi especially in using power query add custom column. I need help what would be the correct formula for this. I have to divide the actual sales field to the planned sales. Tried creating one there is no syntax error
but after saving this error occurs
How did you manage this error?
Thank you
Hi juhoneyighot ,
The Error: Divide by zero error encountered means the divisor you are using, i.e. column msdyn_plannedsales, has a value of 0 or a null value. And in math calculations the divisor can't be 0.
Here is my sample data:Please change the M function into this:
if [plannedsales] = 0 or [plannedsales] = null then "There is no plannedsales" else [actualsales] / [plannedsales]You can change the content of "xxx" by yourself.
And the final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi juhoneyighot ,
audreygerred Thanks for your concern about this case!
And juhoneyighot , the two screenshots you put up are the same, what exactly is the Error you are experiencing? Can you resubmit the screenshots?In addition, your M function has been tested and there is no problem itself, so there may be a problem with the structure of your data, if you can please provide a sample data, thank you!
Best Regards,
Dino TaoAnonymous
I'm sorry for that. Here is the error.
Hi juhoneyighot ,
The Error: Divide by zero error encountered means the divisor you are using, i.e. column msdyn_plannedsales, has a value of 0 or a null value. And in math calculations the divisor can't be 0.
Here is my sample data:Please change the M function into this:
if [plannedsales] = 0 or [plannedsales] = null then "There is no plannedsales" else [actualsales] / [plannedsales]You can change the content of "xxx" by yourself.
And the final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi! Rather than creating a column in Power Query for this, I would make measures in Power BI using DAX.
Actual Sales = SUM('YourTable'[mydyn_actualsales])
Planned Sales = SUM9'YourTable'[mydyn_plannedsales])
% Comp Rev = DIVIDE([Actual Sales], Planned Sales])
Hello audreygerred ,
yes I already tried it using DAX but our boss advice that we will create all the formulas in Power Query.
Hope you could me with this.
Measures should be created in Power BI, not in Power Query. Some columns can be created in Power Query, but I wouldn't do that if it will result in a percent because when you try to aggregate it in Power BI, it will not be accurate - you'll have to sum it, average it, etc. Measures in Power BI are the way to go here.
