Search code examples
ssasmdxcalculated-columns

SSAS Calc measure on filtering multi-members

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

Solution

  • 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;