Downloads
Power Query MList: summarizeBeginner

List.Average

Returns the mean (average) of the numbers in a list.

Syntax
List.Average(list, optional precision)

Returns: A number, the sum of the items divided by how many there are.

What it does

List.Average adds the numbers in a list and divides by how many there are. It is the M version of AVERAGE. Use it for an average invoice size, the average days to pay, or the average monthly sales for a customer over a period. Be careful with nulls, because they affect the count.

Syntax and arguments

Example

A collections team wants the average days taken to pay, for a set of paid invoices, to compare against the stated terms. Some invoices have not been paid yet and their day counts are blank.

let
    DaysToPay = {12, 30, null, 45, 18},
    Paid = List.RemoveNulls(DaysToPay),
    AverageDays = List.Average(Paid, Precision.Decimal)
in
    AverageDays

Paid drops the blank entry, so the average is taken over the four paid invoices: (12 + 30 + 45 + 18) divided by 4, which is 26.25. Leaving the blank in would make it unclear how many invoices the average covers, so removing nulls first is the safe habit.

Example: a second, different use

An average unit price is total cost divided by total quantity, not the simple average of the unit prices. Use List.Sum on each list, then divide the two totals.

Things to watch

In business terms

Average days sales outstanding, average invoice size, average cost per hour and average monthly spend per vendor are common finance measures. State which items were included, so the average can be reproduced.

Related functions

All Power Query M functions · View as Markdown · M basics