DIvided by Zero error when loading

[kristel_tulio]

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)

[aduguid · Accepted solution]

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
	)

[kristel_tulio]

Hi @xifeng_L and @aduguid ,

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.

[xifeng_L]

Can you provide more details, such as error messages?

[kristel_tulio]

Here is the error message

[xifeng_L · Accepted solution]

Hi @kristel_tulio ,

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~

[aduguid · Accepted solution]

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
	)