District | Attendance Rate |
A | (null) |
A | 0.0% |
A | 94.5% |
With Member Total AS SUM(EXISTING(FILTER([Attending School].[District], NOT ISEMPTY(Measures.[Attendance Rate])),Measures.[Attendance Rate])
The result was still null. I tried a variety of things to remove the empty cells from the calculated measure or transform them into zero but with no success. The result was always the same, null for the sum instead of 94.5.
Consequently I moved away from manipulating the calculated member itself and came up with a solution in a subcube. I was able to remove those cells without an attendance rate from the cube before summing.
WITH MEMBER Total AS SUM(Existing([Attending School].[District]),Measures.[Attendance Rate])) SELECT Measures.Total ON COLUMNS, [Attending School].[District] ON ROWS FROM (SELECT [Enrolled From Date].[Academic Calendar].[Academic Year] ON COLUMNS, Filter([Attending School].[District] NOT ISEMPTY([Measures].[Attendance Rate])) ON ROWS FROM [EdDW]) WHERE [Enrolled From Date].[Academic Calendar].[Academic Year].&[20082009]