The functions in this section are designed to supplement the Array capabilities of the software. Using these functions you can find the sum, the standard deviation, or the mean across dimensions of arrayed variables. The details for using these Builtin functions follow.
The ARRAYMEAN Builtin calculates the arithmetic mean across the elements in a one or two-dimensional Array. The calculation is defined as the sum of the elements, divided by the number of elements in the Array.
When using the ARRAYMEAN builtin to report the mean of a one-dimensional arrayed variable, you must choose [*] from the secondary dialog, which indicates that you want the mean for all the elements within the variable. The ARRAYMEAN calculation can be done in either an arrayed or a non-arrayed converter or flow.
When using the ARRAYMEAN builtin to report the mean of a two-dimensional arrayed variable, it is possible to calculate the mean three ways:
To report the mean across both dimensions of the arrayed input variable, select [*,*] in the secondary dialog. You can report the arrayed variable's mean in either an arrayed or a non-arrayed converter or flow.
Example:
Average Salary for All Employees = Salaries[*,*]
To report the mean across one dimension of the arrayed input variable, select the element name from the appropriate Dimension in the secondary dialog, and then select the [*] for the other Dimension.
Example:
Average Salary for Employees in Boston where your input variable is Salaries, your Dimensions are Location and Function =
Average Salary[Boston]=ARRAYMEAN(Salaries[Boston,*])
This setup will return a scalar value, consisting of the mean salary of all employees in Boston. You can make this calculation in either an arrayed or a non-arrayed converter or flow.
It is possible to roll-up the mean across (or down) one dimension of the two-dimensional arrayed variable into a one-dimensional arrayed variable. In defining the one-dimensional variable, select the Dimensions that you want to have reflect the roll-up. Define the ARRAYMEAN builtin function by selecting the Dimension name to define the dimension the variables have in common. Select [*] for the other dimension.
Example:
Average Salary across Location where your input variable is Salaries, and your Dimensions are Location and Function =
ARRAYMEAN(Salaries[Location,*])
This setup will provide the mean salary for all Employees in Boston in the Salaries[Boston] element of the variable, the mean for Chicago in the Salaries[Chicago] element, etc.
The ARRAYSTDDEV Builtin calculates standard deviation for all the elements in a given arrayed variable. The calculation can be done either in an arrayed or a non-arrayed variable.
When using the ARRAYSTDDEV builtin to report the standard deviation of a one-dimensional arrayed variable, you must choose [*] from the secondary dialog, which indicates that you want the standard deviation of all the elements within the variable.
When using the ARRAYSTDDEV builtin to report the standard deviation of a two-dimensional arrayed variable, it is possible to calculate it in three ways:
To report the standard deviation across both dimensions of the arrayed input variable, select [*,*] in the secondary dialog. You can report the arrayed variables standard deviation in either an arrayed or a non-arrayed converter or flow.
Example:
Standard Deviation in Salary = Salaries[*,*]
To report the standard deviation across one dimension of the arrayed input variable, select the element name from the appropriate Dimension in the secondary dialog, and then select the [*] for the other Dimension.
Example:
The Standard Deviation in Salaries of all Employees in Boston, where your input variable is Salaries, your Dimensions are Location and Function =
ARRAYSTDDEV(Salaries[Boston,*]).
This setup will return a scalar value, consisting of the standard deviation in salary of all employees in Boston. You can make this calculation in either an arrayed or a non-arrayed converter or flow.
It is possible to roll-up the standard deviation across (or down) one dimension of the two-dimensional arrayed variable into a one-dimensional arrayed variable. In defining the one-dimensional variable, select the Dimension that you want to have reflect the roll-up. Define the ARRAYSTDDEV builtin function by selecting the Dimension name to define the dimension the variables have in common. Select [*] for the other dimension.
Example:
The Standard Deviation in Salaries of all Employees across Location, where your input variable is Salaries and your Dimensions are Location and Function =
ARRAYSTDDEV(Salaries[Location,*])
This setup will return the standard deviation in salary for all Employees in Boston in the Salaries[Boston] element of the variable, the standard deviation for Chicago in the Salaries[Chicago] element, etc.
The ARRAYSUM Builtin calculates the sum of all the elements in a given one or two dimensional Array. The calculation can be done in either an arrayed or a non-arrayed variable.
When using the ARRAYSUM builtin to report the sum across a one-dimensional arrayed variable, you must choose [*] from the secondary dialog, which indicates that you want the sum across all the elements within the variable.
When using the ARRAYSUM builtin to report the sum across a two-dimensional arrayed variable, it is possible to calculate it in three ways:
To report the sum across both dimensions of the arrayed input variable, select [*,*] in the secondary dialog. You can report the arrayed variables sum in either an arrayed or a non-arrayed converter or flow.
Example:
Total Salary for All Employees = Salaries[*,*]
To report the sum across one dimension of the arrayed input variable, select the element name from the appropriate Dimension in the secondary dialog, and then select the [*] for the other Dimension.
Example:
Total Employees in Boston where your input variable is Employees, your Dimensions are Location and Function =
ARRAYSUM(Employees[Boston,*]).
This setup will return a scalar value, consisting of the total Employees in Boston. You can make this calculation in either an arrayed or a non-arrayed converter or flow.
It is possible to roll-up the sum across (or down) one dimension of a two-dimensional arrayed variable into a one-dimensional arrayed variable. In defining the one-dimensional variable, select the Dimension that you want to have reflect the roll-up. Define the ARRAYSUM builtin function by selecting the Dimension name to define the dimension the variables have in common. Select [*] for the other dimension.
Example:
Total Employees across Location where your input variable is Employees, your Dimensions are Location and Function =
ARRAYSUM(Employees[Location,*]).
This setup will return the total number of Employees in Boston in the Employees[Boston] element of the variable, the total Employees for Chicago in the Employees[Chicago] element, etc.