Data model best practices in Power BI - Cell-by-Cell Formula Trace

Sample Data

Sample data showing Products and Sales tables with keys to demonstrate data model relationships.

Cell	Value
A1	ProductID
B1	ProductName
C1	CategoryID
D1	CategoryName
E1	SalesID
F1	ProductID
G1	Quantity
H1	SalesDate
A2	101
B2	Apple
C2	1
D2	Fruits
E2	5001
F2	101
G2	10
H2	2024-06-01
A3	102
B3	Banana
C3	1
D3	Fruits
E3	5002
F3	102
G3	5
H3	2024-06-02
A4	201
B4	Carrot
C4	2
D4	Vegetables
E4	5003
F4	201
G4	7
H4	2024-06-01

Formula Trace

SUMX(RELATEDTABLE(Sales), Sales[Quantity])

Step 1: RELATEDTABLE(Sales) for ProductID 101

Step 2: SUMX(related sales rows, Sales[Quantity])

Step 3: RELATEDTABLE(Sales) for ProductID 102

Step 4: SUMX(related sales rows, Sales[Quantity])

Step 5: RELATEDTABLE(Sales) for ProductID 201

Step 6: SUMX(related sales rows, Sales[Quantity])

Cell Reference Map

Products Table       Sales Table
+----+------------+    +-------+----------+----------+------------+
| A1 | B1         |    | E1    | F1       | G1       | H1         |
|----|------------|    |-------|----------|----------|------------|
| 101| Apple      |    | 5001  | 101      | 10       | 2024-06-01 |
| 102| Banana     |    | 5002  | 102      | 5        | 2024-06-02 |
| 201| Carrot     |    | 5003  | 201      | 7        | 2024-06-01 |
+----+------------+    +-------+----------+----------+------------+

The Products table has ProductID as key. The Sales table references ProductID to link sales to products. The formula uses this relationship.

Result

+------------+----------------+---------------------+
| ProductID  | ProductName    | Total Quantity Sold  |
|------------|----------------|---------------------|
| 101        | Apple          | 10                  |
| 102        | Banana         | 5                   |
| 201        | Carrot         | 7                   |
+------------+----------------+---------------------+

The result shows total quantity sold per product by summing related sales quantities using the data model relationship.

Sheet Trace Quiz - 3 Questions

What does RELATEDTABLE(Sales) do in the formula?

A Finds all sales rows matching the current product's ProductID

B Sums all sales quantities in the Sales table

C Filters products by category

D Returns the product name for each sale

Key Result

Use RELATEDTABLE to get related rows from another table, then SUMX to sum values over those rows.