Avg (MDX)

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

Arguments

• 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.

Remarks

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.

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.

Examples

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]

See Also

Reference

MDX Function Reference (MDX)

Help and Information

Getting SQL Server 2005 Assistance

Change History