View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.
I am sure just about everyone here has run into divide by zero issues. I have a standard way of handling divide by zero problems that I have found on this, and other sites. Here is code I found somewhere on a blog or forum, and began using:
Public Function DivideBy(ByVal Exp1, ByVal Exp2)
If Exp2 = 0 Then
DivideBy = 0
Else: DivideBy = Exp1/Exp2
This code is very easy to call from the expression builder, and provides a very consistent way to handle divide by zero errors. I recently was working on a report where I was using a reference to report textbox as the denominator. That textbox however, had some additional data in the expression that returned a N/A or something similar when the expression had some undesired results due to some bad data. This was fairly easy to deal with right in the expression, but I thought I might have to deal with this again and set out to modify my function. I ran into a slight problem in that the code window is not aware of all .Net name spaces. So I ended up having to use the full name space to get the function working. So here is my simple tweak that will handle both a divide by zero and a non numeric component in the equation.
Public Function DivideBy(ByVal Exp1, ByVal Exp2)If Microsoft.VisualBasic.IsNumeric(Exp1) And Microsoft.VisualBasic.IsNumeric(Exp2) Then If Exp2 = 0 Then DivideBy = 0 Else: DivideBy = Exp1/Exp2 End IfElse DivideBy = "N/A"End IfEnd Function
I hope you find this useful.
Other posts by DanielBowlin