Multiple data sources in one report in Power BI - Cell-by-Cell Formula Trace

Sample Data

Two data tables: Sales data (A1:C4) and Product details (E1:G3) from different sources.

Cell	Value
A1	SalesID
B1	ProductID
C1	Quantity
A2	101
B2	P01
C2	5
A3	102
B3	P02
C3	3
A4	103
B4	P01
C4	2
E1	ProductID
F1	ProductName
G1	Price
E2	P01
F2	Notebook
G2	10
E3	P02
F3	Pen
G3	2

Formula Trace

SUMX(RELATEDTABLE(Products), Sales[Quantity] * Products[Price])

Step 1: RELATEDTABLE(Products) for Sales row with ProductID 'P01'

Step 2: Sales[Quantity] * Products[Price] for ProductID 'P01' with Quantity 5 and Price 10

Step 3: RELATEDTABLE(Products) for Sales row with ProductID 'P02'

Step 4: Sales[Quantity] * Products[Price] for ProductID 'P02' with Quantity 3 and Price 2

Step 5: RELATEDTABLE(Products) for Sales row with ProductID 'P01' (third row)

Step 6: Sales[Quantity] * Products[Price] for ProductID 'P01' with Quantity 2 and Price 10

Step 7: SUMX over all rows: 50 + 6 + 20

Cell Reference Map

  A      B        C       E       F          G
+-----+--------+-------+-------+---------+-------+
|SalesID|ProductID|Quantity|ProductID|ProductName|Price |
| 101  |  P01   |   5   |  P01  | Notebook |  10   |
| 102  |  P02   |   3   |  P02  | Pen      |   2   |
| 103  |  P01   |   2   |       |          |       |
+-----+--------+-------+-------+---------+-------+
  ^       ^        ^       ^       ^          ^
  |       |        |       |       |          |
  |       |        |       |       |          |
  +-------+--------+-------+-------+----------+
References link Sales[ProductID] to Products[ProductID], Sales[Quantity] to Products[Price]

Shows how Sales table columns (A-C) relate to Products table columns (E-G) by ProductID.

Result

  A      B        C       H
+-----+--------+-------+-----+
|SalesID|ProductID|Quantity|Total|
| 101  |  P01   |   5   |  50 |
| 102  |  P02   |   3   |   6 |
| 103  |  P01   |   2   |  20 |
+-----+--------+-------+-----+

Grand Total Sales Amount: 76

Final calculated sales amount per row and total sum combining data from both sources.

Sheet Trace Quiz - 3 Questions

What does RELATEDTABLE(Products) do in this formula?

A Finds matching product rows for each sales row

B Calculates total sales quantity

C Filters sales rows by quantity

D Adds prices from all products

Key Result

SUMX iterates over related rows from another table, multiplying and summing values.