Why data modeling connects related tables in Power BI - Business Case Study

Scenario Mode

👤 Your Role: You are a business analyst working with sales data.

📋 Request: Your manager wants you to create a report that shows total sales by product category and customer region. The data is in separate tables, and you need to connect them properly to get accurate results.

📊 Data: You have three tables: Sales (with sales amounts and product IDs), Products (with product IDs and categories), and Customers (with customer IDs and regions).

🎯 Deliverable: Create a data model in Power BI that connects these tables correctly, then build a report showing total sales by product category and customer region.

Sample Data

SalesID	ProductID	CustomerID	SalesAmount
1	101	201	100
2	102	202	150
3	101	203	200
4	103	201	50
5	102	204	120
6	104	202	80

ProductID	Category
101	Electronics
102	Furniture
103	Electronics
104	Office Supplies

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 three tables from your data source.

Expected Result

All three tables appear in Power BI's data view.

2

Step 2: Create relationships between the tables to connect related data.

In the Model view, drag ProductID from Sales table to ProductID in Products table. Then drag CustomerID from Sales table to CustomerID in Customers table.

Expected Result

Two one-to-many relationships are created: Products to Sales, and Customers to Sales.

3

Step 3: Verify relationships are active and set to single direction filtering from Products and Customers to Sales.

Check relationship properties to ensure 'Cross filter direction' is 'Single' and 'Active' is checked.

Expected Result

Relationships filter Sales data correctly based on Products and Customers.

4

Step 4: Create a report with a matrix visual to show total sales by product category and customer region.

Add a Matrix visual. Place 'Category' from Products table in Rows, 'Region' from Customers table in Columns, and sum of 'SalesAmount' from Sales table in Values.

Expected Result

Matrix shows total sales amounts broken down by product category and customer region.

5

Step 5: Check that the totals are correct by manually summing sales for each category and region.

Manually sum sales amounts for Electronics in North region: SalesID 1 (100) + SalesID 4 (50) = 150.

Expected Result

Matrix shows 150 for Electronics in North region, matching manual calculation.

Final Result

---------------------------------------------
|           Sales by Category and Region     |
---------------------------------------------
| Category       | North | South | East | West|
|------------------------------------------------
| Electronics    |  150  |   0   |  200 |  0  |
| Furniture      |   0   |  150  |   0  | 120 |
| Office Supplies|   0   |   80  |   0  |  0  |
---------------------------------------------

✓Electronics products have the highest sales in the North and East regions.

✓Furniture sales are strong in the South and West regions.

✓Office Supplies sales are only in the South region.

✓Connecting tables with relationships allows accurate aggregation across categories and regions.

Bonus Challenge

Add a Date table and connect it to the Sales table to analyze sales trends over time.

Show Hint

Create a Date table with continuous dates, then create a relationship between Date and Sales on the sales date column.