Divide By Zero Error

Question

I have the following calculated measure in my cube:

([Measures].[Actual Line Total], [DIM ESTIMATE].[Function Category].[FEE]) / ([Measures].[Estimate Line Total], [DIM ESTIMATE].[Function Category].[FEE])

I'm trying to include an IIF statement in order to avoid divide by zero errors, but when I do I get an error message. Here's what I came up with:

IIF

(([Measures].[Estimate Line Total], [DIM ESTIMATE].[Function Category].[FEE])=0, NULL,

([Measures].[Actual Line Total], [DIM ESTIMATE].[Function Category].[FEE]) / ([Measures].[Estimate Line Total], [DIM ESTIMATE].[Function Category].[FEE])        

Error 4 MdxScript(ESTIMATES) (14, 1) Parser: The syntax for 'FORMAT_STRING' is incorrect.  0 0 

The measure is set up to format as a percentage. Not sure where the problem is...!

Any insight would be appreciated!

Thanks

---

A. M. Robinson

Answer 1

Hi ansonee,

Is the code you have given, complete? Because, when I tried to check it, there is a parenthses missing over there, please see indented code below -

IIF ( ([Measures].[Estimate Line Total], [DIM ESTIMATE].[Function Category].[FEE])=0, NULL, ([Measures].[Actual Line Total], [DIM ESTIMATE].[Function Category].[FEE]) / ([Measures].[Estimate Line Total], [DIM ESTIMATE].[Function Category].[FEE]) 

I can't see closing ")" for IIF. Please add ")" and see if it works for you.

Regards,
Santosh

---

http://microsoftbizintel.wordpress.com/

Answer 2

That was the ticket...wow.  ;-)

Thanks!!!

---

A. M. Robinson