Skip to main content

New Article

How to SUM by matching partial text in Excel

Post a Comment

How to create relations among 3 tables in PowerPivot?

In real life, some times you need to work with 2 or more tables to make a report. Assume that, you have 3 tables tblCustomers, tblProducts and tblOrdersData. From these 3 tables you need to create a report Customer vs Product - Sales Quantity.

Image 1: tblCustomers Table

Image 2: tblProducts Table

Image 3: tblOrdersData Table

Cust_ID is the primary key of tblCustomers table, Product_ID is the primary key of tblProducts and Record_Num is the primary key of tblOrdersData.


Why you need to create a relationship between tables?

If you do not create relations between tables, then you have to work some extra time for make extra helping reports and then finally create the target report which you want to create. But if you create a relation between 2 tables then it would be very easier to get the target data within just few seconds. And you can create your desire reports very easily.

In others database program like, SQL, Access, Oracle you can create table relationship among few tables with a Primary Key and Foreign Key.


What is Primary Key and Foreign Key?

Primary Key: 
It locates in the Main table and it contains the unique value of a whole column in the Main table. No value will found as duplicate in the Main table. Example: Product_ID column is set as Primary Key for the tblProducts main table, where each code found as single in each row.

Foreign key: 
It locates in the New table and it contains the unique value many times. Example: Product_ID column is set as Foreign Key for the tblOrdersData table, where each Product_ID can be found 2 or more times. If it is confusing to you to understand, then what will be the primary key for tblOrdersData table? Well, it would be Record_Num.

Image 4: In tblOrdersData table, Order_Number is Primary Key and Product_ID and Cust_ID is Foreign Key.


How to build relationship between tables?

Step 1: At first you need to import your 3 tables into PowerPivot window. In this part if you wish, your data will automatically changed and updated, then you can import as Linked Table otherwise simply use Copy ~ Paste method.

Image 5: Imported 3 tables in PowerPivot window as Linked Table

Step 2: You can view these 3 tables through Diagram View. To do this just click on Diagram View from Home in PowerPivot window.

Step 3: Click on Design tab in PowerPivot window and Click on Create Relationship icon from Relationship group.

Step 4: After clicking on it, a new dialog box will appear named Create Relationship.


Step 5: Select tblOrdersData from Table: and select Cust_ID from Column: for applying the relationship command on it. Now to set the relationship, Select tblCustomers from Related Lookup Table and Cust_ID as Related Lookup Column. And then click on Create button.

Image 6: Settings of Create Relationship dialog box

Step 6: A relationship will successfully create like below image:


Image 7: Created 1 relation between tblCustomers and tblOrdersData

Step 7: Create another relationship between tblProducts and tblOrdersData table where tblOrdersData will be selected from Table: and Product_ID will be selected from Column: for applying the relationship command on it. Select tblProducts from Related Lookup Table and Product_ID from Related Lookup Column.

Image 8: Settings of Create Relationship dialog box for 2nd relation

Now the relationship diagram will looks like below image:

Image 9: Created Complete relationship in between 3 tables

Step 8: Now it's time to play. Open the PivotTable from PowerPivot window by clicking on Home menu and then click on PivotTable and again click on PivotTable.

Step 9: While Insert Pivot dialog box appeared, Select New Worksheet and click on Ok.

Image 10: Selected New Worksheet for new PivotTable

Step 10: You will see all 3 tables are already loaded in PivotTable.

Image 11: Loaded 3 tables

Step 11: We want to create Customer vs Product - Sales Quantity Report from these 3 tables within a few seconds. Click on tblCustomers table and move the Cust_Name in ROWS section. Again click on tblProducts table and move the Product_Name in COLUMNS section. Now Click on tblOrdersData table and move the Order_Quantity in VALUES section. And your reports are generated in seconds like below image:

Image 12: Final settings in PivotTable

 Image 13: The report as we want

You can now change the design of this report and share it. As I have used the Linked Table, so I don't need to update the PowerPivot tables. It will update automatically if my source data in the same Excel Workbook updated.
Labels:

Popular posts from this blog

How to display an image in worksheet based on a List or based on IF condition?

Excel can show image on worksheet based on a specific IF condition. So, how to do it? Simple follow the below steps: Step by Step: Step 1: Insert images in your Excel Worksheet. Here I've inserted 5 different types of balls, Football, Cricket, Pool, Basketball, Tennis ball. Note that, All balls are placed into different cell. These are placed in Picture sheet. Image 1: 5 balls placed in 5 different cells and covered photo's wide and height Step 2: In the report sheet, design the report as you wish. I've designed in my way like below: Image 2: Kids asking to Donald Duck, which ball need to throw now Step 3: Make a drop down list "Games" in E6 cell in Report sheet from Data Validation. which is as below: Football Cricket Pool Basketball Tennis You can do an IF function here in E6 in Report sheet, which will meet a certain condition and returned Football, Cricket, Pool, Basketball or Tennis. Step 4: Now the tricky part is...
Read more

Cumulative Closing Balance like a Bank Statement in PivotTable

A Bank Employee in many case needs to calculate the Closing Balance after each transaction in PivotTable like a Bank Statement . But with the help of Calculated Field of a PivotTable , you can only calculate Field with Field . It's not possible to use a formula in Calculated Field where you can mention a single cell with Relative Reference Cell . Because a PivotTable acts like a Table format where you can work with Field or Column Name . A Helping Column (A regular column, that helps to get a partial result where formula applied) can be a good idea. But there is an option in PivotTable by which you can Calculate Cumulative . In this article I will show you, how you can do it. Calculating with Formula: Assume that, you have an Excel file with Debit and Credit transaction of an Account . Image 1: Sample Bank Statement In a Bank Statement , Month wise Cumulative Balance is important. In the Excel sheet, as above, it is very easy to use a formula and calc...
Read more

Value Paste and Formula Paste

In many case, in our professional life, we need to do Copy and Value Paste or Copy and Formula Paste in all most always. Those employees, who works under MIS Department, have to do it lots of time in a single day to prepare reports. It is very time costing task in Office. Many of us use Excel Menubar to do it, others are using Keyboard Shortcut by pressing ALT key (like for Value Paste Press ALT+H+V+V and for Formula Paste Press ALT+H+V+F ). But did you noticed that, these two ways strongly need your attention to do this task. More clearly, if you choose Value Paste from Menubar , then what you need to do? First you should take the mouse in Hand Wheel the mouse in Home menu Then Click on Paste Then you pressure your eyes to find the Value Paste icon And then Click on it to paste it These all steps can take more than 1 second. Am I right? Below is an image of this process: Image 1: Value Paste from Home Menu On the other hand, if you choose the shortcut f...
Read more