Ravindra BagaleCourses & study guides मराठी Track your progress

24. Power Pivot and the Data Model

24.3 Diagram View and Relationships

By Last updated:

Example first. Build a PivotTable from the model with City (from Stores) in Rows and Sum of Amount (from Orders) in Values before any relationship exists. Every city shows ₹2,315, the grand total, and Excel says Relationships between tables may be needed. Drag StoreID from Orders to StoreID in Stores, and each city shows its own sales.

Relationship in Diagram View — in 30 seconds

What: A relationship links a column in one table to the matching column in another, like Orders[StoreID] to Stores[StoreID]. Stores has each StoreID once (the "one" side); Orders has it many times (the "many" side).

Why: Without the link, the model cannot know which orders belong to which city, so every row shows the grand total. With it, filters flow from Stores to Orders.

Tiny example: After the relationship: Pune ₹940, Nashik ₹670, Nagpur ₹355, Solapur ₹350.

First look at the data (before → after):

Relationship in Diagram View: before and after tables, fictional Blinkit Maharashtra data

Expected result — PivotTable after the relationship (correct)

City Sum of Amount (₹)
Pune 940
Nashik 670
Nagpur 355
Solapur 350
Grand Total 2,315
Before table and formula as text

Before — PivotTable before the relationship (wrong)

City Sum of Amount (₹)
Pune 2,315
Nashik 2,315
Nagpur 2,315
Solapur 2,315
Grand Total 2,315

Formula / click path

Power Pivot window › Home › View › Diagram View
Drag Orders[StoreID] onto Stores[StoreID]   (and Orders[ProductID] onto Products[ProductID])
or Excel: Data › Data Tools › Relationships › New

Do it step by step:

  1. Open Power Pivot › Data Model › Manage.
  2. Click Home › View › Diagram View. You see three boxes, one per table.
  3. Drag StoreID in the Orders box and drop it on StoreID in the Stores box. A line appears.
  4. Drag ProductID in Orders onto ProductID in Products.
  5. Double-click a line to check it: Orders (many) → Stores (one).
  6. Go back to the PivotTable and click PivotTable Analyze › Refresh. Each city now shows its own total.

Rules for a good relationship

Rule Why
The "one" side column has no duplicates and no blanks Stores[StoreID] must be unique, or Excel refuses the relationship
Both columns have the same data type Text "101" and number 101 do not match
Exactly the same spelling and spaces "BLK-PUN-01 " with a trailing space is a different key; clean it with Trim in Power Query first
Filters flow from the one side to the many side Slicing by City (Stores) filters Orders
One active relationship between two tables A second one is shown dotted (inactive)

Key words: the unique column in Stores is the primary key; the matching column in Orders is the foreign key. One store has many orders, so this is a one-to-many relationship.

Practice task (try it before you open the answer):

  1. Build the wrong PivotTable first (no relationships) and note the message.
  2. Create both relationships in Diagram View.
  3. Put Category (from Products) in Rows instead of City and refresh.
Answer

By Category: Dairy ₹480, Fruits ₹810, Staples ₹585, Snacks ₹440; grand total ₹2,315.

Ravindra Bagale's Tip

When every row of a model PivotTable shows the same number, do not touch the formulas. Check the relationships first; a missing one is the cause almost every time.