Microsoft Office Tutorials and References

In Depth Information

Statistical Functions

Almost every PowerPivot data model needs to operate on aggregated data. DAX offers a set

of functions that aggregate the values of a column in a table and return a single value. We

call this group of functions
statistical functions
. For example, the expression

= SUM( Sales[Amount] )

calculates the sum of all the numbers in the Amount column of the Sales table. This expression

aggregates all the rows of the Sales table if it is used in a calculated column, but it considers

only the rows that are filtered by slicers, row, columns, and filter conditions in a pivot table

whenever it is used in a measure.

Note
The syntax of aggregation functions in a DAX expression is identical to the one used by

Excel formulas. Moreover, in Excel you can specify a range of cells by using the same syntax

table[column] used also by DAX.

In Table A-1 of the Appendix, you can see the complete list of statistical functions available

in DAX. The main four aggregation functions (SUM, AVERAGE, MIN, and MAX) operate only

on numeric values. These functions are identical to the corresponding Excel functions both in

name and in behavior: any data that is not numeric is ignored in the operation. In PowerPivot,

these functions work only if the column passed as argument is of numeric or date type. In

Figure 3-16, you can see an example of measures defined by these statistical functions.

FIguRE 3-16
Different measures using statistical functions aggregate LineTotal.

As in Excel formulas, DAX offers an alternative syntax to these functions to make the calculation

on columns that can contain both numeric and non-numeric values, such as a text column.

That syntax simply adds the suffix
A
to the name of the function, just to get the same name

and behavior as Excel. However, these functions are useful only for columns containing

TRUE/FALSE values because TRUE is evaluated as 1 and FALSE as 0. Any value for a text