A company's sales table has millions of rows, while customers and products live in separate tables. The usual approach: add “City”, “Category” and “Price” columns to the sales table with VLOOKUP. The result is a huge, slow file that also hits the worksheet limit of 1,048,576 rows. The Data Model keeps the tables separate, links them with relationships and works on compressed data in memory; Power Pivot is the tool for managing that model.
The star schema and relationships
Fact and dimension tablesDefinition
A fact table stores events (each sale is one row: date, customer ID, product ID, amount). Dimension tables describe the event (customers, products, calendar), and each key appears there only once. The dimensions sit around the central fact table like a star.
Prepare the tables
Turn each list into an Excel Table with
Ctrl+Tand name it:Sales,Customers,Products(Table Design › Table Name).Add them to the model
In the
Insert › PivotTabledialog tickAdd this data to the Data Model, or enable Power Pivot:File › Options › Add-ins › Manage: COM Add-ins › Go…›Microsoft Power Pivot for Excel; thenPower Pivot › Tables › Add to Data Model.Create the relationship
Data › Data Tools › Relationships › New…:Table=Sales,Column (Foreign)=CustID;Related Table=Customers,Related Column (Primary)=CustID. In the Power Pivot window (Diagram View) you can also drag a field from one table onto the other.
Interactive
DAX: calculated columns and measures
| Aspect | Calculated column | Measure |
|---|---|---|
| When it is calculated | on refresh, once per row | for every PivotTable cell, according to its filters |
| Example | =Sales[Qty]*RELATED(Products[Price]) | Total Sales := SUM(Sales[Amount]) |
| Where it is used | Rows, Columns, filters (to slice by) | only Values |
| Memory | makes the file bigger | takes almost no space |
Where
- [Total Sales]
- an existing measure:
SUM(Sales[Amount]) - CALCULATE(expr, filter…)
- evaluates the expression in a modified filter context: it replaces the city filter with “Baku” and keeps the other filters
To create a measure: Power Pivot › Calculations › Measures › New Measure… (or right-click the table in the PivotTable Fields pane › Add Measure…).
Three measures in filter context
Problem
The model has 6 sales: C1 (Baku) — Laptop 1200, Lamp 80; C2 (Ganja) — Phone 650, Laptop 1150; C3 (Baku) — Chair 300, Phone 700. Laptop and Phone are Electronics, Chair and Lamp are Home. The PivotTable has Products[Category] in rows. What will [Total Sales], [Baku Sales] and [Baku Share] := DIVIDE([Baku Sales],[Total Sales]) show on each row?
Show solutionHide solution
Solution
[Total Sales] = 1200 + 650 + 1150 + 700 = 3700; [Baku Sales] = 1200 + 700 = 1900; share = 1900 / 3700 ≈ 51.35%.Home row:
[Total Sales] = 300 + 80 = 380; [Baku Sales] = 380 (both are in Baku); share = 100%.Grand total: 4080; Baku 2280; share ≈ 55.88%.
CALCULATE changed only the city filter, while the category filter stayed — that is the essence of filter context. If the denominator is 0,
DIVIDE returns a blank instead of an error.Total Sales := SUM ( Sales[Amount] )
Total Cost := SUMX ( Sales, Sales[Qty] * RELATED ( Products[UnitCost] ) )
Margin % := DIVIDE ( [Total Sales] - [Total Cost], [Total Sales] )
Baku Sales := CALCULATE ( [Total Sales], Customers[City] = "Baku" )
Share of All Cities := DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALL ( Customers[City] ) ) )SUMX calculates row by row and then adds up (the correct fix for the “Price × Quantity” trap from the PivotTable lesson), and ALL removes the city filter entirely — that gives the denominator for a share.Remember
- The Data Model keeps tables separate and joins them with one-to-many relationships — no more VLOOKUP columns.
- The key must be unique in the dimension table, and the data types must match on both sides.
- A calculated column works row by row; a measure is evaluated in each PivotTable cell's filter context.
- CALCULATE changes the filter context, DIVIDE makes division by zero safe, ALL removes a filter.
- Ratios and shares must always be measures, not calculated columns.