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

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.

audreygerred's avatar
audreygerred
Super User
2 years ago

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.