List.Median
Returns the middle value of a list of numbers, half of the items above it and half below.
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
list: the numbers to find the middle of. Remove nulls first, because a null is not a number.
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
- The median needs a sorted list internally, so it can be slower on very long lists than a sum.
- With an even number of items, the result is the average of the two middle values, which may not be one of the items.
- Nulls and text cause errors or wrong results. Clean the list first.
- A median does not show the spread. Use it with List.Min and List.Max, or with a standard deviation, when the range matters.
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.