DCSIMG
Dividing by zero when creating a fraction as a calculated field - Ella Maschiach's BI Blog

Ella Maschiach's BI Blog

BI, Olap Technologies, Tools, System Analysis and Design

News

About Ella Maschiach

Business Inteligence

Dividing by zero when creating a fraction as a calculated field

I found myself recently trying to remember how a formula I wrote a few months ago in Report Builder. Doing the same thing twice, made me think I should put it on the blog, so that next time I won't need to remember it...
A user complained a report in report builder didn't run for her, as she divided two measures and the report would error when the denominator would equal zero.

The error message she got was:

Semantic query execution failed. Divide by zero error encountered.

----------------------------

Cannot read the next data row for the data set dataSet.

----------------------------

An error has occurred during report processing.

And a picture of that error would be:

 

So let's assume you have two measures: A and B and you need a new measure of A/B. Only thing is, B can sometimes be equal to zero.
I don't write

if(B=0, 0, A/B)

Because Report Builder evaluates A/B before checking if B=0, and so returns an error.
This is therefore the suggested formula:

if(B=0, 0, A / if(B=0, 1, B))

The beginning is pretty straight forward.
In the last part of the If function I check again if B=0. If it is, then I would hypothetically return A/1. We never actually return A/1 as if B=0, then the second case of zero is returned. The 1 in the second if function is just so we don't get an error in the evaluation of the formula if B=0 (as is the case in the wrong way of writing the formula). If B is different from zero, then we get A/B.

And that the way I could promise my user that she wouldn't be dividing by zero when she'd create a fraction as a calculated field.

Comments

Pecas said:

Thaaaaanks!!!!!!!! This blog resolved my problem....

# September 10, 2010 8:38 PM

Steve Templeton said:

What if you want to do this in the group or subtotal row.

# October 21, 2010 10:23 PM

Ella Maschiach said:

Hey Steve,

If you put a Sum() around the If() formula I mentioned before, the relevant subtotal or total will compute correctly as well.

All the best,

Ella

# November 10, 2010 2:55 PM

Richard said:

Hi Ella!

I have a problem when processing a cube in a the SSAS project, when process the cube, this showing me a error:

Done

Sending deployment script to the server...

Error -1056899072 : The following system error occurred:  Division by zero.

Deploy complete -- 1 errors, 0 warnings

========== Deploy: 0 succeeded, 1 failed, 0 skipped

Can you help me please?, I search this message error but I don't have any reference.

Thanks!

# December 22, 2010 7:06 PM

Ella Maschiach said:

Hi Richard,

I suggest you go over these two post:

Mosha Pasumansky
sqlblog.com/.../averages-ratios-division-by-zero-and-non-empty-behavior.aspx

Thomas Ivarsson
thomasivarssonmalmo.wordpress.com/.../division-by-null-and-zero-in-mdx

Happy New Year,
Ella

# December 27, 2010 11:27 PM

Jim said:

Very helpful!  Simple, elegant - never occurred to me that Report Builder would evaluate the A/B before the If/Then . . . counterintuitive but as you point out, easy to compensate for.  Thanks!!!

# May 2, 2011 6:24 PM

Shalin Bhavsar said:

Hi Dear,

I am dividing values but not fisplaying..

which condition ll apply for it..

# November 18, 2011 2:26 PM
Leave a Comment

(required) 

(required) 

(optional)

(required) 


Enter the numbers above: