Data relationships model in Tableau - Cell-by-Cell Formula Trace

Sample Data

Sample data showing two tables: Orders (A1:D4) and Customers (F1:H3). Orders has sales data linked to customers by Customer ID.

Cell	Value
A1	Order ID
B1	Customer ID
C1	Order Date
D1	Sales
A2	1001
B2	C001
C2	2024-01-10
D2	250
A3	1002
B3	C002
C3	2024-01-15
D3	450
A4	1003
B4	C001
C4	2024-01-20
D4	300
F1	Customer ID
G1	Customer Name
H1	Region
F2	C001
G2	Alice
H2	North
F3	C002
G3	Bob
H3	South

Formula Trace

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

Step 1: RELATEDTABLE(Orders) for Customer ID 'C001'

Step 2: SUMX(Orders[Sales]) over related rows

Cell Reference Map

Orders Table       Customers Table
+----+----------+------------+-------+    +----------+--------------+--------+
| A1 | B1       | C1         | D1    |    | F1       | G1           | H1     |
|----|----------|------------|-------|    |----------|--------------|--------|
|1001| C001     | 2024-01-10 | 250   |    | C001     | Alice        | North  |
|1002| C002     | 2024-01-15 | 450   |    | C002     | Bob          | South  |
|1003| C001     | 2024-01-20 | 300   |    +----------+--------------+--------+

The formula uses Customer ID to relate Orders table rows to Customers table rows. The relationship is on Customer ID column in both tables.

Result

+----------+--------------+----------------+
| Customer | Total Sales  | Explanation    |
+----------+--------------+----------------+
| Alice    | 550          | Sum of sales for orders 1001 and 1003
| Bob      | 450          | Sum of sales for order 1002
+----------+--------------+----------------+

The result shows total sales per customer by summing sales from related orders using the data relationship on Customer ID.

Sheet Trace Quiz - 3 Questions

What does RELATEDTABLE(Orders) return for Customer ID 'C001'?

A All customers with orders

B All orders with Customer ID 'C001'

C Sum of sales for all customers

D Single order with highest sales

Key Result

RELATEDTABLE returns all rows from a related table matching the current row's key, used with aggregation functions like SUMX to calculate totals.