Search code examples
ssasmdx

MDX script to ignore zero's when calculating average

I am trying to use MDX to create a calculated member for computing averages. I have two measures, [High School GPA] and [Has High School GPA]. [Has High School GPA] is set to 1 if [High School GPA] is numeric, else it's 0. So, in the past, the following calculated member worked just fine.

case
    when [MEASURES].[Has High School GPA] is null 
        or [MEASURES].[Has High School GPA] = 0
then null
    else
        [MEASURES].[High School GPA] / [MEASURES].[Has High School GPA]
end

Now, I need to exclude [High School GPA] = 0.00 from my average calculation even though [Has High School GPA] will be equal to 1. So, I modified my script as follows.

case
    when [MEASURES].[Has High School GPA] IS null 
        or [MEASURES].[Has High School GPA] = 0 
        or [MEASURES].[High School GPA] = 0
then null
    else
        [MEASURES].[High School GPA] / [MEASURES].[Has High School GPA]
end

However, my average is still including the records with [High School GPA] = 0.00. Any suggestion on how I can fix this?

Solution

  • The easiest way to solve this is to create a computed column on that table in the DSV. The calc should be:

    NULLIF(GPA,0)

    Then on the measures that use that column make sure the NullHandling property is set to Preserve. That should ensure both 0 and Null end up Null.

    By the way, in MDX the IS NULL syntax isn't what you want. Use IsEmpty() instead. But in this case it's redundant because Null = 0 in MDX. So the proper MDX should be:

    case
    when [MEASURES].[Has High School GPA] = 0 
        or [MEASURES].[High School GPA] = 0
then null
    else
        [MEASURES].[High School GPA] / [MEASURES].[Has High School GPA]
end