Power Pivot Principles: The A to Z of DAX Functions – AVERAGEA

In our long-established Power Pivot Principles articles, we are starting a new series on the A to Z of Data Analysis eXpression (DAX) functions. This week we look at AVERAGEA.

The AVERAGEA function

Like the AVERAGE function (discussed in our last A to Z of Data Analysis eXpression (DAX) functions blog), this function calculates the average (arithmetic mean) of the values in a column, handles text and non-numeric values.  However, the key difference here is that this function handles non-numeric data types based on certain rules.

The AVERAGEA function measures what is known as “central tendency”, which is the location of the centre of a group of numbers in a statistical distribution.  There are three common measures of central tendency:

• average: the arithmetic mean, calculated by adding a group of numbers and then dividing by the count of those numbers.  For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5
• median: the middle number of a group of numbers.  That is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median.  For example, the median of 2, 3, 3, 5, 7, and 10 is 4.  If the number of values in the selection is an even number, the median is defined as the midpoint between the two central numbers
• mode: the most frequently occurring number in a group of numbers.  For example, the mode of 2, 3, 3, 5, 7, and 10 is 3.

For a symmetrical distribution of a group of numbers, these three measures of central tendency are all the same.  For a skewed distribution of a group of numbers, they can be different.

The syntax is straightforward:

There is only one argument:

• column: the column that contains the numbers that you want the average.

It should be further noted that:

• the AVERAGEA function takes a column and averages the numbers in it, but also handles non-numeric data types according to the following rules: 
• values that evaluate to TRUE count as one [1]
• values that evaluate to FALSE count as zero [0]
• values that contain non-numeric text count as zero [0]
• empty text ("") counts as zero [0]
• if you do not want to include logical values and text representations of numbers in a reference as part of the calculation, use the AVERAGE function
• whenever there are no rows to aggregate, the function returns a blank.  However, if there are rows, but none of them meet the specified criteria, the function returns zero [0].  Microsoft Excel also returns a zero if no rows are found that meet the conditions
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

This is not quite analogous to its Excel counterpart:

Unlike AVERAGEA function in Excel, the AVERAGEA function in DAX produces different results. This is due to the data type in Power Pivot. As indicated in the column name, the data type for each column is text, decimal number and TRUE / FALSE:

• in the column Cost in Text Type, all records are forced into a text type. By the rules of AVERAGEA function, all texts are counted as zero [0]. Therefore, the returned result is 0.00
• in the column Cost in Number Type, all records are treated as decimal number. By the rules of AVERAGEA function, all values are counted as zero [0]. Therefore, the returned result is 0.00
• in the column True / False, all records are either TRUE or FALSE. By the rules of AVERAGEA function, TRUE is counted as one [1] and FALSE is counted as zero [0]. Therefore, the returned result is 0.50.

Come back next week 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.