Dividing by zero when creating a fraction as a calculated field

July 1, 2010

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

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

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. 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
  6. Shalin BhavsarNovember 18, 2011 ב 14:26

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

    Reply