CODEWITHSUNDEEP
sql-servermdxmeasure
invertigo
invertigo

Reputation: 115

SQL 2008 - MDX Leaf Level Calculation with Parent Aggregates

What is the optimal way to create a calculated measure to compute a custom variance formula (I cannot use the default Variance function because it does not accommodate custom weights). So the leaf level formula will have 2 components one component will be a fact at the leaf level and the other component will be a parent aggregate at various levels in the hierarchy. So the formula is as follows: (Need this computed at each leaf level)

(a-b)^2

Where a is a leaf level fact [Measure].[Score] b is a parent level aggregate ([Dim].[Level].[All], [Measure].[MeanScore])

Upvotes: 2

Views: 1583

Answers (1)

invertigo
invertigo

Reputation: 115

I was able to address this by using CELL CALCULATION in conjunction with CalculationPassValue:

CELL CALCULATION SDCalc
 FOR '([Dim].[Level].[Leaf], [Measures].[SD])'
  AS 'CalculationPassValue(([Measures].[Score] - [Measures].[MeanScore])^2, 1)', CONDITION = 'NOT IsEmpty([Measures].[FactCount])'

Upvotes: 1

Related Questions