Pivot Table

Use me when you need to summarize data across two dimensions at once. I take a flat dataset and turn it into a structured cross-tabulation — rows are one dimension, columns are another, and every cell holds an aggregated metric. Perfect for sales by product and region, signups by month and plan, or any analysis where you want to see how two categorical dimensions interact.

Overview

A pivot table groups your data by a row dimension, optionally spreads a second dimension across column headers, and computes aggregated metrics at each intersection. The result is a structured summary table — not a chart — that lets you scan patterns across two axes simultaneously. You can stack multiple metrics (Revenue sum, Order count) and bucket date-based rows by Year, Quarter, Month, or Day.

Best used for:

• Cross-tabulating two categorical dimensions (e.g., Product × Region)
• Summarizing time-series data by period and category (e.g., Month × Plan)
• Comparing aggregated metrics across groups
• Building management-style summary reports
• Spotting which combination of dimensions drives the highest or lowest values

Common Use Cases

Sales & Revenue Analysis

• Revenue by product category and sales region
• Units sold by SKU and store location
• Average order value by customer segment and channel
• Discount rate by sales rep and quarter

User & Growth Metrics

• New signups by month and subscription plan
• Churn by cohort month and plan tier
• Feature adoption by team size and industry
• Session counts by device type and country

Finance & Operations

• Budget vs actual spend by department and cost center
• Headcount by office and team
• Support ticket volume by priority and assignee
• Inventory levels by warehouse and product category

Options

Plot Title

Optional - A label displayed at the top of the pivot table. Defaults to "Pivot Table" if left blank.

Metrics

Required - Define one or more numeric aggregations to compute at each row/column intersection.

Each metric entry has two fields:

Metric Field

Select a numerical column from your dataset to aggregate (e.g., Revenue, Order Count, Duration).

Aggregation

Choose how to summarize the selected column:

• Sum - Total of all values (best for revenue, counts of events)
• Average - Mean value (best for rates, scores, durations)
• Count - Number of records in each group
• Min - Smallest value in the group
• Max - Largest value in the group

You can add multiple metrics. For example, adding both Revenue (Sum) and Orders (Count) produces two value columns per pivot cell.

Column

Optional - A categorical, datetime, or text column whose unique values are spread as individual column headers.

When you pick Region as Column, each distinct region value becomes a column. If no Column is set, each metric name becomes a column instead, and rows group only by the Row field.

Row

Required - The field to group rows by. Accepts any column type.

Each unique value in this field becomes one row in the output table. For high-cardinality fields, consider filtering your data upstream to keep the table readable.

Date Bucket

Optional - Only relevant when the Row field is a datetime column.

Groups date values into time periods before pivoting:

• Year - One row per year
• Quarter - One row per quarter (Q1–Q4)
• Month - One row per calendar month (default for time-series pivots)
• Week - One row per ISO week
• Day - One row per calendar day

Settings

The pivot table renders as an inline structured table. No additional display settings are required — the output is automatically formatted with the Row field as the first column, followed by the spread Column values and metric aggregations.

Tips & Interpretation

1. Start with one metric: Add Revenue (Sum) first. Once the structure is correct, layer in a second metric like Orders (Count) to build a richer picture.

2. Column cardinality matters: If your Column field has 50 distinct values, the table will have 50 columns — hard to read. Filter or group high-cardinality columns before pivoting (e.g., group countries into regions).

3. Date Bucket is only active on datetime Rows: If your Row field is a date column, always set a Date Bucket — otherwise each unique timestamp becomes its own row.

4. No Column = metric-as-columns: Omitting the Column field is useful when you want a simple one-dimensional summary: one row per Row value, one column per metric.

5. Combining Sum + Count reveals averages: Even without an Average metric, having both Revenue (Sum) and Orders (Count) lets you mentally compute Revenue per Order for each row.

6. Empty cells mean no data: A blank cell at a row/column intersection means there were no records matching that combination — not zero. If you expect zero, verify your source data.

7. Pivot Table vs regular Table: Use the plain Table when you want to display raw or pre-aggregated records. Use Pivot Table when you need the system to group and aggregate on the fly across two dimensions.

8. Pivot Table vs Heatmap: For the same row × column structure, a Heatmap colors each cell by value — better for spotting patterns visually. Pivot Table shows exact numbers — better for reporting and precise comparison.