Understanding the RankX

HI mp390988 ,

Thank you for reaching out to the Microsoft Community Forum.

Key Differences:

ALL('DimProduct'[Color]):

1.This removes only the filter on the [Color] column, meaning the ranking is done at the Color level.

2.Other filters from the DimProduct table (e.g., size, product, etc.) remain intact if applied in the visual.

3.This is what you want if you're ranking aggregated Sales Amount per Color, and it gives the correct ranks one rank per color.

ALL(DimProduct):

1.This removes all filters on the DimProduct table, including Color, Size, ProductKey, etc.

2.As a result, you're ranking individual products (rows in the table), not aggregated color values.

3.When the visual is grouped by Color, but you're ranking by a row-level context, it causes multiple products under the same color to get ranked leading to odd behavior like multiple ranks of 1.

Note: You're calculating rank across all products, not across distinct colors. So even though the visual groups by Color, the rank measure is still working row-by-row behind the scenes, which is misleading in an aggregated visual.

That's why:

Black with $8.8M is ranked 1 (correct as a color total),

But Blue, Red, Silver, etc., also get rank 1 — because the RANKX is being evaluated at the product level within those colors.

Rank Sales Amount =
RANKX(
ALL('DimProduct'[Color]),
[Sales Amount],
,
,
DENSE
)

Note: This ensures you're ranking over the distinct list of colors, using their aggregated Sales Amount, which aligns with how your visual is grouped.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you