Search code examples
sql-serverssasmdxolapolap-cube

percentages in SSAS Data Cube


So I have a dimension called Attendance Mark that contains strings, such as 'PRESENT'. I have a measure 'Attendance Count' that counts the total entries. I came up with this (heavily inspired by Google searching) MDX calculation to get the percentage of Attendance Mark entries that read 'PRESENT':

Case
  When IsEmpty ([Measures].[Attendance Count])
  Then NULL
  Else ( [Attendance Mark].[Attendance Mark].&[PRESENT],
    [Measures].[Attendance Count]) /
  (  [Attendance Mark].[Attendance Mark],
    [Measures].[Attendance Count] )
End

This seems to work thought I'm not 100% confident in it, but it gets very strange results if I view that measure by the Mark dimension. As you'd expect PRESENT displays 100%, but you'd expect the others to show 0%- because there are 0 'PRESENT' values within their scope. Instead they're showing things like 865.29%, 1338.17% or 169.76%. Totally errant data.

Any ideas where I've gone wrong?

P.S Obviously you'd never display the percentage measure by the mark dimension in practical use anyway but I don't want it to confuse users, and it'd be useful to understand why this is happening anyway.


Solution

  • I broke the problem down into three simple parts: the numerator, the denominator, and the final percentage.

    The trick is that you want the calculation to respect all [PRESENT] and non-[PRESENT] members of [Attendance Mark] as well as the [All] member. So, [PRESENT] should be 100%, non-[PRESENT] should be null, and [All] should be the percentage of [PRESENT] out of the total.

    with
        member [Measures].[x] as
            aggregate(
                exists(
                    {[Attendance Mark].[Attendance Mark].&[PRESENT]},
                    {[Attendance Mark].[Attendance Mark].CurrentMember}
                ), [Measures].[Attendance Count]
            )
        member [Measures].[y] as
            aggregate(
                exists(
                    {[Attendance Mark].[Attendance Mark].CurrentMember},
                    {[Attendance Mark].[Attendance Mark].&[PRESENT]}
                ), [Measures].[Attendance Count]
            )
        member [Measures].[z] as
            [Measures].[x] / [Measures].[y], format_string = 'Percent'
    select
        {[Measures].[Attendance Count], [Measures].[x], [Measures].[y], [Measures].[z]} on 0,
        non empty [Attendance Mark].[Attendance Mark].Members on 1
    from
        [cube]
    

    where [Measures].[x] represents the numerator, [Measures].[y] represents the denominator, and [Measures].[z] represents the final percentage calculation. You can obviously do this in one single step. I broke it out for clarity.