Avg

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

Syntax

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

set Set specification.
numeric_value_expression Numeric value expression. Avg() sums the numeric value expression and then takes the average.
IncludeEmpty Use this keyword if you want to include in the average any tuples with #MISSING values. Otherwise, they are omitted by default.

Description

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:

Cola Diet Cola Caffeine Free Cola
California 678 118 145
Oregon 160 140 150
Washington 130 190 #Missing
Utah 130 190 170
Nevada 76 62 #Missing
Western Avg 234.8 140 93

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:

Cola Diet Cola Caffeine Free Cola
California 678 118 145
Oregon 160 140 150
Washington 130 190 #Missing
Utah 130 190 170
Nevada 76 62 #Missing
Western Avg 234.8 140 155

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.

©2004 Hyperion Solutions Corporation. All Rights Reserved.
http://www.hyperion.com