I have a many-to-many relationship between dimension D and measure M.
I need to create a generic calculated measure that would have the following formula: M/Σ(M), where Σ stands for the sum of all measure's facts that are associated with at least one member of a given dimension.
It's easy for other (one-to-many) dimensions, but getting Σ in many-to-many... well, if it was just a regular MDX query, that would be easy as well. I could just slice on all children of a dimension:
SELECT [Measures].[M] ON 0
FROM [MyCube]
WHERE [D].[All].CHILDREN
But how do I slice in a calculated measure?
A simplified example of what I would expect to work:
CREATE MEMBER [Measures].[Calc] AS
[Measures].[M] / ( DRILLUPLEVEL( AXIS( 1 ).ITEM( 0 ).HIERARCHY.MEMBERS ).ITEM( 0 ), [Measures].[M] )
WHERE DRILLUPLEVEL( AXIS( 1 ).ITEM( 0 ).HIERARCHY.MEMBERS ).ITEM( 0 ).CHILDREN
But of course, MDX doesn't support a WHERE
clause in MEMBER
definitions.
Please advise me how to approach this.
In the end, this is what I got:
CREATE HIDDEN [Total M] =
AGGREGATE(
DESCENDANTS(
AXIS( 1 ).ITEM( 0 ).DIMENSION.LEVELS( 0 ).ITEM( 0 ),
AXIS( 1 ).ITEM( 0 ).DIMENSION.LEVELS.COUNT
) - AXIS( 1 ).ITEM( 0 ).DIMENSION.LEVELS( 0 ).ITEM( 0 ),
[Measures].[M]
);
In short, I subtracted the root member from the set of the dimension's members. Aggregating over the remaining set gives proper value in many-to-many relationships. I can now use this measure in other measures to calculate M/Σ(M), etc.