Power BI Pivot Table: How to Build a Matrix Report

Power BI does not have an Excel-style PivotTable object, but its Matrix visual provides a similar way to summarize data across row and column categories. Add fields to the matrix’s Rows, Columns, and Values areas to build a report that can group, expand, and compare measures.

What people mean by a Power BI pivot table

When someone asks for a pivot table in Power BI, they usually want a compact report that groups records and calculates totals, counts, or other summaries. In Excel, a PivotTable is a dedicated feature. In Power BI, the closest report visual is called a Matrix. It arranges categories in rows and columns, with one or more aggregated values at their intersections.

For example, a sales matrix could list product categories down the left, calendar years across the top, and total revenue in the body. Add region as another row level and users can expand a category to see its regions. The result behaves like a pivoted summary, but it is a Power BI visual connected to a data model, not a worksheet grid in which users can freely type into individual cells.

A regular Table visual is useful for showing a flat list of fields, such as one row per order. Choose a Matrix when the goal is grouped totals, nested categories, or a cross-tab comparison. The distinction matters because the two visuals have different layouts and interaction options.

Build a matrix visual in Power BI Desktop

Start with data that has meaningful categories and values, such as a sales table with date, product, region, and amount columns. Load or connect to the data in Power BI Desktop, then create a report page. The basic workflow is:

In the Visualizations pane, select the Matrix visual. If the newer on-object interaction experience is enabled, use the visual-building controls shown on the canvas.
Drag a category field, such as Product Category, into Rows.
Drag a second category, such as Year, into Columns if you want a cross-tab layout.
Drag a numeric field or a prepared measure into Values.
Check the aggregation and formatting, then resize the visual so its labels and columns are readable.

Power BI aggregates numeric columns by default, often by sum, but the selected aggregation depends on the field and its data type. Use the field’s menu in the visual’s field well to select an available summary such as Sum, Average, or Count. For a business definition that needs to be reused consistently, create a measure in the model rather than relying on an accidental default aggregation.

Arrange rows, columns, and values

Rows and Columns define the matrix structure; Values contains the calculation displayed for each intersection. You can place multiple fields in Rows to create a hierarchy, such as Year, Quarter, and Month, or Department and Employee. You can also add multiple measures to Values, for example revenue and order count. The matrix then displays more than one result for each category combination.

Field order is important. If Rows contains Category followed by Product, products appear nested under their category. Reordering the fields changes the hierarchy. If a hierarchy has several levels, users can expand or collapse it to move between a summary and its detail. Depending on the visual formatting and layout, levels may appear indented or in separate columns.

Use Columns when comparing a category across the page, such as sales by year or status by month. Too many distinct column values can make the visual wide and difficult to read. If a field has hundreds of unique values, it may work better as a row, a filter, or a slicer instead. A matrix is most useful when its categories answer a clear comparison question rather than simply displaying every available field.

Choose the right calculation

A matrix does not merely rearrange records: it evaluates values in the context of each row and column. If the matrix shows Category and Year, Power BI calculates the selected measure for each category-year combination, as well as for applicable subtotals and grand totals. Filters on the page, report, or visual can also change those results.

For instance, a revenue measure based on the sum of a sales amount will return a different result for each product category and year. A distinct customer count is a different kind of measure and should not be replaced with a simple row count if a customer may have many transactions. Make sure the calculation matches the question before interpreting the displayed total.

Totals can sometimes surprise users because a total is not always the sum of the visible values. Measures may be recalculated in the total’s broader filter context. This is expected for calculations such as averages, ratios, and distinct counts. If a total seems wrong, inspect the measure definition and the filters that apply to the total rather than assuming the matrix has added its displayed cells.

Format and read the matrix

Use the Format options for the selected visual to adjust its appearance. Depending on the current Power BI interface and available formatting options, you can change headings, grid lines, values, row padding, subtotals, and grand totals. Conditional formatting can help emphasize important values through options such as background or font color, data bars, or icons when supported for the selected field.

Formatting should make comparisons easier, not turn the matrix into a wall of decoration. Apply consistent number formats to measures: currency for monetary values, percentage for rates, and suitable decimal places for quantities. Give columns enough width for their labels, and consider whether long category names should wrap. If users need to scan trends, a restrained color scale or a separate chart may communicate the pattern more clearly than numerous colored cells.

Subtotals are helpful when the row hierarchy has meaningful parent groups, but they can add clutter when many levels are present. Review the matrix at both its collapsed and expanded states. Also test it at the size readers will actually see; a report that looks clear on a large monitor may require scrolling or different proportions on a smaller screen.

Use drill, filters, and report interactions

Matrix hierarchies can let readers expand or collapse levels, and Power BI’s drill controls can support moving through a hierarchy when the visual is configured for it. A user might begin with year totals, then inspect quarters or months. These controls are different from changing the underlying data: they change the level of detail shown in the report.

Report filters and slicers can narrow the matrix to a product, date range, or region. Selecting a matrix value may also filter or highlight other visuals on the page, depending on the report’s interaction settings. That makes a matrix useful as part of a dashboard, not just as a standalone table. If selecting a row produces an unexpected effect, check the report’s visual interactions and any active slicers or filters.

Matrix pivoting versus Power Query pivoting

Power BI has another feature with the word pivot: the Pivot Column transformation in Power Query. It changes the shape of a table during data preparation by turning distinct values from one column into separate columns. For example, rows containing Month and Sales could be transformed so that each month becomes a column. This changes the query’s output table before it reaches the model.

That is not the same as adding a Matrix visual to a report. A matrix summarizes model data dynamically in response to report filters. A Power Query pivot reshapes the table itself during refresh. Use the visual when readers need to explore summaries; use the transformation when the source data is arranged in a form that must be reshaped for modeling or analysis. The reverse transformation, Unpivot Columns, turns selected columns into attribute-value rows and is often useful for normalizing wide source files.

How it compares with an Excel PivotTable

The Matrix visual is the closest Power BI equivalent for presenting grouped summaries, but it is not a full replacement for Excel’s PivotTable workflow. Power BI is designed for interactive reports built on a data model. Excel PivotTables live in a workbook and work naturally alongside worksheet formulas, cell-level formatting, and other spreadsheet tasks. The Matrix visual has its own formatting and interaction rules and does not let a reader edit a particular summarized cell as if it were a worksheet cell.

If you want to analyze a Power BI semantic model in Excel, the Power BI service can offer an Analyze in Excel option for eligible models and users. It creates or opens an Excel workbook connected to the model, where an Excel PivotTable can be used. Availability can depend on permissions, organizational settings, and the model. In that setup the PivotTable is in Excel; it is not a PivotTable visual embedded in the Power BI report.

Common problems and practical fixes

The matrix shows Count instead of Sum: Check the field’s aggregation and data type. A number imported as text may not offer the expected numeric summaries. Correct the type in Power Query or the model, then verify the visual’s field setting.
Values repeat or appear unexpectedly: Review relationships between tables, the direction and behavior of those relationships, and the fields used in the visual. A poorly modeled relationship can produce confusing filter context or duplicated-looking results.
The total differs from visible rows: Determine whether the measure is additive. Distinct counts, averages, and ratios often recalculate at the total level instead of adding the row results.
The matrix is too wide or crowded: Reduce the number of column categories, move a field to Rows or a slicer, remove unnecessary levels, or use a chart for the main comparison.
A value is blank: Check for missing source data, filters that exclude the record, and whether the measure returns a blank in that context. A blank is not automatically the same as zero.

For troubleshooting, test the visual with a small number of fields and a straightforward measure first. Add fields and filters one at a time. This makes it easier to identify whether the issue comes from the source data, the model, the calculation, or the visual’s layout.

Frequently Asked Questions

Does Power BI have a PivotTable visual?
No. Power BI does not include an Excel PivotTable object as a report visual. The Matrix visual is the closest built-in option for grouped row-and-column summaries.

What is the difference between a Power BI Table and Matrix?
A Table displays data in a relatively flat set of columns and rows. A Matrix supports nested row and column groups, subtotals, and cross-tab summaries, making it more suitable for pivot-style reports.

Can I create a pivot table in Power BI Service?
You can create and edit reports in the Power BI service when you have the required permissions and editing access. Use a Matrix visual for the pivot-style layout; the exact editing controls can differ from Power BI Desktop.

Can I export a Power BI matrix to Excel?
Power BI provides export options for report data in supported situations, but availability and output can depend on the visual, permissions, and organization settings. Exporting does not turn the report visual into an interactive Excel PivotTable; use Analyze in Excel when you need a PivotTable connected to an eligible semantic model.

Should I pivot data in Power Query or use a Matrix?
Use Power Query Pivot Column when you need to reshape the table during data preparation. Use a Matrix when you want a report visual that summarizes model data and responds to report filters.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.