MDX ability to calculate ratio of sums from child levels

Question

I'm very much a novice at MDX, and have no idea how to approach this problem. I'd appreciate any help, even if just pointers to where relevant functions are explained.

I need to be able to calculate the ratio of sums, where the numerator is a trivial SUM measure, but the denominator is the sum of dimension-specific values. For those knowledgeable of the insurance industry, this is a "per member per month" calculation.

Our time dimension, at the lowest level (month), has an associated "number of members" numeric. When viewing the month level, our measure is the simple sum of "paid amount / members for that month". We've got this one no problem by pre-calculating the ratios as a column in the fact table, and defining a SUM measure on that column.

However, when viewing any slice above the monthly level, this sum of ratios no longer is applicable. Rather we need an average of paid amounts. So if I'm looking at a yearly axis, I need to add up the paid amounts for all months, and divide that sum by the sums of the members for each month. How can I grab the member count for each unique month and add them up? We have a column in our fact table for holding this monthly value, so the needed value is there.

Pseudo-SQL for calculating this denominator would be something like "select sum(members) as denominator from select members group by month".

Any ideas?

Answer 1

So it sounds like number of members is a numeric property on Time. Really, it should probably be a measure. But, I know how those things go. Anyway, I'd create a calculated measure for this, like so:

create member currentcube.[Measures].[Month Members] as
    ([Time].[YQM].CurrentMember.Properties("number of members")),
visible = 0;

Then, I'd make another measure which was the sum of that across whatever month you're in:

create member currentcube.[Measures].[Number of Members] as
    sum(({Descendants([Time].[YQM].CurrentMember, [Time].[YQM].[Month]}
        ,[Measures].[Month Members])),
visible = 1;

Now, your question is easily answered with the following formula:

create member currentcube.[Measures].[Paid per members per month] as
    ([Measures].[Paid])/([Measures].[Number of Members]),
visible = 1;

However, if I were me and had design over this, [Number of Members] would be a native measure, not a calculated one. The performance is a lot better, and it makes life a lot easier.