Answered by:
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
 Edited by ansonee Monday, February 20, 2012 9:03 PM change
Monday, February 20, 2012 9:02 PM
Answers

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/
 Edited by Santosh Kumar Joshi Tuesday, February 21, 2012 1:58 AM
 Proposed as answer by Challen Fu Tuesday, February 21, 2012 6:24 AM
 Marked as answer by ansonee Tuesday, February 21, 2012 10:49 PM
Tuesday, February 21, 2012 1:56 AM
All replies

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/
 Edited by Santosh Kumar Joshi Tuesday, February 21, 2012 1:58 AM
 Proposed as answer by Challen Fu Tuesday, February 21, 2012 6:24 AM
 Marked as answer by ansonee Tuesday, February 21, 2012 10:49 PM
Tuesday, February 21, 2012 1:56 AM 
That was the ticket...wow. ;)
Thanks!!!
A. M. Robinson
Tuesday, February 21, 2012 4:39 PM