Forum Discussion

juhoneyighot's avatar
juhoneyighot
Helper III
2 years ago
Solved

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....
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

juhoneyighot's avatar
juhoneyighot
Helper III
2 years ago

Anonymous 

I'm sorry for that. Here is the error.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.