How to Create a Pivot Chart in Power BI

If you're coming to Power BI from Excel, your first question is probably, "Where is the Pivot Chart button?" It’s a completely fair question, but Power BI approaches data visualization a bit differently. This article will show you exactly how to replicate - and improve upon - the Pivot Chart functionality you know and love.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

First, Why Is There No 'Pivot Chart' in Power BI?

In Excel, a PivotTable summarizes your data, and a PivotChart visualizes that summary. They are two parts of a single feature. Power BI, however, is a dedicated business intelligence tool built from the ground up for dynamic reporting. It separates the acts of summarizing data and visualizing it.

Here’s the simple translation:

• The Matrix visual in Power BI is the equivalent of an Excel PivotTable. It lets you summarize data with rows, columns, and values.
• Any chart in Power BI can act like a PivotChart by interacting with the Matrix visual or by being built from the same data model.

This separation is actually a huge advantage. Instead of being locked into one PivotTable connected to one PivotChart, you can have a single Matrix table dynamically filter a dozen different charts on your report dashboard. Once you get the hang of it, you'll see how much more flexible this approach is. Let’s build one step at a time.

Step 1: Build Your Base with the Matrix Visual (the 'PivotTable')

Before you can make a chart, you need a summarized table of data to visualize. In Power BI, the Matrix visual is your go-to tool for this. It lets you create that familiar crosstab layout with hierarchies you can drill into.

Let's assume you've already loaded a simple sales dataset into Power BI Desktop. The data might have columns for Order Date, Product Category, Region, and Sales Amount.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Create a Matrix

1. Insert the Matrix Visual: In the Visualizations pane on the right-hand side, click the icon for the Matrix visual. A blank placeholder will appear on your report canvas.

2. Configure the Fields: With the blank Matrix selected, look at the Fields pane (where your dataset's columns are listed) and the Visualizations pane (where you see formatting options). The Matrix has three main field wells, just like a PivotTable:

• Rows: The categories that will appear down the left side.
• Columns: The categories that will appear across the top.
• Values: The numbers you want to calculate (e.g., summarize, average, count).

3. Drag and Drop Your Data:

• Drag the Region field from your dataset into the Rows well.
• Drag the Product Category field into the Columns well.
• Drag the Sales Amount field into the Values well.

Instantly, you’ll see a familiar crosstab table on your canvas, showing total sales amounts broken down by region and product category. You’ve just recreated a PivotTable.

Take it a Step Further with Hierarchies

One of the best features of a Matrix is creating drill-downs. Let’s say you also have a Sub-Category field. Simply drag Sub-Category into the Rows well, right underneath Region.

Now, your Matrix will have little plus signs next to each region. Clicking the '+' icon will expand that region to show the sales breakdown by sub-category, just like in Excel. This ability to drill up and down is fundamental to creating interactive reports.

Step 2: Creating the Interactive 'Pivot Chart'

Now that you have your data summarized in a Matrix, it's time to create the visual component. We’ll cover two powerful methods for achieving this.

Method 1: The Interactive Dashboard Approach (Recommended)

This is the most common and "Power BI-native" way to think. Instead of creating a single, connected chart, you build a report where visuals filter each other.

1. Add a Second Visual: Keep your Matrix on the report page. Now, from the Visualizations pane, click on the icon for a 'Clustered column chart' and add it to the canvas. Resize it to sit nicely next to your Matrix.

2. Configure the Column Chart: With the new, empty column chart selected, drag fields to its wells.

• Drag Product Category to the X-axis well.
• Drag Sales Amount to the Y-axis well.

Now you have a Matrix showing a table of sales by region and category, and a simple bar chart showing total sales by category.

3. See the "Pivot" Action: This is where the magic happens. Click on a row header in your Matrix visual. For example, click on the "North America" row.

Watch your column chart. It automatically updates, instantly filtering to show the sales breakdown for only North America. You can click on any row, column, or even an individual cell in your Matrix, and the column chart will react. This dynamic cross-filtering is Power BI's version of a Pivot Chart, and it’s arguably more powerful because you can link your Matrix to line charts, pie charts, maps, and more - all at once.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 2: Directly Converting a Matrix into a Chart

Sometimes you just want the chart without keeping the table on your report. Power BI makes this simple by letting you transform one visual type into another.

1. Start with Your Configured Matrix: Follow the steps in the first section to create a Matrix that has the exact structure - rows, columns, and values - that you want your final chart to represent.

Let's say you have Month on Rows, Region on Columns (which will become the legend), and Sales Amount as Values.

2. Select the Matrix: Click on your Matrix visual to make sure it's active. Its border will be highlighted.

3. Change the Visual Type: In the Visualizations pane, simply click the icon for a different chart type, like the 'Stacked column chart'.

Voilà! Power BI instantly converts your Matrix into a Stacked Column Chart. It intelligently maps the fields:

• The Rows field (Month) moves to the X-axis.
• The Columns field (Region) moves to the Legend.
• The Values field (Sales Amount) moves to the Y-axis.

You have now directly created a "Pivot Chart" from your pivoted data structure.

Advanced Tips for Your New 'Pivot Charts'

Replicating a pivot chart is only the beginning. Here's how to make your report even better.

Add Slicers for User-Friendly Filtering

While an Excel PivotChart has filter buttons, Power BI reports use Slicers for a much better user experience. A slicer is a standalone visual that does one thing: filter other visuals on the page.

Go to the Visualizations pane and click the 'Slicer' icon. Drag your Year or Quarter field into the slicer's field well. Now your users can click a year button to filter both your Matrix and your charts simultaneously, without having to mess with dropdown menus.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Easily Change Calculations

Just like in a PivotTable, you can change the calculation type in your Matrix or chart. In the Visualizations pane, find your measure in the Values well (e.g., Sum of Sales Amount). Click the small downward arrow next to its name. You'll see a menu where you can change the aggregation from 'Sum' to 'Average', 'Count', 'Minimum', 'Maximum', and more.

Don't Go Back - Drill Through

Another area where Power BI shines is the "drillthrough" feature. You can set up a whole separate report page dedicated to, for example, the details of a single product category. Then, in your main chart, a user can right-click a bar (like "Accessories") and select "Drillthrough" to jump to that detailed page, with all the data automatically filtered for "Accessories." It makes large, complex datasets much easier to navigate.

Final Thoughts

Creating a pivot chart in Power BI isn't about finding a hidden button, it's about shifting your mindset. By combining the data-summarizing power of the Matrix visual with the interactivity of other charts like bar or line graphs, you can build reports that are far more dynamic and insightful than a standard Excel PivotChart - a process that swaps button-finding for genuine data exploration.

This process of building reports, even if powerful, can still feel time-consuming, especially when dealing with dozens of data sources. When clients come to us, they're often bogged down in the manual work - the drag-and-drop, the field configuration, the formatting - just to get to the answer. We designed Graphed to short-circuit that entire process. Instead of setting up a Matrix and configuring a chart in 10 steps, you can just ask, "Show me a chart of sales by region and product category for Q4," and the right visualization appears instantly, connected to your live data. This lets your team focus on the questions and the answers, not the setup.