Avg (Average)

Avg calculates the sum of a single value list and divides the result by the number of values in the list. This returns the average (arithmetic mean) of the listed values.

Avg is often used to create subtotals and metrics based on fact data. This is a group-value function.

To calculate an average while applying a weight to the values, see WeightedMean.

Syntax

Avg<Distinct, FactID

Where:

• Argument is an attribute, fact, or metric representing a list of numbers.
• Distinct is a TRUE/FALSE parameter that allows you to use all values in the calculation or to calculate using only the unique values.
• FactID is a parameter that forces a calculation to take place on a fact table that contains the selected fact.

Usage Notes

The Avg function ignores NULL values but uses zero values in its calculation.

Example

Example 1: In this simple example the average of a value list containing the values (4,9,2,9) is calculated as follows:

(4+9+2+9)/4 = 6

Example 2: This report obtains the average salary for employees in each region. The report contains the attributes Region, Employee, and Salary as well as the metric Average Salary. A report filter limits the regions displayed to Northeast and Southeast. The metric Average Salary is defined as follows: Avg(Salary){Region}

The function sums all salaries within a region and divides by the number of values, resulting in the regional average salary.

For further examples of using the Avg function, see Example 1: Transformed Fact and Example 2: Compound Metric, as well as Example 1: Average.