Aggregation functions

Aggregation functions operate on a set of values to return a single value. For example, they can provide the value of a key performance indicator (KPI) or measure based on a set of metrics.

The following table describes each aggregation function.

Aggregation Function	Description
Minimum	Returns the smallest value from the set of values.
Maximum	Returns the largest value from the set of values.
Sum	Adds the values and returns the total.
Count	Counts the number of values.
Average	Calculates the average of the set of values. When a counter or integer metric is aggregated as a measure using the Average function, the resulting value is an integer. To get decimal precision, you must aggregate using an underlying metric that is of type Decimal. Counters and integer metrics can be copied to a decimal metric if decimal precision is required.
Standard deviation	Calculates how widely spread the values are.
Count distinct (for cube measures only)	Counts the number of the unique values.
Median (for cube measures only)	Calculates the median of the set of values, which is the numeric value separating the higher half of the set of values from the lower half.
Variance (for cube measures only)	Calculates the variance of the set of values, which is another measure of how widely spread the values are.

Null values are not included in the calculation. For example, the Count function counts the number of values that are not null. Suppose that you create a metric that can have a value of either 0 or 100. You define a measure based on the metric and use the Count aggregation function. If there are three instances of the measure, one with a value of 0, a second with a value of 100, and a third with no value (null), the count is 2.

Similarly, the Average function averages the values that are not null. Suppose that you have an Average Order Amount measure that points to an Order Amount metric. At run time, you have three instances with order amounts of $100, $200, and null. The Average Order Amount measure ignores the null instance and has a value of $150.

Available aggregation functions

Only certain types of functions are valid based on the source you choose. The following table shows the valid functions based on the type of metric. Stopwatches and counters are included at the end of the table.

Type	Valid Aggregation Functions
Boolean	Count, Count distinct
Date	Minimum, Maximum, Count, Count distinct
DateTime	Minimum, Maximum, Count, Count distinct
Decimal	Average, Minimum, Maximum, Sum, Count, Standard deviation, Count distinct, Median, Variance
Duration	Average, Minimum, Maximum, Sum, Count, Count distinct, Median
Integer	Average, Minimum, Maximum, Sum, Count, Standard deviation, Count distinct, Median, Variance
String	Count, Count distinct
Time	Minimum, Maximum, Count, Count distinct
Stopwatch	Average, Minimum, Maximum, Sum, Count, Count distinct, Median
Counter	Average, Minimum, Maximum, Sum, Count, Standard deviation, Count distinct, Median, Variance