The request
From Samantha Price, VP of Merchandising
“The board keeps asking how dependent we are on a handful of hits. For each year, how much did that year's five best-selling products bring in? I'll put it next to total sales.”
Your goal
The matrix has 'Date'[Year] on its rows and the report's [Sales] measure. Create a measure called Top 5 Sales: [Sales] of the five products (by products[name]) with the highest [Sales] in the cell's period.
- Fill
[Top 5 Sales]on every row of the visualnot done yet - The Total row right toonot done yet
Your measure
- Visual
- A matrix: rows = 'Date'[Year]; values = [Sales], [Top 5 Sales]
- Measures in the report
Sales = CALCULATE(SUM(order_items[line_total]), 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…
Reference solution
| Year | Sales | Top 5 Sales |
|---|---|---|
| 2,023 | $1,600,511 | |
| 2,024 | $1,993,604 | |
| 2,025 | $2,300,170 | |
| Total | $5,894,284 |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
What Samantha gets, and why
The lesson
Samantha’s question is about concentration: if the five biggest sellers drive a large share of the year, the business is exposed to any one of them going out of stock. The number she needs is “sales, but only of the top five products”, and the five change from year to year.
What you are working in
This is a practice report built for this site, not Power BI: Power BI Desktop runs only on Windows. A DAX engine on the page evaluates your measure in every cell of the matrix following Power BI’s rules. In Power BI Desktop: Home → New measure, type the formula, and drop Top 5 Sales next to Sales in the matrix.
TOPN returns a table
TOPN(5, ALL(products[name]), [Sales])5is how many rows to keep.ALL(products[name])is the table to choose from: every product name.[Sales]is the expression that orders it, evaluated once per name. Like any measure reference inside an iterator, it gets a context transition, so each name gets its own sales. The default order is descending: highest first.
TOPN does not return a number; it returns a five-row table. Ties at the cut-off can return more than five rows. A table on its own can’t go in a matrix cell, so you use it as a filter.
A table as a CALCULATE filter
Top 5 Sales =
CALCULATE(
[Sales],
TOPN(5, ALL(products[name]), [Sales])
)CALCULATE accepts a table as a filter argument: the five names become the only names allowed,
and [Sales] is computed for those products. Everything else in the cell’s context stays. In
the 2024 row the year filter is still there, so TOPN ranks products by their 2024 sales and the
outer [Sales] adds up their 2024 sales.
Why the Total isn’t the sum of the rows
The Total row has no year filter. TOPN picks the five best products across 2023 to 2025, which are not the same five as in any single year, and sums their sales over all three years. It is correct for that question, and it is different from adding the three rows.
Turning it into a share
Divide by the whole to get the concentration ratio the board will actually read:
Top 5 Share = DIVIDE([Top 5 Sales], [Sales])Common mistakes
- No ALL. If products are on the visual’s rows,
VALUES(products[name])holds one name and “top 5” is just that product. - Ordering by a column.
TOPN(5, ALL(products[name]), products[unit_price])can’t read a price from a list of names, and even with the whole products table it would rank by price, not by what sold. - TOPN on its own as a measure. A measure must return one value; TOPN returns a table.
Lantern lit!
Practised: topncalculatealltable functionsconcentration
- Solved
- No hints
- First run