Export (0) Print
Expand All
Avg
IIf
Lag
Max
Min
Mtd
Qtd
Sum
Var
Wtd
Ytd
Expand Minimize
3 out of 9 rated this helpful - Rate this topic

Avg (MDX)

Updated: 12 December 2006

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.


Avg( Set_Expression [ , Numeric_Expression ] )
Set_Expression

A valid Multidimensional Expressions (MDX) expression that returns a set

Numeric_Expression

A valid numeric expression that is typically a Multidimensional Expressions (MDX) expression of cell coordinates that return a number.

If a set of empty tuples or an empty set is specified, the Avg function returns an empty value.

The Avg function calculates the average of the nonempty values of cells in the specified set by first calculating the sum of values across cells in the specified set, and then dividing the calculated sum by the count of nonempty cells in the specified set.

ms146067.note(en-US,SQL.90).gifNote:
Analysis Services ignores nulls when calculating the average value in a set of numbers.

If a specific numeric expression (typically a measure) is not specified, the Avg function averages each measure within the current query context. If a specific measure is provided, the Avg function first evaluates the measure over the set, and then the function calculates the average based on the specified measure.

Note   When using the CurrentMember function in a calculated member statement, you must specify a numeric expression because no default measure exists for the current coordinate in such a query context. To force the inclusion of empty cells, the application must use the CoalesceEmpty function or specify a valid Numeric_Expression that supplies a value of zero (0) for empty values. For more information about empty cells, see the OLE DB documentation.

The following example returns the average for a measure over a specified set. Notice that the specified measure can be either the default measure for the members of the specified set or a specified measure.

WITH SET [NW Region] AS 
   {[Geography].[State-Province].[Washington]
   , [Geography].[State-Province].[Oregon]
   , [Geography].[State-Province].[Idaho]}
MEMBER [Geography].[Geography].[NW Region Avg] AS 
   AVG ([NW Region]
   --, [Measures].[Reseller Gross Profit Margin]
   )
SELECT [Date].[Calendar Year].[Calendar Year].Members ON 0
FROM [Adventure Works]
WHERE ([Geography].[Geography].[NW Region Avg])

The following example returns the daily average of the Measures.[Gross Profit Margin] measure, calculated across the days of each month in the 2003 fiscal year, from the Adventure Works cube. The Avg function calculates the average from the set of days that are contained in each month of the [Ship Date].[Fiscal Time] hierarchy.

WITH MEMBER Measures.[Avg Gross Profit Margin] AS
   Avg(
      Descendants(
         [Ship Date].[Fiscal].CurrentMember, 
            [Ship Date].[Fiscal].[Day of Month]
      ), 
      Measures.[Gross Profit Margin]
   )
SELECT
   Measures.[Avg Gross Profit Margin] ON COLUMNS,
      [Ship Date].[Fiscal].[Month].Members ON ROWS
FROM
   [Adventure Works]
WHERE
   ([Ship Date].[Fiscal Year].[FY 2003])

The following example returns the daily average of the Measures.[Gross Profit Margin] measure, calculated across the days of each semester in the 2003 fiscal year, from the Adventure Works cube.

WITH MEMBER Measures.[Avg Gross Profit Margin] AS
   Avg(
      Descendants(
         [Ship Date].[Fiscal].CurrentMember, 
            [Ship Date].[Fiscal].[Day of Month]
      ), 
      Measures.[Gross Profit Margin]
   )
SELECT
   Measures.[Avg Gross Profit Margin] ON COLUMNS,
      [Ship Date].[Fiscal].[Fiscal Year].[FY 2003].Children ON ROWS
FROM
   [Adventure Works]

Release History

12 December 2006

Changed content:
  • Clarified impact of nulls.
  • Added another example.
  • Fixed broken example

17 July 2006

Changed content:
  • Updated syntax and arguments to improve clarity.
  • Added updated examples.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.