Pivot Table in Power BI: Complete Guide for Data Analysis

A pivot table in Power BI comes in handy when you’re dealing with a large volume of data. It’s used for viewing data from different perspectives.

The goal of this guide is to introduce the reader to Pivot Table and its principles. In this guide, you’ll discover how Pivot Tables work, and how to analyze your business data.

Table of Contents:

1. What is a Pivot Table?
2. What are Pivot Tables Used for?
3. Why Do We Create a Power BI Pivot Table?
4. How to Create a Pivot Table in Power BI?
5. How to Work with Pivot Tables Based on Matrix Visuals?
6. How to Visualize Your Data Using Power BI?
7. Wrap Up

First…

What is a Pivot Table?

A pivot table is a statistics tool that reorganizes and summarizes selected rows and columns of data in a database table or spreadsheet to generate the desired report. The database or spreadsheet is not changed, it merely turns or “pivots” the data so it can be viewed from multiple perspectives.

Pivot tables are of the essence when you’re handling large data volumes that will be difficult to calculate by hand. With large data volumes, pivot tables can help you perform data processing functions like sums, ranges, outliers, or averages. The information is arranged in the table in a meaningful layout that draws attention to the key values.

What are Pivot Tables Used for?

Pivot tables are used for summarizing, analyzing, exploring, and presenting data in a concise and flexible format. Here are some key uses:

1. Data Summarization: It can be used to summarize large data volumes by aggregating them using operations like count, max, min, average, and sum. For instance, calculating the average product sales per region, or summing up total sales per product.
2. Trend Analysis: It’s used in analyzing data over time, which is essential for effective trend analysis. This way, it becomes easy to identify trends like decreasing or increasing sales trends over months or years.
3. Comparative Analysis: It’s used to compare data across multiple categories. For instance, comparing the sales performance between multiple products or regions.
4. Data Filtering: It filters irrelevant data to focus on specific subsets, such as using the cross-filter direction in Power BI to ensure that filters are applied correctly across related tables. For instance, filtering sales data can show transactions from a particular period or region.
5. Data Categorization and Grouping: It’s used in grouping data into multiple categories. For instance, grouping sales data by years, quarters, or months to identify trends.

Why Do We Create a Power BI Pivot Table?

Here are some reasons for the creation of a Power BI pivot table.

1. Dynamic Data Visualization: Power BI offers a wide range of visualizations like graphs, charts, and maps. It also enables users to create interactive dashboards and reports.
2. Enhanced Data Analysis: The tool gives room for complex measures and calculations using Data Analysis Expressions (DAX).
3. Collaboration and Sharing: The Power BI services give provision for cloud-based sharing where dashboards and reports are shared with stakeholders.

How to Create a Pivot Table in Power BI?

Pivot Table is found in Excel, but cannot be found in Power BI. However, “Matrix Visualization” is a great alternative to the Pivot Table in Excel. To create a Pivot, navigate to “Matrix Visual.”

After creating a Matrix, you’ll get options like Columns, Rows, and Values.

How to Work with Pivot Tables Based on Matrix Visuals?

Here are key functionalities to help you get the most from your pivot tables in Power BI.

Sorting

To sort the matrix visual (pivot table), use the subtotal column or row field. To do that, click the subtotal header or row header to sort the matrix.

A close look at the row header shows the up or down triangle that represents the column used for sorting. From the image below, the pivot table has been sorted using the “Color” row.

Filtering with a Slicer

The Filter pane in Power BI can be used in applying general filters to the matrix visual. However, if you want a more interactive experience, you should add a slicer in Power BI. The slicer helps the user to filter the pivot based on other columns.

To add a slicer, navigate to the Visualization pane and choose the Slicer visual. Add the field you desire to use for slicing the data. In this illustration, a date-based slicer is added to filter the pivot table based on quarters.

The slicer is used to slice or filter the matrix visual data. For instance, a slicer has been created using the “Quarter” value from the date column. Choosing a quarter (like Qtr 1) will filter the Power BI pivot table to showcase data within that quarter only.

To customize the layout of the slicer, navigate to the formatting pane of the Power BI.

Conditional Formatting

To apply for conditional formatting, select the matrix visual and navigate to the “Visualizations” pane. To see the conditional formatting options, you’ll have to expand the “Cell elements” section.

You have the option of changing the icons, data bars, font color, background color, and website URLs. For instance, if you want to apply conditional formatting to the background color, toggle the option, and click the “Fx” button.

The various format style options are:

• Rules: Custom rules help in changing the background color
• Gradient: The background is changed based on the color scale
• Field Value: Color is assigned based on the specific field values.

From the illustration below, you’ll notice that the gradient-based conditional formatting has been applied, and it is based on the sum of values. After completing the settings, click “OK” to apply the formatting.

The result will look like the image below:

You can also create a PivotChart-like visual in Power BI by applying the data bar’s conditional formatting.

Data Aggregation

By default, the matrix visual adds all the values in the column. However, you can change the aggregation type depending on your analytical needs within the analytical report. For instance, changing the average values in certain cases is possible.

To do that, click the column name that’s found beneath the “Values,” and choose the aggregation type from the options below:

• Sum
• Average
• Maximum
• Minimum
• Count
• Count (Distinct)
• Median
• Variance
• Standard deviation.

Adding Multiple Columns, Rows, or Values

If you want to add another row (like Quarter) into the matrix visual, you’ll have to drag and drop the field into the “Rows” section.

Click the + (plus) sign on the left. Alternatively, you can click on the double-headed arrow icon to expand the pivot table to all rows.

The image below shows how the Power BI pivot table with multiple rows expanded looks like:

How to Visualize Your Data Using Power BI?

Stage 1: Logging in to Power BI

• • Log in to Power BI.
  • Enter your email address and click the “Submit” button.

• You are redirected to your Microsoft account.
• Enter your password and click “Sign in“.

• You can choose whether to stay signed in.

• Once done, the Power BI home screen will open.

Stage 2: Creating a Data Set and Selecting the Data Set to Use in Your Chart

• Go to the left-side menu and click the “Create” button.
• Select “Paste or manually enter data“.

• We’ll use the sample data below for this example.

Country	Revenue Stream	Revenue (in $)
USA	Digital Advertising Revenue	39,620,000
USA	Event Marketing Revenue	10,670,000
USA	Content Marketing Revenue	5,580,000
USA	Print & Outdoor Revenue	455,270
UK	Digital Advertising Revenue	40,710,000
UK	Event Marketing Revenue	24,770,000
UK	Content Marketing Revenue	6,330,000
UK	Print & Outdoor Revenue	552,190
DNK	Digital Advertising Revenue	47,040,000
DNK	Event Marketing Revenue	29,070,000
DNK	Content Marketing Revenue	7,740,000
DNK	Print & Outdoor Revenue	600,690
DNK	Media Relations Revenue	106,430
AUS	Digital Advertising Revenue	53,790,000
AUS	Event Marketing Revenue	38,530,000
AUS	Content Marketing Revenue	6,590,000
AUS	Print & Outdoor Revenue	9,040,000
AUS	Media Relations Revenue	6,130,000
FR	Digital Advertising Revenue	57,860,000
FR	Event Marketing Revenue	50,450,000
FR	Content Marketing Revenue	3,560,000
FR	Print & Outdoor Revenue	18,790,000
FR	Media Relations Revenue	15,460,000
IND	Digital Advertising Revenue	60,470,000
IND	Event Marketing Revenue	63,200,000
IND	Content Marketing Revenue	2,080,000
IND	Print & Outdoor Revenue	29,500,000
IND	Media Relations Revenue	30,020,000

• Paste the data table into the “Power Query” window
• Choose the “Create a dataset only” option.

• Navigate to the left-side menu, and click the “Data Hub” option. After that, Power BI will populate the data set list. If no data set has been created, you’ll get an error message. Next, click “Create report.”

• To see the chart metrics, click “Expand All.” You can also check the dimensions and metrics.

• Click the “Get more visuals.” Next, search ChartExpo and select Comparison Bar Chart.

• Click “Add.”

• After that, you’ll get the Comparison Bar Chart in the visuals list:

• Click on the License Settings and add the key. Next, the comparison bar chart will be displayed.

• Here’s what the Comparison Bar Chart in Power BI looks like:

Insights

Here are three insights from the chart:

• The country with the highest total revenue is India, followed by France, Australia, and Denmark.
• “Digital Advertising” is a significant revenue contributor in most countries, but “Event Marketing” is the leading revenue contributor in India.
• Countries like the US and UK have no “Media Relations” revenue stream.

Elevate Your Data Analysis with Mastery of Pivot Tables in Power BI:

Learn about Pivot Tables in Power BI through this video, an essential tool for pivot reporting, data aggregation, and detailed analysis. Mastering Pivot Tables will enable you to create structured reports and uncover valuable insights from your data.

FAQs

Can you use a pivot table in Power BI?

Yes, Power BI helps in the creation of pivot tables for data visualization and analysis.

Is Power BI similar to a pivot table?

Power BI offers more than the traditional pivot table. It offers advanced data modeling, sharing features, and visualization. It also provides a more dynamic and comprehensive analytical platform.

Is there a Pivot Table visual in Power BI?

Yes, the “Matrix” visual is similar to the Pivot Table. It allows users to analyze and summarize data across columns, rows, and values.

Wrap Up

The pivot table in Power BI is a data summarization tool that helps in analyzing and organizing data from spreadsheets or databases. It enables dynamic data visualization and analysis. This will, in turn, help stakeholders make informed decisions.

Pivot tables are used to identify trends and filter data. Cases like decreasing or increasing sales trends can be identified by merely analyzing data using the Pivot table. The pivot table is also used to filter out irrelevant data. For instance, it can be used to filter sales data to show transactions within a period.

By following the steps outlined in this guide, you’ll be able to create compelling visualization in Power BI. And in no time, become proficient in using Power BI.

Now you know what pivot table in Power BI is, what kind of data will you be analyzing with the tool?