Returns the computed average of the values in a list.
Sample usage
AVERAGE(Products[Price])
: The average of the all values in the Price
column of the Products
table. Equivalent to AVERAGE(SELECT(Products[Price], TRUE))
. See also: SELECT()
AVERAGE([Discounts])
: The average of the all items in the Discounts
column value, where Discounts
is of type List
for some numeric type.
AVERAGE(LIST(1, 2, 3))
: 2.00
AVERAGE(LIST(0) - LIST(0))
(a valid constructed list with a type but no items): 0.00
Average of select rows
Compute average product rating from customer feedback within the past month, excluding feedback with no rating:
AVERAGE(
SELECT(
Feedback[Product Rating],
AND(
([Product Rating] > 0),
([Submitted] >= (EOMONTH(TODAY(), -2) + DAY(TODAY())))
)
)
)
SELECT(Feedback[Product Rating], ...)
gets a list ofProduct Rating
values from select rows of theFeedback
table.AND(..., ...)
limits theSELECT()
results to only those rows that match all of the conditions.([Product Rating] > 0)
limits the selection to only rows with aProduct Rating
value greater than zero, where 0 is the default value and indicates "no rating".([Submitted] >= ...)
further limits the rows to those with a submission date on or after the computed date.(EOMONTH(TODAY(), -2) + DAY(TODAY()))
computes the date one month prior to today.AVERAGE(...)
computes the average of the selected values.
See also: AND()
, DAY()
, EOMONTH()
, SELECT()
, TODAY()
Common problems
AVERAGE(1, 2, 3)
: the arguments are not in list form. To fix, wrap them in LIST()
to construct a list: AVERAGE(LIST(1, 2, 3))
.
Syntax
AVERAGE(list)
list
- List of any numeric type.