Downloads
Power Query MList: summarizeIntermediate

List.Median

Returns the middle value of a list of numbers, half of the items above it and half below.

Syntax
List.Median(list)

Returns: A number, the median. With an even count, the average of the two middle values.

What it does

The median is the middle value when the numbers are sorted. Unlike an average, a few very large or very small items do not pull it far away from the typical value. That makes it useful for invoice sizes, payment days and salaries, where a handful of big items would distort the mean.

Syntax and arguments

Example

A controller wants a typical invoice size for a vendor, to compare with the average. A few very large one-off invoices make the average high.

let
    InvoiceAmounts = {210, 185, 240, 198, 9400},
    Average = List.Average(InvoiceAmounts),
    Median = List.Median(InvoiceAmounts)
in
    Median

The median is 210, the middle value once the amounts are sorted. The average is much higher, because of the 9,400 invoice. Reporting both tells the reader the typical bill and the effect of the large one.

Example: a second, different use

Median days to pay for a customer group, shown next to the average, when a few late payers would otherwise dominate.

Things to watch

In business terms

Median invoice size, median days sales outstanding and median salary in a peer group are measures that resist outliers. They are standard in benchmarking and in reviews of unusual items.

Related functions

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