How to Create a Power PivotTable in Excel?

When we have to compare the data (such as name/product/items, etc.) between any of the columns in excel then we can easily do with the help of Pivot table and pivot charts. But it fails when it comes to comparing those data which are in two different datasets, at that time Power Pivot comes into role. So, Basically, Power Pivot is useful for comparisons of those data which are in two or more different datasets. Power pivot is useful to perform strong data analysis and create data models also. The main use of this is to mash up a large volume of data from different insights and perform different types of analysis very quickly.

Example: For more clarification, let’s understand with one example,

Step 1: First, take any data sets in which you want to compare those data which are in different datasets. Let’s say here, we are taking three different datasets Orders, products, and returns. and our aim is to find the total sum of sales in each region.

Dataset 1: Here we are taking the first dataset of name order.

Dataset 2: Here we are taking the second dataset of name Product.

Dataset 3: Here we are taking the third dataset of name Return.

Convert the whole data into table form (First, select the whole data and press ctrl + T). then it looks like the images mentioned above.

Step 2: Our task is to find the total sum of sales in each region, and we can notice that sales and region columns are not in the same datasets, so we need to apply some relationships in power pivot, by which we can include the common things between each dataset, such as if you notice in all these three tables then [Order_id] is common between Order dataset and Return dataset. Similarly, [Product_id] is common between the Order dataset and the Product dataset. So, in this way we connect these three datasets together, which means now we can do visualization and compare anything between these three datasets.

Step 3: For this now click on Power Pivot on the top of the ribbon. Now in the Tables group. Select the Add to Data Model option. Then a new page of Power Pivot will be open. 

Here you can see the Power pivot of the Order Table.

Follow the same steps and now you can see the Power Pivot of the Product Table.

Follow the same steps and now you can see the Power Pivot of Return Dataset.

Step 4: Go to the power pivot table slide and select the Design tab on the top of the ribbon. Then from the relationship group select Create Relationship.

Create the first relationship, Choose [Order_id] from the Order dataset and [Order_id] from the Return dataset.

Similarly, create a second relationship and choose [Product_id] from the Order dataset and [Product_id] from the Product dataset.

Then at the end we can check or view our active relationships by going to the option “Manage relationship”, your all-active relationships are shown there.

Step 5: In the power pivot window, go to the ‘Home’ tab on the top of the ribbon and then Click on Pivot Table.

Then it will show you a Create PivotTable dialog box, whether you want it in a new sheet or not. Here we are selecting a new worksheet, and then Click Ok.

Then it will be redirecting you to a new sheet automatically.

Step 6: Now select the “Region” in “Rows” and “Sales” in “Value”.

So, with the help of Power Pivot, we did our task which is, to show the Sum of sales region-wise.