9. Lighthouse

Level 8 · Line cost with RELATED

From Laura Simmons, Category Finance Partner

“I need cost of goods sold by category for completed orders. The unit cost lives on the product, the quantity on the order line. Can you put a cost on every line so I can total it?”

Your goal

Add a calculated column called Line Cost to order_items: the line's quantity times its product's unit_cost, fetched from products with RELATED. The page is filtered to completed orders, and the matrix shows the given Cost measure (SUM of your column) by products[category].

  • Add the column Line Cost to order_items
  • The Total row right too

Your calculated column

DAX
Visual
A matrix: rows = your new column on order_items, values = [Cost]
Slicers
orders[status] = completed
Measures in the report
Cost = SUM(order_items[Line Cost])
Data pane: tables and columns
  • order_items order_item_id, order_id, product_id, quantity, unit_price, discount_pct, line_total
  • orders order_id, customer_id, order_date, status, channel, shipping_fee, coupon_code
  • products product_id, name, category, subcategory, unit_price, unit_cost, launched_on, is_active
  • customers customer_id, first_name, last_name, email, city, state, signup_date, segment, referral_source
  • returns return_id, order_item_id, returned_on, reason, refund_amount
  • Date Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday

Loading the editor…

    VisualData view of order_items: your column appears in the visual once you press Check
    order_item_idorder_idproduct_idquantityunit_pricediscount_pct
    11,001121931.990
    21,001154311.990
    31,002154411.990
    41,00219411490
    51,002193219.990
    61,002196225.990
    71,002156153.990
    81,003134192.9910

    Mote checks your result

    Press Check. Mote fills in your answer and compares it with what the report needs.

    The lesson

    The quantity is on the order line and the cost is on the product. A calculated column on order_items can reach across the relationship and bring the cost over, one line at a time.

    What you are working in

    This is a practice report built for this site; Power BI Desktop runs only on Windows. Your column goes in order_items. The page is filtered to completed orders, and the matrix shows Cost = SUM(order_items[Line Cost]) by products[category], so it fills in as soon as your column exists.

    In Power BI Desktop: select order_items in the Data pane, Table tools → New column, type the formula and press Enter. RELATED only works if the relationship from order_items to products exists in Model view.

    In a calculated column on order_items, each row is one order line. Its own columns are available directly; columns of other tables are not, because DAX doesn’t know which of their rows you mean. RELATED tells it: follow this row’s relationship to the “one” side and read the column there.

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

    For a line with product_id 17 and quantity 2, RELATED finds product 17 and returns its unit cost; the line cost is twice that. 30,000 lines, 30,000 lookups, done once at refresh.

    RELATED works in one direction: from the many side to the one side, where there is exactly one row to read. Going the other way, from a product to its many lines, needs RELATEDTABLE, which returns a table you then aggregate.

    Blank costs

    Nine products have no unit_cost in the data. For their lines, RELATED returns a blank, and a number multiplied by blank is blank in DAX, so those lines have no cost and SUM skips them. That is honest: the cost is unknown, not zero. A finance report should say which products are missing a cost rather than quietly count them as free.

    Column, then measure

    The column does the per-line arithmetic; the measure aggregates it. You could also skip the column with an iterator, SUMX(order_items, order_items[quantity] * RELATED(products[unit_cost])), which computes the same thing inside a measure without storing a column. You’ll write those in the advanced DAX chapter.

    Common mistakes

    • No RELATED. order_items[quantity] * products[unit_cost] fails: in a row of order_items there is no single product cost to read.
    • The selling price instead of the cost. order_items[unit_price] is what the customer paid.
    • RELATED the wrong way. In a column on products, RELATED(order_items[quantity]) fails: a product has many lines.

    Cookie settings

    Pick what may run on this site. You can change this any time from "Cookie settings" at the bottom of every page.