We are often required to present data where a business needs to analyse the average, median or any type of aggregation over time in order to see how the business is doing and where improvement is required. Data aggregation is simple when a record is stored in a single row in your data table, but often this is not the case. For example a single sales transaction to a customer may be captured in multiple rows. Each row can contain the data on a SKU and temperature category for example. To just simply calculate the average sales per customer transaction will not be possible by using a measure such as ‘AVERAGE’ by itself. We will shed more light on this in the blog post below.
Data Aggregation through Measures and Functions
We will use the example as explained in the introduction where a single sales order is captured across multiple rows. What we want to see is the average sales per customer order over a period of time. Let’s see our sample data below:
|Customer #||Sales Order||Product Code||Product Temp||QTY Sold||Date|
We are first going to calculate the Average sales on 2021/04/01. We will use the AVERAGE function:
Average = AVERAGE(‘Sales Data'[QTY Sold])
The result that we get is the average units sold per row in the data set.
This is not exactly what we require. The average units sold per sales order is 136.
Another function that we will use in the final result is AVERAGEX. The AVERAGEX function reference a table and returns the average value. The DAX for AVERAGEX is shown as below:
The result will be the same as the Average function.
The Simple Solution
In order to achieve the desired result we need to make use of two measures. Firstly, we use the SUM measure. This is a simple measure that sums the values in a certain column. We sum the “QTY Sold” in the dataset :
From the dataset we use the Sales order column as the input column for the VALUES function:
Now we use the AVERAGEX function with the VALUES and Total QTY measures to calculate the average sales per sales order. See the formula below: