Percent Contribution for ALL Hierarchies

I ran across an interesting question from the MSDN forum which challenged my MDX ability making it worth sharing. The question posed on the forum was, "The user requirement is ability to get cross tabulations among the dimensions. For example, they may want the proportion of cases in each year at a given location or the proportion of cases in each year by product? In this case, I'd need a generalized contribution precentage formula because I'll never be sure which dimension(s) users may cross-tab."

This type of "generic" contribution precentage calculation is useful for reports where a users experience is "guided", however, I'd caution against using this type of percentage calculation for ad-hoc users because they'll likely end up with slices of the data which affect percentages displayed not realizing all the slices in effect.

The tricks to this calculated member are: (1) Avoid division by zero error, and (2) Use the .DefaultMember when the .CurrentMember is the highest member in the hierarchy.

Trick (1) is taken care of in the IIF() statement which returns NULL if ANY (.CurrentMember, Measure) tuple returns empty. Trick (2) is handled in the denominator of the percentage calculation by detecting the .CurrentMember level and using DefaultMember when appropriate.

WITH MEMBER Measures.[Percent Customer Count] AS
'
IIF(ISEMPTY([Measures].[Customer Count]), NULL,
IIF([Product].[Product Categories].CurrentMember.Level.Ordinal <> 0 AND
ISEMPTY(([Product].[Product Categories].CurrentMember.Parent, Measures.[Customer Count])), NULL,
IIF([Date].[Calendar].CurrentMember.Level.Ordinal <> 0 AND
ISEMPTY(([Date].[Calendar].CurrentMember.Parent, Measures.[Customer Count])), NULL,
IIF([Customer].[Customer Geography].CurrentMember.Level.Ordinal <> 0 AND
ISEMPTY(([Customer].[Customer Geography].CurrentMember.Parent, Measures.[Customer Count])), NULL,
// Do the calculation
([Product].[Product Categories].CurrentMember
,[Date].[Calendar].CurrentMember
,[Customer].[Customer Geography].CurrentMember
,[Measures].[Customer Count])
/
(IIF([Product].[Product Categories].CurrentMember.Level.Ordinal = 0, [Product].[Product Categories].DefaultMember, [Product].[Product Categories].CurrentMember.Parent)
,IIF([Date].[Calendar].CurrentMember.Level.Ordinal = 0, [Date].[Calendar].DefaultMember, [Date].[Calendar].CurrentMember.Parent)
,IIF([Customer].[Customer Geography].CurrentMember.Level.Ordinal = 0, [Customer].[Customer Geography].DefaultMember, [Customer].[Customer Geography].CurrentMember.Parent)
,[Measures].[Customer Count])
)
)
)
)
' ,SOLVE_ORDER=10, FORMAT_STRING="#,0.0%"


SELECT
{[Measures].[Customer Count], Measures.[Percent Customer Count]} ON COLUMNS
,{[Product].[Product Categories].[All]
,DESCENDANTS([Product].[Product Categories].[All], [Product].[Product Categories].[Category])} ON ROWS
FROM [Adventure Works]
WHERE(
[Date].[Calendar].[Calendar Year].&[2003]
,[Customer].[Customer Geography].[Country].&[United States]
)

The above example works with the AdventureWorks DW database, but only on three of the hierarchies. Which implies the biggest drawback of this implementation technique - if you have more than 5-6 hierarchies the nesting of the IIF() and maintenance of the MDX is tedious and error prone. However, if you're diligent it will work.