Clever Geek Handbook

MDX ability to calculate the ratio of sums from child levels

I am very new to MDX and have no idea how to approach this problem. I would appreciate any help, even if the pointers were pointing to the appropriate functions.

I need to be able to calculate the ratio of sums where the numerator is the trivial measure of SUM, but the denominator is the sum of the dimensional values. For those familiar with the insurance industry, this is a “per member per month” basis.

Our time dimension, at its lowest level (month), has a number of members associated with a number. When looking at the monthly level, our measure is the simple amount of "paid amount / members for this month". We don't have this problem by pre-calculating the relationship as a column in the fact table and defining a SUM measure on that column.

However, when viewing any snippet above the monthly level, this sum of odds no longer applies. Rather, we need an average paid amount. So if I look at the annual axis, I need to add the paid amounts for all months and divide that amount by the member amounts for each month. How can I get the number of members for each unique month and add them? We have a column in our fact table to store this monthly value, so the required value is there.

Pseudo-SQL to compute this denominator would be something like "select sum (members) as the denominator from a group of selected members by month".

Any ideas?

+2


source to share


1 answer


So it looks like which number of members

is a numeric property on Time

. Indeed, this should probably be the measure. But I know how it all happens. Anyway, I would create a calculated measure for this, for example:

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

Then I would make another measure, which would be the sum for whatever month you are 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 easy to answer 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 a design over it, [Member Count] would be a native measure, not a calculated one. The performance is much better and it makes life easier.

+1


source






More articles:


All Articles