Power Pivot Relationships: One PivotTable from Two Tables
Ravina's Sales table has Product ID and Qty. Her Products table has the product name, category and price. To see revenue by category she would normally add three VLOOKUP columns to Sales. With the Data Model she draws one line between the two Product ID columns and builds the PivotTable straight away.
The Data Model stores several tables inside the workbook. A relationship tells Excel that Sales[Product ID] matches Products[Product ID]. Power Pivot is the window where you see and manage those tables, relationships and DAX measures.
Download the practice file (power-pivot-relationships.xlsx)
Excel file, 8 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.
Quick answer
- Make both lists Excel Tables named Sales and Products.
- Click in Sales, Insert > PivotTable, tick Add this data to the Data Model, click OK.
- In the PivotTable Fields pane click All, so both tables show.
- Tick Products > Category and Sales > Qty. Click Create when Excel asks for a relationship, or create it in Data > Relationships.
- For revenue, add a measure (see below).
Revenue := SUMX(Sales, Sales[Qty] * RELATED(Products[Price]))
The two tables
| Date | Product ID | City | Qty |
|---|---|---|---|
| 02-09-2026 | P1 | Pune | 10 |
| 03-09-2026 | P2 | Nashik | 4 |
| 05-09-2026 | P1 | Nashik | 6 |
| 09-09-2026 | P3 | Pune | 3 |
| 12-09-2026 | P2 | Pune | 8 |
| 18-09-2026 | P3 | Nagpur | 5 |
| 21-09-2026 | P1 | Nagpur | 7 |
| 27-09-2026 | P2 | Kolhapur | 2 |
| Product ID | Product | Category | Price |
|---|---|---|---|
| P1 | Milk 1L | Dairy | 56 |
| P2 | Paneer 200g | Dairy | 90 |
| P3 | Bread | Bakery | 40 |
Create the relationship in Diagram View
- Turn on Power Pivot once: File > Options > Add-ins > Manage: COM Add-ins > Go, tick Microsoft Power Pivot for Excel.
- Click Power Pivot > Manage.
- Click Home > Diagram View.
- Drag Product ID from Sales onto Product ID in Products.
- A line appears: many Sales rows (*) point to one Products row (1).
Add a revenue measure
- In the PivotTable Fields pane, right-click the Sales table and choose Add Measure.
- Name it
Revenue. - Type the formula
=SUMX(Sales,Sales[Qty]*RELATED(Products[Price])). - Choose Currency format and click OK.
- Tick Revenue. The PivotTable shows revenue by category.
| Category | Qty | Revenue |
|---|---|---|
| Bakery | 8 | ₹320 |
| Dairy | 37 | ₹2,548 |
| Grand Total | 45 | ₹2,868 |
The practice file checks these numbers with normal formulas: Dairy revenue is =SUMPRODUCT((Sales[Product ID]="P1")*Sales[Qty])*56+SUMPRODUCT((Sales[Product ID]="P2")*Sales[Qty])*90 = 23 × 56 + 14 × 90 = 2548.
Rules for good relationships
- The lookup table (Products) must have each Product ID only once.
- Both columns must have the same data type.
- Put descriptions (category, price) in the lookup table, and events (date, qty) in the fact table. This shape is called a star schema.
- Use a measure for totals that multiply columns from two tables; you cannot drag Price into Values and get revenue.
Ravindra Bagale's Tip
If the PivotTable shows the same number on every row, the relationship is missing or points the wrong way. Open Diagram View and check the line goes from Sales (many) to Products (one).
Ravindra Bagale's Tip – मराठी
PivotTable मध्ये प्रत्येक row वर एकच number दिसत असेल तर relationship नाहीये किंवा उलटी आहे. Diagram View उघडा आणि line Sales (many) कडून Products (one) कडे जाते का ते बघा.
Ravindra Bagale's Tip – हिंदी
PivotTable में हर row पर एक ही number दिखे तो relationship नहीं है या उल्टी है। Diagram View खोलिए और देखिए line Sales (many) से Products (one) की तरफ़ जाती है या नहीं।
Practice task
- Open the practice file with the Sales and Products tables.
- Create a PivotTable from Sales with Add this data to the Data Model ticked.
- Create the relationship on Product ID.
- Add the Revenue measure and put Category in Rows.
- Check Dairy shows ₹2,548 and Bakery ₹320, the same as the check cells on the Sales sheet.
Learn it properly
Course lessons:
- 24.1 Turn On Power Pivot and Meet the Data Model
- 24.3 Diagram View and Relationships
- 24.7 DAX Measures: SUM, COUNTROWS, DISTINCTCOUNT and CALCULATE
Related guides: How to Create a PivotTable · Power Query Merge in Excel: Combine Two Tables Like VLOOKUP · PivotTable Slicers and Timelines: Clickable Filters in Excel
Frequently asked questions
Do I need Power Pivot to use relationships?
No. Data > Relationships and the PivotTable's Add this data to the Data Model work without opening Power Pivot. Power Pivot adds Diagram View and easier measure editing.
Why can I not create the relationship?
Usually the lookup column has duplicates or blanks, or the two columns have different data types.
Is the Data Model the same as Power BI?
It uses the same engine and DAX language. A Data Model workbook can be imported into Power BI Desktop.
Which Excel versions have Power Pivot?
Microsoft 365 and Excel 2016 and later on Windows. It is not available in Excel for the web.