The AVERAGE aggregation function takes a set of values from a source module and returns the mean average in a result module.

For example, you can use the AVERAGE function to determine the average sales revenue generated by employees.

Values to average[AVERAGE: Mapping, AVERAGE: Mapping 2, etc.]

ArgumentData typeDescription
Values to averageNumberThe values to return the average of based on the Mapping argument.
MappingList, date, time period

The mapping that determines which values to return the average of. Must be a dimension in both the source and target modules.

You can enter multiple arguments as mappings. Arguments must be separated by a comma.

The AVERAGE function returns a numeric result. The result line item must share all dimensions used for the Mapping arguments.

'Employee Details'.Sales Revenue[AVERAGE:'Employee Details'.Seniority, AVERAGE: 'Employee Details'.Region]

In this example, the average sales revenue is returned for each combination of seniority and region.

Average can also be used as a summary method. You can use the summary method on line items with the number data type.

You can reference the Users list with the AVERAGE function. However, you can't reference specific users within the Users list as this is production data.

The dimensions of the Mapping argument must also be dimensions of the Values to average argument.

AVERAGE

These examples each use two modules. A source module that contains the data to aggregate, and a result module that uses the AVERAGE function with this data.

The source module, Employee salaries, has Employees list on rows, four line items that contain information about each employee are on columns:

  • Seniority, a List data type on the Grade list
  • Region, a List data type on the Region list
  • Salary, a Number data type
  • Count, a Number data type

This table shows the data for the Salary and Count line items:


SeniorityRegionSalaryCount value
AliceGrade 2Region A40,0001
BobGrade 1Region B22,0001
CarolGrade 3Region C70,0001
DanGrade 4Region B140,0001
EllieGrade 2Region D50,0001
FelixGrade 4Region A80,0001

This module has the list Region on columns, and a line item Average salary on rows. This line item contains a formula that uses AVERAGE to calculate the average salary in each region.


Region ARegion BRegion CRegion D
Average salary
Employee salaries.Salary[AVERAGE: Employee salaries.Region]
60,00081,00070,00050,000


This module has the list Grade on columns, and a line item Average salary on rows. This line item contains a formula that uses AVERAGE to calculate the average salary for each seniority level.


Grade 1Grade 2Grade 3Grade 4
Average salary by seniority
Employee salaries.Salary[AVERAGE: Employee salaries.Seniority]
22,00045,00070,000110,000


This module has the list Region on columns, the list Grade on rows, and a line item Average salary on pages.

Average salary with the formula: Employee salaries.Salary[AVERAGE: Employee salaries.Region, AVERAGE: Employee salaries.Seniority]


Region ARegion BRegion CRegion D
Grade 1022,00000
Grade 240,0000050,000
Grade 30070,0000
Grade 480,000140,00000

This module has the list Region on columns, the list Grade on rows, and a line item Count value on pages. This module is used to determine if there's an employee for each combination of region and seniority. If so, the formula returns a value of 1. If SUM were used, it'd count the total number of employees for each combination of region and seniority.

Count value with the formula: Employee salaries.Count value[AVERAGE: Employee salaries.Region, AVERAGE: Employee salaries.Seniority]


Region ARegion BRegion CRegion D
Grade 10100
Grade 21001
Grade 30010
Grade 41100