xmetricRecipes

Recipe Tableau and SQL

How do I get the median and P90 for each group?

Use MEDIAN([x]) and PERCENTILE([x], 0.9) in Tableau, or MEDIAN(x) and QUANTILE_CONT(x, 0.9) in SQL, grouped by your category. On skewed data the mean can rank the groups backwards: here Enterprise support tickets have the slowest average, 55.6 hours, and the fastest median, 4.1 hours.

Updated

The data

Pennant support tickets, January to June 2025

Synthetic datatickets.csv

Support for a project-management app: 1,600 tickets across four plans. Enterprise gets a priority queue, except for the bug escalations that wait on engineering.

ticket_idopened_dateplanchanneltopichours_to_resolve
PN-310002025-01-01FreeChatAccess64.9
PN-310012025-01-01FreeChatBug21.6
PN-310022025-01-01StarterChatHow-to6.5
PN-310032025-01-01StarterChatHow-to13.1

Step 1

Average hours to resolve, by plan

By the average, Enterprise customers wait longest: more than two days per ticket. That is the number that ends up in a quarterly review. It is also misleading.

Tableau

AVG([hours_to_resolve])

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

SQL

SELECT plan, AVG(hours_to_resolve) AS mean_hours
FROM input
GROUP BY plan
ORDER BY mean_hours DESC

Rows

PlanHours to resolve
Enterprise55.58
Free35.16
Starter24.16
Team16.07

Step 2

Now the median

The ranking flips. The typical Enterprise ticket closes in about four hours, faster than any other plan. A small group of bug escalations, each waiting weeks on engineering, drags the Enterprise mean up.

Tableau

MEDIAN([hours_to_resolve])

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

SQL

SELECT plan, MEDIAN(hours_to_resolve) AS median_hours
FROM input
GROUP BY plan
ORDER BY median_hours DESC

Rows

PlanHours to resolve
Free30.05
Starter20.05
Team11.55
Enterprise4.1

Why does the mean say the opposite of the median?

Because 19 of the 160 Enterprise tickets are bug escalations that wait days or weeks on engineering. They are 12% of the tickets and 93% of the hours. The mean spreads those hours over every ticket, so Enterprise looks slowest (55.6 hours). The median ignores how long the long ones are, so it shows what a typical Enterprise customer gets: an answer in 4.1 hours.

Step 3

How many tickets beat their own plan's average?

On symmetric data, about half the rows sit below the mean (the reference line). Here 88% of Enterprise tickets close faster than the Enterprise average. When this share is far from half, the mean is describing almost nobody.

Tableau

AVG(IIF([hours_to_resolve] < { FIXED [plan] : AVG([hours_to_resolve]) }, 1, 0))

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

SQL

WITH m AS (
  SELECT plan, AVG(hours_to_resolve) AS mean_hours
  FROM input
  GROUP BY plan
)
SELECT
  i.plan,
  AVG(CASE WHEN i.hours_to_resolve < m.mean_hours THEN 1 ELSE 0 END) AS share_below_mean
FROM input i JOIN m USING (plan)
GROUP BY i.plan
ORDER BY share_below_mean DESC

Rows

PlanShare below mean
Enterprise88.1%
Team71.8%
Starter62.9%
Free62.4%

Common mistake

Ranking groups by the mean on skewed data

The mean puts Enterprise first for slow support; the median puts it last. Both are right about different things: the mean counts the weeks lost to escalations, the median tells you what a typical customer gets. Report the median and a high percentile, not the mean alone.

WrongStarts with Enterprise…
Point at a bar to read it.
55.58Hours to resolve

Tableau

AVG([hours_to_resolve])
// sorted descending

SQL

SELECT plan, AVG(hours_to_resolve) AS hours
FROM input
GROUP BY plan
ORDER BY hours DESC
LIMIT 1
RightStarts with Free…
Point at a bar to read it.
30.05Hours to resolve

Tableau

MEDIAN([hours_to_resolve])
// with PERCENTILE([hours_to_resolve], 0.9) beside it

SQL

SELECT plan, MEDIAN(hours_to_resolve) AS hours
FROM input
GROUP BY plan
ORDER BY hours DESC
LIMIT 1

Which percentile should I report?

The median for the typical case, and one high percentile for the tail your customers remember. Turn the slider.

50%99%

Step 4

Pick a percentile

P90 is the time 90% of tickets beat. Slide the percentile and watch Enterprise: fastest at the median, still fastest at P85, then the slowest plan by far at P90, once the escalations enter the window.

Tableau

PERCENTILE([hours_to_resolve], 0.9)

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

SQL

SELECT
  plan,
  QUANTILE_CONT(hours_to_resolve, 0.9) AS percentile_hours
FROM input
GROUP BY plan
ORDER BY percentile_hours DESC

Rows

PlanPercentile hours
Enterprise29
Free62
Starter42.54
Team28.99

At 90%, the slowest plan is Enterprise, at 29 hours. Between P85 and P90 Enterprise goes from fastest to slowest: a single percentile can hide a whole group of tickets, so show two.

Step 5

Put the whole spread in one table

Median for the typical ticket, P90 or P95 for the tail, and mean over median as a skew alarm: near 1 the mean is fine, above about 1.3 the tail is driving it. Enterprise sits above 13.

Tableau

MEDIAN([hours_to_resolve])
// and PERCENTILE([hours_to_resolve], 0.25), 0.75, 0.9, 0.95; AVG([hours_to_resolve]) / MEDIAN([hours_to_resolve])

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

SQL

SELECT
  plan,
  COUNT(*) AS tickets,
  AVG(hours_to_resolve) AS mean_hours,
  QUANTILE_CONT(hours_to_resolve, 0.25) AS p25,
  MEDIAN(hours_to_resolve) AS median_hours,
  QUANTILE_CONT(hours_to_resolve, 0.75) AS p75,
  QUANTILE_CONT(hours_to_resolve, 0.9) AS p90,
  QUANTILE_CONT(hours_to_resolve, 0.95) AS p95,
  AVG(hours_to_resolve)
    / MEDIAN(hours_to_resolve) AS mean_to_median
FROM input
GROUP BY plan
ORDER BY median_hours

Rows

PlanTicketsHours to resolveP25Hours to resolveP75P90P95Hours to resolve
Enterprise16055.582.64.16.32294513.56
Team34016.07711.5516.8328.9938.151.39
Starter48024.1613.220.0529.4242.5452.211.2
Free62035.1621.2830.0543.66275.981.17

Can I average the plan medians?

No. Medians don't add up or average. Work them out again from the rows at the level you report.

Common mistake

Averaging group medians to get the overall median

A median can't be rolled up. The average of the four plan medians is 16.4 hours; the median of all 1,600 tickets is 20.6. Compute the overall median from the rows, with a FIXED expression or a query without GROUP BY.

Wrong
Point at a bar to read it.
16.44Hours to resolve

Tableau

WINDOW_AVG(MEDIAN([hours_to_resolve]))
// Compute Using: plan

SQL

SELECT AVG(m) AS overall_median
FROM (
  SELECT plan, MEDIAN(hours_to_resolve) AS m
  FROM input
  GROUP BY plan
)
Right
Point at a bar to read it.
20.6Hours to resolve

Tableau

{ FIXED : MEDIAN([hours_to_resolve]) }

SQL

SELECT MEDIAN(hours_to_resolve) AS overall_median
FROM input

Run it on your own data

Any table with a group column and a numeric measure works: response times by team, deal sizes by region, order values by channel. Drop your CSV and pick the two columns; the steps above recompute in your browser, and nothing is uploaded. Make sure one row is one thing you want the median of: if your file has one row per order line, total the lines per order first, or you get the median line instead of the median order.

Drop a CSV anywhere on this page

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

Questions

When should I report the median instead of the mean?

Whenever a few huge values can drag the mean: response times, deal sizes, house prices, order values. Report the median for the typical case and P90 or P95 for the tail. The mean still matters when you need totals, such as hours of staff time.

What is the difference between QUANTILE_CONT and QUANTILE_DISC?

QUANTILE_CONT interpolates between the two nearest values; QUANTILE_DISC returns a value that is in the data. On 10, 20, 30 and 100, the continuous median is 25 and the discrete median is 20. MEDIAN in DuckDB is the continuous one.

What is the difference between PERCENTILE and RANK_PERCENTILE in Tableau?

PERCENTILE([x], 0.9) returns a value: the hours that 90% of tickets beat. RANK_PERCENTILE(SUM([x])) returns a rank between 0 and 1 for each mark: where one ticket or customer sits in the list.

Can I add medians up or average them?

No. Medians are not additive: the median of the whole is not the average or the median of the parts. Compute it again from the rows at the level you need.

Is my data uploaded?

No. Your CSV is read and calculated in this browser tab.

Sources