Retail Trading & Merchandising Performance
A Power BI trading report combining SQL Server store EPOS and ERP data with Shopify online orders in a Fabric Lakehouse. It tracks Sales against target, Gross Margin % and Average Basket Value by store, channel and product hierarchy, with year-on-year comparisons. Store managers see only their stores through row-level security.
Audience: Retail directors, merchandising and buying teams, regional and store managers. Generated by Blueprint BI on 2026-11-19 and shown exactly as generated.
What was entered
These are the only inputs. Everything below was generated from them.
- Industry
- Retail
- Report type
- Retail & Merchandising
- BI tool
- Power BI
- Currency
- GBP (£)
- Data sources
- SQL Server, Shopify
- Columns
- OrderID, OrderDate, StoreCode, SKU, Quantity, UnitPrice, Discount, CustomerID, Channel
- KPIs
- Sales (target £5M), Gross Margin % (target 40%), Average Basket Value (target £42)
- Notes
- None
- Style and pages
- Corporate Clean, 3 pages
Scope
Purpose, default period and where each input column lands in the model.
- Purpose
- How are sales, margin and basket value trending against target and last year, by channel, store and product category? Which stores and categories need action?
- Audience
- Retail directors, merchandising and buying teams, regional and store managers
- Default period
- Current month to date, compared with the same period last year; refreshed daily by 06:00.
| Source column | Model destination | Notes |
|---|---|---|
| OrderID | FactSales[OrderID] | Degenerate key. Shopify IDs are prefixed 'SH-' so they cannot collide with EPOS transaction numbers. |
| OrderDate | FactSales[DateKey], DimDate[Date] | Converted to a yyyymmdd integer key. Times are truncated to the UK local date. |
| StoreCode | FactSales[StoreKey], DimStore[StoreCode] | Looked up to a surrogate key. Shopify orders map to the virtual store code 'WEB01'. |
| SKU | FactSales[ProductKey], DimProduct[SKU] | Looked up to a surrogate key. Shopify variant SKUs are matched to ERP SKUs. |
| Quantity | FactSales[Quantity] | Returns are kept as negative quantities on the original order. |
| UnitPrice | FactSales[UnitPrice], FactSales[GrossAmount] | Selling price excluding VAT. GrossAmount = Quantity × UnitPrice. |
| Discount | FactSales[DiscountAmount] | Assumed to be a line-level £ value excluding VAT. A percentage is converted to £ in Silver. |
| CustomerID | FactSales[CustomerKey], DimCustomer[CustomerID] | Anonymous EPOS sales map to the 'Unknown' member, CustomerKey -1. |
| Channel | FactSales[ChannelKey], DimChannel[ChannelName] | Standardised to Store, Online and Click & Collect. |
Business rules
- Net Sales. NetSales = Quantity × UnitPrice − DiscountAmount, excluding VAT, with returns netted on the original order line. Calculated in: Gold layer, every load
- Cost of Sales. CostAmount = Quantity × the ERP standard unit cost effective on the order date. Calculated in: Gold layer, every load
- Order (basket). A distinct OrderID with positive net sales. Fully returned orders do not count as baskets. Calculated in: Semantic model measure [Orders]
- Like-for-like store. A store trading for the full current period and the same period last year, excluding refits closed for 7 or more days. Calculated in: Gold layer, DimStore[IsLikeForLike], refreshed monthly
1. Architecture
From source systems to the semantic model and the people who use it.
Data flow detail
- Ingestion
- Copy activities land SQL Server and Shopify data in Bronze; notebooks transform it to Silver and Gold.
- Bronze
- Raw, append-only copies of the source tables and Shopify JSON.
- Silver
- Cleaned, deduplicated and conformed orders, SKUs, stores and channels.
- Gold
- Star schema facts and dimensions with surrogate keys and derived amounts.
- Semantic model
- Direct Lake over the Gold Delta tables, with a dedicated _Measures table.
- Caveat: Queries fall back to DirectQuery if RLS is defined on a SQL endpoint, so RLS is defined in the semantic model only.
- Caveat: Calculated columns are not supported, so derived columns are built in Gold.
- Caveat: Exceeding capacity guardrails on rows per table forces fallback; size the capacity at F64 or higher.
Governance & security
- Row-level security on DimStore[AreaManagerUPN] limits managers to their stores.
- Data quality checks run in Silver: unmatched SKUs, unmatched stores and negative prices are logged and alerted.
- A daily reconciliation compares Gold net sales with EPOS Z-reads and Shopify payouts, with a tolerance of 0.5%.
- Sensitivity label 'Confidential' applies; customer IDs are pseudonymised.
- Refresh monitoring uses the Fabric Monitoring hub with failure alerts.
Source detail
- EPOS & ERP: Store transaction lines, product master, standard costs and store hierarchy.
- Shopify Store: Online and click-and-collect orders, line items, discounts and customers.
- Sales Targets: Monthly sales targets by store, signed off by Finance.
| Refresh and alerts | Ready by 06:00 daily; failures alert the BI support Teams channel and the on-call data engineer. |
|---|---|
| Storage mode | Direct Lake |
| History | 3 complete years plus the current year, covering YoY comparisons for the default period. |
| Environments | Dev, Test and Prod Fabric workspaces |
| Deployment | Fabric deployment pipelines with Test sign-off by Merchandising |
| Source control | Semantic model and report stored as PBIP in Azure DevOps Git; notebooks under Fabric Git integration |
2. Data model
Star schema with grain, keys and relationships.
Star schema (galaxy with target fact)
2 fact · 5 dimension tables · 7 relationships · measures on _Measures
- DateKeyINT
- DateDATE
- YearINT
- MonthNumberINT
- MonthNameNVARCHAR(10)sort by MonthNumber
- YearMonthNumberINT
- YearMonthNVARCHAR(10)sort by YearMonthNumber
- DayOfWeekNumberINT
- DayOfWeekNVARCHAR(10)sort by DayOfWeekNumber
- IsCompleteBIT
- ProductKeyINT
- SKUNVARCHAR(30)Business key
- ProductNameNVARCHAR(150)
- DepartmentNVARCHAR(50)
- CategoryNVARCHAR(50)
- BrandNVARCHAR(50)
- PriceBandSortINT
- PriceBandNVARCHAR(20)sort by PriceBandSort
- ChannelKeyINT
- ChannelCodeNVARCHAR(10)Business key
- ChannelNameNVARCHAR(30)
- OrderIDNVARCHAR(40)Degenerate dimension
- DateKeyINT→ DimDate[DateKey]
- StoreKeyINT→ DimStore[StoreKey]
- ProductKeyINT→ DimProduct[ProductKey]
- CustomerKeyINT→ DimCustomer[CustomerKey]
- ChannelKeyINT→ DimChannel[ChannelKey]
- QuantityINT
- UnitPriceDECIMAL(18,2)
- GrossAmountDECIMAL(18,2)
- DiscountAmountDECIMAL(18,2)
- NetSalesDECIMAL(18,2)
- CostAmountDECIMAL(18,2)
- DateKeyINT→ DimDate[DateKey]
- StoreKeyINT→ DimStore[StoreKey]
- TargetSalesDECIMAL(18,2)
- StoreKeyINT
- StoreCodeNVARCHAR(10)Business key
- StoreNameNVARCHAR(100)
- RegionNVARCHAR(50)
- StoreFormatNVARCHAR(30)
- IsLikeForLikeBIT
- AreaManagerUPNNVARCHAR(100)
- ValidFromDATE
- ValidToDATE
- CustomerKeyINT
- CustomerIDNVARCHAR(40)Business key
- CustomerTypeNVARCHAR(20)
- HomeRegionNVARCHAR(50)
Grain note. FactSalesTarget has no relationship to DimProduct, DimCustomer, DimChannel, so its measures are only meaningful by the other dimensions.
| From (many) | To (one) | Cardinality | Filter direction | Active |
|---|---|---|---|---|
| FactSales.DateKey | DimDate.DateKey | many-to-one | Single | Yes |
| FactSales.StoreKey | DimStore.StoreKey | many-to-one | Single | Yes |
| FactSales.ProductKey | DimProduct.ProductKey | many-to-one | Single | Yes |
| FactSales.CustomerKey | DimCustomer.CustomerKey | many-to-one | Single | Yes |
| FactSales.ChannelKey | DimChannel.ChannelKey | many-to-one | Single | Yes |
| FactSalesTarget.DateKey | DimDate.DateKey | many-to-one | Single | Yes |
| FactSalesTarget.StoreKey | DimStore.StoreKey | many-to-one | Single | Yes |
3. DAX measures
An excerpt: the first 6 of 27 measures. Download the .dax file for all of them.
Sales =
SUM ( FactSales[NetSales] )Gross Sales =
SUM ( FactSales[GrossAmount] )Discount Amount =
SUM ( FactSales[DiscountAmount] )Discount % =
VAR g = [Gross Sales] RETURN IF ( g <= 0, BLANK (), DIVIDE ( [Discount Amount], g ) )Units Sold =
SUM ( FactSales[Quantity] )Orders =
CALCULATE ( DISTINCTCOUNT ( FactSales[OrderID] ), FILTER ( VALUES ( FactSales[OrderID] ), CALCULATE ( SUM ( FactSales[NetSales] ) ) > 0 ) )4. KPIs and RAG rules
| KPI | Target | Direction | Green (1) | Amber (2) | Red (3) | Example |
|---|---|---|---|---|---|---|
Sales Net sales excluding VAT after discounts and returns, against the £5M monthly target. [Sales] RAG: [Sales RAG] | £5M per month | Higher | ≥ £5,000,000 | ≥ £4,500,000 | < £4,500,000 | £4.7M +3.8% Amber |
Gross Margin % Net sales minus standard cost, divided by net sales. [Gross Margin %] RAG: [Gross Margin RAG] | 40% | Higher | ≥ 40.0% | ≥ 37.0% | < 37.0% | 41.2% +0.6 pts Green |
Average Basket Value Net sales divided by distinct baskets with positive net sales. [Average Basket Value] RAG: [Average Basket Value RAG] | £42 | Higher | ≥ £42.00 | ≥ £39.00 | < £39.00 | £37.80 -2.1% Red |
How RAG is shown
Each KPI has a RAG measure returning 1, 2 or 3 (BLANK when there is no value), so the thresholds live in one place. In Power BI, use conditional formatting with Format style "Rules" based on the RAG measure: 1 green, 2 amber, 3 red, and an icon set on the same rule. Status is never shown by colour alone: every colour is paired with its icon shape or word.
5. Report pages
Page layouts with the theme applied. Charts show placeholder shapes; the KPI cards use Claude's sample values.
Page 1: Executive Summary
- Purpose
- Company trading position against target and last year.
- Audience
- Retail directors and the executive team
- Default filter
- Current month to date
- Slicers
- DimDate[YearMonth] (dropdown): Single select; defaults to the current month. · DimChannel[ChannelName] (buttons) · DimStore[Region] (dropdown): RLS limits the options to the regions a user can see.
- Title label
- [Selected Period Label]
- Store
- Online
- Click & Collect
| Visual | Type | Fields | Formatting and interaction | Answers |
|---|---|---|---|---|
| Trading KPIs | kpi strip | values: [Sales], [Gross Margin %], [Average Basket Value] tooltip: [Sales YoY %], [Sales vs Target %] |
| Are sales, margin and basket value on target? |
| Sales vs Last Year and Target by Month | line | axis: DimDate[YearMonth] values: [Sales], [Sales PY], [Sales Target] |
| Is the sales trend ahead of last year and target? |
| Sales Mix by Channel | donut | legend: DimChannel[ChannelName] values: [Sales] tooltip: [Sales YoY %] |
| How much of sales comes from each channel? |
| Sales vs Target % by Region | bar | axis: DimStore[Region] values: [Sales vs Target %] tooltip: [Sales Variance] |
| Which regions are behind target? |
| Sales Bridge: Last Year to This Year by Department | waterfall | axis: DimProduct[Department] values: [Sales] tooltip: [Sales PY] |
| Which departments drove the change in sales? |
Page 2: Merchandising Performance
- Purpose
- Category, brand and SKU performance on sales, margin and discount.
- Audience
- Merchandisers and buyers
- Default filter
- Current month to date
- Slicers
- DimProduct[Department] (dropdown) · DimChannel[ChannelName] (buttons) · DimProduct[PriceBand] (buttons)
- Title label
- [Selected Period Label]
| Name | Actual | vs Target |
|---|---|---|
| Dresses | £1273K | 92.9% |
| Knitwear | £1089K | 79.5% |
| Denim | £1510K | 110.2% |
| Trainers | £1080K | 78.8% |
| Bedding | £1073K | 78.3% |
| Fragrance | £1198K | 87.4% |
| Name | Actual | vs Target |
|---|---|---|
| SKU-104582 | £1126K | 82.2% |
| SKU-209331 | £1454K | 106.1% |
| SKU-311746 | £1387K | 101.2% |
| SKU-418920 | £1442K | 105.2% |
| SKU-502117 | £1083K | 79.1% |
| Visual | Type | Fields | Formatting and interaction | Answers |
|---|---|---|---|---|
| Category Trading Summary | matrix | rows: DimProduct[Department], DimProduct[Category] values: [Sales], [Sales YoY %], [Gross Margin %], [Gross Margin % YoY pts], [Discount %], [Units Sold] |
| Which categories are under-delivering on margin or growth? |
| Discount vs Margin by Category | scatter | x: [Discount %] y: [Gross Margin %] size: [Sales] details: DimProduct[Category] |
| Is discounting eroding margin in specific categories? |
| Top 10 Brands by Sales | bar | axis: DimProduct[Brand] values: [Sales] tooltip: [Gross Margin %], [Sales YoY %] |
| Which brands generate the most sales? |
| Bottom 20 SKUs by Gross Margin % | table | values: DimProduct[SKU], DimProduct[ProductName], DimProduct[Category], [Units Sold], [Sales], [Discount %], [Gross Margin %] |
| Which lines need a price or cost review? |
Page 3: Store Performance
- Purpose
- Store-level target delivery, basket value and channel trends.
- Audience
- Regional and store managers
- Default filter
- Current month to date
- Slicers
- DimStore[Region] (dropdown): Filtered by RLS on DimStore[AreaManagerUPN]. · DimStore[StoreName] (dropdown): Shows only stores permitted by RLS. · DimStore[IsLikeForLike] (buttons)
- Drill-through from
- Executive Summary
- Title label
- [Selected Period Label]
| Name | Actual | vs Target |
|---|---|---|
| Oxford Street | £1367K | 99.8% |
| Manchester Arndale | £1492K | 108.9% |
| Leeds Trinity | £1144K | 83.5% |
| Bristol Cabot Circus | £1299K | 94.8% |
| Glasgow Buchanan | £1083K | 79.1% |
| Online (WEB01) | £1074K | 78.4% |
| Visual | Type | Fields | Formatting and interaction | Answers |
|---|---|---|---|---|
| Store League Table | table | values: DimStore[StoreName], DimStore[Region], [Sales], [Sales Target], [Sales vs Target %], [Sales YoY %], [Average Basket Value], [Gross Margin %] |
| Which stores are behind target and why? |
| Average Basket Value by Month | column | axis: DimDate[YearMonth] values: [Average Basket Value] tooltip: [Average Basket Value PY], [Orders] |
| Is basket value improving month on month? |
| Sales by Day of Week and Channel | stacked column | axis: DimDate[DayOfWeek] legend: DimChannel[ChannelName] values: [Sales] |
| When do stores and online peak in the week? |
6. AI-designed page
Page 1 as drawn by the AI Design Studio (a Pro feature), unedited.
Assumptions and open questions
| # | Assumption or question | Impact if different |
|---|---|---|
| 1 | Prior-year comparisons use the calendar year; the business may report on a 4-4-5 or 52/53-week retail calendar. | Add fiscal week, period and year columns to DimDate and rewrite the PY measures to shift by 364 days or by fiscal period. |
| 2 | Sales and UnitPrice exclude VAT, and Discount is a line-level £ amount. | Silver must strip VAT or convert a percentage discount to £, otherwise Sales and margin are overstated. |
| 3 | Sales targets are set by store and calendar month only, so target measures are not sliced by product, channel or day. | A product or channel target grain needs extra keys on FactSalesTarget; weekly targets need a DateKey per week. |
| 4 | The £5M Sales target and RAG bands apply to the company total per month; store status uses Sales vs Target %. | If the bands should apply per store, the Sales KPI must move to [Sales vs Target %] with bands of 1.00 and 0.95. |
| 5 | Click & Collect orders are attributed to the online store WEB01, not to the collecting store. | Store attribution would need a collection StoreCode from Shopify, which would shift sales between stores and Online. |
| 6 | Cost of sales uses the ERP standard cost effective on the order date, not weighted-average or landed cost. | Gold must join a costing history table, and Gross Margin % values will change. |
| 7 | The RLS mapping of area managers to stores is maintained in the ERP store master. | If HR holds the mapping, add a security bridge table fed from HR, which also allows multi-store access. |
Recommendations
- Partition FactSales by month in Gold and use V-Order so Direct Lake stays within guardrails.
- Pre-aggregate Shopify payloads in Silver; do not land line-item JSON in Gold.
- Document every measure's description in the PBIP model and publish a data dictionary to the Power BI App.
- Use consistent naming: Fact/Dim prefixes, PascalCase columns and measure folders matching KPI names.
- Run Best Practice Analyzer and DAX query tests in the deployment pipeline before promoting to Prod.
- Drive adoption with a weekly trading subscription email to store managers, sent at 07:00 on Mondays.