All samples
Sample · Retail · Retail & Merchandising

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.
Columns provided
9 columns supplied, and where each one lands in the model.
Source columnModel destinationNotes
OrderIDFactSales[OrderID]Degenerate key. Shopify IDs are prefixed 'SH-' so they cannot collide with EPOS transaction numbers.
OrderDateFactSales[DateKey], DimDate[Date]Converted to a yyyymmdd integer key. Times are truncated to the UK local date.
StoreCodeFactSales[StoreKey], DimStore[StoreCode]Looked up to a surrogate key. Shopify orders map to the virtual store code 'WEB01'.
SKUFactSales[ProductKey], DimProduct[SKU]Looked up to a surrogate key. Shopify variant SKUs are matched to ERP SKUs.
QuantityFactSales[Quantity]Returns are kept as negative quantities on the original order.
UnitPriceFactSales[UnitPrice], FactSales[GrossAmount]Selling price excluding VAT. GrossAmount = Quantity × UnitPrice.
DiscountFactSales[DiscountAmount]Assumed to be a line-level £ value excluding VAT. A percentage is converted to £ in Silver.
CustomerIDFactSales[CustomerKey], DimCustomer[CustomerID]Anonymous EPOS sales map to the 'Unknown' member, CustomerKey -1.
ChannelFactSales[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.

1Sources
EPOS & ERP
SQL Server · Daily 02:00
Shopify Store
Shopify Admin API · Daily 02:30
Sales Targets
SQL Server (Finance planning schema) · Daily 02:00
2Ingestion
Microsoft Fabric Data Factory
Incremental load using a modified-date watermark, with a 7-day lookback for returns
3Storage & transform
Bronze
Fabric Lakehouse (Delta)
Silver
Fabric Lakehouse (Delta, Spark notebooks)
Gold
Fabric Lakehouse (Delta)
4Semantic model
Power BI semantic model (Fabric)
Direct Lake
5Consumers
Retail directors
Power BI App
Merchandising team
Power BI App and Excel Analyse
Store and regional managers
Power BI mobile

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 alertsReady by 06:00 daily; failures alert the BI support Teams channel and the on-call data engineer.
Storage modeDirect Lake
History3 complete years plus the current year, covering YoY comparisons for the default period.
EnvironmentsDev, Test and Prod Fabric workspaces
DeploymentFabric deployment pipelines with Test sign-off by Merchandising
Source controlSemantic 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

Key Business key Foreign key Degenerate Numeric Hidden Sort by
DimDateDate tabledimension
Grain: One row per calendar day
  • DateKeyINT
  • DateDATE
  • YearINT
  • MonthNumberINT
  • MonthNameNVARCHAR(10)
    sort by MonthNumber
  • YearMonthNumberINT
  • YearMonthNVARCHAR(10)
    sort by YearMonthNumber
  • DayOfWeekNumberINT
  • DayOfWeekNVARCHAR(10)
    sort by DayOfWeekNumber
  • IsCompleteBIT
DimProductSCD type 1dimension
Grain: One row per SKU
  • ProductKeyINT
  • SKUNVARCHAR(30)
    Business key
  • ProductNameNVARCHAR(150)
  • DepartmentNVARCHAR(50)
  • CategoryNVARCHAR(50)
  • BrandNVARCHAR(50)
  • PriceBandSortINT
  • PriceBandNVARCHAR(20)
    sort by PriceBandSort
DimChannelSCD type 1dimension
Grain: One row per sales channel
  • ChannelKeyINT
  • ChannelCodeNVARCHAR(10)
    Business key
  • ChannelNameNVARCHAR(30)
FactSalesfact
Grain: One row per order line (OrderID and SKU)
  • 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)
FactSalesTargetfact
Grain: One row per store per calendar month
  • DateKeyINT
    → DimDate[DateKey]
  • StoreKeyINT
    → DimStore[StoreKey]
  • TargetSalesDECIMAL(18,2)
DimStoreSCD type 2dimension
Grain: One row per store version
  • StoreKeyINT
  • StoreCodeNVARCHAR(10)
    Business key
  • StoreNameNVARCHAR(100)
  • RegionNVARCHAR(50)
  • StoreFormatNVARCHAR(30)
  • IsLikeForLikeBIT
  • AreaManagerUPNNVARCHAR(100)
  • ValidFromDATE
  • ValidToDATE
DimCustomerSCD type 1dimension
Grain: One row per customer
  • 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.

Relationships
From (many)To (one)CardinalityFilter directionActive
FactSales.DateKeyDimDate.DateKeymany-to-oneSingleYes
FactSales.StoreKeyDimStore.StoreKeymany-to-oneSingleYes
FactSales.ProductKeyDimProduct.ProductKeymany-to-oneSingleYes
FactSales.CustomerKeyDimCustomer.CustomerKeymany-to-oneSingleYes
FactSales.ChannelKeyDimChannel.ChannelKeymany-to-oneSingleYes
FactSalesTarget.DateKeyDimDate.DateKeymany-to-oneSingleYes
FactSalesTarget.StoreKeyDimStore.StoreKeymany-to-oneSingleYes

3. DAX measures

An excerpt: the first 6 of 27 measures. Download the .dax file for all of them.

Validated: 27 measures, 0 errors, 4 warnings
Sales
Net sales excluding VAT, after discounts and returns.
Sales
Sales =
SUM ( FactSales[NetSales] )
Gross Sales
Sales before discounts.
Sales
Gross Sales =
SUM ( FactSales[GrossAmount] )
Discount Amount
Total discounts given.
Sales
Discount Amount =
SUM ( FactSales[DiscountAmount] )
Discount %
Discount as a share of gross sales.
Sales
Discount % =
VAR g = [Gross Sales] RETURN IF ( g <= 0, BLANK (), DIVIDE ( [Discount Amount], g ) )
Units Sold
Net units sold.
Sales
Units Sold =
SUM ( FactSales[Quantity] )
Orders
Distinct baskets with positive net sales.
Sales
Orders =
CALCULATE ( DISTINCTCOUNT ( FactSales[OrderID] ), FILTER ( VALUES ( FactSales[OrderID] ), CALCULATE ( SUM ( FactSales[NetSales] ) ) > 0 ) )
Download all 27 measures

4. KPIs and RAG rules

KPITargetDirectionGreen (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

1 Green#15803D2 Amber#B453093 Red#B91C1C

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]
Retail Trading & Merchandising Performance
Executive Summary
Current month to date
Sales Amber
£4.7M
+3.8%Target £5M per month
Gross Margin % Green
41.2%
+0.6 ptsTarget 40%
Average Basket Value Red
£37.80
-2.1%Target £42
Sales vs Last Year and Target by Month
Apr 2025Jun 2025Aug 2025
Sales Mix by Channel
57%
  • Store
  • Online
  • Click & Collect
Sales vs Target % by Region
London
112%
South East
101%
Midlands
96%
North West
85%
Scotland
78%
Online
76%
Sales Bridge: Last Year to This Year by Department
PriorWomenswMensweaKidsweaFootweaHomeBeautyCurrent
Visual specification: Executive Summary
VisualTypeFieldsFormatting and interactionAnswers
Trading KPIskpi strip
values: [Sales], [Gross Margin %], [Average Basket Value]
tooltip: [Sales YoY %], [Sales vs Target %]
  • Card status from [Sales RAG], [Gross Margin RAG] and [Average Basket Value RAG], shown as an icon plus the status word.
  • Each card shows its YoY change underneath.
Are sales, margin and basket value on target?
Sales vs Last Year and Target by Monthline
axis: DimDate[YearMonth]
values: [Sales], [Sales PY], [Sales Target]
  • Sales PY shown as a dashed grey line; target as a stepped line.
  • Data labels on the last point only, in £M.
  • Filter: Last 12 complete months
Is the sales trend ahead of last year and target?
Sales Mix by Channeldonut
legend: DimChannel[ChannelName]
values: [Sales]
tooltip: [Sales YoY %]
  • Data labels show the percentage of total.
How much of sales comes from each channel?
Sales vs Target % by Regionbar
axis: DimStore[Region]
values: [Sales vs Target %]
tooltip: [Sales Variance]
  • Constant line at 100%.
  • Sorted ascending so the worst regions appear first.
  • Drill-through to Store Performance
Which regions are behind target?
Sales Bridge: Last Year to This Year by Departmentwaterfall
axis: DimProduct[Department]
values: [Sales]
tooltip: [Sales PY]
  • Breakdown versus Sales PY, with the starting bar labelled 'Last Year'.
  • Increases and decreases are labelled with signed £K values.
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]
Retail Trading & Merchandising Performance
Merchandising Performance
Current month to date
Category Trading Summary
NameActualvs Target
Dresses£1273K92.9%
Knitwear£1089K79.5%
Denim£1510K110.2%
Trainers£1080K78.8%
Bedding£1073K78.3%
Fragrance£1198K87.4%
Discount vs Margin by Category
Top 10 Brands by Sales
Own Label
114%
Levi's
114%
Nike
109%
Joules
94%
Barbour
83%
Clarks
77%
Bottom 20 SKUs by Gross Margin %
NameActualvs Target
SKU-104582£1126K82.2%
SKU-209331£1454K106.1%
SKU-311746£1387K101.2%
SKU-418920£1442K105.2%
SKU-502117£1083K79.1%
Visual specification: Merchandising Performance
VisualTypeFieldsFormatting and interactionAnswers
Category Trading Summarymatrix
rows: DimProduct[Department], DimProduct[Category]
values: [Sales], [Sales YoY %], [Gross Margin %], [Gross Margin % YoY pts], [Discount %], [Units Sold]
  • Gross Margin % shows an icon and text from [Gross Margin RAG].
  • Sales YoY % uses a diverging font colour with +/- signs.
Which categories are under-delivering on margin or growth?
Discount vs Margin by Categoryscatter
x: [Discount %]
y: [Gross Margin %]
size: [Sales]
details: DimProduct[Category]
  • Constant Y line at [Gross Margin % Target].
  • Category data labels shown.
Is discounting eroding margin in specific categories?
Top 10 Brands by Salesbar
axis: DimProduct[Brand]
values: [Sales]
tooltip: [Gross Margin %], [Sales YoY %]
  • Sorted descending; data labels in £K.
  • Filter: Top 10 by [Sales]
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 %]
  • Gross Margin % shows an icon and text from [Gross Margin RAG].
  • Sorted ascending by Gross Margin %.
  • Filter: Bottom 20 by [Gross Margin %], where [Sales] > £1,000
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]
Retail Trading & Merchandising Performance
Store Performance
Current month to date
Store League Table
NameActualvs Target
Oxford Street£1367K99.8%
Manchester Arndale£1492K108.9%
Leeds Trinity£1144K83.5%
Bristol Cabot Circus£1299K94.8%
Glasgow Buchanan£1083K79.1%
Online (WEB01)£1074K78.4%
Average Basket Value by Month
Apr 2025May 2025Jun 2025Jul 2025Aug 2025Sep 2025
Sales by Day of Week and Channel
MonTueWedThuFriSatSun
Visual specification: Store Performance
VisualTypeFieldsFormatting and interactionAnswers
Store League Tabletable
values: DimStore[StoreName], DimStore[Region], [Sales], [Sales Target], [Sales vs Target %], [Sales YoY %], [Average Basket Value], [Gross Margin %]
  • Average Basket Value shows an icon and text from [Average Basket Value RAG]; Gross Margin % from [Gross Margin RAG].
  • Data bars on Sales vs Target %; sorted ascending.
Which stores are behind target and why?
Average Basket Value by Monthcolumn
axis: DimDate[YearMonth]
values: [Average Basket Value]
tooltip: [Average Basket Value PY], [Orders]
  • Constant line at [Average Basket Value Target] (£42).
  • Column colour from [Average Basket Value RAG], with data labels in £0.00.
  • Filter: Last 12 complete months
Is basket value improving month on month?
Sales by Day of Week and Channelstacked column
axis: DimDate[DayOfWeek]
legend: DimChannel[ChannelName]
values: [Sales]
  • Sorted by DayOfWeekNumber.
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

Confirm these with the business before build. Each one changes the model or measures if the answer differs.
#Assumption or questionImpact if different
1Prior-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.
2Sales 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.
3Sales 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.
4The £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.
5Click & 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.
6Cost 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.
7The 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.

Make one for your own report

Start free