MDX: Average based on any dimension on row axis

This was an interesting learning i did. There was a requirement were user wishes to calculate the average of sales based on any dimension that he wishes to pass. i.e. the calculated member need to be dynamic enough to calculate based on any dimension passed (not multiple).

To achieve this one need to use the Axis function provided by MDX

WITH MEMBER [Average] AS       [Measures].[Internet Sales Amount]/       SUM(Axis(1).Item(0).Item(0).Dimension.DefaultMember,[Measures].[Internet Sales Amount])       ,FORMAT='PERCENT' SELECT       {[Measures].[Internet Sales Amount],[Average]} on COLUMNS,       NONEMPTY       (             [Product].[Product Categories].[Category].MEMBERS,             [Measures].[Internet Sales Amount]       ) ON ROWS FROM       [Adventure Works]