xmetricRecipes

Recipe Tableau and SQL

How do I calculate a weighted average by group?

Multiply each value by its weight, sum, and divide by the sum of the weights: SUM([price_per_kg] * [kg]) / SUM([kg]). Plain AVG gives a 100-gram order the same say as a 40-kilo one. In this tea company it doesn't just overstate the price: it ranks the regions upside down.

Updated

The data

Fernhill Tea, order lines in 2025

Synthetic dataorders.csv

A tea merchant selling six teas two ways: small bags online at full price, and bulk sacks to cafés at a trade price, in three regions.

order_idorder_dateregionchannelteakgprice_per_kgrevenue
FT-101002025-01-01NorthOnlineChamomile0.153.015.3
FT-109512025-01-01SouthCaféDarjeeling First Flush4065.482619.2
FT-110972025-01-01WestOnlineAssam Breakfast0.561.5730.79
FT-112682025-01-01WestOnlineChamomile0.555.2927.64

Step 1

The plain average

One number per order line, averaged. The North looks like the premium region. But a line is a 100-gram online bag or a 40-kilo café sack, and AVG counts them the same.

Tableau

AVG([price_per_kg])

The xmetric engine ran this formula and got the SQL’s Price per kg column, row for row.

SQL

SELECT region, AVG(price_per_kg) AS simple_avg
FROM input
GROUP BY region
ORDER BY simple_avg DESC

Rows

RegionPrice per kg
North$65
West$61
South$59

Step 2

Weight every line by its kilos

Price times kilos is what each line earned; divided by total kilos it's the price the region really gets per kilo. The order flips: the North is now last.

Tableau

SUM([price_per_kg] * [kg]) / SUM([kg])

The xmetric engine ran this formula and got the SQL’s Kg column, row for row.

SQL

SELECT
  region,
  SUM(price_per_kg * kg) / SUM(kg) AS weighted_avg
FROM input
GROUP BY region
ORDER BY weighted_avg DESC

Rows

RegionKg
South45.89
West42.68
North37.23

Why does the North go from first to last?

Because AVG counts order lines, not tea. The North's plain average is 64.56 a kilo, the highest of the three regions; weighted by what it actually sold, it gets 37.23, the lowest. The South goes the other way, from 58.84 to 45.89. Nobody changed a price. The North simply has the most small online orders, and each one counts as much as a café's 40-kilo sack.

Step 3

Put the two side by side

The plain average is high everywhere, and by different amounts, which is why it reorders the regions. Switch Group by to channel: inside one channel the two averages almost agree. The gap comes from mixing small and big orders.

Tableau

AVG([price_per_kg]) - SUM([price_per_kg] * [kg]) / SUM([kg])
// the gap, or both measures with Measure Names on Color

SQL

SELECT
  region,
  'Plain AVG' AS method,
  AVG(price_per_kg) AS price
FROM input
GROUP BY region
UNION ALL 
SELECT
  region,
  'Weighted by kg',
  SUM(price_per_kg * kg) / SUM(kg)
FROM input
GROUP BY region
ORDER BY 1, 2

Rows

RegionMethodPrice per kg
NorthPlain AVG$65
NorthWeighted by kg$37
SouthPlain AVG$59
SouthWeighted by kg$46
WestPlain AVG$61
WestWeighted by kg$43
Group by

Step 4

Find the cause: the order mix

Online bags are most of the order lines and almost none of the tea. The North has the most online lines, so its plain average is pulled furthest toward the online price, while its kilos go to cafés at the lowest trade price of the three regions.

Tableau

COUNT(IF [channel] = "Online" THEN [order_id] END) / COUNT([order_id])
// and SUM(IF [channel] = "Online" THEN [kg] END) / SUM([kg])

The xmetric engine ran this formula and got the SQL’s Online share of lines column, row for row.

SQL

SELECT
  region,
  COUNT(*) FILTER (WHERE channel = 'Online')
    * 1.0
    / COUNT(*) AS online_share_of_lines,
  SUM(kg) FILTER (WHERE channel = 'Online')
    / SUM(kg) AS online_share_of_kg
FROM input
GROUP BY region
ORDER BY 1

Rows

RegionOnline share of linesKg
North70%3.8%
South42.5%1.2%
West55.2%1.9%

How much does it matter overall?

Online bags are 56.7% of the order lines and 2% of the tea sold. So the plain average of every line, 61.67, describes the online shop; the company actually gets 42.72 a kilo. AVG overstates the price by 44.3%. Any pricing, margin or forecast built on it starts from the wrong number.

Common mistake

AVG of a rate stored on each row

Any price, rate, percentage or score stored per row has this problem when rows carry different volumes. Weight by the volume, or divide the two totals.

WrongStarts with North, South, West…
Point at a bar to read it.
0204060NorthSouthWest

Tableau

AVG([price_per_kg])

SQL

SELECT region, AVG(price_per_kg) AS price
FROM input
GROUP BY region
RightStarts with North, South, West…
Point at a bar to read it.
02040NorthSouthWest

Tableau

SUM([revenue]) / SUM([kg])

SQL

SELECT region, SUM(revenue) / SUM(kg) AS price
FROM input
GROUP BY region

Common mistake

A row-level ratio, then aggregated

A calculated field [revenue] / [kg] with no aggregation is computed per row, and Tableau then sums or averages those per-row results. Put the aggregation inside the field so it divides totals at every level.

Wrong

Tableau

[revenue] / [kg]
// dropped on Rows as AVG
Right

Tableau

SUM([revenue]) / SUM([kg])

Step 5

The grand total, three ways

Only the last bar is the price the company gets per kilo. Averaging the regions' weighted averages is closer, but still treats a small region like a big one.

Tableau

SUM([price_per_kg] * [kg]) / SUM([kg])
// Total Using: Automatic, not Average

SQL

WITH g AS (
  SELECT region, SUM(price_per_kg * kg) / SUM(kg) AS w
  FROM input
  GROUP BY region
)
SELECT
  'Plain AVG of all lines' AS method,
  (
    SELECT AVG(price_per_kg)
    FROM input
  ) AS price
UNION ALL 
SELECT 'Average of the group averages', AVG(w)
FROM g
UNION ALL 
SELECT
  'Weighted by kg',
  (
    SELECT SUM(price_per_kg * kg) / SUM(kg)
    FROM input
  )

Rows

MethodPrice
Plain AVG of all lines61.67
Average of the group averages41.93
Weighted by kg42.72

Common mistake

Total Using: Average

Setting a grand total to Average averages the rows above it, so each region counts once whatever it sold. Leave Total Using on Automatic and the total runs the same weighted formula on all the data.

Wrong
Point at a bar to read it.
41.93Avg(w)

Tableau

Analysis > Totals > Total Using > Average

SQL

WITH g AS (
  SELECT region, SUM(revenue) / SUM(kg) AS w
  FROM input
  GROUP BY region
)
SELECT AVG(w)
FROM g
Right
Point at a bar to read it.
42.72(sum(revenue) / sum(kg))

Tableau

Analysis > Totals > Total Using > Automatic

SQL

SELECT SUM(revenue) / SUM(kg)
FROM input

Run it on your own data

Any table with a group, a value per row and a weight works: price and units, score and respondents, rate and hours. Drop your CSV and pick the columns; the steps recompute in your browser, and nothing is uploaded.

Drop a CSV anywhere on this page

Up to 10,000 rows. The sample, orders.csv, shows the shape.

Questions

Is the weighted average the same as total revenue over total kilos?

Yes, when each row's revenue is its price times its quantity. Then SUM([revenue]) / SUM([kg]) is the simpler formula, and it's what most dashboards should use.

When is plain AVG the right answer?

When each row is one decision of equal weight: the average list price across a catalogue, or the typical order a customer places. Ask whether a big row should count more. If yes, weight it.

How do I weight a score that has missing values?

Leave the missing rows out of both sums. Otherwise their weight lands in the denominator with nothing in the numerator and drags the average down: SUM([score] * [weight]) / SUM(IF NOT ISNULL([score]) THEN [weight] END), or FILTER (WHERE score IS NOT NULL) on both sums in SQL.

How do I compute a rolling weighted average?

Use window sums of the numerator and of the weights: WINDOW_SUM(SUM([price_per_kg] * [kg]), -6, 0) / WINDOW_SUM(SUM([kg]), -6, 0) in Tableau, and the same two SUM() OVER windows in SQL.

Sources