SUM( ) with multiple CUBEVALUE( )'s issue

HI.

I'm trying to do a SUM of two CUBEVALUE's in a Cell and i am Getting a #VALUE result every time one of them returns a #VALUE. I've tried encapsulating the individual CUBEVALUE in a IFERROR(..,0) but that doesn't work. It is like excel is not recognizing the "0" value of the IFERROR(..,0). The cell is set as a number format as well.

Here is an example:

=
SUM(
IFERROR(CUBEVALUE("ThisWorkbookDataModel","[Measures].[Sum of Amount]",...filters),0)
,
IFERROR(CUBEVALUE("ThisWorkbookDataModel","[Measures].[Sum of Amount]",...diff filters),0)
)

.. and one returns #VALUE

Any ideas?

Thanks

Without seeing a sample of your work, the only thing I can think of that overrides the IFERROR is if your formula pulls back a string.

String + anything = #VALUE.

Hi Both,

I tried putting iserror in front of the sum which returns 0 - now this is "presentable" but the summing should not be 0. hence my question

What i can't understand is why the return value works when both cubevalues return a non-zero value, but when one does not - it doesn't work. I guess what daffodil11 is suggesting has something to do with it but that is why i put and IFERROR for each cubevalue (so it returns 0)..

Ok Found a "Solution". I guess CUBEVALUE sends "no data" so it actually is not an error. So i had to use IF Function within the Measure when using PowerPivot's "Calculated Fields"

So a calculated field for Sum of Revenue would be
=IF(SUM([Revenue Amount])=0,0,SUM([Revenue Amount]))

There may be better/efficient way but did the trick

Thanks!