Tuesday, March 27, 2012

calculated measure problem(ssas 2005)

I have the data like following, count and Amount are the measures.

BU Tiear Designation Count Amount
BU1 1 PM 10 200
BU1 2 SE 20 300
BU2 1 PM 40 300

i need to have calculated measure like following

(10*200)+(20*30)+(40*300)/ (10+20+40)

pls tel me any way to do that
tried like following. but there is some error.pls help me to solve this.

CALCULATE;
CREATE MEMBER CURRENTCUBE.[Measures].[SumOfECount]
AS
sum
(
[Measures].[Employee Count]
) ,
VISIBLE = 1 ;
CREATE MEMBER CURRENTCUBE.[Measures].[multi]
AS

'[Measures].[Employee Count]*[Measures].[CPC Value]'
,
VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[Measures].[SumOfEMulti]
AS
sum
(
[Measures].[multi]
) ,

VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[Measures].[Calcula]
AS '[Measures].[SumOfEMulti]/[Measures].[SumOfECount]' ,
VISIBLE = 1 ;

No comments:

Post a Comment