9. Lighthouse

Level 1 · Revenue line by line with SUMX

From Laura Mitchell, Finance Controller

“We're dropping line_total from the import: it's a stored copy of maths the source columns already hold, and the ERP rounds it per line. Before we do, I need a Net Revenue measure built from quantity, unit price and discount, so the category report survives without it.”

Your goal

The matrix has products[category] on its rows and a slicer keeps completed orders only. Create a measure called Net Revenue that adds up, for every order line, order_items[quantity] × order_items[unit_price] × (1 − order_items[discount_pct] / 100). Don't use line_total.

  • Fill [Net Revenue] on every row of the visual
  • The Total row right too

Your measure

DAX
Visual
A matrix: rows = products[category]; values = [Net Revenue]
Slicers
orders[status] = completed
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…

    Visual[Net Revenue] fills in when you press Check
    categoryNet Revenue
    Decor
    Furniture
    Kitchen
    Lighting
    Outdoor
    Textiles
    Total

    Mote checks your result

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

    The lesson

    SUM can only add up a column that already exists. Laura wants a number that doesn’t exist as a column any more: quantity × price × (1 − discount) on every order line, then added up. That is the job of an iterator.

    What you are working in

    This page is a practice report, not Power BI: Power BI Desktop runs only on Windows. The DAX engine behind the matrix was built for this site and follows Power BI’s rules, so the measure you write here works there unchanged. In Power BI Desktop: select order_items in the Data pane, Home → New measure, type the formula, press Enter, then drag the measure into the matrix’s Values well.

    SUMX: calculate on each row, then add

    Net Revenue =
SUMX(
    order_items,
    order_items[quantity] * order_items[unit_price] * (1 - order_items[discount_pct] / 100)
)

    SUMX(table, expression) takes two arguments:

    1. The table to walk. Here order_items, already filtered by the cell: the Kitchen row only lets it see Kitchen lines of completed orders.
    2. An expression evaluated once per row. Inside it, order_items[quantity] means “this row’s quantity”. That per-row view is called the row context, and only iterators (and calculated columns) create one.

    SUMX then adds up the per-row results. AVERAGEX, MINX, MAXX and COUNTX work the same way and finish with a different aggregation.

    Why SUM × SUM is wrong

    The tempting shortcut is SUM(order_items[quantity]) * SUM(order_items[unit_price]). It multiplies the total units of the category by the total of every line’s price, a number thousands of times too big. Totals can be multiplied only when every row has the same multiplier. Prices and discounts differ from line to line, so the multiplication has to happen inside each line first.

    Discounts are stored as percents

    discount_pct holds 15 for a 15% discount, so the line keeps 1 - 15 / 100 = 85% of its price. Forgetting the / 100 gives 1 - 15 = −14, and revenue turns negative on every discounted line.

    Why not keep line_total?

    Storing a computed column costs memory on every row and goes stale if the logic changes. A SUMX over the source columns is computed at query time from the columns you already load. It is also not rounded per line, so your Kitchen total will sit a dollar or so away from SUM(order_items[line_total]). That difference is the ERP’s rounding, not an error.

    Common mistakes

    • Multiplying sums. Aggregate after the row maths, never before.
    • A bare column in a measure. order_items[quantity] * order_items[unit_price] outside an iterator has no row to read from and fails.
    • Discount as a fraction. Divide discount_pct by 100 once.

    Cookie settings

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