Pricing health dashboard
One question for the owner of a small store: where am I losing margin because my prices fell behind my costs? The answer is a ranked to-do list for the next price review.
The overview and the below-cost list, on the demo's synthetic store (a year of sales for 128 products).
What it shows
Four numbers, then the list.
- Revenue and gross margin %, on revenue without VAT.
- Revenue on costs older than two years: how much of the business is priced on costs nobody has checked.
- Money lost selling below cost in the last 90 days.
- Markup by product family against the family's usual markup, in red where it is below.
The table under it is the point of the report: products sold below cost, ranked by money lost. In the real store, the same idea uses audit materiality (ISA 320) to decide what is worth a person's time.
Data model
A star schema, kept boring on purpose.
| Table | One row per | Key |
|---|---|---|
| fact_sales | product and day | product_code, day |
| fact_price_changes | price written or undone | product_code |
| dim_product | product | code |
| dim_family | product family, with its usual margin | family |
| below_cost_90d | product sold below cost in 90 days | code |
| Calendar | day (built in DAX) | Date |
Measures
Revenue ex VAT = SUMX ( fact_sales, fact_sales[revenue] / ( 1 + RELATED ( dim_product[vat] ) / 100 ) )
Cost of goods = SUMX ( fact_sales, fact_sales[qty] * RELATED ( dim_product[cost] ) )
Gross margin % = DIVIDE ( [Revenue ex VAT] - [Cost of goods], [Revenue ex VAT] )
Markup % = DIVIDE ( [Revenue ex VAT], [Cost of goods] ) - 1
-- same basis as dim_family[usual_margin]: a markup over cost, not a margin on sales
Stale cost revenue % =
DIVIDE ( CALCULATE ( [Revenue], dim_product[cost_date] < DATE ( 2024, 9, 30 ) ), [Revenue] )
A detail that matters
Store owners say "margin" for two different things. The family targets are a markup over cost, so they are compared with Markup %, never with Gross margin %. Mixing the two makes every family look worse than it is.
How it is built
The data comes from the demo's own report command, which writes the CSV files the model loads. The image above is drawn by a small Python script that computes the same measures with pandas, so the numbers can be checked without Power BI. The README has the model, the DAX and the page layout to rebuild it in Power BI Desktop.
- Power BI
- DAX
- Power Query
- Star schema
- pandas
- matplotlib