Case study · Project 03
Enterprise Procurement & Spend Intelligence
Project overview
An enterprise-style procurement intelligence solution analysing purchase orders, invoices, supplier performance, procurement savings, contract compliance and multi-currency spend. Built as an independent portfolio project on a synthetic dataset, it applies the patterns of a real enterprise deployment — complex many-to-many modelling, currency conversion, row-level security and performance optimisation at scale.
The business problem
Procurement data is enterprise data at its messiest: purchase orders raised in one system, invoices booked in another, suppliers recorded differently in each, and spend spread across currencies and financial years. Category managers want to know where money leaks — off-contract buying, price drift, supplier concentration — but every answer currently requires a bespoke extract. The project models those source systems into one governed spend model with security built in.
Business objectives
Dataset description
The synthetic dataset simulates an enterprise procurement landscape: three years of purchase order lines with goods-receipt and invoice status, a supplier master with parent-company groupings, contract terms with baseline prices, daily currency exchange rates, and an organisational user-to-category mapping used to demonstrate row-level security. Volume and data messiness were tuned to make performance work meaningful.
FactPurchaseOrders
PO line items with quantities, unit prices, currency, status and supplier keys
FactInvoices
Invoice lines matched to purchase orders with amounts, payment terms and variances
DimSupplier
Supplier master with parent groups, countries and risk tiers
DimContract
Contract terms, baseline prices and validity windows
FactFxRates / security mapping
Daily exchange rates plus the user-to-category mapping that drives row-level security
Data preparation methodology
Model the PO-to-invoice lifecycle
Linked purchase orders, goods receipts and invoices so matching status and variances stay traceable end to end.
Conform the supplier master
Resolved duplicate supplier records and grouped subsidiaries under parent companies for group-level analysis.
Stage dated FX rates
Loaded daily exchange rates and bound them to transaction dates so historical spend restates correctly.
Classify spend
Mapped free-text descriptions into a category and subcategory taxonomy aligned to contract structure.
Instrument for performance
Set storage modes, relationships and measure patterns so the model stays responsive at enterprise-scale row counts.
Data model
Data model diagram — placeholder
A diagram showing the purchase-order-to-invoice lifecycle, supplier and contract dimensions, the FX rate table and the security mapping will be added here.
Report preview
Report page screenshot — placeholder
Report page screenshot — placeholder
Screenshots of each report page — spend overview, supplier performance, savings and compliance, and the secured category view — will be added here.
Key KPIs & business metrics
Total spend (group currency)
Consolidated spend across entities, normalised to the reporting currency at transaction-date rates
Savings vs baseline
Realised savings measured against contract baseline prices and prior-year averages
Contract compliance
Share of spend placed with contracted suppliers at contracted terms
PO-to-invoice variance
Price and quantity variance between ordered, received and invoiced values
Supplier concentration
Share of spend held by the top suppliers, flagged against risk thresholds
On-time delivery
Supplier delivery performance against promised dates, by category
DAX & technical implementation
Three patterns carry the model: a bridge-table many-to-many between the spend fact and contracts, a currency conversion chain bound to transaction dates, and dynamic row-level security driven by a user-mapping table.
Spend in reporting currency
Converts each transaction at its own date's rate, so every downstream KPI inherits correct currency logic.
Spend GBP =
SUMX (
FactPurchaseOrders,
VAR TxnRate =
CALCULATE (
MAX ( FactFxRates[Rate] ),
FactFxRates[Currency] = FactPurchaseOrders[Currency],
FactFxRates[Date] = FactPurchaseOrders[OrderDate]
)
RETURN FactPurchaseOrders[Amount] * TxnRate
)Contract compliance %
Shares spend placed under an active contract with the contracted supplier against total spend.
Contract Compliance % =
DIVIDE (
CALCULATE ( [Spend GBP], FactPurchaseOrders[UnderContract] = TRUE () ),
[Spend GBP]
)Savings vs baseline
Compares actual unit price with the contract baseline, weighted by purchased volume.
Savings vs Baseline =
SUMX (
VALUES ( DimContract[ContractItemKey] ),
VAR BaselinePrice = CALCULATE ( MAX ( DimContract[BaselinePrice] ) )
VAR ActualQty = CALCULATE ( SUM ( FactPurchaseOrders[Quantity] ) )
VAR ActualPrice = DIVIDE ( CALCULATE ( [Spend] ), ActualQty )
RETURN ( BaselinePrice - ActualPrice ) * ActualQty
)Technical challenges & solutions
One purchase order could map to several invoices and partial goods receipts, breaking simple one-to-many joins.
Approach — Introduced bridge tables with deduplicated keys and defined filter direction deliberately, keeping bidirectional filters to the minimum that correctness allows.
Currency conversion distorted trends when a single period rate was applied to all transactions.
Approach — Bound each transaction to its own dated rate and built the conversion into the base measure so every downstream KPI inherits it.
Category-level security needed to hold for aggregated dashboards and detail tables alike.
Approach — Implemented dynamic row-level security with a user-mapping dimension and validated it under both aggregate and row-level visuals.
Large fact tables made the drill-down pages sluggish.
Approach — Reduced model width, moved high-cardinality text into dimensions, and replaced iterator-heavy measures with optimised CALCULATE patterns.
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.
- 01Off-contract spend clusters in a handful of categories — exactly where compliance tracking pays back first.
- 02Price drift against baseline shows up months before budgets feel it, visible in the savings trend.
- 03Supplier concentration risk is measurable: the top-supplier view flags dependence that individual purchase-order reviews miss.
- 04Delivery reliability varies more between suppliers within a category than between categories.
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.