9. Lighthouse

Level 2 · Completed sales with CALCULATE

From Laura Bennett, Finance Manager

“The channel matrix uses Gross Sales, which counts every order line, including cancelled and refunded orders and ones still pending. For the board pack I need what we actually earned: only orders whose status is completed. Can you add that column next to Gross Sales?”

Your goal

The matrix has orders[channel] on its rows and the given measure Gross Sales = SUM(order_items[line_total]). Create a measure called Completed Sales that returns Gross Sales for completed orders only (orders[status] = "completed"), using CALCULATE.

  • Fill [Completed Sales] on every row of the visual
  • The Total row right too

Your measure

DAX
Visual
A matrix: rows = orders[channel]; values = [Gross Sales], [Completed Sales]
Measures in the report
Gross Sales = SUM(order_items[line_total])
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[Completed Sales] fills in when you press Check
    channelGross SalesCompleted Sales
    app$2,262,479
    marketplace$930,273
    web$3,323,593
    Total$6,516,346

    Mote checks your result

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

    The lesson

    CALCULATE is the most important function in DAX. It evaluates an expression in a modified filter context: the cell’s filters, changed by the filter arguments you give it.

    What you are working in

    This page runs a practice DAX engine built for this site. It evaluates your measure in every cell of the matrix (one row per channel, plus the Total) and compares each cell with the answer. In Power BI Desktop you would select the orders table in the Data pane, choose Home → New measure, type the formula in the formula bar and press Enter, then drag the measure into the matrix’s Values.

    The shape of CALCULATE

    Completed Sales = CALCULATE([Gross Sales], orders[status] = "completed")
    • The first argument is the expression to evaluate: here the existing measure [Gross Sales].
    • Every argument after it is a filter. orders[status] = "completed" is a boolean filter: a condition on one column. DAX turns it into a filter that keeps the rows of orders whose status is completed.

    Behind the scenes, that boolean filter is shorthand for a table of allowed values:

    CALCULATE([Gross Sales], FILTER(ALL(orders[status]), orders[status] = "completed"))

    You will rarely write the long form, but it explains the rule that matters most.

    Add or replace

    For the app row, the cell’s filter context holds orders[channel] = "app". CALCULATE adds the status filter. The two are on different columns, so both apply: app orders that are completed.

    If a filter argument names a column that is already filtered, CALCULATE replaces that filter instead of combining with it. CALCULATE([Gross Sales], orders[channel] = "web") shows the web number on the app row, the marketplace row and the Total, because the row’s channel filter is overwritten. Keep that in mind; it is the reason shares of total work, and the reason a careless filter puts the same number on every row.

    Why a measure and not a filter on the visual

    You could drag status into the Filters pane and set it to completed. That changes every measure on the visual, including Gross Sales, which Laura wants to keep. A CALCULATE filter belongs to one measure, travels with it to every page, and nobody can untick it by accident.

    Common mistakes

    • Wrong text. Text comparisons in DAX ignore case, but a typo such as "complete" matches nothing and returns a blank.
    • Single quotes for text. 'completed' is a table name in DAX. Text goes in double quotes.
    • SUM of a measure. SUM([Gross Sales]) fails: SUM needs a column. A measure is already one number; use [Gross Sales] on its own.

    Cookie settings

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