RELATED for cross-table values in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a report showing each sales transaction with the product name and category included, even though this information is in a separate product table.

📊 Data: You have two tables: Sales with columns TransactionID, ProductID, Quantity, and SalesAmount; and Products with columns ProductID, ProductName, and Category.

🎯 Deliverable: Create a report table that lists each transaction with ProductName and Category shown by using the RELATED function to bring product details into the Sales table.

Sample Data

Sales Table				Products Table
TransactionID	ProductID	Quantity	SalesAmount	ProductID	ProductName	Category
1001	101	2	40	101	Notebook	Stationery
1002	102	1	15	102	Pen	Stationery
1003	103	3	90	103	Mug	Kitchen
1004	101	1	20	104	Plate	Kitchen
1005	104	4	80

1

Step 1: Ensure the Sales and Products tables are loaded into Power BI and related by ProductID.

In Power BI Model view, create a relationship: Sales[ProductID] (many) to Products[ProductID] (one).

Expected Result

A one-to-many relationship is established between Products and Sales tables on ProductID.

2

Step 2: Create a new calculated column in the Sales table to get the ProductName from the Products table using RELATED.

ProductName = RELATED(Products[ProductName])

Expected Result

Sales table now has a ProductName column showing the correct product name for each transaction.

3

Step 3: Create another calculated column in the Sales table to get the Category from the Products table using RELATED.

Category = RELATED(Products[Category])

Expected Result

Sales table now has a Category column showing the correct category for each transaction.

4

Step 4: Build a table visual in Power BI report view with columns: TransactionID, ProductName, Category, Quantity, SalesAmount.

Add fields from Sales table: TransactionID, ProductName, Category, Quantity, SalesAmount to the table visual.

Expected Result

The report table shows each transaction with product name and category alongside quantity and sales amount.

Final Result

TransactionID | ProductName | Category   | Quantity | SalesAmount
-------------------------------------------------------------
1001          | Notebook    | Stationery | 2        | 40
1002          | Pen         | Stationery | 1        | 15
1003          | Mug         | Kitchen    | 3        | 90
1004          | Notebook    | Stationery | 1        | 20
1005          | Plate       | Kitchen    | 4        | 80

✓RELATED function successfully brings product details into the Sales table.

✓Each sales transaction now clearly shows product name and category.

✓This helps the manager understand sales by product and category without duplicating data.

Bonus Challenge

Create a measure to calculate total sales amount by category using the RELATED function in combination with aggregation.

Show Hint

Use SUMX over Sales table and RELATED to access category, then group by category in a visual.