Avg

Returns the average of values found in the tuples of a set.

Syntax

Avg ( set [,numeric_value_expression [,IncludeEmpty ] ])

Notes

The average is calculated as (sum over the tuples in the set of numeric_value_expr) / count, where count is the number of tuples in the set. Tuples with missing values are not included in count unless IncludeEmpty is specified.

The return value of Avg is #MISSING if either of the following is true:

Example

Empty Values Included in Calculation of the Average

The following query

WITH MEMBER
 [Market].[Western Avg]
AS
 'Avg ( [Market].[California]:[Market].[Nevada], [Measures].[Sales], INCLUDEEMPTY)'
SELECT
 { [Product].[Colas].children }
ON COLUMNS,
 { [Market].[West].children, [Market].[Western Avg] }
ON ROWS
FROM 
 Sample.Basic
WHERE
 ([Measures].[Sales], [Year].[Jan], [Scenario].[Actual])

returns the grid:

Western Avg for Caffeine Free Cola is 93 because the sales for all Western states is divided by 5, the number of states.

Empty Values Not Included in Calculation of the Average

The following query is the same as the above query, except that it does not use IncludeEmpty:

WITH MEMBER
 [Market].[Western Avg]
AS
 'Avg ( [Market].[California]:[Market].[Nevada], [Measures].[Sales])'
SELECT
 { [Product].[Colas].children }
ON COLUMNS,
 { [Market].[West].children, [Market].[Western Avg] }
ON ROWS
FROM 
 Sample.Basic
WHERE
 ([Measures].[Sales], [Year].[Jan], [Scenario].[Actual])

returning the grid:

Western Avg for Caffeine Free Cola is 155 because the sales for all Western states is divided by 3, the number of states that do not have empty values for Caffeine Free Cola.