Measures

More Than One Measure

With a single measure, each generated column is headed by its key and nothing else. Give values a second entry and every crossing splits into one leaf per measure, with the key moving up into a header group above them.

Set header on a measure to name its leaf. A leaf without one takes the header of the column it measures, which reads well until two measures share a column: a sum and an average of Amount would both be headed Amount.

pivot<Sale>({
  rows: ['region'],
  columns: ['quarter'],
  values: [
    // With more than one measure, each crossing splits into a leaf per
    // measure, and header names the leaf. Without it the leaf takes the
    // header of the column it measures.
    { field: 'amount', agg: 'sum', header: 'Amount' },
    { field: 'units', agg: 'sum', header: 'Units' }
  ]
});

// agg takes anything grouping() takes: the nine named reductions, a
// percentile, a weighted average, or a function of your own.
pivot<Sale>({
  rows: ['region'],
  columns: ['quarter'],
  values: [
    { field: 'amount', agg: 'median', header: 'Median' },
    { field: 'amount', agg: { kind: 'percentile', p: 90 }, header: 'P90' },
    { field: 'amount', agg: (values, rows) => rows.length * 2, header: 'Custom' }
  ]
});

Shares

showAs reads a measure as something other than the number it reduced to. Three of its values divide by a total, and they differ only in which one. Switch between them below and follow where the 100% lands: at the end of each row, at the foot of each column, or in the one corner.

pivot<Sale>({
  rows: ['region'],
  columns: ['quarter'],
  values: [{ field: 'amount', agg: 'sum', showAs: 'percentOfRow' }],
  totalColumn: 'Total',
  totalRow: 'All regions'
});

// percentOfRow     over the rows the gathered row holds: a row reads across to 100%
// percentOfColumn  over the rows under the same crossing: a column reads down to 100%
// percentOfTotal   over every row the grid is showing

// A share is a fraction, and the generated column is typed percent, so
// 0.34 is drawn as 34% on screen and in every export.

// Change the reading later, by the measure's place in values:
import { getPivot } from '@sv5ui/datagrid';

getPivot(grid)?.setValueShowAs(0, 'percentOfColumn');
getPivot(grid)?.setValueShowAs(0, undefined); // as measured

Each denominator is measured from the rows rather than added up from the cells, for the reason Pivot gives. For a sum that changes nothing. For an average it is the difference between a share that means something and one that does not.

Along the Row

The other four values read a cell against the crossings beside it rather than against a total, so they follow the order the crossing is drawn in. A running total is how a year to date reads, a difference is how growth between two periods reads, and a rank answers which quarter was best without anyone scanning for it.

// These four read a cell against the crossings beside it, in the order
// the crossing is drawn in.
pivot<Sale>({
  rows: ['region'],
  columns: ['quarter'],
  values: [{ field: 'amount', agg: 'sum', showAs: 'runningTotal' }],
  totalColumn: 'Total'
});

// runningTotal           the measure over every crossing up to this one
// differenceFrom         this cell less the one before it
// percentDifferenceFrom  that difference over the one before
// rankInRow              its place among the cells on its row, 1 being the largest

// runningTotal takes a sum or a count and nothing else. Those are the two
// reductions where running the rows and adding the cells give the same
// answer. For any other the cells are left empty and the grid says so once.

// All four are left empty on a subtotal and on a total column, which stand
// outside the run.

Two things to notice in the grid. The total column goes empty under all four, because a total stands outside the run and a difference from it would be a difference from nothing in particular. And the first quarter has nothing before it, so a difference there is empty rather than a difference from zero. A gap does the same on both sides: West sold nothing in Q2, so neither its Q2 nor its Q3 has a difference to show, while its running total simply carries across the gap.

A running total accepts a sum or a count and refuses everything else, a measure with per included. Those two are the reductions where running the rows and adding the cells give the same answer. A running median is not something the grid will guess at: it leaves the cells empty and says so once in the console.

A Ratio

per divides a measure by the same reduction of another column, which is how a margin, a rate or an average per something is read. It divides the two reductions, not the rows.

The difference is not a rounding one. Take two sales: 50 of profit on 100, and 90 on 900. Together that is 140 on 1000, a margin of 14%. The two sales have margins of 50% and 10%, whose average is 30%. The larger sale carries nine times the weight, and only dividing the sums knows it.

pivot<Sale>({
  rows: ['region'],
  columns: ['quarter'],
  values: [
    { field: 'amount', agg: 'sum', header: 'Amount' },
    {
      field: 'profit',
      agg: 'sum',
      // Divide by the same reduction of another column.
      per: 'amount',
      // Profit is money, but profit over amount is a percentage, and only
      // the application knows that.
      type: 'percent',
      header: 'Margin'
    }
  ]
});

// It divides the two sums, not the rows. Take two sales: 50 of profit on
// 100, and 90 on 900. Together that is 140 on 1000, a margin of 14%. The
// two rows have margins of 50% and 10%, whose average is 30%. The larger
// sale carries nine times the weight, and only dividing the sums knows it.

type comes with it. Profit is money, but profit over amount is a percentage, and nothing but the application knows which. A reduction written as a function could compute the same figure, but only on the client and only until the view is saved. This form is two column names and a reduction, so it goes into a saved view and across the wire to a server.

Measures Down the Page

Measures on the column axis multiply it. Three measures over four quarters is twelve columns, and over twelve months it is thirty six. valuesOn: 'rows' stops the multiplying: each crossing becomes a single column, and every group opens into one row per measure, headed by a generated column that names the measure.

pivot<Sale>({
  rows: ['region'],
  columns: ['quarter'],
  values: [
    { field: 'amount', agg: 'sum', header: 'Amount' },
    { field: 'profit', agg: 'sum', header: 'Profit' },
    { field: 'units', agg: 'sum', header: 'Units' }
  ],
  // Each crossing is one column, and every group opens into one row per
  // measure, headed by a generated column naming the measure.
  valuesOn: 'rows'
});

// Three measures over four quarters is twelve columns on 'columns' and four
// on 'rows', so maxColumns buys three times the crossings.

grid.api.setPivotValuesOn?.('columns');

Two things follow from a column no longer naming a measure. Sorting by a crossing sorts the groups by the first measure, since the header a reader clicked cannot say which one they meant. And measures whose results are of different types now share a column, so that column carries no type and each value is drawn as it comes. Amount and profit are money and units are not, which is why the grid above loses its currency formatting when the measures go down the page. Give the measures the same type to get it back.