Components · Tables

Pivot table

Measures cross-tabulated by a row and a column dimension, with totals.

Open in playground

Preview

Revenue by region and plan

PlanStarterGrowthEnterpriseSum
RegionRevenueRevenueRevenueRevenue
North America$52K$133K$139K$325K
Europe$39K$92K$90K$221K
Asia Pacific$21K$72K$53K$146K
Latin America$11K$36K$37K$84K
Middle East$9.5K$28K$24K$61K
Sum$133K$361K$343K$837K

Usage

tsx
import {
  ChartsProvider,
  PivotTable,
  dimension,
  measure,
  results,
} from 'em-kit';

const region = dimension({ name: 'region', title: 'Region' });
const plan = dimension({ name: 'plan', title: 'Plan' });
const revenue = measure({
  name: 'revenue',
  title: 'Revenue',
  inputs: {
    currency: 'USD',
    abbreviateLargeNumber: true,
    columnAggregation: ['sum'],
    rowAggregation: ['sum'],
  },
});

export const Example = () => (
  <ChartsProvider>
    <div style={{ height: 440 }}>
      <PivotTable
        title="Revenue by region and plan"
        rowDimension={region}
        columnDimension={plan}
        measures={[revenue]}
        results={results(rows)}
      />
    </div>
  </ChartsProvider>
);

const rows = [
  { region: 'North America', plan: 'Starter', revenue: 51855 },
  { region: 'North America', plan: 'Growth', revenue: 133461 },
  { region: 'North America', plan: 'Enterprise', revenue: 139290 },
  { region: 'Europe', plan: 'Starter', revenue: 39151 },
  { region: 'Europe', plan: 'Growth', revenue: 92491 },
  { region: 'Europe', plan: 'Enterprise', revenue: 89555 },
  { region: 'Asia Pacific', plan: 'Starter', revenue: 21212 },
  { region: 'Asia Pacific', plan: 'Growth', revenue: 71767 },
  { region: 'Asia Pacific', plan: 'Enterprise', revenue: 53432 },
  { region: 'Latin America', plan: 'Starter', revenue: 11394 },
  { region: 'Latin America', plan: 'Growth', revenue: 36004 },
  { region: 'Latin America', plan: 'Enterprise', revenue: 36747 },
  { region: 'Middle East', plan: 'Starter', revenue: 9475 },
  { region: 'Middle East', plan: 'Growth', revenue: 27647 },
  { region: 'Middle East', plan: 'Enterprise', revenue: 23998 },
];
  • Totals are per measure: columnAggregation and rowAggregation in the measure's inputs.

Install with npm install em-kit. Needs <ChartsProvider> once and a parent with a height: Quick start.

Examples

Average, min and max

One measure with row statistics instead of plain totals. rowAggregation adds a column per statistic and columnAggregation a row, always in the order sum, min, max, average. Both take any of 'sum', 'min', 'max', 'average'.

tsxDiffers from Usage
Show full code (52 lines)
import {
  ChartsProvider,
  PivotTable,
  dimension,
  measure,
  results,
} from 'em-kit';

const region = dimension({ name: 'region', title: 'Region' });
const plan = dimension({ name: 'plan', title: 'Plan' });
const revenue = measure({
  name: 'revenue',
  title: 'Revenue',
  inputs: {
    currency: 'USD',
    abbreviateLargeNumber: true,
    columnAggregation: ['average'],
    rowAggregation: ['average', 'min', 'max'],
  },
});

export const Example = () => (
  <ChartsProvider>
    <div style={{ height: 440 }}>
      <PivotTable
        title="Revenue by region and plan"
        rowDimension={region}
        columnDimension={plan}
        measures={[revenue]}
        results={results(rows)}
      />
    </div>
  </ChartsProvider>
);

const rows = [
  { region: 'North America', plan: 'Starter', revenue: 51855 },
  { region: 'North America', plan: 'Growth', revenue: 133461 },
  { region: 'North America', plan: 'Enterprise', revenue: 139290 },
  { region: 'Europe', plan: 'Starter', revenue: 39151 },
  { region: 'Europe', plan: 'Growth', revenue: 92491 },
  { region: 'Europe', plan: 'Enterprise', revenue: 89555 },
  { region: 'Asia Pacific', plan: 'Starter', revenue: 21212 },
  { region: 'Asia Pacific', plan: 'Growth', revenue: 71767 },
  { region: 'Asia Pacific', plan: 'Enterprise', revenue: 53432 },
  { region: 'Latin America', plan: 'Starter', revenue: 11394 },
  { region: 'Latin America', plan: 'Growth', revenue: 36004 },
  { region: 'Latin America', plan: 'Enterprise', revenue: 36747 },
  { region: 'Middle East', plan: 'Starter', revenue: 9475 },
  { region: 'Middle East', plan: 'Growth', revenue: 27647 },
  { region: 'Middle East', plan: 'Enterprise', revenue: 23998 },
];
const revenue = measure({
  name: 'revenue',
  title: 'Revenue',
  inputs: {
    currency: 'USD',
    abbreviateLargeNumber: true,
    columnAggregation: ['average'],
    rowAggregation: ['average', 'min', 'max'],
  },
});

<PivotTable
  title="Revenue by region and plan"
  rowDimension={region}
  columnDimension={plan}
  measures={[revenue]}
  results={results(rows)}
/>

Share of total

Each cell as a percentage, with column totals. showAsPercentage shows each cell as its share; other measures may be ignored when it's on. columnAggregation: ['sum'] adds the totals row.

tsxDiffers from Usage
Show full code (52 lines)
import {
  ChartsProvider,
  PivotTable,
  dimension,
  measure,
  results,
} from 'em-kit';

const region = dimension({ name: 'region', title: 'Region' });
const plan = dimension({ name: 'plan', title: 'Plan' });
const revenue = measure({
  name: 'revenue',
  title: 'Revenue',
  inputs: {
    currency: 'USD',
    decimalPlaces: 1,
    showAsPercentage: true,
    columnAggregation: ['sum'],
  },
});

export const Example = () => (
  <ChartsProvider>
    <div style={{ height: 440 }}>
      <PivotTable
        title="Revenue by region and plan"
        rowDimension={region}
        columnDimension={plan}
        measures={[revenue]}
        results={results(rows)}
      />
    </div>
  </ChartsProvider>
);

const rows = [
  { region: 'North America', plan: 'Starter', revenue: 51855 },
  { region: 'North America', plan: 'Growth', revenue: 133461 },
  { region: 'North America', plan: 'Enterprise', revenue: 139290 },
  { region: 'Europe', plan: 'Starter', revenue: 39151 },
  { region: 'Europe', plan: 'Growth', revenue: 92491 },
  { region: 'Europe', plan: 'Enterprise', revenue: 89555 },
  { region: 'Asia Pacific', plan: 'Starter', revenue: 21212 },
  { region: 'Asia Pacific', plan: 'Growth', revenue: 71767 },
  { region: 'Asia Pacific', plan: 'Enterprise', revenue: 53432 },
  { region: 'Latin America', plan: 'Starter', revenue: 11394 },
  { region: 'Latin America', plan: 'Growth', revenue: 36004 },
  { region: 'Latin America', plan: 'Enterprise', revenue: 36747 },
  { region: 'Middle East', plan: 'Starter', revenue: 9475 },
  { region: 'Middle East', plan: 'Growth', revenue: 27647 },
  { region: 'Middle East', plan: 'Enterprise', revenue: 23998 },
];
const revenue = measure({
  name: 'revenue',
  title: 'Revenue',
  inputs: {
    currency: 'USD',
    decimalPlaces: 1,
    showAsPercentage: true,
    columnAggregation: ['sum'],
  },
});

<PivotTable
  title="Revenue by region and plan"
  rowDimension={region}
  columnDimension={plan}
  measures={[revenue]}
  results={results(rows)}
/>

Full numbers

Every cell and total written out in full, in wider columns. columnWidth and firstColumnWidth are in pixels; the table scrolls sideways when the columns outgrow the card.

tsxDiffers from Usage
Show full code (54 lines)
import {
  ChartsProvider,
  PivotTable,
  dimension,
  measure,
  results,
} from 'em-kit';

const region = dimension({ name: 'region', title: 'Region' });
const plan = dimension({ name: 'plan', title: 'Plan' });
const revenue = measure({
  name: 'revenue',
  title: 'Revenue',
  inputs: {
    currency: 'USD',
    decimalPlaces: 0,
    columnAggregation: ['sum'],
    rowAggregation: ['sum'],
  },
});

export const Example = () => (
  <ChartsProvider>
    <div style={{ height: 440 }}>
      <PivotTable
        title="Revenue by region and plan"
        rowDimension={region}
        columnDimension={plan}
        measures={[revenue]}
        results={results(rows)}
        columnWidth={130}
        firstColumnWidth={170}
      />
    </div>
  </ChartsProvider>
);

const rows = [
  { region: 'North America', plan: 'Starter', revenue: 51855 },
  { region: 'North America', plan: 'Growth', revenue: 133461 },
  { region: 'North America', plan: 'Enterprise', revenue: 139290 },
  { region: 'Europe', plan: 'Starter', revenue: 39151 },
  { region: 'Europe', plan: 'Growth', revenue: 92491 },
  { region: 'Europe', plan: 'Enterprise', revenue: 89555 },
  { region: 'Asia Pacific', plan: 'Starter', revenue: 21212 },
  { region: 'Asia Pacific', plan: 'Growth', revenue: 71767 },
  { region: 'Asia Pacific', plan: 'Enterprise', revenue: 53432 },
  { region: 'Latin America', plan: 'Starter', revenue: 11394 },
  { region: 'Latin America', plan: 'Growth', revenue: 36004 },
  { region: 'Latin America', plan: 'Enterprise', revenue: 36747 },
  { region: 'Middle East', plan: 'Starter', revenue: 9475 },
  { region: 'Middle East', plan: 'Growth', revenue: 27647 },
  { region: 'Middle East', plan: 'Enterprise', revenue: 23998 },
];
const revenue = measure({
  name: 'revenue',
  title: 'Revenue',
  inputs: {
    currency: 'USD',
    decimalPlaces: 0,
    columnAggregation: ['sum'],
    rowAggregation: ['sum'],
  },
});

<PivotTable
  title="Revenue by region and plan"
  rowDimension={region}
  columnDimension={plan}
  measures={[revenue]}
  results={results(rows)}
  columnWidth={130}
  firstColumnWidth={170}
/>

Sub-rows on demand

Each region opens to its countries, loaded the first time it is opened. setExpandedRowKey is called with a row’s rowDimension value the first time it is opened: add it to expandedRowKeys and load resultsSubRows, whose rows carry the rowDimension, subRowDimension and columnDimension values and the measures.

PivotSubRows.tsx
import { useState } from 'react';
import { ChartsProvider, PivotTable, dimension, measure, results } from 'em-kit';

const region = dimension({ name: 'region', title: 'Region' });
const country = dimension({ name: 'country', title: 'Country' });
const quarter = dimension({ name: 'quarter', title: 'Quarter' });
const revenue = measure({
  name: 'revenue',
  title: 'Revenue',
  inputs: { currency: 'USD', decimalPlaces: 0 },
});

// The table's rows: one per region and quarter.
const rows = [
  { region: 'Americas', quarter: 'Q1', revenue: 182000 },
  { region: 'Americas', quarter: 'Q2', revenue: 197000 },
  { region: 'EMEA', quarter: 'Q1', revenue: 143000 },
  { region: 'EMEA', quarter: 'Q2', revenue: 151000 },
];

// The sub-rows: one per region, country and quarter. Each carries its
// region, so the table can put it under the right row.
const byCountry = [
  { region: 'Americas', country: 'United States', quarter: 'Q1', revenue: 141000 },
  { region: 'Americas', country: 'United States', quarter: 'Q2', revenue: 150000 },
  { region: 'Americas', country: 'Canada', quarter: 'Q1', revenue: 41000 },
  { region: 'Americas', country: 'Canada', quarter: 'Q2', revenue: 47000 },
  { region: 'EMEA', country: 'Germany', quarter: 'Q1', revenue: 79000 },
  { region: 'EMEA', country: 'Germany', quarter: 'Q2', revenue: 83000 },
  { region: 'EMEA', country: 'France', quarter: 'Q1', revenue: 64000 },
  { region: 'EMEA', country: 'France', quarter: 'Q2', revenue: 68000 },
];

export function RevenueByRegion() {
  // The regions opened so far. The table calls setExpandedRowKey with a
  // region the first time a reader opens it; closing it is the table's own.
  const [opened, setOpened] = useState<string[]>([]);
  const open = (key: string) =>
    setOpened((keys) => (keys.includes(key) ? keys : [...keys, key]));

  // Stand-in for your API: the countries of the opened regions only.
  const subRows = byCountry.filter((row) => opened.includes(row.region));

  return (
    <div style={{ height: 300 }}>
      <PivotTable
        title="Revenue by region"
        rowDimension={region}
        subRowDimension={country}
        columnDimension={quarter}
        measures={[revenue]}
        results={results(rows)}
        resultsSubRows={results(subRows)}
        expandedRowKeys={opened}
        setExpandedRowKey={open}
      />
    </div>
  );
}

// In your app, the provider goes once around the part with charts.
export const Example = () => (
  <ChartsProvider>
    <RevenueByRegion />
  </ChartsProvider>
);

Props

PropTypeDefaultDescription
rowDimensionrequiredDimensionThe dimension down the side of the grid.
columnDimensionrequiredDimensionThe dimension across the top of the grid.
measuresrequiredMeasure[]The numbers shown in the cells. Their inputs set the formatting and the row and column totals.
resultsrequiredDataResponseYour query’s state: { isLoading, data, error }, where data is your rows. results(rows) builds it from an array.
resultsSubRowsDataResponseRows for the second level, loaded when a row is expanded.
subRowDimensionDimensionA second row dimension, shown when a row is expanded.
displayNullAsstringText shown where a value is null, for example "–".
columnWidthnumberA fixed width for the value columns, in pixels.
firstColumnWidthnumberA fixed width for the row-label column, in pixels.
expandedRowKeysstring[]The rows whose sub-rows you load, by rowDimension value: each key setExpandedRowKey has given you. Only needed with subRowDimension.
setExpandedRowKey(expandedRowKeys: string) => voidCalled with a row’s rowDimension value, as a string, the first time a reader opens the row. Add it to expandedRowKeys and load resultsSubRows for it; closing the row is the table’s own.
titlestringThe card’s title. A "key|fallback" string is looked up in your translations.
descriptionstringSmaller text under the title. Translates like title.
tooltipstringText for an info icon beside the title.
hideMenubooleanfalseHide the card’s header: the title, description and menu.
menuOptions("csv" | "xlsx" | "png" | "maximize")[]["csv", "xlsx", "png", "maximize"]Which entries the card menu offers. Pass [] for none.

A blank default means the prop is unset unless you pass it.

Field options

Set in a field’s inputs, beside the common options:

OptionTypeSet onDescription
columnAggregation('sum' | 'min' | 'max' | 'average')[]measuresAdd a row under the table for each calculation you list, worked out down each column.
rowAggregation('sum' | 'min' | 'max' | 'average')[]measuresAdd a column at the end of the table for each calculation you list, worked out along each row.
showAsPercentagebooleanmeasuresShow each cell as a share of the total instead of its value. Other measures may be left out. Default: false.

Keyboard

KeyWhereWhat it does
Tab, Shift + TabTableWith subRowDimension, moves between the row headers. Without it, nothing in the table takes focus.
Enter or SpaceRow header, with subRowDimensionOpens or closes its sub-rows.

The card menu’s keys, and what is still missing, are in Accessibility.