Model and measures

01The problem

Two traps wait between a clean sales table and a correct profit figure.

The cost trap. Voltline's costs went up 18% on 1 January 2026. Join sales to cost_prices on product code alone and every sale matches two cost rows: 27,588 lines become 55,176, and every total doubles. Pick just the latest cost instead, and 2025's sales are costed at 2026 prices: the first half of 2025 shows a gross margin of −2.7%, as if Voltline sold everything at a loss.

The target trap. Targets are one number per store per month. Join them to sales lines and each target is repeated once per line, so a store's target appears hundreds of times over.

Both are grain problems, and both are invisible unless you check. This lesson builds the model that avoids them.

02The concept

The star schema

Looking up a cost that changes over time

Each sale needs the cost whose effective_from is the latest one on or before the sale date. That's a "range lookup":

The Power BI calculated column, on sales_clean:

Unit Cost =
VAR Code = sales_clean[product_code]
VAR SaleDate = sales_clean[sale_date]
VAR Effective =
    MAXX (
        FILTER ( cost_prices, cost_prices[product_code] = Code && cost_prices[effective_from] <= SaleDate ),
        cost_prices[effective_from]
    )
RETURN
    MAXX (
        FILTER ( cost_prices, cost_prices[product_code] = Code && cost_prices[effective_from] = Effective ),
        cost_prices[unit_cost]
    )

Whichever you use, check that the row count doesn't change after the lookup.

Comparing with targets: aggregate first

Total the sales to store and month, then compare with the targets. In Power BI, relate targets to stores (via store_id) and to Date (via a month-start date column you add to targets), and write Target = SUM ( targets[net_sales_target] ). The measure only makes sense at month level or above. On a single day it would show the whole month's target.

The core measures

Net Sales = SUM ( sales_clean[net_sales] )
Gross Profit = SUMX ( sales_clean, sales_clean[net_sales] - sales_clean[qty] * sales_clean[unit_cost] )
Gross Margin % = DIVIDE ( [Gross Profit], [Net Sales] )
Target = SUM ( targets[net_sales_target] )
Target Attainment % = DIVIDE ( [Net Sales], [Target] )
Transactions = DISTINCTCOUNT ( sales_clean[txn_id] )

03Example

Gross margin by category for January to June 2026, with costs looked up correctly:

Phones and solar bring in almost the same sales, but solar's margin is twice as high, so solar now earns about twice the gross profit of phones. Accessories are tiny in sales but earn 44p in every naira. Keep that in mind for lesson 5.

04Walkthrough

1. Add unit_cost to the clean sales with a range lookup in your chosen tool. Check the row count is still 27,588.
2. Calculate gross margin for January to June 2025. It should be about 12.9%. If you get −2.7%, your lookup used 2026 costs for 2025 sales.
3. Build the model: relate the fact to stores, products and a date table, and the targets to stores and dates (at month start).
4. Write the six core measures (or the equivalent SQL or pandas summaries) and format them.
5. Test them: total net sales should be ₦5,811,600,950 (from lesson 3), and target attainment for one store and month should match a hand calculation.

05Practice

06Check your understanding