The request
From Jordan Hayes, Category Director
“In the monthly pack the categories are listed alphabetically, because that's how the regional managers find their own. I still want a Rank column next to Sales, so they can see at a glance where their category stands in 2025.”
Your goal
The matrix lists products[category] alphabetically, with a slicer on 'Date'[Year] = 2025 and the report's [Sales] measure next to it. Create a measure called Sales Rank that gives each category its rank by [Sales] among all six categories: 1 for the biggest.
- Fill
[Sales Rank]on every row of the visualnot done yet
Your measure
- Visual
- A matrix: rows = products[category]; values = [Sales], [Sales Rank]
- 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 | Sales Rank |
|---|---|---|
| Decor | $290,301 | |
| Furniture | $573,131 | |
| Kitchen | $292,808 | |
| Lighting | $385,145 | |
| Outdoor | $507,358 | |
| Textiles | $251,426 |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
What Jordan gets, and why
The lesson
Sorting a visual by Sales shows the order, but only while that sort lasts. Jordan’s readers sort by name. A rank measure puts the position in a column of its own, so it stays right however the table is sorted.
What you are working in
The matrix below is a practice report built for this site. Power BI Desktop runs only on Windows, so a DAX engine on the page fills each cell, following Power BI’s rules. In Power BI Desktop: Home → New measure, type the formula, and drop Sales Rank into the matrix’s Values next to Sales.
RANKX in two arguments
Sales Rank = RANKX(ALL(products[category]), [Sales])RANKX(table, expression):
- Who competes. A table with one row per competitor.
ALL(products[category])is the list of all six categories with every filter on that column removed. - What decides the order. RANKX evaluates the expression once per competitor, each in its own context, then evaluates it once more in the current cell and returns its position.
Optional arguments follow: a value to rank instead of the current one, the order (DESC, the
default, makes the biggest 1; ASC makes the smallest 1) and how ties are handled (SKIP, the
default, gives 1, 2, 2, 4; DENSE gives 1, 2, 2, 3).
Why ALL is required
Inside the Furniture row the filter context holds only Furniture. VALUES(products[category])
there returns one category, so RANKX compares Furniture with Furniture and returns 1. Every row
is 1. ALL( ) removes the row’s filter on that column, so all six compete.
Other filters still apply. The 2025 slicer filters the Date table, not the category column, so
each category’s [Sales] is its 2025 sales. Rank is computed inside whatever the reader has
selected on other columns.
Rank by a measure, not a column
The second argument should be a measure, [Sales]. Inside RANKX, a measure reference applies
the current competitor as a filter, so each category gets its own number. A bare
SUM(order_items[line_total]) there gets no such filter and returns the same total for every
competitor, so every rank is 1. If you must write the expression inline, wrap it in CALCULATE.
The Total row
On a Total row there is no current category, and RANKX would rank the grand total against the six
categories, returning 1. This visual hides the Total row. In your own reports, wrap the rank in
IF(ISINSCOPE(products[category]), RANKX( … )) so the Total cell stays blank.
Common mistakes
- VALUES instead of ALL. One competitor, rank 1 everywhere.
- ALL(products). Removes every filter on the products table and ranks against 120 products, not six categories.
- Expecting 1 to be the smallest. The default order is descending.
Lantern lit!
Practised: rankxallrankingfilter context
- Solved
- No hints
- First run