Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
mp390988
Helper V
Helper V

Understanding the RankX

I noticed when I replace the table ALL(DimProduct) with ALL('DimProduct'[Colour]) inside my RankX function it yields different results. I don't quite understand. I know the ranking results are incorrect with ALL('DimProduct']) and correct with ALL('DimProduct'[Colour]) but my understanding is that ALL('DimProduct'[Colour]) removes the filter context from the visual (i.e. the colour) but I thought ALL(DimProduct) removes all filters inlcuding colour so I would have expected them to give same results.

 
RankX with ALL(DimProduct)
 
mp390988_0-1742512416583.png

 

 

RankX with ALL('DimProduct'[Colour])

 

mp390988_1-1742512982705.png

 

 

I added a calculated column to my DimProducts table to calculate the SALES AMOUNT.
Here is the link to the excel file containing all the records from DimProducts with SALES AMOUNT - here 

 

mp390988_2-1742513455619.png

 

I don't know how the rank with ALL(DimProducts) is ranking colour BLACK with SALES AMOUNT = 8,838,411.96 as 1 and then colour BLUE with SALES AMOUNT = 2,279,096.28 as 1 also. Doesn't make sense.

 

 

8 REPLIES 8
v-dineshya
Community Support
Community Support

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

Hi @mp390988 ,

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

Hi @mp390988 ,

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

Hi @mp390988 ,

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

Deku
Super User
Super User

RANKX iterates the table you provide, calculates the expression as a extra column, and ranks with that new column

 

With all(DimProduct) you are ranking on the entire table. With all(DimProduct[colour]) you are ranking on only a list of the distinct colours, 


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Ok so I guess the question I have is how does the ranking on the whole table work ? Because I don't get the same ranks when I try to work it out manually.

Try adding each field from dimproduct into a table with sales amount. I assume you'll see a combination where blue, black, yellow, red and silver all have the same top amount.


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Will that not be the excel file I added as a link in my original post? That excel file is the whole of DimProduct table with a calculated sales amount column right at the end. 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.