Column Formulas and Column Rollups: Per-Row Math and a Totals Row on Configurable Tables

A formula column is a column you write yourself: a spreadsheet-style formula over the other columns on an agents, tickets or organizations table, computed once per row. A column rollup is the same idea applied to the whole column: its own formula over every row the table shows, pinned in a totals row directly under the table header as Label: value. Together they turn a configurable table into something much closer to the spreadsheet your team probably keeps on the side, with the difference that the numbers refresh with the dashboard.

This guide walks through adding a formula column from a table, giving it a rollup, and reading the result. If you want the longer story of building a metric from agent status collections, start with Status Collections and Formula Columns: Build Your Own Agent Metrics; this article picks up where that one leaves the formula editor.

Before you start

Formula columns are switched on per account. If you do not see Table Column Formulas in Settings, contact Qvasa Support and we will enable them.
Two permissions govern writing formulas. Manage your own table column formulas lets someone create formulas and edit or delete the ones they made; manage all table column formulas lets them edit or delete anyone's. Admins hold both. Rollups live on the formula, so whoever can edit a formula can add, change or remove its rollup.
Using a formula is a different permission from writing one. Anyone who can edit a dashboard can add an existing formula column to a table on it, whether or not they can write formulas.
Every configurable table gets its own formulas. Agents, tickets, organizations, groups, skills and the other configurable tables each have formulas scoped to that table's columns. The examples here use the agents table; the steps are the same everywhere.

Step 1: Open the formula editor from the table

Open the dashboard, click Edit, and click the + at the right end of the table's header row. The Add a column or formula panel opens with the table's remaining columns on the left and its existing formulas on the right, both searchable. Picking a formula adds it to the table straight away, and offers to add the columns it reads at the same time.

To write a new one, click Create formula column… at the top of the panel.

The same editor is available from Settings → Table Column Formulas as a single scrolling page. From a dashboard it opens as a three-step wizard, Details → Calculation → Rollup, with a bar fixed to the bottom that shows which step you are on, what is still missing, and the Create formula button, so nothing scrolls out of reach on a small screen.

Step 2: Details

Give the formula a Name, which becomes the column header. The Table is already set to the table you started from. Show the result as picks the output format: Number, Percent, Duration or Text, with Decimal places beside it. The Description is optional but worth a sentence; it becomes the column header's tooltip and shows in the settings list, so the person reading the table in six months knows what you meant.

Two format details save a lot of head-scratching:

Percent means a fraction. With Percent selected, a result of 0.25 displays as 25%. Divide; do not multiply by 100.
Durations are seconds. A duration column reads as a number of seconds inside a formula. Divide by 3600 for hours, or set the output format to Duration and let Qvasa format it as hours and minutes.

Step 3: Calculation

The Calculation step has the table's columns on the left and your formula on the right.

Insert a column lists every column the table can show, grouped: On this table first (the columns already on the widget, including its formulas), then Formulas, then the metric families such as Agent statuses, Talk, Messaging and Email. Search or expand a group and click Insert to drop the column in at your cursor. Each row shows the column's format, so you know whether you are about to divide a duration by a count.
The Formula box shows the stored reference for each column rather than its display name. That is deliberate: a column can be renamed without breaking the formula. Reads as, underneath, shows the same formula back in plain column names, and that is the line to read while you work.
Columns in this formula lists what you have actually referenced. It also decides what a rollup may use in the next step.
It validates as you type. ✓ This formula is valid appears when it parses; otherwise the message points at the problem. Preview in the bottom bar runs it against real rows with the arithmetic shown.
Functions are one click away, grouped into Logic, Errors, Math and Text. The full list is in the language reference section below.

A formula can read another formula. Define a percentage once and point a second formula at it, and when you change the first, both columns follow.

Step 4: Rollup

The Rollup step is optional, and it is where the totals row comes from. Skip it and the column has no rollup cell; fill it in and the value appears in a row pinned under the header on every table that shows this formula.

A rollup is its own formula. In a rollup a column reference means the whole column, and {{this}} means the formula's own column, so the expression has to fold everything down to one value with SUM, AVERAGE, MEDIAN, COUNTIF and the like. There are three ways to fill it in:

Start from offers the common folds of this column: Total, Average, Avg (excl. 0), Avg (skip errors), Median, Median (excl. 0), Min, Max, Count, P80, P95 and Avg (middle 80%). One click fills the rollup formula and, if you have not typed one, the label. You can edit the result afterwards; a preset is a starting point, not a mode.
Combine the formula's columns builds a rollup out of the columns your formula reads, each folded first: Add the column totals, Formula on totals, Add the column averages and First total minus the rest. Formula on totals is the one to reach for when the rows are a ratio: it wraps every column in SUM, so a row formula of handled ÷ offered becomes SUM(handled) ÷ SUM(offered), a ratio of totals rather than an average of each row's ratio. Those two numbers are usually different, and the ratio of totals is normally the one a manager means by "overall".
Columns in this formula, on the left, inserts a whole column where your cursor is, or its SUM or AVG with the small buttons beside it, when you want to write the rollup by hand.

Give the rollup a Label (it prints before the value, as in Team total: 252), choose Show the rollup as if the format should differ from the formula's own (a count of rows under a percentage column, for example), and set Decimal places if needed. Both default to the formula's settings. Reads as and ✓ This rollup is valid work exactly as they do for the row formula. Clear rollup empties the step. Then click Create formula.

One rule to know: a rollup may use only the columns in its own formula, plus {{this}}. If you later remove a column from the formula that its rollup still reads, the editor flags the rollup and asks you to fix it before saving.

Percentile rollups: P80, P95 and averages without outliers

An average can hide what is happening at the edges of a team. A percentile rollup answers a different question: what value do most rows stay at or under? A P80 of 45.6 hours means 80% of the agents in the table worked 45.6 hours or less. Service levels are often set this way. "80% of chats answered within 2 minutes" is a P80 target.

The presets

In the rollup step, Start from has three ready-made percentile rollups. Each one fills in the label and the rollup formula, which you can then edit:

P80: PERCENTILE({{this}}, 0.8), the value 80% of the rows are at or below.
P95: PERCENTILE({{this}}, 0.95), the value 95% of the rows are at or below.
Avg (middle 80%): TRIMMEAN({{this}}, 0.2), the average after dropping the lowest 10% and the highest 10% of rows. It's an average that one outlier can't drag up or down.

The functions behind them

PERCENTILE(range, k): the k-th percentile, with k between 0 and 1. It interpolates between rows the same way PERCENTILE does in Excel and Google Sheets.
QUARTILE(range, q): q = 1 is the 25th percentile, 2 the median, 3 the 75th.
LARGE(range, n) and SMALL(range, n): the n-th largest or smallest row, for example the second-highest value.
TRIMMEAN(range, fraction): the average after dropping that fraction of rows, split between the top and the bottom.

Empty cells are skipped, as in SUM and AVERAGE.

Across rows, not inside them

A rollup works on the values the table shows, so a percentile rollup is a percentile across the table's rows. In the table below, the P80 of Hours worked is taken across the eight agents' totals: 0, 9, 18, 36, 36, 42, 48 and 63 hours give 45.6.

That's different from a percentile inside each row, such as the time within which 80% of one group's tickets were answered. A per-row percentile is a metric of its own, so it's a column rather than a rollup. On the Groups table, add P80 Queue Wait, P95 Queue Wait, P80 Comment FTT or P95 Comment FTT. Those columns can then have a rollup of their own, for example the average of each group's P80.

Segment, then reduce

To roll up only part of a column, blank out the rows you don't want with IF, then reduce what's left. AVERAGE skips the blanks:

AVERAGE(IF({{this}} > PERCENTILE({{this}}, 0.9), "", {{this}})): the average of the rows at or below the 90th percentile. In the table above this is Avg up to P90: 27, the average without the one agent who worked 63 hours.
COUNT(IF({{this}} > PERCENTILE({{this}}, 0.8), 1, "")): how many rows are above the P80. COUNT counts only the 1s.

Hidden rows still count

Format rules that hide rows only hide them on screen, after the table loads, so a rollup still includes them. To leave rows out of a percentile, filter the table, or use the segment-then-reduce pattern above.

What the table shows

Back on the dashboard the formula appears as a column with a small fx marker in each cell, and its rollup sits in the totals row under the header. A few behaviours are worth knowing so the numbers never surprise you:

Rollups are computed over the rows the table finally shows. Row filters (such as hiding zero rows) and the row limit apply first, so a table limited to the top ten agents totals those ten. The quick-search box does not change a rollup; it only hides rows on screen.
Format rules do not change a rollup either. A rule that hides rows (hide agents with zero productive time, say) runs in your browser after the table has loaded, while the rollup is computed on the server, so the hidden rows are still in it. To leave rows out of a rollup, filter the table, or blank them inside the rollup itself with IF, as the examples below do.
The totals row stays put. It is part of the header, so sorting, quick search and scrolling never move it.
Hidden and collapsed columns hide their rollup cell, and the whole row disappears when no visible column has a rollup. A table with no rollups looks exactly as it did before.
Error rows behave like a spreadsheet. An agent with no activity gives #DIV/0! in a ratio column, and a plain Average over that column inherits the error. Use Avg (skip errors), or wrap the row formula in IFERROR, to average the rows that have a number.
CSV export includes the rollup as a Label: value row under the header, and dynamically sized widgets make room for the extra row.
Hover a cell for its working. The tooltip on a formula value shows the inputs and the arithmetic for that row; the header tooltip carries the description and the formula in readable names.

Managing formulas and rollups

Settings → Table Column Formulas lists every formula on the account, grouped by table, with its calculation in plain column names, its rollup on a second line (Rollup · Team total: SUM({{this}})), its output format, its owner and how many tables use it. The pencil opens the same editor as a single page, where the Column rollup section sits collapsed under the formula until you open it, and Remove rollup takes the totals cell away without touching the formula.

Formulas and their rollups can also be created, updated and removed through the User Access API, including a suggestions endpoint that returns the same presets and shortcuts the editor offers, ready to save, and an evaluate endpoint that runs any expression over values you supply, no table needed, so a formula or rollup can be tested before it meets real rows. See User Access Tokens and the Qvasa MCP for how to connect.

The formula language

It is a subset of what you already know from Excel and Google Sheets, with the same precedence rules.

References: a column on the same table, including other formulas; in a rollup, the whole column, and {{this}} for the formula's own column.
Values: numbers, "text", TRUE / FALSE, and percentages such as 50%.
Operators: + - * / ^, & to join text, and the comparisons = <> < <= > >=.
Logic: IF, IFS, AND, OR, NOT.
Errors and checks: IFERROR, ISBLANK, ISERROR, ISNUMBER.
Math: ROUND, ROUNDUP, ROUNDDOWN, ABS, VALUE, MIN, MAX, SUM, AVERAGE, MEDIAN, SUMPRODUCT, COUNT, COUNTA, COUNTBLANK, COUNTIF, SUMIF, AVERAGEIF, PERCENTILE, QUARTILE, TRIMMEAN, LARGE, SMALL.
Text: CONCATENATE.

Operators and single-value functions apply to a whole column cell by cell, the way ARRAYFORMULA does in Sheets, and the reducing functions follow Sheets range rules: blanks are skipped by AVERAGE and COUNT, text is ignored by SUM, and an error in any row travels through until an IFERROR catches it. VALUE() converts text to a number explicitly. Existing row formulas are unaffected by the rollup additions.

Rollup examples to build from:

SUM({{this}}): the plain total.
SUM({{Handled}}) / SUM({{Offered}}): a ratio of totals, for a column whose rows are {{Handled}} / {{Offered}}.
AVERAGEIF({{this}}, "<>0"): the average leaving out zero rows.
AVERAGE(IFERROR({{this}}, "")): the average skipping rows whose formula is an error.
MEDIAN(IF({{this}} <> 0, {{this}}, "")): the median excluding zeros.
SUMPRODUCT({{Score}}, {{Weight}}) / SUM({{Weight}}): a weighted average.
COUNTIF({{this}}, ">=85%"): how many agents cleared a target. COUNTIF against a number matches numbers only, not text that looks like one.
PERCENTILE({{this}}, 0.8): the P80 across the rows, the value 80% of the rows are at or below, interpolated between rows exactly as PERCENTILE does in Sheets and Excel. QUARTILE({{this}}, 3) is the third quartile; LARGE({{this}}, 2) the second-largest row.
TRIMMEAN({{this}}, 0.2): the average of the middle 80% of rows, the lowest 10% and highest 10% dropped. An average without the outliers, in one call; the Avg (middle 80%) preset.
AVERAGE(IF({{this}} > PERCENTILE({{this}}, 0.9), "", {{this}})): the average without the top 10%. This is the general recipe for running any reducer over part of a column: blank the rows you want out with a cell-by-cell IF (a percentile, a threshold, a zero check), then fold what is left with AVERAGE, MEDIAN, SUM, COUNT or MAX.

One distinction worth a sentence. A percentile in a rollup is a percentile across the table's rows: on a groups table, the P80 of each group's value. If what you need is the P80 of the individual tickets inside each group, the speed of answer 80% of chats were answered within, that is a column of its own: the groups table offers P80 Queue Wait, P95 Queue Wait, P80 Comment FTT and P95 Comment FTT beside the average and median columns, and a formula can read them like any other column.

Troubleshooting

The editor says the rollup must come out as one value. Something in the rollup is still per row. Wrap the column references in SUM, AVERAGE or another reducing function, or use a preset.
The rollup is flagged because it uses a column not in the formula. A rollup can read only its formula's columns and {{this}}. Add the column back to the formula, or rewrite the rollup without it.
The totals row is missing. No visible column on that table has a rollup, or the column with one is hidden or collapsed. Check the formula in Settings to confirm its rollup is set.
An average reads #DIV/0!. One row is an error and AVERAGE inherits it. Use Avg (skip errors), or IFERROR in the row formula.
A rollup still counts rows a format rule hides. Expected: rules hide rows in the browser, rollups are computed on the server. Filter the table, or blank the rows inside the rollup with IF.
I need a P80 of answer time per group, not across groups. Use the P80 / P95 Queue Wait and Comment FTT columns on the groups table; a PERCENTILE rollup gives the percentile across the rows.
The "overall" percentage does not match the average of the rows. That is expected. A ratio of totals weights busy agents more than quiet ones; an average of per-row ratios weights every agent equally. Pick the one your report means and label it accordingly.
A percentage shows as 8500%. The Percent format already multiplies by 100. Remove the * 100.
The total changed after adding a row filter. Rollups cover every row the table loaded, after its row filter and row limit. Anything you then hide on screen, with a format rule, the quick search box or sorting, is still counted. Remove the row filter or the row limit to total everyone.

Status Collections and Formula Columns: Build Your Own Agent Metrics, a worked example from status groups to a target check.
Configurable Table Widget: Agents Table, the table these formulas live on.
Combine Metrics with Widget Formulas, arithmetic between two widgets rather than between columns.
Ticket and Agent Drill-in Capabilities with Configurable Column Sets.