# Avg (MDX)

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

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.

Note: |
---|

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]