SSAS Calc measure on filtering multi-members

0
votes

I am trying to create a formula in Calculated measure but none is working correctly

Dimension Cell has multiple members including Control and Core. I also have measure called [Measures].[Rate] which is precalculated as percentage

I need to achieve below formula

(([Measures].[Rate] in [Cell].[ABC].&[Control] and [Cell].[ABC].&[Core])
 - ([Measures].[Rate] not in [Cell].[ABC].&[Control] and [Cell].[ABC].&[Core]))
 / ([Measures].[Rate] in [Cell].[ABC].&[Control] and [Cell].[ABC].&[Core])

Something like (A+B)/A but I am not able to calculate individual A and B.

Please note [Measures].[Rate] is in percentage format so cannot be summed up

EDIT

Also any idea if the same above has to be done with two slices from different dimension for single measure
eg.

([Measures].[Rate] in [Cell].[ABC].&[Control] and [Cell].[ABC].&[Core] also in [Data].[PQR].&[Yes])

or

    SUM (
     { [Cell].[ABC].&[Control] , [Cell].[ABC].&[Core] }
     ,{[Data].[PQR].&[Yes])}
     ,[Measures].[A]
        )

Is above workable or what will be its syntax

2
ssasmdxcalculated-columns

2 Answers

1
votes

Uhmm maybe something like:

CREATE MEASURE [Measures].[SpecialRate]
AS
AGGREGATE({[Cell].[ABC].&[Control],[Cell].[ABC].&[Core]}, [Measures].[Rate])
 - AGGREGATE(EXCEPT([Cell].[ABC].MEMBERS,{[Cell].[ABC].&[Control],[Cell].[ABC].&[Core]}), [Measures].[Rate])
 / AGGREGATE({[Cell].[ABC].&[Control],[Cell].[ABC].&[Core]}, [Measures].[Rate])
,VISIBLE = 1;
1
votes

The easiest way is to redesign the Cell dimension to include a new rollup column which includes both Control and Core into one rollup.

If that's not feasible and you have to do it in MDX then one way is to create a calculated measure on the dimension and then use it:

CREATE MEMBER CurrentCube.[Cell].[ABC].[All].[Control and Core] as 
AGGREGATE({[Cell].[ABC].&[Control], [Cell].[ABC].&[Core]})
,VISIBLE=0;

CREATE MEMBER CurrentCube.[Cell].[ABC].[All].[Not Control and Core] as 
AGGREGATE(-{[Cell].[ABC].&[Control], [Cell].[ABC].&[Core]})
,VISIBLE=0;

CREATE MEMBER CurrentCube.[Measures].[My Calc] as
(([Measures].[Rate], Cell].[ABC].[All].[Control and Core])
 - ([Measures].[Rate], Cell].[ABC].[All].[Not Control and Core]))
 / ([Measures].[Rate], Cell].[ABC].[All].[Control and Core]);