Home      Functions     Common Solutions      Contact     






MDX Functions:
 
MDX Functions

 
Common Solutions

Common Solutions
 
Resource Links

MDX Books
MDX Related Software
MDX Related Blogs
 
Other Links

Contact Us
 
Microsoft SQL Server Analysis Services Function Page
Function Name: Avg
Category: Statistical
Description: The Avg function returns the average of the cells in the supplied set. The average is computed using non empty values of a supplied measure or measures in a set.
Syntax: AVG( «Set»[, «Numeric Expression»] )
Technet Link: MDX Avg Function




The query below returns a set of date members in the Fiscal hierarchy where the Internet Order Count for that day is greater than or equal to the average daily Internet Order Count of all dates in the Fiscal hierarchy.
 

SELECT
      {[Measures].[Internet Order Count]} ON COLUMNS,
      FILTER({[Date].[Fiscal].[Date].Members}, [Measures].[Internet Order Count] >= AVG([Date].[Fiscal].[Date].Members,[Measures].[Internet Order Count])) ON ROWS
FROM
      [Adventure Works]
 
 

 
The query below shows the Internet Order Count along with the average daily Internet Order Count for all date members in the Fiscal hierarchy of the Date dimension.
 
WITH
      MEMBER [Measures].[AvgDailyIntOrdCount] AS
      AVG([Date].[Fiscal].[Date].Members,[Measures].[Internet Order Count])
SELECT
      {[Measures].[Internet Order Count], [Measures].[AvgDailyIntOrdCount]} ON COLUMNS,
      {[Date].[Fiscal].[Date].Members} ON ROWS
FROM
      [Adventure Works]
 
 

 
The following query shows the Internet Order Count along with the average daily Internet Order Count for each Month in the Fiscal hierarchy in the Date dimension.
 
WITH
      MEMBER [Measures].[AvgDailyIntOrdCount] AS
      AVG([Date].[Fiscal].CurrentMember.Children,[Measures].[Internet Order Count])
SELECT
      {[Measures].[Internet Order Count], [Measures].[AvgDailyIntOrdCount]} ON COLUMNS,
      {[Date].[Fiscal].[Month].Members} ON ROWS
FROM
      [Adventure Works]
 
 

 
 

 
Copyright 2008 by MDXpert.com.
 
Terms of Use                              Privacy Statement
 
Site Powered By - WinHost.Com