Why connecting disparate data enables insights in Tableau - Formula Trace Breakdown

Sample Data

Two separate tables: Customers (A1:C3) and Purchases (D1:F3). Customers have IDs, names, and regions. Purchases have CustomerID, amount, and date. Some customers have purchases, some do not.

Cell	Value
A1	CustomerID
B1	Name
C1	Region
A2	101
B2	Alice
C2	North
A3	102
B3	Bob
C3	South
D1	CustomerID
E1	PurchaseAmount
F1	PurchaseDate
D2	101
E2	250
F2	2024-05-01
D3	103
E3	300
F3	2024-05-03

Formula Trace

SUM(IF Customers.CustomerID = Purchases.CustomerID THEN Purchases.PurchaseAmount ELSE 0 END)

Step 1: Match CustomerID 101 in Customers with Purchases

Step 2: Match CustomerID 102 in Customers with Purchases

Step 3: Match CustomerID 103 in Purchases with Customers

Step 4: Sum all matched PurchaseAmount values

Cell Reference Map

Customers Table      Purchases Table
+----+-------+--------+  +----+---------------+------------+
| A1 | B1    | C1     |  | D1 | E1            | F1         |
|----|-------|--------|  |----|---------------|------------|
|101 | Alice | North  |  |101 | 250           | 2024-05-01 |
|102 | Bob   | South  |  |103 | 300           | 2024-05-03 |
+----+-------+--------+  +----+---------------+------------+

Arrows: Customers.CustomerID -> Purchases.CustomerID to find matching purchases.

Shows two tables side by side with CustomerID columns linked to find matching purchase amounts.

Result

+---------------------+
| Total Purchase Amount|
|          250        |
+---------------------+

The final result sums purchase amounts only for customers found in both tables, resulting in 250.

Sheet Trace Quiz - 3 Questions

Why does Customer 102 not contribute to the total purchase amount?

A Because Customer 102's purchase amount is zero

B Because Customer 102 is not in the Customers table

C Because Customer 102 has no matching purchase record

D Because Customer 102's purchase date is missing

Key Result

Join tables on common key to aggregate related data across datasets