Star schema concept in Power BI - Cell-by-Cell Formula Trace

Sample Data

Sample data showing a star schema with a fact table (Orders) and two dimension tables (Customers and Products).

Cell	Value
A1	OrderID
B1	CustomerID
C1	ProductID
D1	OrderDate
E1	Quantity
F1	SalesAmount
A2	1001
B2	C01
C2	P01
D2	2024-01-10
E2	2
F2	40
A3	1002
B3	C02
C3	P02
D3	2024-01-11
E3	1
F3	25
A4	1003
B4	C01
C4	P03
D4	2024-01-12
E4	3
F4	75
H1	CustomerID
I1	CustomerName
J1	Region
H2	C01
I2	Alice
J2	North
H3	C02
I3	Bob
J3	South
L1	ProductID
M1	ProductName
N1	Category
L2	P01
M2	Pen
N2	Stationery
L3	P02
M3	Notebook
N3	Stationery
L4	P03
M4	Chair
N4	Furniture
O1	Price
O2	20
O3	25
O4	25

Formula Trace

SUMX(Orders, Orders[Quantity] * RELATED(Products[Price]))

Step 1: Evaluate RELATED(Products[Price]) for each row in Orders

Step 2: Calculate Quantity * Price for each row

Step 3: SUMX sums all calculated values

Cell Reference Map

Orders Table:       Products Table:
+-------+---------+   +---------+-------+
|OrderID|ProductID|   |ProductID| Price |
|  1001 |  P01    |   |   P01   |  20   |
|  1002 |  P02    |   |   P02   |  25   |
|  1003 |  P03    |   |   P03   |  25   |
+-------+---------+   +---------+-------+
   |          \___________/
   |                     
   +--------------------->

Orders table references Products table by ProductID to get Price for calculation.

Result

+-----------------+
| Total Sales = 140|
+-----------------+

The total sales amount calculated by multiplying quantity by product price and summing all orders.

Sheet Trace Quiz - 3 Questions

What does the RELATED function do in the formula?

A Sums all quantities in Orders

B Fetches matching product price from Products table

C Filters Orders by ProductID

D Calculates total sales directly

Key Result

SUMX iterates over fact table rows, multiplying measure by related dimension value, then sums results.