Composite models 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 combines detailed sales data from a local Excel file with summarized customer data from a cloud-based SQL database. The goal is to analyze sales performance by customer segments without importing all customer data locally.

📊 Data: You have two data sources: 1) An Excel file with detailed sales transactions including Date, Product, CustomerID, and SalesAmount. 2) A SQL database with customer information including CustomerID, Segment, and Region.

🎯 Deliverable: Create a Power BI report using a composite model that combines the imported sales data with the direct query customer data. Build a visual showing total sales by customer segment.

Sample Data

Date	Product	CustomerID	SalesAmount
2024-01-05	Widget A	C001	150
2024-01-07	Widget B	C002	200
2024-01-10	Widget A	C003	120
2024-01-15	Widget C	C001	180
2024-01-20	Widget B	C004	220
2024-01-22	Widget C	C002	160
2024-01-25	Widget A	C005	130
2024-01-28	Widget B	C003	210

1

Step 1: Import the sales data Excel file into Power BI as an imported table named 'Sales'.

Use Power BI Desktop: Home > Get Data > Excel > Select file > Load data as Import mode.

Expected Result

Sales table with 8 rows loaded into Power BI.

2

Step 2: Connect to the SQL database containing customer data using DirectQuery mode.

Home > Get Data > SQL Server > Enter server and database > Choose DirectQuery mode > Load 'Customer' table.

Expected Result

Customer table with columns CustomerID, Segment, and Region available via DirectQuery.

3

Step 3: Create a relationship between 'Sales'[CustomerID] and 'Customer'[CustomerID] in the model view.

Model view > Drag 'CustomerID' from Sales to Customer > Set relationship as Many-to-One, Single direction.

Expected Result

Active relationship established between Sales and Customer tables.

4

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

Total Sales = SUM(Sales[SalesAmount])

Expected Result

Measure 'Total Sales' created showing sum of SalesAmount.

5

Step 5: Build a bar chart visual with 'Customer'[Segment] on the axis and 'Total Sales' as values.

Visualizations pane > Bar chart > Axis = Customer[Segment], Values = Total Sales measure.

Expected Result

Bar chart showing total sales grouped by customer segment.

Final Result

Sales by Customer Segment

Segment A  | ██████████  $660
Segment B  | ███████     $370
Segment C  | ████        $280

(Bar lengths represent sales amounts)

✓Segment A customers generated the highest total sales of $660.

✓Segment B and C have lower sales, indicating potential growth opportunities.

✓Using composite models allowed combining detailed sales data with live customer info without full data import.

Bonus Challenge

Add a slicer to filter the sales report by Region from the Customer table using DirectQuery.

Show Hint

Use the 'Region' column from the Customer table in a slicer visual. The composite model will apply the filter dynamically.