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.

Practice

1. What is the main benefit of using multiple data sources in one Power BI report?

easy

A. You can combine different types of data for richer insights.

B. It automatically cleans all data without user input.

C. It limits the report to only one data table.

D. It removes the need for relationships between tables.

Solution

1. Step 1: Understand the purpose of multiple data sources

   Multiple data sources allow combining different datasets to get a fuller picture.

2. Step 2: Identify the benefit in report insights

   Combining data from various sources enriches the analysis and insights.

3. Final Answer:

   You can combine different types of data for richer insights. -> Option A

4. Quick Check:

   Multiple data sources = richer insights [OK]

Hint: Multiple sources mean combining data for better insights [OK]

Common Mistakes:

• Thinking multiple sources clean data automatically
• Believing multiple sources limit data to one table
• Assuming relationships are not needed

2. Which of the following is the correct way to add a new data source in Power BI Desktop?

easy

A. Right-click on a visual and select Add Data Source

B. Use the Modeling tab to create a new data source

C. Go to Home tab > Get Data > Choose source > Load

D. Drag and drop files directly onto the report canvas

Solution

1. Step 1: Recall the process to add data

   In Power BI Desktop, new data sources are added via Home tab's Get Data option.

2. Step 2: Confirm the correct menu and steps

   Choose the source type and then load the data into the report.

3. Final Answer:

   Go to Home tab > Get Data > Choose source > Load -> Option C

4. Quick Check:

   Get Data on Home tab = add source [OK]

Hint: Use Home > Get Data to add sources [OK]

Common Mistakes:

• Trying to add data from the Modeling tab
• Right-clicking visuals to add data
• Dragging files onto the canvas doesn't load data

3. Given two tables from different data sources: Sales and Products, which DAX formula correctly calculates total sales amount by product category assuming a relationship exists?

medium

A. Total Sales = SUM(Sales[Amount])

B. Total Sales = CALCULATE(SUM(Sales[Amount]), Products[Category])

C. Total Sales = SUMX(Products, RELATED(Sales[Amount]))

D. Total Sales = SUMX(Products, SUM(Sales[Amount]))

Solution

1. Step 1: Understand the relationship usage

   With a relationship between Sales and Products, SUM(Sales[Amount]) aggregates sales correctly by category in visuals.

2. Step 2: Evaluate each formula

   Total Sales = SUM(Sales[Amount]) sums Sales amount directly, which works with relationships. Total Sales = CALCULATE(SUM(Sales[Amount]), Products[Category]) syntax is invalid. Total Sales = SUMX(Products, RELATED(Sales[Amount])) misuses RELATED. Total Sales = SUMX(Products, SUM(Sales[Amount])) incorrectly sums the grand total for each product row, inflating results.

3. Final Answer:

   Total Sales = SUM(Sales[Amount]) -> Option A

4. Quick Check:

   SUM with relationships = correct total [OK]

Hint: Use SUM on sales column when relationships exist [OK]

Common Mistakes:

• Using CALCULATE without filter expression
• Misusing RELATED in SUMX
• Overcomplicating simple SUM aggregation

4. You imported two data sources but your report shows no combined data. What is the most likely issue?

medium

A. You added too many visuals on the report page.

B. You did not refresh the data after import.

C. You used the wrong file format for import.

D. You forgot to create relationships between tables.

Solution

1. Step 1: Identify why combined data is missing

   Without relationships, tables from different sources cannot connect, so combined data won't show.

2. Step 2: Check other options

   Refreshing data or file format issues usually cause errors, not missing combined data. Too many visuals do not affect data combination.

3. Final Answer:

   You forgot to create relationships between tables. -> Option D

4. Quick Check:

   Missing relationships = no combined data [OK]

Hint: Create relationships to combine data sources [OK]

Common Mistakes:

• Ignoring relationships after importing data
• Assuming refresh fixes missing relationships
• Blaming visuals for data connection issues

5. You have sales data from Excel and customer data from SQL Server. You want to create a report showing total sales per customer region. How should you combine these sources effectively?

hard

A. Create separate reports for each source and link them with bookmarks.

B. Load both sources, create a relationship between customer IDs, then build visuals using fields from both tables.

C. Load only the sales data and manually type customer regions in Power BI.

D. Merge the Excel and SQL tables into one before loading into Power BI.

Solution

1. Step 1: Load both data sources separately

   Import Excel sales data and SQL customer data into Power BI as separate tables.

2. Step 2: Create a relationship between customer IDs

   Link the tables on customer ID to connect sales with customer regions.

3. Step 3: Build visuals using fields from both tables

   Use the relationship to show total sales by customer region in one report.

4. Final Answer:

   Load both sources, create a relationship between customer IDs, then build visuals using fields from both tables. -> Option B

5. Quick Check:

   Separate sources + relationship + visuals = combined report [OK]

Hint: Load sources, relate keys, then build combined visuals [OK]

Common Mistakes:

• Trying to merge tables outside Power BI unnecessarily
• Ignoring relationships and typing data manually
• Splitting data into separate reports instead of combining