Metabase Pivot Table Guide: Build and Configure

A pivot table in Metabase reshapes data from multiple dimensions and measures into an interactive row-and-column view. Unlike a regular data table, a pivot table compacts your information and lets you cross-tab it on the fly. You use pivot tables when you need to compare values across two or more breakout dimensions--like sales by region and product category--without writing SQL.

What Is a Pivot Table, and When Should You Use One?

A pivot table restructures data by placing one or more dimensions as rows, another as columns, and numeric values (measures) in the cells. It is used when you need to cross-tabulate data: comparing a metric across two categorical axes and preferring a visual, interactive format over raw SQL queries.

Metabase pivot tables work only with the visual Query Builder; you cannot create them directly from SQL queries. The interface gives you three buckets: rows, columns, and values (measures). You drag fields into each bucket to build your cross-tab layout.

A real example: imagine you have sales data with columns for date, region, product category, and revenue. A pivot table with Region in rows, Month in columns, and Sum of Revenue as the measure lets you see total revenue by region for each month. You can instantly spot which regions outperformed in which months without scrolling through a long CSV file.

When should you use one? Pivot tables shine when you need to compare a single metric across two dimensions and spot patterns or anomalies. If your data has three or more breakout axes, or if you need drill-down interactivity with click-through links, SQL crosstabs or a dedicated analytics tool may be more appropriate. Pivot tables also work well for heatmap-style visualizations: by color-coding cells based on value magnitude, you can surface outliers or trends in seconds.

How to Build a Pivot Table in Metabase: Step-by-Step

Start by creating a new question in Query Builder and add at least one aggregation (measure) and one or more groupings (dimensions). Then switch the visualization from table to pivot table. Metabase will auto-place your fields, but you can refine the layout in the pivot settings once you see it.

Here are the steps:

Create a new question and select your data source (table or saved question).
In the Query Builder, add a grouping by one or more dimensions (for example, Region or Month).
Add a metric to aggregate (for example, Sum of Revenue or Count).
Click the visualization button at the bottom left and choose Pivot table from the dropdown.
Metabase auto-assigns fields to rows, columns, and values based on field count and type.
Open the gear icon (Pivot table settings) to manually reorder or move fields between buckets.

Creating the question is straightforward: you are just selecting a table and setting up a basic aggregation. The magic happens when you switch to the pivot visualization.

When you first click Pivot table, Metabase uses a heuristic to place fields: typically, the first grouping becomes rows, the second becomes columns, and the measure lands in the values bucket. If this does not match your intent, you can drag fields around in the settings pane. Reordering takes effect immediately, so you see your pivot reshape in real time.

The Query Builder enforces one rule: a pivot table must have at least one dimension in rows or columns and at least one measure in values. If Metabase cannot auto-assign fields to meet this requirement, you will see an error. Simply add a grouping or aggregation and try again.

Configuring Rows, Columns, and Measures

The three buckets define your pivot: rows, columns, and values (measures). Rows hold dimensions like Region or Date. Columns hold a second dimension like Product or Month. Values hold numeric aggregations like Sum, Count, or Average. Understanding the hierarchy matters.

Metabase displays rows and columns in the order you add them. If you add Region first and then Country, your pivot will nest Country under Region on the left side. This is useful for drill-down analysis: you can collapse and expand groups. However, if you add them in the wrong order, your pivot becomes hard to read. You can reorder them by dragging in the settings pane.

Let us work through a concrete example. You have sales data: date, region, product, and revenue. You want to see total revenue by region and product. You place Region in rows and Product in columns. Then you add Sum of Revenue as the measure. Metabase generates a pivot with regions down the left and products across the top. Each cell shows total revenue for that region-product pair.

What if you want to break down revenue by region, then by sales rep within each region, then by product month? You can add multiple dimensions to rows: Region, Sales Rep, and Product Month. Metabase nests them hierarchically. Click the arrow next to Region to collapse or expand each region. This is where pivot tables become powerful: you go from a single dense view to an exploratory interface. However, be warned: too many nested dimensions (more than three or four) makes the pivot confusing and slow. Stick to two dimensions maximum for clarity.

Adding and Customizing Totals and Subtotals

Metabase pivot tables automatically display subtotals and grand totals. Subtotals appear at each nesting level in your rows and columns. The grand total appears at the bottom right. You do not need to set these up manually; they are calculated and displayed by default.

If you want to hide totals, open the gear icon in the pivot settings. You will see toggles for Show row totals and Show column totals. Switch them off if you prefer to see only the cell values. Most practitioners leave them on because the totals provide context: you can compare a cell value against its row or column total to see the proportion.

Totals are calculated from the measure you specified. If your measure is Sum of Revenue, the totals are sums. If it is Average of Revenue, the totals are averages of the entire group. Metabase respects your aggregation function, so the totals remain mathematically sound.

One gotcha: if you have multiple measures in your values bucket, Metabase shows totals only for the first measure by default. You can add multiple measures, and the pivot will display each in its own column. However, if you have, say, Sum of Revenue and Count of Orders, your totals row will show the total revenue and total count side by side. This is correct behavior, but the visual can feel cluttered. Consider limiting yourself to one or two measures per pivot.

Handling Performance with Large Datasets

Pivot tables can slow down when your dataset is large or your groupings are complex. Metabase has to fetch all rows, group them, and then reshape them into a pivot. The more dimensions and rows you cross-tab, the more computation required.

Metabase 64 introduced GROUPING SETS, an optimization for pivot tables on large datasets. This allows Metabase to compute multiple grouping levels in a single database query instead of pulling all raw rows and reshaping them in memory. The result: faster pivots on datasets with millions of rows. If you are self-hosting Metabase, make sure you are running version 64 or later to benefit from this optimization.

If a pivot query times out or runs slowly, here are practical workarounds. First, add a filter to reduce the dataset size before the pivot is calculated. For example, filter by a date range or a specific region. A pivot on 100,000 rows is much faster than one on 10 million. Second, reduce the number of breakout dimensions. A pivot with Region and Product is faster than one with Region, Product, and Sales Rep. For larger installations, consider Opsily's managed Metabase hosting to handle infrastructure scaling automatically. Third, if the database is extremely slow, consider using a SQL crosstab instead: a crosstab query runs at the database level and is sometimes more efficient than Metabase's pivot visualization.

Testing is critical. Before building a pivot on production data, test on a smaller dataset or time range. Monitor the query runtime in the bottom-left corner of the Query Builder. If it exceeds a few seconds, you have a performance problem. Adjust your filters or dimensions and re-run until you reach acceptable speed.

Pivot Tables vs. SQL Crosstabs: Which Should You Use?

A pivot table is a Metabase visualization built on the Query Builder. A SQL crosstab is a native SQL query that produces a cross-tabulated result set. Both accomplish similar goals, but they have different trade-offs.

Pivot tables are interactive: you can collapse and expand groups, hide totals, and reorder fields without rewriting the query. They work on top of any table or saved question, so you do not need to know SQL. The downside: pivot tables only work with the Query Builder, not with raw SQL. If your data requires complex SQL (window functions, CTEs, subqueries), you cannot use a pivot table. You must write a SQL crosstab instead.

SQL crosstabs are less interactive but more powerful. You write a query using CASE statements or database-specific crosstab syntax (like PostgreSQL's CROSSTAB function). The query runs at the database level, so it can leverage all SQL features. This means crosstabs handle complex transformations that the Query Builder cannot express. The downside: once you write a crosstab, you cannot easily tweak it without re-writing the query. There is no drag-and-drop interface.

Here is a decision framework. Use a pivot table if your data fits the Query Builder: a single table or a few joined tables, grouped by simple dimensions, aggregated by basic measures. Use a SQL crosstab if you need complex transformations, complex joins, or advanced SQL logic. If performance is critical and your dataset is large, test both and pick the faster one. For most business users, a pivot table is the faster path to an answer.

Common Limitations and Workarounds

Metabase pivot tables have real constraints. Understanding them will save you frustration.

First: pivot tables only work with the Query Builder, not with SQL queries. If you write a SQL question, the pivot table visualization is not available; Metabase will show only table, number, scatter, and other non-pivot visualizations. Workaround: if you have a SQL query you want to pivot, save it as a question, then create a new question on top of that saved question using the Query Builder. You can then apply groupings and aggregations and switch to the pivot visualization.

Second: all dimensions and measures appear in the pivot. You cannot hide individual fields. If you have ten dimensions, they all show as rows or columns. Workaround: create a saved question with only the fields you need, then build your pivot on top of that question. This limits the pivot to relevant columns.

Third: pivot tables do not support click-through links (drill-down clicks to a detail page). If you need interactive drill-down to linked data, you may need to build a dashboard with multiple cards: one pivot table and one detail table below it, linked by filters. This is more complex but achievable.

Fourth: exporting a pivot table as Excel works, but the output is a flat table, not a true Excel pivot. The formatting and hierarchical nesting do not export cleanly. If you need a true Excel pivot, download the raw data and create a pivot manually in Excel or use a tool like Tableau.

If these constraints are blocking your use case, explore Metabase alternatives for self-hosted deployments that may offer different capabilities.

Frequently Asked Questions

How do you create a pivot table in Metabase?

Create a new question in Query Builder. Add one or more groupings (dimensions) and one or more aggregations (measures). Click Visualize, then choose Pivot table from the visualization dropdown. Drag fields into rows, columns, and values buckets to shape your pivot.

What are the key limitations of Metabase pivot tables?

Pivot tables only work with the Query Builder, not SQL queries. All dimensions and measures must be displayed (you cannot hide fields). Click-through links are not supported. Exporting to Excel produces a flat table, not a true Excel pivot.

Can you use pivot tables with SQL queries in Metabase?

No. Pivot tables only work with the visual Query Builder. If you have a SQL query, you must either rewrite it using the Query Builder or use a SQL crosstab instead. One workaround: save your SQL query as a question, then build a new Query Builder question on top of it with groupings and aggregations. Then apply the pivot visualization.

How do you configure rows, columns, and measures in a pivot table?

Use the Query Builder to add dimensions and aggregations. When you switch to pivot visualization, Metabase auto-assigns fields: the first dimension to rows, the second to columns, and the measure to values. You can drag fields between buckets in the Pivot table settings to reorder or reassign them.

How do totals work in Metabase pivot tables?

Totals are automatic. Row totals appear at the right of each row, column totals at the bottom of each column, and a grand total at the bottom right. Metabase nests subtotals by the order of your row and column fields. Toggle totals on or off in the Pivot table settings.

What is the difference between a Metabase pivot table and a SQL crosstab?

A pivot table is interactive and works with the Query Builder; you can reorder and hide totals without rewriting a query. A SQL crosstab is a static query; it runs at the database level and supports complex SQL logic but is not interactive. Use pivot tables for simple cross-tabs; use SQL crosstabs for complex transformations.

How do you handle performance issues with large pivot tables?

Filter your data before building the pivot to reduce the dataset size. Limit the number of dimensions to two or three. Upgrade to Metabase 64 or later to benefit from GROUPING SETS optimization. Test pivot performance on a small dataset first.

The Bottom Line

Pivot tables in Metabase are a fast way to cross-tabulate data without SQL. They work best when you have one or two dimensions to cross, a clear measure to aggregate, and a dataset small to medium in size. If your use case exceeds these bounds--complex SQL, many dimensions, or massive datasets--consider a SQL crosstab or a more specialized tool.

Most teams find pivot tables valuable for quick ad-hoc analysis: a marketing manager checking revenue by region and month, a product team comparing feature adoption by cohort, an ops team tracking support tickets by status and priority. The self-service nature of the Query Builder means less dependency on SQL expertise.

Ready to build your own pivots? Start with Opsily's hosted Metabase: no server management, no DevOps overhead, just log in and build.