Problem Statement:
The finance team needs to create a report that shows each product's total sales value in the Sales table, but the product prices are stored in a separate Products table. They want to calculate total sales by multiplying the quantity sold (in the Sales table) by the product price (from the Products table). To achieve this, the sales data needs to be combined with the price data from the related table.
Why RELATED is the Solution:
- In this scenario, RELATED {Syntax: RELATED(<column>) is ideal because:
- It allows you to retrieve the Price from the Products table directly into the Sales table based on the relationship between Sales and Products.
- It enables row-by-row access to related table data, which is essential for calculations across tables.
DAX Example:
In the Sales table, you could create a calculated column for Total Sales Value like this:
DAX:
Use Case: Total_Sales = Sales[Quantity] * RELATED(Products[Price])
This will:
- Pull the Price from the Products table into each row of the Sales table where the relationship matches,
- Multiply the quantity in each sale by the corresponding product price, giving the total sales value for each transaction.
Key Point:
RELATED is a great fit when you need to use information from a related table for row-level calculations, such as adding data fields for contextual analysis or calculations in your primary table based on established relationships.