Search code examples
mdx

MDX how do you return the Average over a number of months

I've been looking at AVG(MDX) and would like to be able to get an average over a number of months.

Using this as a simpla MDX:

    SELECT NON EMPTY 
    {[Measures].[Freight]} ON COLUMNS
 ,NON EMPTY 
    {[Due Date].[Calendar Month].[Calendar Month].MEMBERS} ON ROWS
FROM [Adventure Works Cube];

It will return:

enter image description here

Is it possible to return:

    Freight
August  108567.2463
February    208635.5203
July    103180.7446
March   209712.7297
November    103873.3682
Avg Freight 146793.9218

I have tried:

WITH MEMBER [Measures].[Average Freight] AS
    AVG([Due Date].[Calendar Month].[All], 
        [Measure].[Sale])
SELECT NON EMPTY 
    {[Measures].[Freight],
    [Measures].[Average Freight]} ON COLUMNS
 ,NON EMPTY 
    {[Due Date].[Calendar Month].[Calendar Month].MEMBERS} ON ROWS
FROM [Adventure Works Cube];

It just returns:

enter image description here

Solution

  • If you do this you do not get the average:

    WITH 
  MEMBER [Measures].[Average Freight] AS 
    Avg([Measures].[Freight Cost]) 
SELECT 
  {
    [Measures].[Average Freight]
   ,[Measures].[Freight Cost]
  } ON COLUMNS
 ,NON EMPTY 
    [Date].[Calendar].[Month].MEMBERS ON ROWS
FROM [Adventure Works];

    The average is just the same as the actual measure:

    enter image description here

    To do an average you need a SET to do the average over:

    WITH 
  MEMBER [Measures].[Average Freight] AS 
    Avg
    (
      [Date].[Calendar].CurrentMember.Children
     ,[Measures].[Freight Cost]
    ) 
SELECT 
  {
    [Measures].[Average Freight]
   ,[Measures].[Freight Cost]
  } ON COLUMNS
 ,NON EMPTY 
    [Date].[Calendar].[Month].MEMBERS ON ROWS
FROM [Adventure Works];

    So the average measure now makes sense:

    enter image description here