none
MDX - Calculate Average of Average with different groupings at each level RRS feed

  • 질문

  • Hi,

    I have been doing some calculations using MDX where I needed to calculate an average of an average. I managed this using a calculated member and a hierarchy on a single dimension.

    This time my requirements are slightly different in that I need to group by different dimensions at different levels. The T-SQL to do achieve what I need would look like this:

    SELECT	TopLevelHierarchyValue,
    	GroupByValue1,
    	AVG(ValueToAverage) AS ValueToAverage
      FROM	(SELECT	TopLevelHierarchyValue,
    		GroupByValue1,
    		GroupByValue2,
    		AVG(ValueToAverage) AS ValueToAverage
    	  FROM	(SELECT	TopLevelHierarchyValue,
    			BottomLevelHierarchyValue,
    			GroupByValue1,
    			GroupByValue2,
    			AVG(ValueToAverage) AS ValueToAverage
    		  FROM	DataSourceTable
    		  GROUP BY TopLevelHierarchyValue, BottomLevelHierarchyValue, GroupByValue1, GroupByValue2) a
    	  GROUP BY TopLevelHierarchyValue, GroupByValue1, GroupByValue2) b
      GROUP BY TopLevelHierarchyValue, GroupByValue1

    Using the calculated member and hierarchy I can calculate these values up to the outer group by but I don't know how to get rid of the additional dimension.

    Below is the MDX query I have so far:

    WITH MEMBER [Measures].[AverageOfAverage] AS
    IIF( ISLEAF( [DimensionName].[DimensionHierarchy].CURRENTMEMBER ),
             [Measures].[CalculateAverage],
             AVG( [DimensionName].[DimensionHierarchy].CURRENTMEMBER.CHILDREN,
                  [Measures].[AverageOfAverage] )
           )
    
    SELECT {(NONEMPTY(DimGroupByValue1.[Name].CHILDREN),
    	   NONEMPTY(DimGroupByValue1.[Name].CHILDREN)
    	 )} ON COLUMNS,
    	 {[Measures].[AverageOfAverage]} ON ROWS
    FROM [SourceData]
    WHERE [DimensionName].[TopLevelHierarchyValue].&[1]

    Can anyone tell me how I can get an average of the values output from this query?

    Thanks, Rich


    • 편집됨 jonesri 2012년 6월 14일 목요일 오후 2:03
    2012년 6월 14일 목요일 오후 2:02

답변

  • Hi all,

    I think I have figured it out.  Below is the code I have ended up with.

    WITH SET GroupByValues1 AS
    {
      [DimGroupByValue1].[Name].&[Value1],
      [DimGroupByValue1].[Name].&[Value2],
      [DimGroupByValue1].[Name].&[Value3]
    }

    MEMBER [Measures].[AverageOfAverage] AS
    IIF( ISLEAF( [DimensionName].[DimensionHierarchy].CURRENTMEMBER ),
      [Measures].[CalculateAverage],
      AVG( [DimensionName].[DimensionHierarchy].CURRENTMEMBER.CHILDREN,
        [Measures].[AverageOfAverage] )
    )
          
    MEMBER AverageOverInitialGrouping AS
    AVG([GroupByValues1], [Measures].[AverageOfAverage])

    SELECT {( NONEMPTY(DimGroupByValue1.[Name].CHILDREN) )} ON COLUMNS,
      {[Measures].[AverageOverInitialGrouping]} ON ROWS
    FROM [SourceData]
    WHERE [DimensionName].[TopLevelHierarchyValue].&[1]

    This seems to give me the expected results.


    Can anyone see any problems with this approach or suggestions for a better way of getting the same result?


    Thanks, Rich

    • 답변으로 표시됨 jonesri 2012년 6월 27일 수요일 오후 3:53
    2012년 6월 27일 수요일 오후 3:53

모든 응답

  • Hi jonesri,

    Thank you for your question.

    I am currently looking into this issue and will give you an update as soon as possible.

    Thank you for your understanding and support.

    Thanks,
    Bin Long

    TechNet Subscriber Support
    If you are
    TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Bin Long

    TechNet Community Support

    2012년 6월 18일 월요일 오전 9:37
    중재자
  • Hi,

    Has anyone got any suggestions for this?

    Thanks, Rich

    2012년 6월 27일 수요일 오전 7:43
  • Hi all,

    I think I have figured it out.  Below is the code I have ended up with.

    WITH SET GroupByValues1 AS
    {
      [DimGroupByValue1].[Name].&[Value1],
      [DimGroupByValue1].[Name].&[Value2],
      [DimGroupByValue1].[Name].&[Value3]
    }

    MEMBER [Measures].[AverageOfAverage] AS
    IIF( ISLEAF( [DimensionName].[DimensionHierarchy].CURRENTMEMBER ),
      [Measures].[CalculateAverage],
      AVG( [DimensionName].[DimensionHierarchy].CURRENTMEMBER.CHILDREN,
        [Measures].[AverageOfAverage] )
    )
          
    MEMBER AverageOverInitialGrouping AS
    AVG([GroupByValues1], [Measures].[AverageOfAverage])

    SELECT {( NONEMPTY(DimGroupByValue1.[Name].CHILDREN) )} ON COLUMNS,
      {[Measures].[AverageOverInitialGrouping]} ON ROWS
    FROM [SourceData]
    WHERE [DimensionName].[TopLevelHierarchyValue].&[1]

    This seems to give me the expected results.


    Can anyone see any problems with this approach or suggestions for a better way of getting the same result?


    Thanks, Rich

    • 답변으로 표시됨 jonesri 2012년 6월 27일 수요일 오후 3:53
    2012년 6월 27일 수요일 오후 3:53