MDX to sum over months with non-empty measure value for the month

Question

This has been stumping me and I'm not sure why it is so difficult. I have a measure that has empty values up until a certain point in time, and then starts having values. I want to get a monthly average, but only on those months that actually have a non-empty value. I also want my time range to be fixed for the query regardless of which months have values (for example, across the whole year)

Here is one variation of MDX that I tried:

WITH 
MEMBER Measures.MonthsWithSales AS
    (IIF( IsEmpty(([Time].[Month].CurrentMember,[Measures].[ProductsSold])), 0,  [Measures].[MonthCount]))

MEMBER  Measures.AvgProductsSold AS
    [Measures].[ProductsSold] /Measures.MonthsWithSales

SELECT
{
[Measures].[ProductsSold], [Measures].[MonthCount],
[Measures].[MonthsWithSales], [Measures].[AvgProductsSold]
} ON 0,

[Time].[Month].Members ON 1

FROM MyCube
WHERE [Time].[Year].&[2010-01-01T00:00:00]

which returns something like this:

    ProductsSold    MonthCount  MonthsWithSales AvgProductsSold
All                     1644    12  **12**  **137**
2010-01-01 00:00:00.000 (null)  1   0       (null)
2010-02-01 00:00:00.000 (null)  1   0       (null)
2010-03-01 00:00:00.000 (null)  1   0       (null)
2010-04-01 00:00:00.000 (null)  1   0       (null)
2010-05-01 00:00:00.000 (null)  1   0       (null)
2010-06-01 00:00:00.000 234     1   1       234
2010-07-01 00:00:00.000 237     1   1       237
2010-08-01 00:00:00.000 236     1   1       236
2010-09-01 00:00:00.000 232     1   1       232
2010-10-01 00:00:00.000 232     1   1       232
2010-11-01 00:00:00.000 233     1   1       233
2010-12-01 00:00:00.000 240     1   1       240

The problem is on the ALL row. I expect that the MonthsWithSales across the whole year returns 7 not 12 and that AvgProductsSold (per month with sales) is 234.86 not 137.

I realize that it's not doing what I want because it's using the MonthCount at the ALL level. But I do not know how to "sink into" the "per month dimension" to sum up the MonthCount only on the relevant months when it is calculating the "ALL".

Answer 1

I assumed you have 2 levels on the month hierarchy: one with the All member and one for the months.

MEMBER Measures.AvgProductsSold AS 
            IIf([Time].[Month].CurrentMember.Level.Ordinal = 0
                    , Avg([Time].[Month].CurrentMember.Children, [Measures].[ProductsSold])
                    , [Measures].[ProductsSold])

(You may have to replace [Time].[Month].CurrentMember.Children with [Time].[Month].Members)

The Avg function computes the average on the non empty values.