Multiple data sources in one report 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 that shows monthly sales performance and customer feedback scores side by side.

📊 Data: You have two data sources: 1) Sales data with columns Date, Product, Region, and Sales Amount. 2) Customer feedback data with columns Date, Product, Region, and Feedback Score.

🎯 Deliverable: Create a Power BI report that combines these two data sources to show monthly total sales and average feedback score by product and region.

Sample Data

Date	Product	Region	Sales Amount
2024-01-15	Widget A	North	1000
2024-01-20	Widget B	South	1500
2024-02-10	Widget A	North	1200
2024-02-15	Widget B	South	1300
2024-03-05	Widget A	North	1100
2024-03-10	Widget B	South	1400

Date	Product	Region	Feedback Score
2024-01-16	Widget A	North	4.5
2024-01-22	Widget B	South	4.0
2024-02-12	Widget A	North	4.7
2024-02-18	Widget B	South	3.8
2024-03-07	Widget A	North	4.6
2024-03-12	Widget B	South	4.1

1

Step 1: Load both data sources into Power BI: Sales data and Customer feedback data.

Use 'Get Data' to import both tables separately.

Expected Result

Two tables named 'Sales' and 'Feedback' appear in the Power BI Fields pane.

2

Step 2: Create a Date table to unify dates for both data sources.

DateTable = CALENDAR( MIN( MIN(Sales[Date]), MIN(Feedback[Date]) ), MAX( MAX(Sales[Date]), MAX(Feedback[Date]) ) )

Expected Result

A DateTable with all dates from the earliest to latest across both data sources is created.

3

Step 3: Create relationships between DateTable and both Sales and Feedback tables on the Date column.

In Model view, drag DateTable[Date] to Sales[Date] and DateTable[Date] to Feedback[Date].

Expected Result

Relationships are active and shown as lines connecting DateTable to Sales and Feedback.

4

Step 4: Create a measure for total sales amount.

Total Sales = SUM(Sales[Sales Amount])

Expected Result

Measure 'Total Sales' calculates the sum of sales amounts.

5

Step 5: Create a measure for average feedback score.

Average Feedback = CALCULATE( AVERAGE(Feedback[Feedback Score]), TREATAS(VALUES(Sales[Product]), Feedback[Product]), TREATAS(VALUES(Sales[Region]), Feedback[Region]) )

Expected Result

Measure 'Average Feedback' calculates the average feedback score, respecting product and region filters from the Sales table.

6

Step 6: Add a matrix visual to the report canvas.

Create calculated column in DateTable: MonthYear = FORMAT(DateTable[Date], "MMM YYYY"). Rows: Product and Region from Sales table; Columns: DateTable[MonthYear]; Values: Total Sales and Average Feedback measures.

Expected Result

Matrix shows monthly total sales and average feedback by product and region side by side.

7

Step 7: Format the matrix for clarity: set number formats, add titles, and enable grid lines.

Format pane: Set Total Sales as currency with no decimals; Average Feedback as decimal with one decimal place.

Expected Result

Matrix is easy to read with clear formatting and labels.

Final Result

Monthly Sales and Feedback Report

+----------+--------+----------+----------+----------+
| Product  | Region | Jan 2024 | Feb 2024 | Mar 2024 |
+----------+--------+----------+----------+----------+
| Widget A | North  | $1,000   | $1,200   | $1,100   |
|          |        | 4.5      | 4.7      | 4.6      |
+----------+--------+----------+----------+----------+
| Widget B | South  | $1,500   | $1,300   | $1,400   |
|          |        | 4.0      | 3.8      | 4.1      |
+----------+--------+----------+----------+----------+

✓Widget A in the North region has steady sales and consistently high feedback scores above 4.5.

✓Widget B in the South region has slightly lower feedback scores, especially in February.

✓Sales amounts vary month to month but remain strong for both products.

Bonus Challenge

Add a slicer to filter the report by region and observe how sales and feedback change.

Show Hint

Use the Region field from either Sales or Feedback table in a slicer visual to filter the matrix.