Power Pivot and the Excel Data Model: Relationships and DAX Measures | Free Excel Course: From Beginner to Advanced

What you'll learn

Understand what the Excel Data Model is and when it beats VLOOKUP.
Relate several tables in a star schema.
Write DAX measures with SUM, DIVIDE, SUMX, RELATED and CALCULATE.
Build PivotTables and free-form reports from several tables.
Spot the DAX mistake that multiplies totals instead of rows.

Until now, whenever you needed to combine two tables, you pulled columns across with VLOOKUP or XLOOKUP (lesson 6). That works, but it has limits: it duplicates data, makes files heavy, and a worksheet can't hold more than 1,048,576 rows. Power Pivot and the Data Model solve all three: you load each table once, relate them like a database and calculate with a language built for the job, DAX. It's the same engine Power BI uses, so everything you learn here carries over directly.

Does your Excel have it?

The Data Model is part of Excel for Windows in Microsoft 365 and in Excel 2016, 2019, 2021 and 2024. To show the Power Pivot window, turn on the add-in under File → Options → Add-ins → Manage: COM Add-ins → Microsoft Power Pivot for Excel. Excel for Mac and Excel for the web don't have the Power Pivot window, so use Excel for Windows for this lesson.

3tables related in a star: sales, products and customers

1,048,576rows is a worksheet's limit; the Data Model can go far beyond it

4DAX functions (SUMX, RELATED, DIVIDE and CALCULATE) cover most needs

Load the tables
Relate them in a star
Write DAX measures
Analyze with PivotTables
Reports with CUBE

The case: three tables from a coffee shop

A coffee shop with locations in the Pacific Northwest keeps its data in three Excel tables (formatted as tables, lesson 4):

Table	Columns	Type
Sales	Date, CustomerID, ProductID, Quantity, Price	Fact table: one row per sale, grows every day
Products	ProductID, Product, Category, Cost	Dimension: describes the products
Customers	CustomerID, Name, City	Dimension: describes the customers

Sample data to follow along:

Date	CustomerID	ProductID	Quantity	Price
3/2	C1	P1	10	20
3/5	C2	P2	5	18
3/9	C1	P3	2	80
3/12	C3	P1	8	20
3/20	C2	P1	4	20
3/28	C3	P3	1	85

Products: P1 Ground coffee (cost $12), P2 Mug (cost $9), P3 French press (cost $45). Customers: C1 and C3 are in Seattle; C2 is in Portland.

Step 1: load the tables into the model

Click the Sales table and go to Power Pivot → Add to Data Model. Repeat with Products and Customers.
If the data comes from outside files, even better: import it with Power Query (lesson 11) and choose Close & Load To… → Only Create Connection with the box Add this data to the Data Model checked. The data then takes up no worksheet space and can exceed a million rows.

Step 2: relate the tables (star schema)

In the Power Pivot window, click Diagram View and drag Sales[ProductID] onto Products[ProductID], and Sales[CustomerID] onto Customers[CustomerID]. You can also do it from Data → Relationships.

The result is a star schema: the fact table in the center and the dimensions around it, each relationship going from "one" (the dimension, where each ID appears once) to "many" (Sales, where it repeats). You no longer need to copy the product name or the city into the sales table: the model finds them through the relationships.

Facts: SalesGrows every day. Stores keys and numbers: quantity, price.
Dimension: ProductsOne row per product (the "1" side). Adds name, category and cost.
Dimension: CustomersOne row per customer. Adds name and city.
Calendar (optional)One row per day; in real models it lets you analyze by month, quarter and year.

1 = each key appears once; ∗ = it repeats many times. Filters flow from the 1 side to the ∗ side: filtering "Seattle" in Customers filters its sales.

Keys must match exactly

If one table has "P1" and another has "P1 " (with a space) or "p1", the relationship can't find the row and those sales land in a "(blank)" bucket. Clean your keys in Power Query before relating tables: trim spaces and standardize capitalization.

1. In a star schema, which side of the relationship is the "one" side?

The Sales table, because it has more rows.
The dimension table (Products, Customers), where each ID appears exactly once.
It doesn't matter; Excel decides at random.

A dimension has one row per product or customer (a unique key); the fact table repeats those keys across many sales.

Step 3: DAX measures

A measure is a calculation evaluated in the context of the PivotTable's filters: the same "Total Sales" returns the grand total, Seattle's total or March's total depending on where you put it. Create measures under Power Pivot → Measures → New Measure, or right-click the table in the PivotTable field list → Add Measure.

Total Sales := SUMX(Sales, Sales[Quantity] * Sales[Price])

Total Cost := SUMX(Sales, Sales[Quantity] * RELATED(Products[Cost]))

Margin := [Total Sales] - [Total Cost]

Margin % := DIVIDE([Margin], [Total Sales])

Seattle Sales := CALCULATE([Total Sales], Customers[City] = "Seattle")

What each function does:

SUMX

Goes through the table row by row, evaluates the expression and adds up the results.

Here: quantity × price on each sale.

Pulls a value from the "one" side table by following the relationship.

Here: the cost of the product on each sale. It's the model's VLOOKUP.

DIVIDE

Divides and returns a blank instead of an error when the denominator is 0.

Here: margin % without #DIV/0!.

CALCULATE

Evaluates a measure with modified filters.

Here: "total sales, but only for Seattle."

With the sample data: Total Sales = $775, Total Cost = $444, Margin = $331, Margin % = 42.7% and Seattle Sales = $605.

Commas between arguments

DAX function names are always in English (SUM, CALCULATE…), and in U.S. Excel arguments are separated by commas, exactly as above. The same measure pastes straight into Power BI. If a colleague's computer uses a European regional setting, their Excel will show semicolons instead.

Measure or calculated column?

	Calculated column	Measure
Calculated	Once per row, at refresh	When used, based on the filters
Uses memory	Yes, every row stores its value	No
Use it to	Classify or segment (price band, customer type)	Sums, averages, percentages, comparisons

Rule of thumb: if it goes in rows, columns or slicers, make a calculated column; if it goes in Values, make a measure.

2. With the sample data, what is the margin for product P1 (ground coffee)?

$176
$440
$264

P1 sold 22 units at $20 ($440) with a cost of $12 per unit ($264): $440 − $264 = $176. That's what the Margin measure shows when filtered to that product.

Step 4: analyze with PivotTables

Insert a PivotTable and choose Use this workbook's Data Model. The field list shows all three tables. Drag Products[Category] to Rows, Customers[City] to Columns and the measures to Values. Slicers and timelines work just like in lesson 9, but now they can filter by fields from any table.

Worked example: which product earns the best margin?

With Products[Product] in Rows and the Total Sales, Total Cost, Margin and Margin % measures in Values, the PivotTable answers a question that would have taken three helper columns with VLOOKUP:

Product	Total Sales	Total Cost	Margin	Margin %
Ground coffee	$440	$264	$176	40.0%
French press	$245	$135	$110	44.9%
Mug	$90	$45	$45	50.0%
Total	$775	$444	$331	42.7%

Margin % by product

Mug50.0%

French press44.9%

Ground coffee40.0%

The business read: ground coffee brings in 57% of sales ($440 of $775) but has the lowest percentage margin; the mug keeps half its price as margin but barely sells. One idea straight from the model: bundle a mug with each bag of coffee. Swap the product for Customers[City] and you'll see Portland has a better margin (45.3%) than Seattle (42.0%), even though it sells less.

The most common DAX mistake

Writing SUM(Sales[Quantity]) * SUM(Sales[Price]) instead of SUMX. With the sample data it returns 30 × 243 = 7,290, not 775: it multiplies each column's total instead of multiplying row by row and then adding. When the calculation happens per row, use an "X" function (SUMX, AVERAGEX).

3. You want the "Total Sales" measure (quantity × price on each sale). Which one is correct?

SUM(Sales[Quantity]) * SUM(Sales[Price])
SUMX(Sales, Sales[Quantity] * Sales[Price])
CALCULATE(Sales[Quantity] * Sales[Price])

SUMX multiplies on each row and then adds (775). The first multiplies the totals (30 × 243 = 7,290). The third isn't valid: CALCULATE needs an aggregation or a measure, not bare columns.

Free-form reports: CUBE functions

When you need a report with a fixed layout that a PivotTable can't produce, use the CUBE functions, which read the model one cell at a time:

=CUBEVALUE("ThisWorkbookDataModel", "[Measures].[Total Sales]")
=CUBEVALUE("ThisWorkbookDataModel", "[Measures].[Margin]", "[Customers].[City].&[Seattle]")

The shortcut: build the PivotTable and use PivotTable Analyze → OLAP Tools → Convert to Formulas. Every cell becomes a CUBEVALUE formula you can move anywhere. When you're ready to share interactive reports beyond Excel, the dashboards lesson in our data analytics course shows how the same model works in BI tools.

4. What advantage does the Data Model have over pulling columns across with VLOOKUP?

None; it's just a different way of writing.
It lets you edit the data directly in the PivotTable.
Each table is stored once, it handles more than a million rows and measures recalculate under any filter.

The model compresses data, avoids duplicating it and separates calculations (measures) from presentation.

Practice exercise: your first model

Create the three sample tables and add them to the Data Model.
Relate them in Diagram View.
Create the lesson's five measures and check the results ($775, $331, 42.7%, $605).
Create a Portland Sales measure and an Avg Order measure (total sales divided by the number of sales).
Build a PivotTable with product in Rows and city in Columns.

See solution

Portland Sales := CALCULATE([Total Sales], Customers[City] = "Portland")
→ $170   ($90 in mugs + $80 in coffee)

Orders := COUNTROWS(Sales)                     → 6
Avg Order := DIVIDE([Total Sales], [Orders])
→ $129.17

PivotTable (Total Sales):
                    Portland   Seattle   Total
Ground coffee           80        360      440
French press                      245      245
Mug                     90                  90
Total                  170        605      775

Check that Seattle ($605) + Portland ($170) = $775. If it doesn't tie out, make sure every customer key in Sales has its row in the Customers table.

Summary

The Data Model stores several related tables without duplicating data and without a worksheet's row limit.
Organize it as a star: facts in the center, dimensions around it, one-to-many relationships.
DAX measures respond to filters: SUMX, RELATED, DIVIDE and CALCULATE cover most cases.
Use calculated columns to classify; use measures for values.
CUBE functions turn the model into reports with any layout you like.
When the math happens per row, use SUMX: multiplying two SUMs gives the wrong total.

Next lesson: Excel Test for Job Interviews: 10 Practice Exercises with Answers. You'll put the whole course to the test with the exercises employers ask for most, solved step by step.