Relationships (one-to-many, many-to-many) in Power BI - Dashboard Guide

Dashboard Mode - Relationships (one-to-many, many-to-many)

Business Question

How can we connect sales data with product and customer details using relationships to analyze total sales by product category and customer region?

Sample Data

Sales

SaleID	ProductID	CustomerID	Quantity	SaleAmount
1	101	201	2	200
2	102	202	1	150
3	103	201	3	450
4	101	203	1	100
5	104	204	5	500

Products

ProductID	ProductName	Category
101	Chair	Furniture
102	Desk	Furniture
103	Lamp	Lighting
104	Pen	Stationery

Customers

CustomerID	CustomerName	Region
201	Alice	East
202	Bob	West
203	Charlie	East
204	Diana	North

Dashboard Components

• KPI Card: Total Sales Amount
  Formula: Total Sales = SUM(Sales[SaleAmount])
  Shows total sales across all data: 1400
• Bar Chart: Sales by Product Category
  Uses relationship between Sales and Products on ProductID (many-to-one)
  Formula for measure: Total Sales = SUM(Sales[SaleAmount])
  Categories: Furniture (450), Lighting (450), Stationery (500)
• Table: Sales by Customer Region
  Uses relationship between Sales and Customers on CustomerID (many-to-one)
  Columns: Region, Total Sales
  Measure: Total Sales = SUM(Sales[SaleAmount])
  Regions: East (750), West (150), North (500)
• Many-to-Many Relationship Example: Sales and Promotions
  Assume a Promotions table with PromotionID and ProductID, connected many-to-many with Sales via a bridge table (not shown here for simplicity). This allows analysis of sales affected by multiple promotions.

Dashboard Layout

+----------------------+-------------------------+
|      KPI Card        |    Bar Chart             |
|  Total Sales Amount  |  Sales by Product Cat.  |
|        (1400)        |                         |
+----------------------+-------------------------+
|          Table: Sales by Customer Region           |
|  Region   | Total Sales                               |
|  East     | 750                                     |
|  West     | 150                                     |
|  North    | 500                                     |
+----------------------------------------------------+

Interactivity

Filters on Product Category or Customer Region will update all components:

• Selecting a category filters the bar chart and updates total sales and sales by region accordingly.
• Selecting a region filters the table and updates total sales and sales by product category accordingly.
• Relationships ensure data flows correctly from dimension tables (Products, Customers) to fact table (Sales).

Self Check

If you add a filter for Region = East, which components update and what are their new values?

• Total Sales Amount: Updates to 750 (sum of sales for customers in East region)
• Sales by Product Category: Updates to Furniture (300), Lighting (450), Stationery (0)
• Sales by Customer Region Table: Shows only East region with total sales 750

Key Result

Dashboard shows total sales, sales by product category, and sales by customer region using one-to-many relationships between Sales, Products, and Customers tables.