Median, p95 and Spread Per Rep, Not Just the Totals

Whoever runs the quarterly comp conversation starts with one number per rep: revenue, summed from an export or a saved query, to decide who gets the bonus. That number hides the shape of the quarter completely. A rep who closed one $940 deal and three small ones ranks next to a rep who closed four steady $420 deals, because a sum cannot tell a lucky outlier from a dependable quarter, and the panel built to tell them apart is usually its own query against the same orders, with its own groupBy and its own average. Filter that query to one region and it stops matching the table in front of it, because nothing told it to run again. A median and percentile JavaScript grid, reading the same orders, is the fix for a total that cannot tell the two reps apart.

Point a second grid at the orders grid, group by rep, and reduce each group with three kernels instead of one: median for the typical deal, p95 for the outlier end, stddev for how far the deals sit from each other. All three read the orders grid’s own rows, so narrowing the orders to a region re-derives the same three figures per rep on the next frame, not a stale sum.

That turns the comp conversation from a debate about whose export is current into a read of one screen. Filter the orders to a region and the median, p95 and spread per rep recompute together, with no second export to rerun before the meeting starts. A rep whose p95 sits close to their median sold about the same deal every time; a rep whose p95 is three or four times their median sold one big deal and the rest were filler, and the spread says so in one column without anyone opening the raw orders.

Median and percentile JavaScript, grouped by rep

import { createGrid } from '@toclocoinc/lattice-grid';

const orders = [
  { id: 1,  rep: 'Priya Shah',   region: 'EMEA',     amount: 940 },
  { id: 2,  rep: 'Priya Shah',   region: 'EMEA',     amount: 150 },
  { id: 3,  rep: 'Priya Shah',   region: 'Americas', amount: 160 },
  { id: 4,  rep: 'Priya Shah',   region: 'Americas', amount: 175 },
  { id: 5,  rep: 'Mateo Ruiz',   region: 'EMEA',     amount: 420 },
  { id: 6,  rep: 'Mateo Ruiz',   region: 'EMEA',     amount: 410 },
  { id: 7,  rep: 'Mateo Ruiz',   region: 'Americas', amount: 440 },
  { id: 8,  rep: 'Mateo Ruiz',   region: 'Americas', amount: 460 },
  { id: 9,  rep: 'Jin Park',     region: 'EMEA',     amount: 295 },
  { id: 10, rep: 'Jin Park',     region: 'EMEA',     amount: 305 },
  { id: 11, rep: 'Jin Park',     region: 'Americas', amount: 300 },
  { id: 12, rep: 'Jin Park',     region: 'Americas', amount: 310 },
  { id: 13, rep: 'Owen Clarke',  region: 'EMEA',     amount: 95 },
  { id: 14, rep: 'Owen Clarke',  region: 'EMEA',     amount: 150 },
  { id: 15, rep: 'Owen Clarke',  region: 'Americas', amount: 820 },
  { id: 16, rep: 'Owen Clarke',  region: 'Americas', amount: 275 },
  { id: 17, rep: 'Lena Fischer', region: 'EMEA',     amount: 220 },
  { id: 18, rep: 'Lena Fischer', region: 'EMEA',     amount: 230 },
  { id: 19, rep: 'Lena Fischer', region: 'Americas', amount: 260 },
  { id: 20, rep: 'Lena Fischer', region: 'Americas', amount: 340 },
];

const book = createGrid(document.querySelector('#orders'), {
  rowKey: 'id',
  columns: [
    { field: 'id',     title: 'Order' },
    { field: 'rep',    title: 'Rep', filter: { type: 'set' } },
    { field: 'region', title: 'Region', filter: { type: 'set' } },
    { field: 'amount', title: 'Amount', type: 'number' },
  ],
  rows: orders,
});

// A second grid whose rows come from the first.
const byRep = createGrid(document.querySelector('#by-rep'), {
  source: {
    mode: 'derived',
    from: book,            // the grid to read
    follow: 'filtered',    // read whatever the filters leave
    groupBy: 'rep',
    select: {
      median: { of: 'amount', fn: 'median' },
      p95:    { of: 'amount', fn: 'p95' },
      spread: { of: 'amount', fn: 'stddev' },
    },
    sort: [{ col: 'median', dir: 'desc' }],
  },
  columns: [
    { field: 'rep',    title: 'Rep' },
    { field: 'median', title: 'Median', type: 'number' },
    { field: 'p95',    title: 'p95', type: 'number' },
    { field: 'spread', title: 'Spread', type: 'number' },
  ],
});

Filter the orders grid’s Region column to EMEA and byRep re-derives all three figures from only the EMEA rows, because median, p95 and stddev all read the orders grid’s current, filtered rows rather than a copy taken when the page loaded.

React

import React from 'react';
import { createGrid } from '@toclocoinc/lattice-grid';
import '@toclocoinc/lattice-grid/css';
import { createLatticeGrid } from '@toclocoinc/lattice-grid/modules/react';

// Build the component once, at module scope, not on every render.
const LatticeGrid = createLatticeGrid({ React, createGrid });

function RepSpread({ orders }) {
  const [book, setBook] = React.useState(null);

  const orderColumns = [
    { field: 'id',     title: 'Order' },
    { field: 'rep',    title: 'Rep', filter: { type: 'set' } },
    { field: 'region', title: 'Region', filter: { type: 'set' } },
    { field: 'amount', title: 'Amount', type: 'number' },
  ];

  const repColumns = [
    { field: 'rep',    title: 'Rep' },
    { field: 'median', title: 'Median', type: 'number' },
    { field: 'p95',    title: 'p95', type: 'number' },
    { field: 'spread', title: 'Spread', type: 'number' },
  ];

  return (
    <>
      <LatticeGrid
        rowKey="id"
        columns={orderColumns}
        rows={orders}
        onGridReady={setBook}
        style={{ height: 320 }}
      />
      {book && (
        <LatticeGrid
          columns={repColumns}
          source={{
            mode: 'derived',
            from: book,
            follow: 'filtered',
            groupBy: 'rep',
            select: {
              median: { of: 'amount', fn: 'median' },
              p95:    { of: 'amount', fn: 'p95' },
              spread: { of: 'amount', fn: 'stddev' },
            },
            sort: [{ col: 'median', dir: 'desc' }],
          }}
          style={{ height: 160 }}
        />
      )}
    </>
  );
}

onGridReady hands back the live orders grid as soon as it mounts, which is what the rep panel’s from needs; the panel itself only renders once that instance exists. Filtering the orders grid’s Region column moves the rep panel the same way it does in the plain example, because both read the grid’s public API, not a copy of the rows taken at render time.

The options used

What the example actually produced

Running the plain JavaScript example above with createHeadlessGrid from the published package, and printing byRep’s rows, gives:

Priya Shah and Owen Clarke have the lowest medians of the five, yet the highest p95 and spread by a wide margin: each has one deal several times the size of their others, and the spread names it instead of burying it in a sum. Mateo Ruiz and Jin Park sit at the opposite end, their p95 within a few percent of their median, which is what a rep selling about the same deal every time looks like in these columns.

Filtering the orders grid’s Region column to EMEA and re-reading byRep gives:

Every figure moved to match just the EMEA rows, Priya Shah’s spread most of all, because her one outlier deal happened to land in EMEA. Nothing in byRep’s own configuration changed; the region filter sat on the orders grid the whole time.

The derived and chained grids guide covers joins, column profiles, chaining a derived grid from a derived grid, and cross-filtering back into the source in full. For a worked dashboard built the same way, with a per-rep statistical profile alongside a revenue leaderboard, see the sales dashboard demo.