The request
From Marcus Bell, Head of Merchandising
“I have a bar chart with a Top N filter showing our 3 best products in whatever category I click. Now I want the same idea in the category matrix: next to each category's 2025 sales, how much its 3 best products bring in. That tells me which categories hang on a few hits.”
Your goal
The matrix has products[category] on its rows and a slicer on 'Date'[Year] = 2025. Using the given Sales measure, create a measure called Top 3 Product Sales: the Sales of the 3 products (products[name]) with the highest Sales in the current filter context, added together. On the Total row it gives the top 3 products of the whole store.
- Fill
[Top 3 Product Sales]on every row of the visualnot done yet - The Total row right toonot done yet
Your measure
- Visual
- A matrix: rows = products[category]; values = [Sales], [Top 3 Product Sales]
- Slicers
- 'Date'[Year] = 2025
- 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
| category | Sales | Top 3 Product Sales |
|---|---|---|
| Decor | 290,301.42 | |
| Furniture | 573,131.05 | |
| Kitchen | 292,808.2 | |
| Lighting | 385,144.51 | |
| Outdoor | 507,358.12 | |
| Textiles | 251,426.23 | |
| Total | 2,300,169.53 |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
What Marcus gets, and why
The lesson
A Top N filter on a visual keeps the best N items of a field. It’s quick, but it filters a whole visual, and it can’t give you a number you can put in a matrix, divide or compare. For that you write the Top N as a measure.
What you are working in
This is a practice report, not Power BI (Power BI Desktop runs only on Windows). The matrix has the categories on its rows and a 2025 year slicer; a DAX engine built for this site evaluates your measure in every cell, Total included, and compares each with the answer.
To do it for real in Power BI Desktop: Home → New measure, type the formula, then drag it into
the matrix’s Values next to Sales. The visual filter Marcus already uses is in the Filters pane:
select the bar chart, drag products[name] into Filters on this visual, Filter type: Top
N, Show items: Top 3, Sales in By value, Apply filter.
TOPN returns a table
TOPN(3, VALUES(products[name]), [Sales])VALUES(products[name])is the list of product names visible in the current cell: the Furniture products on the Furniture row, all 120 on the Total row.TOPNevaluates[Sales]for each name and returns the 3 rows with the highest values. Ties at the cut-off are all kept, so the table can hold more than 3 rows.- The result is a table, so it can’t be shown on its own. It has to feed something that takes a table.
Use the table as a filter
Top 3 Product Sales = CALCULATE([Sales], TOPN(3, VALUES(products[name]), [Sales]))A table passed to CALCULATE acts as a filter: only those three names stay. The category and the
year from the cell are still there, so Sales is computed for the three products in that category
in 2025. The same result comes from iterating the table:
SUMX(TOPN(3, VALUES(products[name]), [Sales]), [Sales]).
For 2025:
| category | Sales | Top 3 Product Sales | share |
|---|---|---|---|
| Outdoor | $507,358 | $315,719 | 62% |
| Furniture | $573,131 | $269,710 | 47% |
| Kitchen | $292,808 | $104,888 | 36% |
Outdoor depends on a few hits; Kitchen spreads its sales.
Measure or visual filter?
The visual filter and the measure agree on what “top 3” means, but they do different jobs. Use
the visual filter to show only the top items in a chart. Use the measure when the top items are a
number: a column in a matrix, a share of the total, or a KPI card. RANKX(ALLSELECTED(products[name]), [Sales]) is the related tool when you need each product’s position instead of a sum.
Common mistakes
- ALL on the whole table.
ALL(products)removes the row’s category as well, so every row shows the same store-wide top 3. - Ranking by the wrong thing. The third argument decides the order. Rank by a row count or leave it out, and the 3 rows are not the 3 best sellers.
- Expecting exactly N rows. Ties at the cut-off bring extra rows.
Lantern lit!
Practised: topntop n filtercalculatevaluesfilter context
- Solved
- No hints
- First run