Power BI DAX functions AVERAGE, AVERAGEA & AVERAGEX are returns the average (arithmetic mean) of all the numbers in a column, All functions comes under Aggregation functions Dax categories.
1- AVERAGE DAX Function:
Returns the average (arithmetic mean) of all the numbers in a column.
Syntax:
AVERAGE(<Column>)
Description:
S no. | Parameter | Description |
1 | column | The column that contains the numbers for which you want the average. |
Dataset format as below:
ID | Amount | Blank | Boolean | SalesDate | Name |
1 | TRUE | 29/5/2020 | A | ||
2 | 1000 | FALSE | B | ||
3 | 2000 | TRUE | 29/5/2020 | ||
4 | 1000 | FALSE | 28/5/2020 | C |
Note:
- This function takes the specified column as an argument and finds the average of the values in that column.
- Does not support Logical value & Text Value
- Cells with the value zero are included.
- If Data type is Integer/ Numeric/ Decimal, Average skip the null value.
Let’s create measure for Average function with different-different columns
Average with ID column: AVERAGE 1 = AVERAGE(SampleTable[ID]) ---- Output = 2.50
Average with Amount column: AVERAGE 2= AVERAGE(SampleTable[Amount])
Output = 1333.33, Amount column contains 3 values with 1 blank value, so total no of rows are 4 but Amount column data type is Int. So Avg skip the null value now total no of rows are 3 and it will divide total sum of amount with 3.
2- AVERAGEA DAX Function:
Returns the average (arithmetic mean) of the values in a column. Handles text and non-numeric values.
Syntax:
AVERAGEA(<Column>)
Description:
S no. | Parameter | Description |
1 | column | The column that contains the numbers for which you want the average. |
Note :
The AVERAGEA function takes a column and averages the numbers, but also handles non-numeric data types according to the following rules:
- It support Logical values.
- Logical value TRUE count as 1 & FALSE count as 0 (zero).
- Values that contain non-numeric text count as 0 (zero).
- Empty text (“”) counts as 0 (zero).
Let’s create measure for AVERAGEA function with different-different columns
AVERAGEA with ID column: AVERAGEA 1 = AVERAGEA(SampleTable[ID])-- Output = 2.50
AVERAGEA with Amount column: AVERAGEA 2= AVERAGEA(SampleTable[Amount])--- Output = 1333.33
AVERAGEA with Logical value: AVERAGEA With Boolean = AVERAGEA(SampleTable[Boolean]) --- Output = 0.50
As per Sample data set we have 2 True values & 2 False values under “Boolean” column, So AVERAGEA consider True as 1 and False as 0, that’s mean Total Sum of Boolean column values are 2 and no of values are 4, so it will divide 2 with 4 and answer will be 0.50.
3- AVERAGEX DAX Function:
Calculates the average (arithmetic mean) of a set of expressions evaluated over a table.
Syntax:
AVERAGEX(<table>,<expression>)
Description:
S no. | Parameter | Description |
1 | table | Name of a table, or an expression that specifies the table over which the aggregation can be performed. |
2 | expression | An expression with a scalar result, which will be evaluated for each row of the table in the first argument. |
Note:
- The function takes a table as its first argument, and an expression as the second argument.
- The AVERAGEX function enables you to evaluate expressions for each row of a table, and then take the resulting set of values and calculate its arithmetic mean.
- You cannot include non-numeric or null cells. Both the table and expression arguments are required.
- When there are no rows to aggregate, the function returns a blank. When there are rows, but none of them meet the specified criteria, then the function returns 0.
- Does not support Logical/ boolean values.
Let’s create measure for AVERAGEX function with different-different columns
AVERAGEX with ID column:
AVERAGEX 1 =
AVERAGEX(SampleTable,SampleTable[ID])
AVERAGEX with Amount column: AVERAGEX 2 = AVERAGEX(SampleTable, SampleTable[Amount])--- Output = 1333.33
You can use Filter DAX function with AVERAGEX :
Suppose you want see Average of Amount column values where amount values are equal to 1000.
AVERAGEX With Filter = AVERAGEX( FILTER(SampleTable, SampleTable[Amount]=1000), SampleTable[Amount] ) Output = 1000
Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.