Mastering DAX Ranking: Static vs. Dynamic Patterns

RANKX combined with ALL, ALLEXCEPT, and ISINSCOPE gives you static global ranks, ranks within a category, and dynamic ranks that respond when users drill into a hierarchy.

Ranks every product against all products, ignoring visual row filters on Product:

Global Product Rank =
RANKX(
    ALL ( Product[ProductName] ),
    [Total Sales],
    ,
    DESC,
    Dense
)

• ALL ( Product[ProductName] ) removes filters so each row sees the full product list.
• The rank number stays the same whether you slice by Region or not.

Category Product Rank =
RANKX(
    ALLEXCEPT ( Product, Product[Category] ),
    [Total Sales],
    ,
    DESC,
    Dense
)

Electronics products rank 1, 2, 3… only among other Electronics; Furniture has its own 1, 2, 3 sequence.

Dynamic Product Rank =
IF(
    ISINSCOPE ( Product[ProductName] ),
    [Category Product Rank],
    [Global Product Rank]
)

• At Category level only → shows global rank across all products.
• When ProductName is in scope (drill or product on axis) → switches to category rank.
• One measure adapts to hierarchy depth without duplicate visuals.

Top 3 Products =
IF(
    [Global Product Rank] <= 3,
    [Total Sales],
    BLANK()
)

Use in a bar chart with Product on axis—only the top three bars appear. Combine with a Category slicer to see top 3 within a segment by swapping [Global Product Rank] for [Category Product Rank] in the condition.