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
kristel_tulio
Helper III
Helper III

DIvided by Zero error when loading

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

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
	)

 

View solution in original post

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.

 

xifeng_L_0-1715738007848.png

 

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

 

xifeng_L_1-1715738247011.png

 

 

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

 

Thank you~

 

View solution in original post

5 REPLIES 5
kristel_tulio
Helper III
Helper III

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
Solution Sage
Solution Sage

Can you provide more details, such as error messages?

Here is the error message

kristel_tulio_0-1715728258874.png

 

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.

 

xifeng_L_0-1715738007848.png

 

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

 

xifeng_L_1-1715738247011.png

 

 

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

 

Thank you~

 

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
	)

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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