Five DAX Measures Every Retail KPI Dashboard Needs

Most retail dashboards I am asked to review already show sales, units and last year. What they miss are the measures that link sales with stock and profit, the ones a trading meeting actually argues about. Below are the five DAX measures I add to almost every retail model, with the definitions behind them. Table and column names are examples; the logic is what matters.

DAX Measures vs Calculated Columns for Retail KPIs

DAX stands for Data Analysis Expressions, the formula language of Power BI, Power Pivot in Excel and Analysis Services. So yes, you can use DAX in Excel: the same measures work in a Power Pivot data model. It looks like Excel formulas but behaves differently, because a measure is evaluated at query time in the filter context of each cell of a visual.

A calculated column is computed once per row when the model refreshes and then stored. A measure is not stored; it recalculates for whatever the viewer has selected, whether that is one store, a category or the whole season. That leads to a simple test I use: if adding up the value across rows gives a wrong answer, it has to be a measure. Ratios such as sell-through, stock cover or margin percentage fail that test, so they are always measures. Attributes such as price tier or season code belong in columns.

Is DAX harder than SQL? Different rather than harder. SQL describes which rows to return; DAX describes how to aggregate whatever rows the report has filtered. The hard part is filter context, and every measure below uses it deliberately. They all build on three base measures:

Net Units :=
SUM ( Sales[Units] ) - SUM ( Sales[ReturnUnits] )

Net Sales :=
SUM ( Sales[NetSalesExVAT] ) - SUM ( Sales[ReturnValueExVAT] )

COGS :=
SUM ( Sales[LandedCost] ) - SUM ( Sales[ReturnLandedCost] )

Before a new measure goes into a report, I check it against Excel. Pick a small, well-known slice, such as one category in one week, export the underlying rows, recalculate the KPI with ordinary formulas and compare it with a Power BI table filtered to the same slice. When the numbers differ, the cause is usually filter context: blank rows, an inactive relationship or a missing date filter.

Stock Cover Measure in DAX

Stock cover tells you how many weeks the current stock would last at the recent rate of sale. I define it as closing stock units divided by average weekly net units over the last four weeks. Stock is a snapshot, so it must not be summed over time: the measure takes the last date with stock in the selected period.

Stock Units :=
CALCULATE (
    SUM ( Stock[Units] ),
    LASTNONBLANK ( 'Date'[Date], CALCULATE ( SUM ( Stock[Units] ) ) )
)

Avg Weekly Units 4W :=
VAR LastDay = MAX ( 'Date'[Date] )
RETURN
    DIVIDE (
        CALCULATE ( [Net Units], DATESINPERIOD ( 'Date'[Date], LastDay, -28, DAY ) ),
        4
    )

Stock Cover (weeks) :=
DIVIDE ( [Stock Units], [Avg Weekly Units 4W] )

DIVIDE is the answer to the common question of how to divide two measures in DAX: it returns blank instead of an error when the denominator is zero, which happens every time a style has stock but no sales yet. You can sense-check the result with the calculator below.

Full-Price Sales Share Measure in DAX

Full-price share shows how much of the business is sold at the original ticket price, before markdowns and promotions. In fashion it is one of the best early signals of margin: a season with strong sales but a falling full-price share is buying volume with discount. The measure needs a clean price-type attribute on every sales line, which is a data task before it is a DAX task.

Full-Price Sales :=
CALCULATE ( [Net Sales], Sales[PriceType] = "Full price" )

Full-Price Share % :=
DIVIDE ( [Full-Price Sales], [Net Sales] )

Returns Rate Measure in DAX

The returns rate is returned units divided by gross units sold. Online fashion lives or dies by it, and dresses, trousers and footwear usually run far higher than accessories. The subtle part is timing. Counted by return date, a week after a big promotion shows a spike in returns against normal sales. Counted by the original sale date, the rate is correct for that sale, but recent weeks look better than they are because their returns have not arrived yet.

Returns Rate % :=
DIVIDE ( SUM ( Sales[ReturnUnits] ), SUM ( Sales[Units] ) )

Returns Rate by Sale Date % :=
CALCULATE (
    DIVIDE ( SUM ( Returns[Units] ), SUM ( Sales[Units] ) ),
    USERELATIONSHIP ( Returns[OriginalSaleDate], 'Date'[Date] )
)

Weeks of Supply Measure in DAX

Weeks of supply looks similar to stock cover, but it looks forward instead of back. I define it as stock plus open orders divided by the planned or forecast weekly sales for the coming weeks. Stock cover answers “how long will this last at today’s pace?”; weeks of supply answers “is what we own and have ordered enough for the plan?”. Both are useful, and a dashboard should say clearly which one it shows.

Weeks of Supply :=
VAR LastDay = MAX ( 'Date'[Date] )
VAR PlannedWeekly =
    DIVIDE (
        CALCULATE (
            SUM ( Plan[Units] ),
            DATESINPERIOD ( 'Date'[Date], LastDay + 1, 28, DAY )
        ),
        4
    )
RETURN
    DIVIDE ( [Stock Units] + SUM ( Orders[OpenUnits] ), PlannedWeekly )

Contribution Margin Measure in DAX

Gross margin stops at the cost of goods. Contribution margin also deducts the variable costs of selling: fulfilment, payment fees, marketplace commissions and returns handling. For a brand selling online, a category can look healthy on gross margin and weak on contribution margin simply because of its returns rate, which is why I place the two side by side.

Gross Margin :=
[Net Sales] - [COGS]

Variable Selling Costs :=
SUMX ( Sales, Sales[Units] * RELATED ( CostRates[CostPerUnit] ) )
    + SUMX ( Sales, Sales[NetSalesExVAT] * RELATED ( CostRates[FeeRate] ) )

Contribution Margin :=
[Gross Margin] - [Variable Selling Costs]

Contribution Margin % :=
DIVIDE ( [Contribution Margin], [Net Sales] )

Here is the idea with illustrative numbers: a category with net sales of 100,000, COGS of 42,000 and variable selling costs of 18,000 has a gross margin of 58% and a contribution margin of 40%. Agree the cost rates with finance once and document them; otherwise every meeting reopens the question.

Running Total DAX Pattern for Season-to-Date KPIs

Retail reports rarely stop at a single week. Buyers want season-to-date sales, full-price share and margin next to the week. The classic running total in DAX keeps the season and removes the date filter up to the last visible day:

Net Sales STD :=
VAR LastDay = MAX ( 'Date'[Date] )
VAR CurrentSeason = MAX ( 'Date'[Season] )
RETURN
    CALCULATE (
        [Net Sales],
        REMOVEFILTERS ( 'Date' ),
        'Date'[Season] = CurrentSeason,
        'Date'[Date] <= LastDay
    )

-- Financial year to date, year ending 31 January
Net Sales FYTD :=
CALCULATE ( [Net Sales], DATESYTD ( 'Date'[Date], "1/31" ) )

Ratios need a little more care: season-to-date full-price share is season-to-date full-price sales divided by season-to-date net sales, never an average of weekly percentages. The same rule applies to every measure in this article. Both patterns also need a proper date table, marked as a date table, with season and week columns.

• Stock cover: closing stock ÷ average weekly sales of the last four weeks.
• Full-price share: sales at original price ÷ net sales.
• Returns rate: returned units ÷ gross units sold, by return date or by sale date.
• Weeks of supply: stock plus open orders ÷ planned weekly sales ahead.
• Contribution margin: gross margin minus variable selling costs.

These five, together with sell-through, cover most of what a weekly trading review needs. My article on sell-through rate shows that measure in DAX, and the one on Power BI dashboard examples shows how I lay these KPIs out on a page.