Lattice Grid Buy a licence

demo D68

Your own total function

A weighted mean or a median, and why it re-reduces

total: (values, ctx) => …

Building…
Loading a live grid…

The configuration

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@toclocoinc/lattice-grid@1.13.0/lattice-grid.min.css">
<script src="https://cdn.jsdelivr.net/npm/@toclocoinc/lattice-grid@1.13.0/lattice-grid.min.js"></script>

<div id="grid" style="height: 540px"></div>

<script>
  const grid = LatticeGrid.createGrid(document.getElementById('grid'), {
    rowKey: 'id',
    grandTotalRow: 'bottom',
    toolPanel: {
      side: 'left',
      panels: ['columns', 'filters', 'views', 'quick'],
      actions: ['undo', 'redo', 'export', 'restore', 'maximise'],
      exportName: 'lattice-demo',
    },
    columns: [
      { field: 'account', title: 'Account', filter: { type: 'set' }, group: { enabled: true, index: 0 } },
      { field: 'service', title: 'Service', filter: { type: 'set' } },
      { field: 'cost', title: 'Monthly cost', type: 'number', layout: { width: 170 },
        format: { style: 'currency', currency: 'USD', decimals: 2 }, total: 'sum' },
      {
        // Three columns read `cost`, so the two extra ones are named: an id
        // defaults to the field, and two columns cannot share one.
        id: 'costMedian', field: 'cost', title: 'Median cost', type: 'number',
        layout: { width: 160 }, format: { style: 'currency', currency: 'USD', decimals: 2 },
        /*
         * The reduction the built-in set cannot express. It receives the
         * column's values for the group being reduced, and is called again
         * for the grand total.
         *
         * A function is re-reduced on every change, group totals included,
         * because a median has no inverse: you cannot take one row out of it
         * by arithmetic the way you can a sum. That is the trade for
         * arbitrary reductions, and it is why the built-in `sum` beside it
         * is maintained by difference and this one is not.
         */
        total: (values) => {
          const nums = values.filter((v) => typeof v === 'number' && Number.isFinite(v))
            .sort((a, b) => a - b);
          if (!nums.length) return null;
          const mid = nums.length >> 1;
          return nums.length % 2 ? nums[mid] : (nums[mid - 1] + nums[mid]) / 2;
        },
      },
      {
        id: 'costP95', field: 'cost', title: 'p95 cost', type: 'number',
        layout: { width: 150 }, format: { style: 'currency', currency: 'USD', decimals: 2 },
        total: (values) => {
          const nums = values.filter((v) => typeof v === 'number' && Number.isFinite(v))
            .sort((a, b) => a - b);
          if (!nums.length) return null;
          return nums[Math.min(nums.length - 1, Math.floor(nums.length * 0.95))];
        },
      },
      {
        field: 'change', title: 'Weighted change', type: 'number',
        layout: { width: 180 }, format: { style: 'percent', decimals: 2 },
        /*
         * A mean weighted by the row's own value, which is as far as a
         * reduction can go: the function is handed one column's values, not
         * the rows, so a mean weighted by a *different* column is not
         * something this hook can express.
         */
        total: (values) => {
          let weight = 0;
          let sum = 0;
          for (const v of values) {
            if (typeof v !== 'number' || !Number.isFinite(v)) continue;
            const w = Math.abs(v);
            weight += w;
            sum += v * w;
          }
          return weight ? sum / weight : null;
        },
      },
    ],
    rows,  // cloud cost rows, one object per resource
  });
</script>

Writing a custom total function for weighted means and medians

The built-in aggregations, sum, average, min, max, and count, cover most footers, but a weighted mean, a median, or a percentile needs its own logic, and Lattice Grid exposes that through the total column option as a function: total: (values, ctx) => …. A developer reaches for this whenever a plain average would misrepresent the data, such as an order value weighted by quantity, or a latency column where the median is a more honest summary than the mean because a handful of slow requests would skew it. The function receives the array of values in scope, group or grand total, plus a context object carrying the row data for that scope, so a weighted mean can divide a weighted sum by a total weight drawn from a second column. This is also where “why it re-reduces” matters: a custom total cannot be built incrementally from a parent and a delta the way sum can, so Lattice Grid recomputes it from the full value set on every change to that scope rather than adjusting a running figure. For a JavaScript data grid handling frequent updates, that recomputation is bounded to the rows within the affected group, not the whole dataset, so a custom median on a group of a few hundred rows stays cheap while other groups update independently.

Set the column’s total to a function, total: (values, ctx) => …, rather than the string 'average'. Inside it, use ctx to reach the weighting column’s values for the same rows, multiply each value by its weight, sum the products, and divide by the sum of weights. Lattice Grid calls this function for every group footer and grand total row that includes the column.

Related demos