Case study · Project 01
Retail Sales Performance Dashboard
Project overview
An end-to-end retail analytics solution providing insights into revenue, profitability, product performance, customer behaviour and store performance. Built as an independent portfolio project on a synthetic retail dataset, it walks the full delivery path — from raw transaction extracts to a polished, self-serve sales report.
The business problem
Retail teams collect sales data from tills, e-commerce channels and loyalty programmes, but it usually lives in separate exports that nobody joins up. Managers answer basic questions — which stores are behind, which product lines actually make money, whether this month's slowdown is seasonal or structural — by stitching spreadsheets together, and each version tells a slightly different story. The project targets that gap: one governed model where revenue, margin and store performance are measured the same way every time.
Business objectives
Dataset description
The model runs on a synthetic dataset generated to mimic a mid-size multi-channel retailer: daily transaction lines across physical stores and online, a product catalogue with categories and subcategories, a store dimension, and a customer dimension with segment and loyalty tier. Volume was scaled so time-intelligence and aggregation patterns behave realistically.
FactSales
One row per transaction line — quantity, unit price, discount, cost, and store, product, customer and date keys
DimProduct
Product catalogue with category, subcategory, brand and unit cost
DimStore
Store list with region, format, opening date and size band
DimCustomer
Customer records with segment, loyalty tier and sign-up date
DimDate
Calendar table with fiscal periods, week numbers and holiday flags
Data preparation methodology
Profile the raw extracts
Loaded each source into Power Query and profiled columns for nulls, duplicates and out-of-range values before any transformation.
Clean and standardise
Corrected data types, trimmed and cased text keys, resolved product-name variants and removed test transactions.
Shape the fact table
Collapsed returns and exchanges into signed quantities so one fact table serves both gross and net analysis.
Build conformed dimensions
Deduplicated product, store and customer records into stable dimension keys shared across channels.
Automate the refresh
Structured queries into staging, dimension and fact groups with parameters so a new month of data loads with one refresh.
Data model
Data model diagram — placeholder
A star-schema diagram showing FactSales at the centre, the surrounding dimensions and relationship cardinalities will be added here.
Report preview
Report page screenshot — placeholder
Report page screenshot — placeholder
Screenshots of each report page — executive summary, store performance, product analysis and customer insights — will be added here.
Key KPIs & business metrics
Revenue
Total sales value across channels, with same-period comparison to the prior year
Gross margin %
Margin after product cost and discounts, tracked by store, category and channel
Like-for-like growth
Growth measured only across stores trading in both periods, separating real performance from new openings
Basket size
Average items and value per transaction, segmented by customer tier
Repeat purchase rate
Share of customers with more than one purchase in the period
Stock turn
How quickly inventory sells through, by category and store
DAX & technical implementation
The model uses a star schema with a marked date table, and all business logic lives in explicit DAX measures rather than calculated columns — so every KPI recalculates correctly under any filter context.
Revenue (channel-aware base measure)
The base sales measure every other calculation builds on; respects store, product and customer filters.
Revenue =
SUMX (
FactSales,
FactSales[Quantity]
* ( FactSales[Unit Price] - FactSales[Discount] )
)Like-for-like revenue
Excludes stores not trading in both periods so growth reflects real performance.
Revenue LFL =
CALCULATE (
[Revenue],
FILTER (
VALUES ( DimStore[StoreKey] ),
NOT ISBLANK ( CALCULATE ( [Revenue], DATEADD ( DimDate[Date], -1, YEAR ) ) )
&& NOT ISBLANK ( CALCULATE ( [Revenue] ) )
)
)Running total vs target
Cumulative revenue against the monthly target for pace-of-year tracking.
Revenue RT Target % =
DIVIDE (
[Revenue RT],
CALCULATE (
[Target Revenue],
FILTER ( ALL ( DimDate ), DimDate[Date] <= MAX ( DimDate[Date] ) )
)
)Technical challenges & solutions
Returns and exchanges created double-counted revenue when kept as separate rows.
Approach — Collapsed them into signed quantities in Power Query so one fact row represents the net position, then verified totals against source extracts.
Same-period comparisons drifted when new stores entered mid-year.
Approach — Added a like-for-like measure pattern that filters the store dimension to stores present in both periods before comparing.
Category drill-downs slowed as the transaction table grew.
Approach — Removed calculated columns in favour of measures, set proper cardinalities, and kept relationships single-direction to keep the model compact.
Key insights
The findings below describe patterns demonstrated in the synthetic dataset — they show the analytical questions the report answers, not results from a real client.
- 01Margin concentration: a small share of subcategories drives most gross profit, which the product matrix makes obvious at a glance.
- 02Channel divergence: online and in-store baskets behave differently by segment, visible through the segmented basket analysis.
- 03Store formats matter: comparing like-for-like stores by format exposes underperforming formats that raw totals hide.
- 04Seasonality is measurable: the calendar table's holiday flags separate promotional spikes from baseline trend.
Source & repository
GitHub repository link — coming soon
A real repository URL will be linked here once published.
Interactive report
Interactive Power BI report — coming soon
The published Power BI report will be embedded here once available.
Project technologies
Want a build like this on your data?
Tell me what you are measuring and who needs to see it — I will come back with a scope, a timeline and a fixed quote.