Creating Relationships between Tables

If you want to make calculations across the tables, you have to first define relationships among them.

Click the Home tab on the Ribbon in the PowerPivot window. As you can observe, the tables are displayed in the Data View.
Click Diagram View.

The tables appear in the Diagram View. As you observe, some of the tables may be out of display area and all the fields in the tables may not be visible.

Resize each table to show all the fields in that table.
Drag and arrange the tables so that all are displayed.

In the East table, click on Product ID.
Click the Design tab on the Ribbon.
Click Create Relationship. The Create Relationship dialog box appears.

In the box under Table, East is displayed. In the box under Column, Product ID is displayed.

In the box under Related Lookup Table, select Product Catalog.
Product ID appears in the box under Related Lookup Column.
Click the Create button.

Line representing Relationship between the tables East and Product Backlog appears.

Repeat the same steps for the tables – North, South and West. Relationship Lines appear.

Summarizing the Data in the Tables in the Data Model

Now, you are all set to summarize the sales data for each of the products in each region in just few steps.

Click the Home tab.
Click PivotTable.
Select PivotTable from the drop-down list.

Create PivotTable dialog box appears in the Excel tables window. Select New Worksheet.

In a new worksheet, an empty PivotTable appears. As you can observe, the Fields List contains all the tables in the Data Model with all the fields displayed.

Select Product ID from Table 1 (Product Catalog).
Select Total Amount from the other four tables.
For each of fields in ∑ Values, change the Custom Name in Value Field Settings to display the region names as column labels.

The sum of Total Amount will be replaced by the label you give. PivotTable with summarized values from all the data tables shows you the required results.

Adding Data to Data Model

You can add a new data table to the Data Model or new rows of data to the existing tables in the Data Model.

Add a new data table to the Data Model with the following steps.

Click the DATA tab on the Ribbon.
Click Existing Connections in the Get External Data group. The Existing Connections dialog box appears.
Click the Tables tab. The names of all the tables in the workbook will be displayed.
Click the name of the table you want to add to the Data Model.