AVERAGE
Returns the mean of the numbers in one column.
AVERAGE(<column>)Returns: A number (a scalar). BLANK when the column has no numeric values in the current filter context.
What it does
AVERAGE adds the numbers in a column and divides by how many there are. Blank cells are not counted, but zeros are. It respects every filter in the current context, so the average changes with the slicers. Use it for a plain average of values that are already one per row, such as the amount on each invoice line.
Syntax and arguments
- column: a column reference such as
Sales[SalesAmount]. Only a column is accepted.
Example
The sales manager wants the typical amount on one sales line, to compare months.
Average Line Value = AVERAGE(Sales[SalesAmount])
Each line counts as one item. In a matrix by month, each month shows the average of the lines booked in that month. If a line is zero, it pulls the average down. If a line is blank, it is skipped.
Example: a second, different use
The average list price of products in one category, as a quick check on pricing:
Average List Price = AVERAGE(Product[UnitPrice])
This is a simple average of products, not of sales. A product that sells one unit counts as much as a product that sells a million. For a price that reflects what customers actually paid, use a weighted average (see the traps below).
Things to watch
- A simple average is not a weighted average. The average of prices is not the average price paid. For that, use
DIVIDE(SUM(Sales[SalesAmount]), SUM(Sales[SalesQuantity])). - Zeros count and blanks do not. Decide which one your data means before you trust the result.
- An average of averages is wrong. Averaging monthly averages gives a different answer from averaging all the underlying lines.
- Use AVERAGEX when each row needs a calculation first, such as the price of each line.
- If there are no values in the filter context, the result is BLANK, which a card displays as nothing.
In business terms
Averages are used for average invoice size, average days to pay, average cost per hire and average hourly rate. Finance teams should always ask whether the average is weighted by volume, because the weighted and simple versions can point in opposite directions.