RANKX is powerful, but it is also one of the easiest DAX functions to make confusing. Ranking within category means the measure must remove the item filter while keeping the category filter. For broader reporting repair work, see the Power BI consultancy and ERP reporting analytics pages.
Write a base measure, use RANKX over the item list inside the current category, and use ALLEXCEPT or a carefully scoped table expression so category filters remain active.
1Confirm ranking grain
Decide what is being ranked and inside which category. For example, products within product group, customers within region, or sites within business unit.
If the category comes from a different table, check the relationship path. Ranking problems often start with a model that cannot filter the item table cleanly.
- Identify the item column displayed in the visual.
- Identify the category column that should be retained.
- Confirm whether rank 1 is highest or lowest.
- Decide whether blank values should rank or stay blank.
2Create the RANKX measure
The measure below ranks products inside the current category by sales. ALLEXCEPT keeps the category context while removing the individual product filter for the ranking set.
In some models, ALLSELECTED is better because it respects user selections. Test both against the expected business behaviour.
Sales Amount =
SUM ( Sales[Net Amount] )
Product Rank Within Category =
IF (
ISBLANK ( [Sales Amount] ),
BLANK (),
RANKX (
ALLEXCEPT ( Product, Product[Category] ),
[Sales Amount],
,
DESC,
Dense
)
)3Handle ties and blanks
Dense ranking gives tied items the same rank and uses the next available rank without a gap. Skip ranking leaves a gap after ties. Neither is universally right; choose the one users expect.
Blank values should often stay blank rather than ranking last, especially when the visual is used for performance management.
- Filter to one category and manually sort by the base measure.
- Check tied values and confirm dense or skip behaviour.
- Test what happens when a slicer removes some products.
- Add a visual-level filter for Top N only after the rank measure is validated.
Common mistakes to avoid
Removing the category filter
Using ALL(Product) ranks across every product unless the category filter is reapplied.
Ranking a column instead of a measure
Most business ranking should be based on a measure so it respects filters, dates and slicers.
Forgetting blanks
Blank items can appear with misleading ranks unless the measure returns blank deliberately.
Validation checklist
- Rank 1 is the expected item in a single category.
- Changing the category changes the ranking set.
- Date, customer and site slicers affect the base measure and rank as intended.
- Tie behaviour is agreed.
- Blank values do not create misleading bottom ranks.
Need the report to reconcile with ERP?
Digital Adaption helps UK SMEs rebuild trusted Power BI, ERP and operational reporting after migrations, ownership gaps and model drift.
FAQ
Use ALLEXCEPT when the category context must be preserved regardless of visual selection. Use ALLSELECTED when user selections should define the ranking universe.
The table passed to RANKX is probably still filtered to the current row. Remove the item filter while keeping the category filter.