Lesson 6 of 15 · Lab

Mean, median and weighted average in reporting

An aggregate such as an average means something only once you say which rows it covers and what it divides by. The mean is the total over the count, the median is the middle value, and a weighted average gives larger groups more say. Use the median for "typical" when a few large values pull the mean.

Five invoices of 100, 100, 100, 100 and 1100
MeasureResultReads as
Mean300The average invoice, higher than four of the five
Median100The typical invoice

At fictional Vandelay Coffee Supply, Don Tremendous says customer Initech's invoices are unusually large. To test that, you need to know what a typical invoice looks like. This flow holds Q3's 13 invoices, with net_eur as net value in euros.

12 of the 13 invoices have a price, and INV-1014's is unknown. Should the average divide by 12 or by 13?

By 12. Dividing by 13 treats the unknown price as zero, and the metric says unknown prices stay empty.

Build the mean, the median and a group

  1. In this live flow in Strata, Oplema's browser app for data prep, Summarize is selected: net, invoices and priced.
  2. Under Actions, select Add. Summarize stays red until the action has a column.
  3. Set the new Action to Average, and its Column to net_eur.
  4. Read the preview. Compare your new column with net divided by priced. Then select Add, and set the new action to Median of net_eur.
  5. Select Add again. The new action is a Group by. Set its Column to customer, and read Initech's row.

Swipe sideways to see the canvas, or open this page on a larger screen to work with it.

The two averages, before grouping

The mean is €956.50, which is €11,478 over 12. The median is €880, because a few large invoices pull the mean up. By customer, Initech averages €1,500 across just two invoices.

Four averages, four questions

One table, four answers
FigureDivides byAnswers
€956.50 mean12 priced invoicesWhat is the average priced invoice?
€880 medianMiddle of those 12What does a typical invoice look like?
€882.9213, with the unknown as zeroNothing true, because it invents a price
€1,010.504 customers' averagesWhat does the average customer's invoice look like?

Initech's €1,500 is high, and two invoices is too few to call a pattern. Report the count beside the average, as priced does here, and that limit stays visible.

Averaging averages, and the weighted fix

The €1,010.50 counts Initech's two invoices as heavily as Wonka's four priced ones, which changes the question without saying so. A weighted average multiplies each customer's average by its count before dividing, and that returns €956.50. Take stores with 3 sales averaging 10 and 1 sale of 50. The plain mean of the two averages is 30, and the weighted average is 20.

Use a weighted average for rates and prices across groups of different sizes, and use the plain one only when each group should count equally.

Averages in SQL and Excel

SELECT customer,
  AVG(net_eur) AS mean_value,
  COUNT(net_eur) AS priced,
  COUNT(*) AS invoices
FROM invoices
GROUP BY customer

AVG and COUNT(column) skip nulls, while COUNT(*) counts every row. DuckDB has MEDIAN(net_eur), and PostgreSQL uses PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY net_eur). In Excel, use AVERAGE, MEDIAN, and =SUMPRODUCT(averages, counts) / SUM(counts) for a weighted average. See the Summarize reference for this block.