Power BI RANKX Within Category

Guides — Power BI Rankx Within Category

This guide gives UK SME teams a practical implementation path: start with the business question, check the data or workflow, build the smallest useful version, then document what has changed.

Book a data risk call View all services Power BI guide Home / Guides / Power BI

Rank products, customers or sites inside each Power BI category with RANKX while keeping filter context under control.

2026-06-16 9 min read min read Digital Adaption On this page

1. Confirm ranking grain
2. Create the RANKX measure
3. Handle ties and blanks

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.

Practical answer

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.

1 Confirm 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.

2 Create 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
    )
)

3 Handle 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.

1. Filter to one category and manually sort by the base measure.
2. Check tied values and confirm dense or skip behaviour.
3. Test what happens when a slicer removes some products.
4. 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.

Review reporting trust

FAQ

Should I use ALLEXCEPT or ALLSELECTED?

Use ALLEXCEPT when the category context must be preserved regardless of visual selection. Use ALLSELECTED when user selections should define the ranking universe.

Why does every row show rank 1?

The table passed to RANKX is probably still filtered to the current row. Remove the item filter while keeping the category filter.

Start with a 30-minute data risk call Start with a 30-minute data risk call