Aggregation (data aggregation) is what we implicitly do each time when we drag some sort of dimension into the Pivot field of Values, while using explicit definition of an ad with a large set of data, use the filter of the formulas COUNTROWS and FILTER. When we create more complicated reports, it is useful to use aggregation functions (sums, averages, counts) to measure them.
Data aggregation formulas are SUM, AVERAGE and COUNT, and their syntax is the same as for the same Excel function. As an argument in DAX terms, they most often have a column. On the example we will show how we can make three measures in which these functions are used:
SumaKolicina: = SUM ([KOL])
AverageKolicina: = AVERAGE ([KOL])
NumberTransaction: = COUNT ([KOL])
In all three formulas, we used a column for the argument in which we recorded the quantities sold by retail, which are in our case listed in the Transaction table.
COUTROWS and FILTER
When looking for an average or counting elements that are not numerical data (a text containing numerous values), we can also use the functions AVERAGEA and COUNTA. They have the same syntax, and they work with a little different arguments. The COUNTBLANK function can be used to count empty rows in a given column, but most commonly using the formulas are COUNTROWS and FILTER.
To count the total number of rows in the table, we use the formula COUNTROWS, which for the argument has the name of the table: COUNTROWS (<table>). if we want to count how many rows in the base,
Product number: = COUNTROWS (Items [Name])
DAX has a class of formulas that performs data filtering. For the given arguments, one returns the filtered table as a result. Usually they are used in combination with some other functions, and their specificity is that they allow the creation of measures that ignore the constraints of existing filters in the Pivot table, as well as interactive filters such as Slicers and Timeline.
The most common filtering formula is FILTER, and its syntax is:
FILTER (<table name>, <filter condition>)
Usually used in combination with some of the data aggregation functions. For example, if we want to sum up the quantities of all beer sold, we would write:
SumaProdatihKolicinaPiva: = SUMX (FILTER (Items, [CLASSIFICATION] = “BEER”), [SUM KOL])
Formula FILTER is the first argument of the SUMX function and it must return all the rows in the Table Items where the CLASSIFICATION column has the value of “PIVA”. The second argument is implicitly given by the measure of the sum of the column. When the first argument is a filter, the second argument must be some measure.