Power Pivot Principles: Working with Averages – Part 1

Welcome back to the Power Pivot Principles blog.  This week, we will discuss working with averages in DAX.

The AVERAGE function returns the average (arithmetic mean) of all the numbers in a column:

The AVERAGEA function does a similar thing, and is distinguished by its ability to handle text and non-numeric values:

For example, consider we have a Sales table with Date and Sales by date as below (obviously, there are some “unusual” cell values in the Sales column):

Now, we take July 2017 as an example to calculate the average in Excel. In the range B2:B32, there are blank cells and cells with text. We illustrate the >AVERAGE and AVERAGEA functions, as below. We notice that the AVERAGE function does not take blank cells and those containing text into its computation. Meanwhile, the AVERAGEA only excludes blank cells.

If we change the blank cells to formula-driven cells (=“”), the AVERAGEA function will take all the cells into account:

We load this Sales table into the Power Pivot Data Model and create two measures:

The Sales_AVERAGE measure will give us an error.  This is because the Data Type of the Sales column is now Text, and the AVERAGE function cannot handle text.  Meanwhile, the AVERAGEA counts everything and return a zero (0). 

When we try to change the Data Type of the column to ‘Whole Number’, an error appears:

If we remove all the text cells in the Sales column and refresh the Power Pivot Data Model, we can now change the Data Type of the Sales column to ‘Whole Number’. Also, the Sales_AVERAGE and Sales_AVERAGEA measures will now provide the same results:

That is just one thing we need to consider when dealing with averages in DAX…

That’s it for this week!


Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.