AVERAGEX
Works out an expression row by row over a table and gives the mean of the results.
Group: Aggregation. Compatible with DAX.
Syntax
AVERAGEX ( <table>, <expression> )| Parameter | Type | Description |
|---|---|---|
table | Table | The rows to go through: a table of the model or any table expression. |
expression | Expression evaluated for each row | The number worked out for each row. |
Returns
Number. The mean of the expression's results, blanks left out.
Remarks
- The expression is evaluated once per row of the table, in that row's context; rows where it is blank count neither in the total nor in the number of rows.
- Over an empty table, or when every result is blank, the answer is BLANK.
- A measure in the expression is evaluated with the row as its filter (context transition), so
AVERAGEX ( VALUES ( Products[Product] ), [Revenue] )is the mean revenue per product. - Text results are an error; dates average as numbers (days since 30 December 1899).
Examples
Mean liters per order line
The Cider line has no liters, so the mean is over three lines.
EVALUATE
VAR Orders =
DATATABLE (
"Product", STRING, "Liters", INTEGER, "Price", DOUBLE,
{ { "Pils", 120, 38 }, { "IPA", 80, 64 }, { "Cider", BLANK (), 52 }, { "Stout", 45, 70 } }
)
RETURN
ROW ( "Mean liters", AVERAGEX ( Orders, [Liters] ) )| Mean liters |
|---|
| 81.6666666666667 |
Mean revenue per product
The measure Revenue is evaluated for each product, then averaged.
EVALUATE
ROW (
"Mean revenue per product", AVERAGEX ( VALUES ( Products[Product] ), [Revenue] )
)| Mean revenue per product |
|---|
| 20034045.5 |