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.

This argument can be repeated to provide multiple mappings.

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 cannot 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. One that contains the data to aggregate, and one that uses the AVERAGE function with this data.

The first is the Employee Salaries module. In this module, the Employees list is on rows. Several line items that contain information about each employee are on columns. The Seniority and Region line items have the list data type for the Seniority and Region lists respectively.


SeniorityRegionSalaryCount Value
Ben BlackbeardGrade 2Region A400001
Graham GreenacreGrade 1Region B220001
Lisa LimeyGrade 3Region C700001
Chris CollinsGrade 4Region B1400001
Miranda MauveGrade 2Region A500001
Tina TurquoiseGrade 4Region A800001
Peter PlumGrade 3Region A600001
Martina MustardGrade 2Region C500001
Brian BluebottleGrade 1Region D260001
William WhiteGrade 2Region A400001
Rita RedwoodGrade 1Region C240001
Oswald OchreGrade 4Region A800001
Brenda BeigeGrade 3Region C700001
Roger RedshanksGrade 1Region C240001
Sally SilverGrade 1Region D1000001
Gloria GoldGrade 3Region B620001

In this module, the Region list is on columns. On rows, a single line item contains a formula that uses AVERAGE to calculate the average salary in each region.


Region ARegion BRegion CRegion D

Average salary by region

Employee Salaries.Salary[AVERAGE: Employee Salaries.Region]

58,33374,66747,60063,000


In this module, the Seniority list is on columns. On rows, a single line item contains a formula that uses AVERAGE to calculate the average salary for each seniority.


Grade 1Grade 2Grade 3Grade 4

Average salary by seniority

Employee Salaries.Salary[AVERAGE: Employee Salaries.Seniority]

39,20045,00065,500100,000


In this example, the Region list is on columns, and the Seniority list is on rows. Line items are on pages, and the selected line item contains this formula:

Employee Salaries.Salary[AVERAGE: Employee Salaries.Region, AVERAGE:Employee Salaries.Seniority]

The formula calculates the average salary for employees for each combination of region and seniority.


Region ARegion BRegion CRegion D
Grade 1022,00024,00063,000
Grade 243,333050,0000
Grade 360,00062,00070,0000
Grade 480,000140,00000

This example uses a formula with the AVERAGE function with the Count Value line item in the Employee Salaries module 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 would count the total number of employees for each combination of region and seniority. The formula used is:

Employee Salaries.Count Value[AVERAGE: Employee Salaries.Region, AVERAGE:Employee Salaries.Seniority]


Region ARegion BRegion CRegion D
Grade 10111
Grade 21010
Grade 31110
Grade 41100