The request
From Daniel Brooks, Inventory Planner
“Before I place next quarter's purchase orders I want to see demand by category: how many units customers ordered of each, across every order we've had, cancelled ones included. One number per category, and the total.”
Your goal
The report has a matrix with products[category] on its rows. Create a measure called Units Ordered that adds up order_items[quantity], so the matrix shows the units ordered per category and in total.
- Fill
[Units Ordered]on every row of the visualnot done yet - The Total row right toonot done yet
Your measure
- Visual
- A matrix: rows = products[category]; values = [Units Ordered]
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…
Reference solution
| category | Units Ordered |
|---|---|
| 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.
What Daniel gets, and why
The lesson
A measure is a named calculation that Power BI evaluates on the fly, once for every cell of every visual it appears in. You write it once; the report decides which rows it sees.
What you are working in
Power BI Desktop runs only on Windows, so this page gives you a practice report: the store’s data model (orders, order lines, products, customers, a Date table), a matrix visual, and an editor for DAX, the formula language of Power BI. When you press Check, a DAX engine built for this site evaluates your measure in every cell of the matrix and compares each cell with the answer. It follows the same rules as Power BI, so a measure that works here works there.
To do it for real in Power BI Desktop: open the report, select the order_items table in the
Data pane, then Home → New measure (or right-click the table → New measure). Type the
formula in the formula bar and press Enter. Then drag the new measure into the matrix’s
Values well.
The anatomy of a measure
Units Ordered = SUM(order_items[quantity])Units Orderedis the name you will see in the Data pane and in the visual.SUM( … )is an aggregation: it turns many values into one.order_items[quantity]is a column reference: the table name, then the column in square brackets. A table name with spaces needs single quotes, as in'Date'[Year].
One formula, many answers
The matrix has products[category] on its rows. For the Furniture row, Power BI evaluates
Units Ordered with a filter: only Furniture products. That filter travels along the relationship
from products to order_items, so SUM only sees Furniture order lines. The next row repeats
the evaluation with Lighting, and so on. The set of filters a cell applies is called its
filter context, and it is the single most important idea in Power BI. You will meet it in
every chapter.
The Total row is not the sum of the rows above it. Power BI evaluates the measure once more, with no category filter at all. For a plain SUM the two agree; later you will write measures where they don’t, and the difference matters.
Measures, not columns, for numbers people filter
You could add the quantities up in a spreadsheet next to the data. A measure is better because it answers every question at once: by category, by month, by channel, for one customer, without a single new formula. Change the visual and the same measure follows.
Common mistakes
- No aggregation.
Units Ordered = order_items[quantity]fails: a measure cannot read one value from a whole column. Wrap the column inSUM. - The wrong table.
productshas no quantities. Measures can live in any table, but the column must come from the table that holds it. - Smart quotes. Copying a formula from a document can turn
"into“. DAX needs straight quotes.
Lantern lit!
Practised: daxmeasuresummatrixfilter context
- Solved
- No hints
- First run