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.

audreygerred's avatar
audreygerred
Super User
2 years ago

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

 

  • 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.