Search code examples
sql-servermdxmeasure

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])

Solution

  • 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])'