cancel
Showing results 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

Helper III

Hi, I would like to ask how to fix the Divided by zero error when loading the report? I suspect this calculation is the reason. I hope you can help me with it. Thank you

z Payments Calculated Amount =
SWITCH( TRUE(),
[Line Amount Types] = "Inclusive" && [Type] = "ACCREC",
(([Line Amount]-[Tax Amount]) * DIVIDE( [PaymentAmount] , [Invoice Total] )),
[Line Amount Types] = "Exclusive" && [Type] = "ACCREC",
(([Line Amount]) * DIVIDE( [PaymentAmount] , [Invoice Total] )),
[Line Amount Types] = "NoTax" && [Type] = "ACCREC",
(([Line Amount]) * DIVIDE( [PaymentAmount] , [Invoice Total] )),
[Line Amount Types] = "Inclusive" && [Type] = "ACCPAY",
(0-([Line Amount]-[Tax Amount]) * DIVIDE( [PaymentAmount] , [Invoice Total] )),
[Line Amount Types] = "NoTax" || [Line Amount Types] = "NoTax" && [Type] = "ACCPAY",
((0-[Line Amount]) * DIVIDE( [PaymentAmount] , [Invoice Total] )),
[Line Amount Types] = "Exclusive" || [Line Amount Types] = "NoTax" && [Type] = "ACCPAY",
((0-[Line Amount]) * DIVIDE( [PaymentAmount] , [Invoice Total] )),
0)

2 ACCEPTED SOLUTIONS
Resolver IV

You can try to pass in the 3rd parameter as 0 for the DIVIDE function. e.g.

``DIVIDE(<numerator>, <denominator> [,<alternateresult>])``
``````z Payments Calculated Amount =
SWITCH( TRUE()
, [Line Amount Types] = "Inclusive" && [Type] = "ACCREC"
, (([Line Amount]-[Tax Amount]) * DIVIDE( [PaymentAmount] , [Invoice Total], 0 ))
, [Line Amount Types] = "Exclusive" && [Type] = "ACCREC"
, (([Line Amount]) * DIVIDE( [PaymentAmount] , [Invoice Total], 0 ))
, [Line Amount Types] = "NoTax" && [Type] = "ACCREC"
, (([Line Amount]) * DIVIDE( [PaymentAmount] , [Invoice Total], 0 ))
, [Line Amount Types] = "Inclusive" && [Type] = "ACCPAY"
, (0-([Line Amount]-[Tax Amount]) * DIVIDE( [PaymentAmount] , [Invoice Total], 0 ))
, [Line Amount Types] = "NoTax" || [Line Amount Types] = "NoTax" && [Type] = "ACCPAY"
, ((0-[Line Amount]) * DIVIDE( [PaymentAmount] , [Invoice Total], 0 ))
, [Line Amount Types] = "Exclusive" || [Line Amount Types] = "NoTax" && [Type] = "ACCPAY"
, ((0-[Line Amount]) * DIVIDE( [PaymentAmount] , [Invoice Total], 0 )),0
)``````

Solution Sage

This issue you are having is occurring during the process of getting data using PowerQuery, not as a result of PowerBI or DAX.

It may be that the query is simple, making the query collapsed, which will be passed to the SQL database for calculation, and dividing by 0 inside the SQL database will report an error.

An easy way to do this is to use the "Detect Data type" feature to change the datatype of all fields immediately after the navigation step in PowerQuery, so that the query does not collapse, the calculations are handled in PowerQuery, and PowerQuery does not report an error when dividing by zero.

Or you can use try ... otherwise ... statement to protect the division operation, for example:

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

5 REPLIES 5
Helper III

Thank you for your response. I tried to do both of your suggestion but it didn't work on my report. And upon checking with our developer it seems it's an issue in our SQL database.

Solution Sage

Can you provide more details, such as error messages?

Helper III

Here is the error message

Solution Sage

This issue you are having is occurring during the process of getting data using PowerQuery, not as a result of PowerBI or DAX.

It may be that the query is simple, making the query collapsed, which will be passed to the SQL database for calculation, and dividing by 0 inside the SQL database will report an error.

An easy way to do this is to use the "Detect Data type" feature to change the datatype of all fields immediately after the navigation step in PowerQuery, so that the query does not collapse, the calculations are handled in PowerQuery, and PowerQuery does not report an error when dividing by zero.

Or you can use try ... otherwise ... statement to protect the division operation, for example:

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Resolver IV

You can try to pass in the 3rd parameter as 0 for the DIVIDE function. e.g.

``DIVIDE(<numerator>, <denominator> [,<alternateresult>])``
``````z Payments Calculated Amount =
SWITCH( TRUE()
, [Line Amount Types] = "Inclusive" && [Type] = "ACCREC"
, (([Line Amount]-[Tax Amount]) * DIVIDE( [PaymentAmount] , [Invoice Total], 0 ))
, [Line Amount Types] = "Exclusive" && [Type] = "ACCREC"
, (([Line Amount]) * DIVIDE( [PaymentAmount] , [Invoice Total], 0 ))
, [Line Amount Types] = "NoTax" && [Type] = "ACCREC"
, (([Line Amount]) * DIVIDE( [PaymentAmount] , [Invoice Total], 0 ))
, [Line Amount Types] = "Inclusive" && [Type] = "ACCPAY"
, (0-([Line Amount]-[Tax Amount]) * DIVIDE( [PaymentAmount] , [Invoice Total], 0 ))
, [Line Amount Types] = "NoTax" || [Line Amount Types] = "NoTax" && [Type] = "ACCPAY"
, ((0-[Line Amount]) * DIVIDE( [PaymentAmount] , [Invoice Total], 0 ))
, [Line Amount Types] = "Exclusive" || [Line Amount Types] = "NoTax" && [Type] = "ACCPAY"
, ((0-[Line Amount]) * DIVIDE( [PaymentAmount] , [Invoice Total], 0 )),0
)``````

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors