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];

MDX Studion Online

Mosha Pasumansky, father of OLAP and MDX

Mosha Pasumansky is one of the inventors of the MultiDimensional eXpressions (MDX) language, a query language for online analytical processing (OLAP) databases. Pasumansky is also one of the architects of the Microsoft Analysis Services, and an OLAP expert. Mosha Pasumansky is well known in the OLAP community for his Microsoft OLAP information website which contains a collection of technical articles and other resources related to Microsoft OLAP and Analysis Services. He also has a blog dedicated to MDX and Analysis Services. He spoke at Microsoft conferences such as TechEd and PASS, and he published the book Fast Track to MDX. As of 29 December 2009, Mr. Pasumansky had shifted his focus[1] to Bing, the Microsoft Search Engine, and is no longer maintaining his active stewardship of the BI Community. We are going to miss him and his articles regarding OLAP, MDX and Business Intelligence in general.

Source Wikipedia

This is an online version of the MDX Studio product build by Mosha. The full version can be downloaded from http://www.mosha.com/msolap/mdxstudio.htm For discussion, bug reports, feature suggestions etc - please visit our blogg here. Here is the link to MDX Studio Online: http://mdx.mosha.com/default.aspx

Business Intelligence

This Blog will contain information about Business Intelligence, Data Mining, Data Modeling and Data Science including tutorials, white papers, important updates and business cases mostly based in Microsoft platform.
Our intention is open a discussion blog where experts can talk generally about Business Intelligence or can exchange views for particular problems that they experienced. We are going to talk about different BI platforms their advantages and disadvantages, against Microsoft Platform.
Analytics will be the main topic, SSAS will be the most discussed tool and SQL/MDX/DMX will be the most used scripts to explain many of the problems that BI Professionals face every day.
MDX and DMX will be part of this blog too. Advanced calculations that we can handle with MDX and problems for improving time in reporting large data warehouse calculations over dimensions.
Dimensional databases vs relational, OLAP Cubes, algorithms for time improvement will rich our Blog.
You will be updated with podcast, white papers, analysis and links that are important to our auditorium.

Best regards,
Besim Ismaili
Creator of the Blog