AVERAGEA

Updated on
Rick de Groot

AVERAGEA is a DAX function that returns the average (arithmetic mean) of values in a column, handling text and non-numeric values.

Syntax

AVERAGEA( ColumnName )
Argument Properties Description
ColumnName A column that contains the values for which you want the average.

Return Values

A decimal number.

Remarks

  • 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 evaluates to TRUE count as 1.
    • Values that evaluate to FALSE count as 0 (zero).
    • Values that contain non-numeric text count as 0 (zero).
    • Empty text (“”) counts as 0 (zero).

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

Example

The following example returns the average of non-blank cells in the referenced column, given the following table. If you used the AVERAGE function, the mean would be 21/2; with the AVERAGEA function, the result is 22/5.

Transaction ID Amount Result
0000123 1 Counts as 1
0000124 20 Counts as 20
0000125 n/a Counts as 0
0000126 Counts as 0
0000126 TRUE Counts as 1 
= AVERAGEA([Amount])

Other functions related to AVERAGEA are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/dax/averagea-function-dax

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy