Relationships (one-to-many, many-to-many) in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to analyze total sales by product category and customer region. You need to build a report that combines sales data with product and customer information.

📊 Data: You have three tables: Sales (with OrderID, ProductID, CustomerID, Quantity, and SalesAmount), Products (with ProductID and Category), and Customers (with CustomerID and Region).

🎯 Deliverable: Create a Power BI report that shows total sales amount by product category and customer region using correct relationships between tables.

Sample Data

Sales
OrderID	ProductID	CustomerID	Quantity	SalesAmount
1001	101	201	2	40
1002	102	202	1	25
1003	103	203	3	75
1004	101	204	1	20
1005	104	201	4	100
1006	102	202	2	50
1007	103	203	1	25
1008	104	204	3	75

Products
ProductID	Category
101	Electronics
102	Clothing
103	Clothing
104	Electronics

Customers
CustomerID	Region
201	North
202	South
203	East
204	West

1

Step 1: Load the three tables (Sales, Products, Customers) into Power BI.

Use 'Get Data' to import all tables from your data source.

Expected Result

All three tables appear in Power BI's Fields pane.

2

Step 2: Create a one-to-many relationship between Products and Sales tables.

Drag ProductID from Products (one side) to ProductID in Sales (many side). Set relationship as 'Single' direction.

Expected Result

Relationship created: Products (one) -> Sales (many) on ProductID.

3

Step 3: Create a one-to-many relationship between Customers and Sales tables.

Drag CustomerID from Customers (one side) to CustomerID in Sales (many side). Set relationship as 'Single' direction.

Expected Result

Relationship created: Customers (one) -> Sales (many) on CustomerID.

4

Step 4: Create a new measure to calculate total sales amount.

Total Sales = SUM(Sales[SalesAmount])

Expected Result

Measure 'Total Sales' is available in the Sales table.

5

Step 5: Build a matrix visual with Product Category as rows and Customer Region as columns.

Add Products[Category] to Rows, Customers[Region] to Columns, and 'Total Sales' measure to Values.

Expected Result

Matrix shows total sales by product category and customer region.

6

Step 6: Verify the data correctness by checking totals.

Check that sum of all matrix values equals SUM(Sales[SalesAmount]) = 410.

Expected Result

Matrix totals sum to 410, matching total sales amount.

Final Result

Total Sales by Product Category and Customer Region

+----------------+--------+--------+--------+--------+--------+
| Category       | North  | South  | East   | West   | Total  |
+----------------+--------+--------+--------+--------+--------+
| Electronics    | 140    | 0      | 0      | 95     | 235    |
| Clothing       | 0      | 75     | 100    | 0      | 175    |
+----------------+--------+--------+--------+--------+--------+
| Total          | 140    | 75     | 100    | 95     | 410    |
+----------------+--------+--------+--------+--------+--------+

✓Electronics category has highest sales in North and West regions.

✓Clothing category sells mostly in South and East regions.

✓Total sales amount across all categories and regions is 410.

Bonus Challenge

Create a many-to-many relationship scenario by adding a Promotions table that links multiple products to multiple promotions, then analyze sales by promotion.

Show Hint

Use a bridge table between Products and Promotions, then create relationships to enable many-to-many filtering.