Avg MDX function

Function Name : Avg
Category : Numeric
Description : Evaluates a set and returns the average of the non empty values of the cells in the set, averaged over the measures in the set or over a specified measure.
Syntax : Avg ( Set_Expression [ , Numeric_Expression ] )
Technet Link : MDX Avg Function

Example :-

Here we are calculating the Average of the Day Sales in the Month of April, 2008.
That is, taking the total of the Internet Sales during the month of April, 2008 and dividing it by total number of days in that month excluding the days when no Sales were made ( like days when the store was closed ).

WITH
Member [Measures].[Internet Sales - Month (Sum)]
AS Sum ( Descendants ( [Date].[Fiscal].[Month].&[2008]&[4], [Date].[Fiscal].[Date] ) , [Measures].[Internet Sales Amount] )
 
Member [Measures].[Count NonEmpty Days in Month]
AS Count ( NonEmpty ( { Descendants ( [Date].[Fiscal].[Month].&[2008]&[4], [Date].[Fiscal].[Date] ) } , [Measures].[Internet Sales Amount] ) )
 
Member [Measures].[Internet Sales - Month (Avg)]
AS Avg ( Descendants ( [Date].[Fiscal].[Month].&[2008]&[4], [Date].[Fiscal].[Date] ) , [Measures].[Internet Sales Amount] )
 
SELECT {
[Measures].[Internet Sales - Month (Sum)],
[Measures].[Count NonEmpty Days in Month],
[Measures].[Internet Sales - Month (Avg)]
} ON COLUMNS,
{ [Product].[Category].Members } ON ROWS
FROM
[Adventure Works]

Output :-

 
You can see here how the Avg function works. The value of $1,608,750.53, when divided by 30, gives $53,625.02
 

MDX Avg function

MDX Avg function

Practical Application :-

Suppose you need to design an SSRS (SQL Server reporting services) report which has a parameter called ” Fiscal Date “. And you have to show the Average of MTD ( Month-to-Date ) and YTD ( Year-to-Date ) calculation of Internet Sales Amount measure for all the Product Categories.

That is, you need to show following data on the report:
1) Average of the Sales on the Selected Date ( 30/04/2008 )
2) Average of the Sales during MTD period ( 01/04/2008 – 30/04/2008 )
3) Average of the Sales during YTD period ( 01/01/2008 – 30/04/2008 )

WITH
SET Day
AS StrToSet ( "[Date].[Fiscal].[Date].&[20080430]" )
 
Member [Measures].[Internet Sales - Day (AVG)]
AS Avg ( Day , [Measures].[Internet Sales Amount] )
 
Member [Measures].[Internet Sales - MTD (AVG)]
AS Avg ( PeriodsToDate ( [Date].[Fiscal].[Month], Day.item(0) ), [Measures].[Internet Sales Amount] )
 
Member [Measures].[Internet Sales - YTD (AVG)]
AS Avg ( PeriodsToDate ( [Date].[Fiscal].[Fiscal Year], Day.item(0) ), [Measures].[Internet Sales Amount] )
 
SELECT {
[Measures].[Internet Sales - Day (AVG)],
[Measures].[Internet Sales - MTD (AVG)],
[Measures].[Internet Sales - YTD (AVG)]
} ON COLUMNS,
{ [Product].[Category].Members } ON ROWS
FROM
[Adventure Works]

Output :-

MDX Avg function

MDX Avg function – Practical

Leave a Reply

Your email address will not be published. Required fields are marked *

*

* Copy This Password *

* Type Or Paste Password Here *

2,858 Spam Comments Blocked so far by Spam Free Wordpress

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Post Navigation