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

12 October 2021

*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:

**=AVERAGEA(column)**

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**.*