Case study overview: finding what drives retail profit
This case study analyzes the Contoso Retail dataset with SQL Server, Power Query and DAX, and builds interactive Power BI dashboards for sales, profit, margin and country-wise performance.
The business problem. A retailer can grow sales and still lose margin. Leaders need to know which countries, stores and product groups earn their keep, and which ones add revenue but little profit. Reading that from transaction tables is slow and error-prone.
The solution. Validate the data with SQL queries, model it in Power BI, write measures for sales, cost, profit and margin, and use scatter charts and decomposition trees to show what drives profit and where margin is weak.
Who it is for. Data analysts, BI developers, and anyone preparing a Power BI portfolio project or interview.
The data and SQL validation
Contoso is Microsoft’s sample retail dataset for a fictional consumer electronics company. It holds a sales fact table plus product, store, geography and date tables. [State the Contoso version you used and the date range of the sales.]
Validate before you model. Running SQL checks on the source catches problems before they reach the dashboard, and gives you control totals to reconcile against later.
-- Control totals to reconcile with Power BI
SELECT COUNT(*) AS SalesRows,
SUM(SalesQuantity) AS TotalQuantity,
SUM(SalesAmount) AS TotalSales,
SUM(TotalCost) AS TotalCost
FROM dbo.FactSales;
-- Rows with a negative or missing amount
SELECT COUNT(*) AS SuspectRows
FROM dbo.FactSales
WHERE SalesAmount IS NULL OR SalesAmount < 0;
-- Sales rows with no matching store
SELECT COUNT(*) AS OrphanRows
FROM dbo.FactSales AS f
LEFT JOIN dbo.DimStore AS s ON s.StoreKey = f.StoreKey
WHERE s.StoreKey IS NULL;Table and column names are examples, so match them to your copy of the database. Keep the control totals, since they prove the Power BI model matches the source.
Power Query and data model
Power Query steps
- Connect to SQL Server and import only the tables and columns the report needs.
- Set data types, and remove columns that are not used in any visual.
- Rename fields to business-friendly names.
- Add a country field to the store or geography table if it is not already there.
- Check for blank keys and unmatched rows before loading.
Data model
Contoso is already close to a star schema, so the model keeps one fact table and a few dimensions.
| Table | Type | Typical contents |
|---|---|---|
| Sales | Fact | Quantity, sales amount, total cost, discount, return amount, keys |
| Product | Dimension | Product name, brand, subcategory, category |
| Store | Dimension | Store name, country, region |
| Date | Dimension | Date, month, quarter, year, marked as the date table |
Relate each dimension to the fact table, one-to-many, hide the key columns, and expose measures instead of raw columns.
DAX measures for sales, profit and margin
Table and column names are examples, so match them to your model.
Total Sales = SUM ( Sales[SalesAmount] )
Total Cost = SUM ( Sales[TotalCost] )
Total Profit = [Total Sales] - [Total Cost]
Profit Margin % = DIVIDE ( [Total Profit], [Total Sales] )
Total Quantity = SUM ( Sales[SalesQuantity] )
Profit per Unit = DIVIDE ( [Total Profit], [Total Quantity] )
Sales Prior Year =
CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Sales YoY % =
DIVIDE ( [Total Sales] - [Sales Prior Year], [Sales Prior Year] )
Profit YTD =
TOTALYTD ( [Total Profit], 'Date'[Date] )
Share of Profit % =
DIVIDE ( [Total Profit], CALCULATE ( [Total Profit], ALLSELECTED () ) )Margin is a ratio, so always calculate it from summed profit and summed sales, as above. Averaging row-level margins gives the wrong answer. Reconcile Total Sales and Total Cost with the SQL control totals before building visuals.
Dashboard walkthrough
| Page | Question answered | Main visuals |
|---|---|---|
| Sales overview | How are sales, profit and margin trending? | KPI cards, monthly sales and profit, year-over-year change |
| Country performance | Which countries perform best and worst? | Map, ranked bars of sales and margin by country |
| Profit drivers | What drives profit, and where is margin weak? | Scatter chart, decomposition tree |
Scatter chart. Plot sales on one axis and profit margin on the other, with one point per product category, brand or country. Points with high sales and low margin are the ones to investigate, because they add revenue without adding much profit.
Decomposition tree. Start from total profit and let the viewer break it down by category, country or brand. The tree can also rank the factors most associated with high or low profit, which makes it a fast way to explore drivers. Treat that ranking as a lead to check, since it shows association rather than cause.
Design choices that help readers
- Cards at the top show sales, profit and margin together, so growth is never read without margin.
- Slicers for year, country and product category are synced across pages.
- Consistent colour for profit and margin keeps the pages easy to compare.
Explore the working report in the live dashboard.
Insights, business value and lessons learned
What the dashboard reveals
- [Headline: give total sales, profit and margin for your selected period.]
- [Countries: name the top and bottom country by sales and by margin.]
- [Drivers: name the category or brand that contributes most to profit.]
- [Weak margin: name a high-sales segment with below-average margin from the scatter chart.]
- [Trend: describe the year-over-year change in sales and profit.]
Replace each prompt with the figure shown in your live dashboard.
Business value
- Leaders see profit and margin beside sales, so growth that erodes margin is visible.
- Country and product comparisons point to where to expand, reprice or cut.
- Reconciled measures give finance and operations one set of numbers.
Using this as an interview or portfolio project
- Explain how you validated the data with SQL before modelling.
- Be ready to explain why margin is calculated from totals, not averaged.
- Describe one decision the dashboard would change, such as repricing a low-margin segment.
- Show the reconciliation between the SQL control totals and the report.
Lessons for Power BI builders
- Validate at the source first, then reconcile in the model.
- Pair sales with profit and margin on every page.
- Treat decomposition tree rankings as leads, and confirm them with a direct comparison.
Frequently asked questions
What is the Contoso Retail dataset? It is a Microsoft sample database for a fictional consumer electronics retailer, with sales, product, store, geography and date data.
Why validate data with SQL before building a Power BI report? SQL checks catch missing keys and bad values at the source, and give control totals you can reconcile against the Power BI model.
How do I calculate profit margin in DAX? Subtract total cost from total sales to get profit, then divide profit by total sales using DIVIDE.
What is a decomposition tree in Power BI? It is a visual that breaks a measure, such as profit, into categories one level at a time, and can rank the factors linked to high or low values.
What does a scatter chart show in a retail dashboard? It plots two measures, such as sales and margin, so you can spot segments with high sales but weak profitability.
Try it yourself
Explore the live dashboard, download the project file, and rebuild it for your portfolio. Need a retail analytics dashboard for your organization? Contact datascientist.ca