Sign in or start a free trial to avail of this feature.
4. Aggregation Functions
Aggregate functions, such as SUM, AVERAGE, MIN, MAX and so on, calculate a simple formula over a column of data. We’ll look at some of the most common aggregate functions in this lesson.
Lesson Goal (00:12)
The goal of this lesson is to create several aggregation measures to analyze sales by product.
Creating Aggregation Measures (00:25)
Perhaps the most common aggregation measure is SUM. This takes a single argument, which is a column reference. It then adds all the values in that column to produce its result. We use SUM to create a measure calculating total sales, then use this measure to create a visualization showing total sales for each product.
Similar aggregation functions include AVERAGE and MAX. Average returns the average, or mean, value from a column, and MAX returns the highest value from the column. It’s important to understand exactly what these measures compute. For example, in our dataset, the MAX function returns the value of the highest sales transaction in the data set.
Other Aggregation and Statistical Functions (02:14)
In addition to SUM, AVERAGE, and MAX, other common aggregation functions include:
MIN: Calculates the minimum value in a column
COUNT and DISTINCTCOUNT: Count the number of values in a column in different ways
DAX also provides statistical functions, which work in a similar way. Some of the most common statistical functions include:
MEDIAN: Computes the median value of a column
VAR: Calculates the variance of a column
STDEV: Calculates the standard deviation of a column
Building Complex Measures Using Simpler Ones (02:46)
Creating simple measures using aggregate functions allows you to use those measures to create more complex functions. For example, our data model contains a unit price measure which divides total sales by total quantity. We can replace these two calculations with simple measures calculated using SUM. When we refer to a measure in a formula, we enclose the measure name in square brackets.
There are two advantages to building larger measures in this way. First, the larger measures are easier to read and understand, as they contain fewer calculations. Secondly, only one measure in the data model performs a particular calculation, such as the sum of sales. If we change the method of this calculation, we only need to update one measure, and the other measures update automatically.
In this lesson, we'll look at Aggregation Functions. These are probably the simplest DAX functions but we'll see that they can be used to build up more complex functions. Our goal in this lesson is to create several aggregation measures to analyze sales by product.
Note that since the calculations use aggregate data, we're using measures throughout this lesson. We'll start by creating a measure for total sales.
We'll navigate to the Modeling tab and select New Measure.
We'll call the measure Total Sales and enter a formula to sum values in the sales column.
We'll press Enter to create the measure.
We'll now drag total sales onto the canvas along with product name.
This gives us a chart showing the total sales for each product.
As mentioned in a previous lesson, this measure is computed for each product when we add it to the chart. We'll change this to a clustered bar chart and move it to the left half of the canvas.
Next we'll look at average sales.
We'll create a new measure and name it Average Sales.
In this case, we'll use the AVERAGE formula on the sales column and then press Enter to create the measure.
We'll create another visual showing the average sales by product and move it to the top right corner of the canvas.
To be clear, this graph shows the average sales amount per transaction for each product.
We'll now create a final measure to calculate the highest sale per product using the MAX function. We'll name this measure Max Sales and use the MAX function on the sales column.
As before, we'll create a column chart of max sales by product name.
And this time, put it in the bottom right corner of the canvas.
This is another situation where we need to be clear about what we're graphing. In this case, we're seeing the sales from the largest transaction of each product.
In addition to the MAX function, there's also a MIN function available for calculating minimum values. This covers the principles behind aggregate functions in DAX. The other main aggregation functions are COUNT and DISTINCT COUNT which we'll cover in a later lesson. There are also various basic statistical functions available such as MEDIAN, VAR for variance, and S-T-D-E-V for standard deviation.
These work exactly as you might expect them to.
Now that we've seen some of the simple aggregation options available, you might be wondering what the point of using these functions is. After all, you can generally create visualizations like this without using DAX formulas. However, these measures can be reused in more complex situations offering users greater efficiency and flexibility.
Let's return to the previous page of our report where we calculated a unit price measure.
If we select the Unit Price Measure from the fields list, we can see that the formula for this measure divides the sum of sales by the sum of quantity.
However, our total sales measure also calculates the sum of the sales column.
Let's modify the unit price measure to use this total sales measure in place of the first SUM function.
Note, that when referencing a measure we enclose the measure name in square brackets.
We'll press Enter to accept the change and see if the bar chart remains the same.
The measure is performing the same calculation as before, just calculated in a different way.
We can also create a total quantity measure by selecting New Measure and using the SUM formula on the quantity column.
Let's return to the Unit Price Measure and replace the sum of quantity with the total quantity measure we just created.
Again, the chart does not change as the unit price measure is still performing the same calculation. This example illustrates how we can use simple measures to build up larger measures. Creating measures in this way can be quite useful with more complex models. In this model we now have only one measure that calculates total sales.
If we change the method used for this calculation we only need to update one measure.
This can save a significant amount of time if your measures are more complex or your data models are larger.
In this lesson we've covered how to use aggregate functions, as well as how to use some simple measures to build up more complex measures. In the next lesson, we'll introduce a more advanced function the Calculate Function.