Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
juhoneyighot
Helper II
Helper II

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 

juhoneyighot_0-1713884294459.png

but after saving this error occurs

juhoneyighot_1-1713884331415.png

How did you manage this error?

 

Thank you

1 ACCEPTED SOLUTION

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:

vjunyantmsft_0-1714005707367.png

vjunyantmsft_1-1714005801595.png

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:

vjunyantmsft_2-1714005957472.png


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.

View solution in original post

6 REPLIES 6
v-junyant-msft
Community Support
Community Support

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?

vjunyantmsft_0-1713921989866.png

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!

vjunyantmsft_0-1713922398153.png


Best Regards,
Dino Tao

@v-junyant-msft 

juhoneyighot_0-1713956809160.png

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:

vjunyantmsft_0-1714005707367.png

vjunyantmsft_1-1714005801595.png

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:

vjunyantmsft_2-1714005957472.png


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
Super User
Super User

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

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors