DAX, the aggregation functions

Aggregation of data is a process that we’re implicitly performing every time we drag a reporting dimension in the field Values. However, when you create complex reports, it is useful to use functions for aggregation (SUM, AVERAGE, COUNT) to create measures that will be used in the report. Once thought out measures may be used as a building block to create complex DAX expressions.

The most commonly used functions for data aggregation are SUM, AVERAGE, and COUNT. They have the same syntax as homonymous Excel functions. As an argument in DAX expressions they usually have a column. For example, we will show how we can make three measures that use these functions:

SumaKolicina:=SUM([KOL])

ProsekKolicina:=AVERAGE([KOL])

BrojTransakcija:=COUNT([KOL])

In all three functions we have used column KOL (quantities of items sold) as an argument. When we drag these measures into a PowerPivot report we’ll get:

When you ask the average or number of the elements that are not numerical data (text which contains numerical values) you can use functions AVERAGEA and COUNTA. They have the same syntax, and working with slightly different arguments. COUNTBLANK function can be used to count the number of empty rows in a given column.