Percentile Rollups: P80, P95 and Segmenting a Column Before It Rolls Up

Workforce teams rarely report speed of answer as an average. They report it as a percentile: a P80 of 45 seconds means 80% of chats were answered within 45 seconds, and a P95 says how long the slowest 5% waited. An average hides both. One chat that waits ten minutes drags it up, and a hundred chats answered in three seconds drag it down, so it describes nobody's experience.

Qvasa gives you percentiles in two places. Each group's own P80 and P95 are columns on the Zendesk Groups table, calculated live through the day. On top of those, a column rollup can take a percentile, a trimmed average or any other summary across the table's rows, and it can segment the column first: leave out the top 10%, keep only a middle band, count the rows above the P80. This guide covers both in depth, with every number worked from a real table, and ends with how to build it all through the Qvasa MCP.

If you haven't used formula columns or rollups before, start with Column Formulas and Column Rollups, which walks through the editor step by step. This article assumes you know where the editor is and goes deep on percentiles.

Before you start

Formula columns are switched on per account. If you don't see Table Column Formulas in Settings, contact Qvasa Support and we'll enable them.
The P80 and P95 columns depend on what your account tracks. P80 Queue Wait and P95 Queue Wait appear on the Zendesk Groups table once omnichannel queues are tracked on your account; P80 Comment FTT and P95 Comment FTT once ticket comments are.
Writing formulas takes a permission. Manage your own table column formulas lets someone create formulas and edit the ones they made; admins can edit everyone's. A rollup lives on its formula, so whoever can edit the formula can edit its rollup.

Two kinds of percentile: inside a row, and across rows

This is the distinction everything else rests on, so it's worth getting straight first.

A percentile inside a row is a metric column. P80 Queue Wait on the Groups table is each group's own P80: the wait within which 80% of that group's queue stays ended, taken across every stay in the queues where the group is the primary group, for stays that started in the dashboard's date range. It's calculated from the individual stays, never from per-queue summaries. P80 Comment FTT is the first touch time (the time to the first public agent comment) within which 80% of the tickets the group handled were answered. Add these from the column picker like any other column.
A percentile across rows is a rollup. A rollup works on the values the table shows, one per row. PERCENTILE({{this}}, 0.8) under the P80 Queue Wait column is the P80 of the groups' P80s: the value 80% of the groups are at or under.

The two answer different questions. "How long did 80% of chats wait?" is a per-chat percentile, which is what the P80 column gives each group. "How slow is our slower-than-most group?" is a percentile across groups, which is what the rollup gives. A rollup counts every group the same, so a group that handled five chats weighs as much as one that handled five hundred. Label rollups so nobody reads one as the other: P80 of groups, not just P80.

Here is the table the rest of this guide uses: eight groups over the last seven days, with four formula columns whose totals row shows the techniques below. Back Office has no queue, so its cells are blank.

How PERCENTILE calculates

PERCENTILE sorts the numbers and finds the value at a fractional position between them, interpolating when the position falls between two rows. It's the same method as PERCENTILE in Google Sheets and PERCENTILE.INC in Excel, so you can check any result in a spreadsheet.

A worked example with five values, 10, 20, 30, 40 and 50:

The position is k × (count − 1). For the P80 that's 0.8 × 4 = 3.2.
Counting from 0, position 3 is 40 and position 4 is 50.
The .2 means a fifth of the way from 40 to 50: 40 + 0.2 × 10 = 42.

On the table above, the P80 Queue Wait values of the seven groups with data are 9, 14, 20, 33, 74, 84 and 220 seconds. The position is 0.8 × 6 = 4.8, between 74 and 84, so the P80 of groups is 74 + 0.8 × 10 = 82 seconds, shown as 1m 22s. The P95 of the P95 column works the same way: 0.95 × 6 = 5.7, between 420 and 600, so 420 + 0.7 × 180 = 546 seconds, or 9m 6s.

The functions that rank a column:

Function	What it returns	On the P80 column above
PERCENTILE(range, k)	The k-th percentile, k from 0 to 1. 0 is the smallest value, 1 the largest, 0.5 the median.	PERCENTILE({{this}}, 0.8) = 82
QUARTILE(range, q)	A quarter mark, q from 0 to 4: 1 is the 25th percentile, 2 the median, 3 the 75th.	QUARTILE({{this}}, 1) = 17, QUARTILE({{this}}, 3) = 79
LARGE(range, n)	The n-th largest value.	LARGE({{this}}, 2) = 84
SMALL(range, n)	The n-th smallest value.	SMALL({{this}}, 1) = 9
TRIMMEAN(range, fraction)	The average after dropping that fraction of the values, half from the top and half from the bottom.	TRIMMEAN({{this}}, 0.3) = 45

Two details about these functions catch people out:

TRIMMEAN drops whole rows, an even number of them. It works out fraction × count and rounds down to an even number, so it can drop the same number from each end. With seven groups, 0.2 × 7 = 1.4 rounds down to 0, and TRIMMEAN({{this}}, 0.2) equals the plain average. 0.3 × 7 = 2.1 rounds down to 2, so 0.3 drops the fastest and slowest group. On a short table, pick a fraction large enough to drop at least two rows. The Avg (middle 80%) preset uses 0.2, which starts dropping rows at ten.
Out-of-range arguments are #NUM! A k outside 0 to 1, a q outside 0 to 4, an n larger than the number of values, or a column with no numbers at all all return #NUM!. TRIMMEAN of an empty column is #DIV/0!.

Step 1: Give the metric column a formula to carry its rollup

A rollup belongs to a formula column. The P80 Queue Wait metric column can't have one of its own, so to roll it up, make a formula that simply repeats it and give that formula the rollup.

Open the dashboard, click Edit, click the + at the right end of the Groups table's header row, and choose Create formula column….
Name it after what the totals row will show, for example P80 Wait: Across Groups.
Set Show the result as to Duration. The P80 and P95 columns hold seconds, and Duration formats them as minutes and seconds.
In Insert a column, search P80 and click Insert beside P80 Queue Wait. The formula box shows the column's stored reference; Reads as shows {{P80 Queue Wait}}.

The new column shows the same value as P80 Queue Wait on every row. You can keep both on the table, or hide the metric column and keep only the formula.

Step 2: Pick a percentile rollup

In the Column rollup step, Start from has three ready-made percentile rollups. One click fills the rollup formula and, if it's empty, the label:

P80: PERCENTILE({{this}}, 0.8), the value 80% of the rows are at or under.
P95: PERCENTILE({{this}}, 0.95), the value 95% of the rows are at or under.
Avg (middle 80%): TRIMMEAN({{this}}, 0.2), the average once the lowest and highest 10% of rows are dropped.

Change the label to say what the number is across, such as P80 of groups. {{this}} means this formula's own column, the whole column at once.

A preset is only a starting point: the rollup formula stays editable, so you can change 0.8 to 0.9 for a P90, or type any of the recipes in the next section.

Step 3: Segment, then reduce

Sometimes the question isn't "what is the P80" but "what is the average once the outliers are gone", or "how many groups are slower than the rest". That takes two moves in one rollup formula:

Segment: an IF over the whole column keeps the rows you want and turns the others into a blank (""). Because {{this}} is the whole column in a rollup, the IF runs once per row and gives back a column.
Reduce: AVERAGE, COUNT, MEDIAN, PERCENTILE or any other function that takes a column folds what's left into one value. These functions skip blanks, so the rows you segmented out don't count.

The Tail Gap column on the example table uses this pattern. Its rollup averages each group's gap between P95 and P80, leaving out the groups above the 90th percentile:

The recipes below all run on the P80 column from the example table: 220, 84, 74, 33, 20, 14 and 9 seconds, plus Back Office's blank. For comparison, the plain average of those seven values is 64.9 seconds and the median is 33.

You want	Rollup formula	Result
The average without the slowest 10%	AVERAGE(IF({{this}} > PERCENTILE({{this}}, 0.9), "", {{this}}))	39s: the 90th percentile is 138.4, so Disputes (220) drops out
The average without the fastest 10%	AVERAGE(IF({{this}} < PERCENTILE({{this}}, 0.1), "", {{this}}))	1m 14s: the 10th percentile is 12, so Rewards (9) drops out
The average of the middle band, P10 to P90	AVERAGE(IF({{this}} < PERCENTILE({{this}}, 0.1), "", IF({{this}} > PERCENTILE({{this}}, 0.9), "", {{this}})))	45s: both ends drop out
How many rows sit above the P80	COUNT(IF({{this}} > PERCENTILE({{this}}, 0.8), 1, ""))	2 (Disputes and Transfers). Show the rollup as Number
The share of rows over a target, here 60 seconds	COUNTIF({{this}}, ">60") / COUNT({{this}})	42.9% (3 of 7). Show the rollup as Percent
The average of the three slowest rows	AVERAGE(LARGE({{this}}, 1), LARGE({{this}}, 2), LARGE({{this}}, 3))	2m 6s
The spread of the middle half (interquartile range)	QUARTILE({{this}}, 3) - QUARTILE({{this}}, 1)	1m 2s (79 − 17)
A percentile that ignores rows of 0	PERCENTILE(IF({{this}} <> 0, {{this}}, ""), 0.8)	1m 22s here (no zeros); lower otherwise

Three rules make these work:

Segment with IF, not with AND or OR. AND and OR fold everything they're given into a single TRUE or FALSE, so they can't test each row of a column; inside a rollup they give #VALUE!. To keep a band between two limits, nest one IF inside the other, as in the middle-band recipe.
The percentile inside the IF is worked out on the whole column first. In the first recipe, PERCENTILE({{this}}, 0.9) is 138.4 for every row's comparison; the IF then decides row by row.
A rollup may only use the formula's own columns. {{this}} is always available. To segment by another column (for example, average the P80 only for groups with more than 100 tickets), put that column in the row formula too, and it becomes available to the rollup.

Blanks, zeros, errors and hidden rows

Most surprising rollup values come from one of these four.

Blank cells are skipped. PERCENTILE, AVERAGE, COUNT and the rest leave blanks out, which is why Back Office doesn't pull the P80 of groups down. "" from an IF is a blank too.
Arithmetic turns blanks into 0. In a row formula, blank − blank is 0, as it is in a spreadsheet. So a formula like {{P95 Queue Wait}} - {{P80 Queue Wait}} shows 0 on a group with no data, and that 0 then counts in the rollup. In the example table, seven real gaps and one false 0 would have averaged to a very different number. Keep the blank instead:
IF(OR(ISBLANK({{P80 Queue Wait}}), ISBLANK({{P95 Queue Wait}})), "", {{P95 Queue Wait}} - {{P80 Queue Wait}})
OR is fine here, because in a row formula each reference is one value.
Zeros you don't want, leave out in the rollup. If a 0 means "no activity" rather than a real result, use the Avg (excl. 0) or Median (excl. 0) preset, or the percentile-ignoring-zeros recipe above.
Errors stop a rollup unless you skip them. One #DIV/0! in the column makes the rollup an error. Wrap the column: AVERAGE(IFERROR({{this}}, "")) or PERCENTILE(IFERROR({{this}}, ""), 0.8).
Filters change a rollup; format rules don't. A rollup covers the rows left after the table's filters, including a Zendesk Groups (Table Filter) that picks which groups appear. Format rules that hide rows only hide them on screen after the table loads, so the rollup still includes them. To leave rows out of a percentile, filter the table or segment them out in the rollup.

Building it with Claude through the Qvasa MCP

Everything above can be built by an AI agent through the Qvasa MCP, which is often quicker than clicking through the editor, especially when you're trying several rollups. Connect Claude first with User Access Tokens and the Qvasa MCP. The token needs write access to build anything.

The MCP gives Claude a small set of entry tools and a searchable catalog of everything else. qvasa_create_session opens a session; qvasa_search_tools finds a tool from a plain-language phrase; qvasa_get_tool_guide returns a tool's parameters and the order of its calls; qvasa_read_api and qvasa_write_api call it. You don't need to know the tool names. Each phrase below, passed to qvasa_search_tools, returns the tool beside it as its first result:

Search for	Finds	What it's for
create dashboard	create_dynamic_dashboard	A new dashboard (open, private or personal) to hold the table.
add widget to dashboard	create_dashboard_widget	Saves the Groups table (widget zendesk_groups_table) with its columns, sort and filters.
find table columns	find_configurable_table_columns	The columns a table offers. With for_formula, the ones a formula can read, each with a ready-to-paste reference. Search it for "P80".
percentile	describe_configurable_table_formula_language	Every function, operator and error, plus the rollup rules: segmenting, rows included, coercions.
test a formula	evaluate_configurable_table_formula	Runs an expression over numbers you supply, with no table and no data. The quickest way to check a rollup recipe before it meets real rows.
validate formula	validate_configurable_table_formula	Checks a row formula, or a rollup with context rollup, against the table's real columns. Saves nothing.
create column formula	create_configurable_table_column_formula	Saves a formula and its rollup, and adds the column to a table widget in the same call.
column totals row	set_configurable_table_column_formula_rollup	Adds or changes the rollup on a formula that already exists.
percentile rollup	suggest_configurable_table_column_formula_rollups	Ready-to-save rollups for a formula, including the P80, P95 and Avg (middle 80%) presets.

For the whole sequence in one place, qvasa_get_workflow with build_formula_widget returns the steps for a calculated column and its totals row, in order.

The order Claude follows to build the example table:

find_configurable_table_columns on zendesk_groups_table, for_formula true, query "queue wait": the references for P80, P95 and Median Queue Wait.
evaluate_configurable_table_formula on each rollup, with context rollup and a column of sample numbers that includes a blank. For example, PERCENTILE({{this}}, 0.8) over [10, 20, 30, 40, 50] should answer 42.
create_dynamic_dashboard, then create_dashboard_widget with the Groups table and its metric columns.
create_configurable_table_column_formula for each formula, with output_format duration, its rollup, and the widget's id so the column joins the table.
Query the widget and read the rollups back from the table's envelope, to check the totals row shows what was intended.

An example prompt that builds the table in this guide:

Using the Qvasa MCP, build me an open dashboard called "Speed of answer percentiles" with a
Zendesk Groups table for the last 7 days. Columns: group name, Median, P80 and P95 Queue Wait.
Then add formula columns with rollups, all shown as durations:
- "P80 Wait: Across Groups" = the P80 Queue Wait column, rollup "P80 of groups" = PERCENTILE at 0.8
- "Tail Gap (P95 − P80)" = P95 minus P80, blank when either is blank,
  rollup "Avg excl. top 10%" = the average after leaving out rows above the 90th percentile
Test each rollup with the evaluate tool before saving it, then read the table back and tell me
the totals row.

Troubleshooting

The rollup is lower than any group I care about. Rows of 0 are being counted. Look for a row formula doing arithmetic on blanks, and guard it with ISBLANK, or switch the rollup to its "excl. 0" form.
The P80 of groups doesn't match the P80 I expected for all chats. It's a percentile across groups, each group counting once. Each group's own per-chat P80 is in the P80 Queue Wait column.
Avg (middle 80%) shows the same as Average. The table has fewer than ten rows, so TRIMMEAN at 0.2 drops none. Raise the fraction (0.3 drops two of seven) or use the segment-then-reduce recipe.
The rollup says #VALUE! An AND or OR is testing the whole column. Nest IFs instead.
The rollup says #NUM! A percentile above 1 or below 0, a QUARTILE outside 0 to 4, an n larger than the row count, or no numbers in the column at all.
Rows I hid are still in the rollup. Format rules hide rows on screen only. Filter the table, or segment the rows out in the rollup.
A group's P80 Queue Wait is blank. The group isn't the primary group of any omnichannel queue, or none of its queue stays both started in the date range and ended.