Average and AverageA DAX functions with example

In DAX, AVERAGE and AVERAGEA are used to calculate the mean of a column of values, but they differ in how they handle non-numeric values.

---

AVERAGE Function

• Description:
  Calculates the arithmetic mean (average) of numeric values in a column. It ignores blanks and non-numeric values (e.g., text or logical values).
• Syntax:

  AVERAGE(<column>)

• Behavior:
• Includes only numeric values in the calculation.
• Ignores blanks and non-numeric values.

---

AVERAGEA Function

• Description:
  Calculates the arithmetic mean (average) of values in a column, evaluating non-numeric values as follows:
• TRUE is treated as 1.
• FALSE and non-numeric text are treated as 0.
• Blanks are treated as 0.
• Syntax:

  AVERAGEA(<column>)

• Behavior:
• Includes numeric and non-numeric values in the calculation, assigning specific values to non-numeric data.

---

Example Using the Superstore Dataset

Assume the Sample Superstore dataset has the following key columns:

• Order ID
• Sales (numeric column)
• Profit (numeric column)
• Region (categorical column)
• Discount (numeric column)

We’ll focus on the Sales and Discount columns for the examples.

---

1. AVERAGE Example

You want to calculate the average sales across all orders:

Average Sales = 
AVERAGE('Sales'[Sales])

• What it does:
• Takes the Sales column.
• Calculates the mean of all numeric values in Sales.
• Ignores any blanks or invalid entries in the column.

---

2. AVERAGEA Example

Now, assume the Discount column has the following data:

| Discount |
|———-|
| 10 |
| 20 |
| TRUE |
| FALSE |
| | (blank)
| 30 |

To calculate the average discount using AVERAGEA:

Average Discount = 
AVERAGEA('Sales'[Discount])

• How it works:
• Numeric values are included as-is: 10, 20, 30.
• TRUE is treated as 1.
• FALSE and blanks are treated as 0.

The calculation will be:

---

Comparison of Results

If the same column is used in both functions:

Discount Column	Behavior in AVERAGE	Behavior in AVERAGEA
10	Included	Included
20	Included	Included
TRUE	Ignored	Treated as 1
FALSE	Ignored	Treated as 0
(blank)	Ignored	Treated as 0
30	Included	Included

---

Practical Applications in Superstore Dataset

Use Case for AVERAGE:

You want the average sales without any interference from non-numeric data:

Average Sales = 
AVERAGE('Sales'[Sales])

Use Case for AVERAGEA:

You have a Discount column with mixed data types and want an average that considers logical values like TRUE and FALSE:

Average Discount = 
AVERAGEA('Sales'[Discount])

---

Key Differences

Aspect	AVERAGE	AVERAGEA
Numeric Values	Included	Included
Logical Values	Ignored	TRUE = 1, FALSE = 0
Non-numeric Text	Ignored	Treated as 0
Blanks	Ignored	Treated as 0

---

Conclusion

• Use AVERAGE for clean numeric columns where non-numeric values should be ignored.
• Use AVERAGEA when dealing with mixed data types (numeric, logical, text) and you want those non-numeric values to contribute to the calculation.