In this blog post, we are going to dive into the world of averages and how to solve problems when calculating averages by using CALCULATE, SUMMARIZE and AVERAGEX. Specifically, we look at creating an average calculation across months and as you will see in the video below, this can be a challenge on multiple levels!
Working with SUMMARIZE and SUMMARIZECOLUMNS in DAX
Our goal in this video is to return the average sales for the last 3 months, see Figure-1 below.
Figure 1 – Average of last 3 months of sales
Our first attempt at solving this problem may be to just use the expression AVERAGE([Sales Amount]) as seen in Figure-2 below. However, the AVERAGE function will first return the AVERAGE of all the transactions for the month and therefore the results of the following calculation are not quite as expected:
Figure 2 – Average of Sales Amount, incorrect calculation.
The result of the expression in Figure 2 can be seen in the following screenshot. Ultimately, average is returning the SUM of the Sales Amount divided by the number of transactions for each month.
Figure 3– Average of Sales Amount
Now that we understand this behavior and what is occurring, we can take a direct approach at solving the problem. First, we need to create a virtual table that contains the months along with their total sales. In order to create this aggregated table we can use SUMMARIZE or SUMMARIZECOLUMNS. In this scenario I will use SUMMARIZE because SUMMARIZECOLUMNS doesn’t quite work correctly within a modified filter context and we will need to change the default filter context to get 3 months in our aggregate table.
The following expression will return an aggregate table with the Year, Month and Total Sales.
Figure 4 – Creating a virtual, aggregated table with SUMMARIZE
SUMMARIZE is a function that will return a table expression and therefore you would get an error message if you tried to just put this expression in a calculated measure. However, you could test this out or “DEBUG” this expression by putting it in a calculated table or running the expression from Dax Studio. See the results of this expression from Dax Studio below in Figure 5:
Figure 5 – A view of the virtualized table created by SUMMARIZE
Working with AVERAGEX, CALCULATE and DATESINPERIOD
There are a few challenges still left to solve to get our desired result. First, you can’t use the AVERAGE function on a table or table expression, you must provide a column and therefore the result produced by SUMMARIZE here cannot be used by AVERAGE. However, X Functions like AVERAGEX accept a table or table expression as their first parameter and therefore work perfectly with this virtual table.
The next challenge is that the virtual table is returned within the current filter context and therefore we would always be returning the AVERAGE of the current month and only the current month. UNLESS, we use the CALCULATE function to evaluate the expression within a modified filter context, which is exactly what we would do! The calculation in Figure 6 below is the expression that returns the desired result seen in Figure 1.
Figure 6 – Final calculation
Mitchellsql 