Avarage Calculations in MDX

Average is very important function to calculate Key Figures of your business. Here are some samples of Average-Avg(), starting with basic to more advanced calculations like deep diving into Moving Averages :

WITH
  MEMBER [Measures].AvgProductSales AS
    Avg
    (
      NonEmpty
      (
        [Product].[Product].[Product].MEMBERS
       ,[Measures].[Sales Amount]
      )
     ,[Measures].[Sales Amount]
    )
SELECT
  [Measures].AvgProductSales ON 0
 ,[Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works];

WITH
  MEMBER [Measures].AvgProductSales AS
    Avg
    (
      [Product].[Product].[Product].MEMBERS
     ,[Measures].[Sales Amount]
    )
SELECT
  [Measures].AvgProductSales ON 0
 ,[Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works];

WITH MEMBER [Measures].AvgProductSales
  AS Avg(EXISTING [Product].[Product].[Product].MEMBERS, [Measures].[Sales Amount])
SELECT {[Product].[Product Categories].[Subcategory].[Bike Racks]
       ,[Product].[Product Categories].[Subcategory].[Bike Stands]
       } ON 0
, [Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].AvgProductSales
;

// Avg over Filter
WITH
  MEMBER [Measures].AvgGrowingProducts AS
    Avg
    (
      Filter
      (
        [Product].[Product].[Product].MEMBERS
       ,[Measures].[Sales Amount] > ([Measures].[Sales Amount],ParallelPeriod([Date].[Calendar].[Month]))
      )
     ,[Measures].[Sales Amount]
    )
SELECT
  [Measures].AvgGrowingProducts ON 0
 ,Descendants
  (
    [Date].[Calendar].[Calendar Year].&[2003]
   ,[Date].[Calendar].[Date]
  ) ON 1
FROM [Adventure Works];

// Correct Avg over Filter
WITH
  MEMBER [Measures].Growth AS
    IIF
    (
      [Measures].[Sales Amount] > ([Measures].[Sales Amount] ,ParallelPeriod([Date].[Calendar].[Month]))
     ,[Measures].[Sales Amount]
     ,NULL
    )
   ,FORMAT_STRING = 'Currency'
  MEMBER [Measures].AvgGrowingProducts AS
    Avg
    (
      [Product].[Product].[Product].MEMBERS
     ,[Measures].Growth
    )
SELECT
  [Measures].AvgGrowingProducts ON 0
 ,Descendants
  (
    [Date].[Calendar].[Calendar Year].&[2003]
   ,[Date].[Calendar].[Date]
  ) ON 1
FROM [Adventure Works];

//

WITH MEMBER [Measures].AvgByDayOfWeek AS
  Avg(
   Exists(
    NULL:[Date].[Date].CurrentMember,
    [Date].[Day of Week].CurrentMember)
  , [Measures].[Sales Amount])
SELECT [Measures].AvgByDayOfWeek ON 0
--, [Product].[Product].[Product].MEMBERS ON 1
, [Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works]
;
WITH MEMBER [Measures].AvgByDayOfWeek AS
  Avg(
   Nest(
    NULL:[Date].[Date].CurrentMember,
    [Date].[Day of Week].CurrentMember)
  , [Measures].[Sales Amount])
SELECT [Measures].AvgByDayOfWeek ON 0
--, [Product].[Product].[Product].MEMBERS ON 1
, [Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works]
;

// ===========
// Running sum
// ===========

// Typical approach for running sum
WITH
  MEMBER [Measures].RunSales AS
    Sum
    (
      NULL : [Date].[Date].CurrentMember
     ,[Measures].[Sales Amount]
    )
SELECT
  [Measures].RunSales ON 0
 ,[Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works];

// Trying to optimize...
WITH
  MEMBER [Measures].RunSales AS
    Sum
    (
      Union
      (
        NULL : [Date].[Calendar].Parent.PrevMember
       ,
        [Date].[Calendar].FirstSibling : [Date].[Calendar].CurrentMember
      )
     ,[Measures].[Sales Amount]
    )
SELECT
  [Measures].RunSales ON 0
 ,[Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works];

// Forcing back into block mode
WITH
  MEMBER [Measures].RunSales AS
      Sum
      (
        NULL : [Date].[Calendar].Parent.PrevMember
       ,[Measures].[Sales Amount]
      )
    +
      Sum
      (
        [Date].[Calendar].FirstSibling : [Date].[Calendar].CurrentMember
       ,[Measures].[Sales Amount]
      )
SELECT
  [Measures].RunSales ON 0
 ,[Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works];

// Better caching...
WITH
  MEMBER [Measures].RunMonthSales AS
      Sum
      (
        NULL : [Date].[Calendar].CurrentMember
       ,[Measures].[Sales Amount]
      )
  MEMBER [Measures].RunSales AS
      ([Measures].RunMonthSales, [Date].[Calendar].Parent.PrevMember)
    +
      Sum
      (
        [Date].[Calendar].FirstSibling : [Date].[Calendar].CurrentMember
       ,[Measures].[Sales Amount]
      )
SELECT
  [Measures].RunSales ON 0
 ,[Date].[Date].[Date].MEMBERS ON 1
FROM [Adventure Works];