Level 9 · Boss: the category margin review

This review uses everything in the chapter: columns that work row by row, measures that work cell by cell, and the model’s relationships deciding what each cell can see.

What you are working in

A practice report built for this site, since Power BI Desktop runs only on Windows. Step 1 adds calculated columns to order_items; steps 2 and 3 add measures to a matrix by category, filtered to completed orders. In Power BI Desktop the columns come from Table tools → New column with order_items selected, the measures from Home → New measure, and the completed-only filter is a page filter in the Filters pane.

Step 1: margin per line

Line Cost = order_items[quantity] * RELATED(products[unit_cost])

For the nine products without a cost, RELATED returns blank and the product is blank. The margin needs care, because DAX treats a blank as 0 in subtraction: line_total - blank is the whole line total, a 100% margin that never happened. So test first:

Line Margin =
IF(
    ISBLANK(order_items[Line Cost]),
    BLANK(),
    order_items[line_total] - order_items[Line Cost]
)

A later column can use an earlier one; that’s why Line Cost comes first.

Step 2: margin percentage

Margin dollars over the sales that produced them. Use the given Costed Sales, which adds up line_total only on lines with a cost, so the numerator and the denominator cover the same lines. Dividing by all sales would mix lines that have a margin with lines that don’t, and push the percentage down in the categories with missing costs.

Step 3: margin per order

How much margin does an average order bring in each category? Margin over the number of orders. But which orders? The category filter flows from products to order_items and stops there: orders is on the one side of its relationship with order_items, so a single-direction relationship never filters it. COUNTROWS(orders) would return all completed orders on every row. The orders a category touches are the distinct order_ids among its lines:

DISTINCTCOUNT(order_items[order_id])

An order with a lamp and a rug counts once for Lighting and once for Textiles, but only once in the Total.

Reading the results

Margin per Order is highest where baskets are expensive, not necessarily where Margin % is highest. Show both: the percentage says how profitable each sale is, the per-order figure says how much each basket is worth to the business.

Common mistakes

• Subtracting a blank cost. It silently becomes a full-price margin.
• All sales as the denominator. Margin % comes out too low wherever costs are missing.
• Counting orders from orders. The category filter doesn’t reach that table.