Calculated Member not working

Please could somebody help me to understand what is wrong with the following calculated member definition (tried many variations of this now, always the same problem)

CREATE MEMBER CURRENTCUBE.[MEASURES].RatioVal

AS [Measures].[Val] / SUM(([Tbl Acc Codes].[Tbl Acc Lvl1].&[OUR],[Tbl Acc Codes].[Tbl Acc Lvl2].&[CORE],[Tbl Acc Codes].[Tbl Acc Lvl3 - Lvl 3 Code].&[INCOME]),[Measures].[Val]),

NON_EMPTY_BEHAVIOR = { [Val] },

VISIBLE = 1 ;

All I want is to show in cube browser alongside Measures.Val is another member called RatioVal.

RatioVal should be Val divided by the sum of Val with a lvl1 of 'OUR', a lvl2 of 'CORE' and a lvl3 of 'INCOME'

At the moment when I browse my cube I get a RatioVal subtotal for each lvl1, and each lvl2 but anything deeper into the hierarchy than that shows 1.#INF

Please give me any clues you can, this is driving me nuts!

Thanks

Hi Wayne,

The 1.#NF error indicates division by zero - so I would guess that your problem is caused by the expression

SUM(([Tbl Acc Codes].[Tbl Acc Lvl1].&[OUR],[Tbl Acc Codes].[Tbl Acc Lvl2].&[CORE],[Tbl Acc Codes].[Tbl Acc Lvl3 - Lvl 3 Code].&[INCOME]),[Measures].[Val]),

evaluating to zero at the lower levels. Without knowing more about your cube I can't say why this might be though.

Even after you've fixed this, if you think that there's ever chance that this expression could still evaluate to zero, then you should put add a test to your calculated member to handle this scenario, something like this:

CREATE MEMBER CURRENTCUBE.[MEASURES].RatioVal

AS

IIF(SUM(([Tbl Acc Codes].[Tbl Acc Lvl1].&[OUR],[Tbl Acc Codes].[Tbl Acc Lvl2].&[CORE],[Tbl Acc Codes].[Tbl Acc Lvl3 - Lvl 3 Code].&[INCOME]),[Measures].[Val]) = 0, NULL,

[Measures].[Val] / SUM(([Tbl Acc Codes].[Tbl Acc Lvl1].&[OUR],[Tbl Acc Codes].[Tbl Acc Lvl2].&[CORE],[Tbl Acc Codes].[Tbl Acc Lvl3 - Lvl 3 Code].&[INCOME]),[Measures].[Val])

),

NON_EMPTY_BEHAVIOR = { [Val] },

VISIBLE = 1 ;

HTH,

Chris