The request
From Jessica Ward, Pricing Manager
“Before we add new products I want to see how wide each category's price ladder is today: from the cheapest active product to the most expensive. I already have the average list price per category; put the range next to it.”
Your goal
The matrix has products[category] on its rows, filtered to active products, next to the given Average Price measure. Create a measure called Price Range: the highest products[unit_price] minus the lowest, per category.
- Fill
[Price Range]on every row of the visualnot done yet - The Total row right toonot done yet
Your measure
- Visual
- A matrix: rows = products[category]; values = [Average Price], [Price Range]
- Slicers
- products[is_active] = true
- Measures in the report
Average Price = AVERAGE(products[unit_price])
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 | Average Price | Price Range |
|---|---|---|
| Decor | $75.40 | |
| Furniture | $774.63 | |
| Kitchen | $97.26 | |
| Lighting | $189.95 | |
| Outdoor | $429.83 | |
| Textiles | $69.77 | |
| Total | $262.18 |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
What Jessica gets, and why
The lesson
Jessica’s average price says where a category sits. The range says how far it stretches. Two categories can share an average and have completely different ladders.
What you are working in
This is a practice report built for this site; Power BI Desktop runs only on Windows. The
matrix has products[category] on the rows and a filter keeping active products only
(products[is_active] is TRUE). Write one or more measures; the last one is checked in every
cell.
In Power BI Desktop: select products in the Data pane, Home → New measure, type the
formula and press Enter. Repeat for each measure. The active-only filter would be a Filters on
this page card in the Filters pane with is_active set to True.
Four aggregations
| Function | Returns, for the cell’s rows |
|---|---|
SUM(column) |
the total |
AVERAGE(column) |
the arithmetic mean (blanks are skipped) |
MIN(column) |
the smallest value |
MAX(column) |
the largest value |
All four take one column and return one number for whatever rows the cell’s filters leave. In
the Lighting row, MIN(products[unit_price]) is the cheapest active lighting product.
Measures can use measures
A measure can refer to another measure by its name in square brackets:
Lowest Price = MIN(products[unit_price])
Highest Price = MAX(products[unit_price])
Price Range = [Highest Price] - [Lowest Price]Each one appears in the field list on its own, so Jessica can show the low and high ends too,
and if the definition of a price ever changes, you fix it once. Writing it in one line,
MAX(products[unit_price]) - MIN(products[unit_price]), gives the same numbers.
A convention worth adopting: refer to measures without a table name ([Lowest Price]) and to
columns always with one (products[unit_price]). Anyone reading the formula can then tell them
apart.
Reading the Total row
The Total row has no category filter. Its range runs from the store’s cheapest active product to its most expensive, which is wider than any single category. It is not the sum of the rows, and not their average; it is the same formula, evaluated over everything.
Common mistakes
- Using
products[unit_price]alone. A measure needs an aggregation: a category holds many prices, and a bare column can’t say which one you mean. - MIN of the whole table.
MIN(products[unit_price])already respects the category filter; you don’t need to filter it yourself. - Reading the Total as a sum. Every cell, the Total included, is its own calculation.
Lantern lit!
Practised: measureminmaxaveragemeasure reference
- Solved
- No hints
- First run