Sign in or start a free trial to avail of this feature.
4. Aggregate Functions
Aggregate functions are among the simplest functions in DAX, and most of them should be familiar to you from Excel.
- Aggregate functions perform one calculation on a single column of data
- Many common aggregate functions are similar to Excel functions, including SUM, AVERAGE and so on
- The primary use of these functions is as building blocks in more complicated measures
- Alt H, PT, T – Create Pivot Table
- Alt B, F – Create measure (in the Excel window)
- Alt H, 9 – Decrease decimal places
In the previous lesson, we looked at how to create new tables and relationships. In this lesson, we'll look at the aggregate functions available in DAX.
Although this term might sound confusing, these are probably the simplest DAX functions and are very similar to common aggregate functions in Excel, such as SUM, AVERAGE, MIN, and MAX.
We're going to create a few measures to analyze sales by product. We'll use measures instead of calculated columns since we'll be aggregating data that already exists in the model.
We'll create these measures in the calculation area of the Pharma Data table.
We'll start with a measure for Total Sales.
We'll select an empty cell, call the measure Total Sales and apply the SUM function to the Sales column from the Pharma Data table. We can accept auto-complete suggestions using Tab or Enter, and press Enter to create the measure.
We'll now create a new PivotTable, with the shortcut Alt, H, PT, T.
We'll put it on a new sheet and then add the Product name field and the new Total Sales measure to the table. You can see that the measure is automatically computed for each product. Next, we'll look at the Average Sales.
Instead of going into Power Pivot, let's create a measure here in the Excel window.
We'll navigate to the Power Pivot tab, select Measures, and then New Measure.
This opens up a Measures window, allowing us to create a measure directly in Excel.
We'll leave the Pharma Data table selected as the table to add the measure to.
Name the measure Average Sales.
And use the AVERAGE formula of the Sales column to create the measure.
We'll then add this measure to the PivotTable.
Remember, this figure is the average sale per transaction for each product. Finally, we'll create a new measure calculating the max sale per product. Again, we'll do this in Excel.
We'll call the measure Max Sales.
Use the MAX formula on the Sales column, and press OK to create the measure. This is another situation where we need to be clear what the numbers are showing. In this case, we're seeing the sales from the largest transaction for each product.
As we've done previously, we'll select the numeric columns on the table and reduce the number of decimal places to make the figures a bit easier to understand.
At this point, you may wonder why we would use these functions, when we can create this PivotTable by changing the Value field settings of a PivotTable field.
The value of creating these measures is that they could be reused in more complex situations.
In the next lesson, we'll look at the CALCULATE function, and see how to use these simple functions in more complex formulas.