## Dividing by zero when creating a fraction as a calculated field

### July 1, 2010

7 comments

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.

Add comment
facebook linkedin twitter email

# 7 comments

1. PecasSeptember 10, 2010 ב 20:38

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

Reply
2. Steve TempletonOctober 21, 2010 ב 22:23

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

Reply
3. Ella MaschiachNovember 10, 2010 ב 14:55

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

Reply
4. RichardDecember 22, 2010 ב 19:06

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!

Reply
5. Ella MaschiachDecember 27, 2010 ב 23:27

Hi Richard,

I suggest you go over these two post:

Happy New Year,
Ella

Reply
6. JimMay 2, 2011 ב 18:24

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!!!

Reply
7. Shalin BhavsarNovember 18, 2011 ב 14:26

Hi Dear,
I am dividing values but not fisplaying..
which condition ll apply for it..

Reply