The Avg function
returns the average of the values of cells in a specified set.
Returns: A number.
Parameters
- SetExpression
- Specifies a set of cells, the average of which is to be computed.
- NumericExpression
- Specifies a numeric expression, typically an MDX expression of
cell coordinates, that returns a numeric value.
- If a numeric expression is provided, the Avg function first evaluates the measure over the set, and then calculates the average based on the specified measure.
- If no numeric expression is specified, the Avg function averages each measure within the current query context.
Description
The Avg function calculates the average of the non-empty 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 non-empty cells in the specified set. If a set of empty tuples or the empty set is specified, the Avg function returns an empty value.
Example
The following example uses the Avg function to return the average profit amount per day for the years on record.
Query
WITH
MEMBER [Price Analysis].[Measures].[Average Profit Amount]
AS Avg(Descendants([Price Analysis].[Time].CurrentMember, -1, LEAVES),
[Price Analysis].[Measures].[Profit Amount])
SELECT {[Price Analysis].[Measures].[Average Profit Amount]} ON AXIS(0),
{[Price Analysis].[Time].[All Time (Calendar)].Children} ON AXIS(1)
FROM [Price Analysis]Result
| Time | Average Profit Amount |
|---|---|
| 2002 | 1025.386 |
| 2003 | 993.054 |
| 2004 | 1180.585 |
Related functions
The Avg function is part of a family of aggregating functions that includes Aggregate, Count, Sum, Min, and Max. The Average function is a synonym for the Avg function.