Business performance, from revenue to fulfilment
A commercial investigation built around one question: where does revenue lose value? A transactional star schema and carefully defined DAX measures connect executive performance to the customers, products and fulfilment patterns behind it.
Commercial teams need to see more than a sales total. The report connects revenue to discount exposure, customer concentration, product contribution and delivery reliability. Six analytical pages share market, year and customer-segment filters so a headline result can be investigated in context.
| Measure | Result | Meaning |
|---|---|---|
| Sales before discounts | $36.78M | Sum of the source Sales field |
| Net sales | $33.05M | Recorded order-item totals after discounts |
| Recorded profit | $3.97M | Sum of the source profit field |
| Net profit margin | 12.00% | Recorded profit divided by net sales |
| Average order value | $502.71 | Net sales per distinct order |
2017 sales fell 4.03% against 2016.
Sales before discounts: $12.30M in 2016 and $11.81M in 2017.
Investigate changes in category and market mix before setting growth targets.
Limit: January 2018 is a partial year and cannot be compared directly with a full year.The top 20% contribute 49.94% of net sales.
20,652 customers; ranking uses net revenue and deterministic tie handling.
Prioritise service reliability and retention for high-value accounts, then compare concentration by segment.
Limit: This is historical contribution, not predicted customer lifetime value.57.29% of non-cancelled order lines are late.
98,977 late lines; late lines average 1.62 days beyond their scheduled duration. First Class is late for every non-cancelled line in this dataset.
Review scheduled-versus-actual duration by mode and validate whether the First Class SLA is represented correctly before changing carriers.
Limit: Line-weighted results describe the dataset; they do not establish carrier causality or current operational performance.$3.73M of recorded discounts sit alongside $3.97M of recorded profit.
Net sales are $33.05M, compared with $36.78M before discounts.
Compare discounted and undiscounted orders within similar products, markets and customer groups before adjusting pricing.
Limit: A discount is not necessarily avoidable lost profit; margin and purchase behaviour may change without it.13,908 distinct orders have negative aggregate recorded profit.
21.15% of all distinct orders. Lines are grouped by OrderId before classifying the order.
Diagnose the mix, pricing and discount patterns of loss-making orders.
Limit: When product filters are applied, the measure evaluates the selected lines within each order.$18.08M in net sales is associated with late order lines.
16,018 customers have at least one late line across the recorded period.
Connect account prioritisation with fulfilment reviews and track repeat purchasing after late delivery.
Limit: Exposure is not lost revenue; this analysis does not estimate churn or the financial effect of a delay.The fact table contains 180,519 unique order items belonging to 65,752 distinct orders. Seven dimensions filter it through active, single-direction many-to-one relationships. Order-date filtering uses DimDate; the shipping-date key is present but has no active relationship.
Unique dimension keys and complete foreign-key coverage were checked against the embedded tables. Additive monetary measures stay at order-item grain; order counts and order-level loss measures explicitly group or count distinct OrderId values.
Total Orders =
DISTINCTCOUNT(FactOrderItem[OrderId])
Avg Order Value =
DIVIDE([Net Sales], [Total Orders])Profit Margin % =
DIVIDE([Total Profit], [Net Sales]) * 100Late Delivery Rate =
DIVIDE(
CALCULATE(COUNTROWS(FactOrderItem),
FactOrderItem[DeliveryStatus] = "Late delivery"),
CALCULATE(COUNTROWS(FactOrderItem),
FactOrderItem[DeliveryStatus] <> "Shipping canceled")
) * 100Avg Delivery Delay (Days) =
AVERAGEX(
FILTER(FactOrderItem,
FactOrderItem[DeliveryStatus] = "Late delivery"),
FactOrderItem[DaysForShippingReal]
- FactOrderItem[DaysForShipScheduled]
)Source: DataCo SMART Supply Chain for Big Data Analysis, version 5, by Fabian Constante, Fernando Silva and António Pereira. Published under CC BY 4.0.
The public DataCo SMART Supply Chain dataset is a historical transactional sample, covering January 2015 to January 2018 in this model. It supports descriptive comparisons, not causal estimates of discount impact, delivery-induced churn or future customer value. Recorded profit is used as supplied; there is no independent cost ledger to reconstruct gross profit.
Delivery rates are weighted by order lines, and cancellations are excluded from delivered-line percentages. Dollar formatting follows the source report convention; the model contains no currency-conversion treatment. Recorded net totals differ from pre-discount sales minus recorded discounts by $46.13 in aggregate because of line-level rounding.
Next: introduce explicit cohort retention, comparable-period growth, discount-band analysis with matched product groups, and service measures at both order and line grain. A hosted interactive Power BI report can be added once its publication and sharing settings are agreed.