Calculaetd Measure Problem

[Waynest]

Hi

(Using MSAS 2005)

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, I dont know if its the calculated measure itself or the way I have set up the cube, although everything else seems to work ok.

Thanks

 

[irjones]

I'm not so familiar with MSAS 2005 but it looks like you have a divide by zero error you could try the following:-

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
,
0
,
[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 ;

Also member sets are usually bounded by {} rather than ()