Table of Contents
A pivot table in Power BI is a data analysis tool that allows users to summarize and manipulate large amounts of data into a more manageable and organized format. To create a pivot table in Power BI, users can follow these steps:
1. Import the data table into Power BI and ensure it is in a tabular format.
2. Select the “Insert” tab and click on “Pivot Table” from the ribbon menu.
3. Choose the fields you want to include in the pivot table from the “Fields” pane.
4. Drag and drop the fields into the desired rows, columns, or values section of the pivot table.
5. Use the “Filter” option to further refine the data.
An example of how a pivot table can be used is in sales analysis. By creating a pivot table, a user can quickly summarize sales data by product, region, or time period, and identify trends or patterns that can help inform business decisions. It can also be used to compare sales performance across different categories and easily spot any outliers or discrepancies. Overall, a pivot table in Power BI is a powerful tool for data analysis and can be applied to various industries and business scenarios.
Create a Pivot Table in Power BI (With Example)
The easiest way to create a pivot table in Power BI is to use the Matrix visualization.
Note: This is not to be confused with the Table visualization located directly next to it.
The following example shows how to create a pivot table in Power BI in practice.
Example: How to Create a Pivot Table in Power BI
Suppose we have the following table in Power BI named my_data that contains information about points scored by basketball players on various teams:
Suppose we would like to create a pivot table that summarizes the total points scored by team and position.
To do so, click the Report View icon on the left hand side:
Then click the Matrix icon under the Visualizations tab.
Then drag the Team field under the Rows label, the Position field under the Columns label and the Points field under the Values label:
This will produce the following pivot table:
The rows show the names of the teams, the columns show the names of the positions, and the values inside the table represent the sum of points for each combination of team and position.
For example, we can see:
- Centers on Team A scored a total of 30 points.
- Forwards on Team A scored a total of 57 points.
- Guards on Team A scored a total of 36 points.
And so on.
If you’d like, you can also use a different summary metric in the pivot table.
For example, you could display the average value instead of the sum of values by clicking the dropdown arrow next to Sum of Points, then clicking Average:
The pivot table will now display the average points scored by players on each team and position:
Feel free to use whichever summary metric you would like within your pivot table.
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: