topic Re: RANKX doesnt work as It should in DAX Commands and Tips

RANKX doesnt work as It should

lucie_raboch — Sat, 17 May 2025 05:12:42 GMT

Hi all,

My goal is to show top 20 master by metric Sales and the rest will be grouped like 'Others'.

I'm working with RANKX to be able rank values, but it is showing 11111 insted of 1,2,3,4,5.

Can you tell me what I'm doing wrong please?

Result (excel version)

This is my Power BI file: https://drive.google.com/file/d/1HagYTmRAdT6SFQd1HXw7CM10jwrFFOcy/view?usp=sharing

Thank you for your help on this issue.

Lucie

Re: RANKX doesnt work as It should

DataNinja777 — Sat, 17 May 2025 07:10:13 GMT

Hi lucie_raboch ,

To create a proper ranking for your "Master" by Sales in Power BI and group everything outside the Top 20 as "Others", you need to use RANKX in a way that evaluates all values in the dataset regardless of what’s currently being filtered by the visual. The reason you were getting only 1s is likely because the RANKX function was evaluating over a single visible row context due to the table visual.

You can fix it by first calculating the ranking using the ALL function inside RANKX to remove any filters on the "Master" column. Here's the corrected measure:

Ranking = RANKX( ALL('YourTable'[Master]), CALCULATE(SUM('YourTable'[Sales])), , DESC, DENSE )

To create a grouping for Top 20 and "Others", define a new column or measure like this:

Master Group = VAR CurrentRank = RANKX( ALL('YourTable'[Master]), CALCULATE(SUM('YourTable'[Sales])), , DESC, DENSE ) RETURN IF(CurrentRank <= 20, SELECTEDVALUE('YourTable'[Master]), "Others" )

Then you can use Master Group as the row field in your matrix and sum of Sales as the value. This will show the Top 20 masters individually and group everything else under "Others".

If you want to make sure “Others” always shows up at the bottom, create a sort order measure like this:

Sort Order = VAR CurrentRank = RANKX( ALL('YourTable'[Master]), CALCULATE(SUM('YourTable'[Sales])), , DESC, DENSE ) RETURN IF(CurrentRank <= 20, CurrentRank, 999)

Use this to sort the "Master Group" column. This ensures that "Others" comes last in your visual.

Best regards,

Re: RANKX doesnt work as It should

lucie_raboch — Sat, 17 May 2025 10:32:28 GMT

Thank you for your answer, but your solution doesnt work, still getting 1,1,1,1,1

Re: RANKX doesnt work as It should

techies — Sat, 17 May 2025 20:02:23 GMT

Hi lucie_raboch for ranking you can use this

Rank by Sales =

RANKX(

ALL('Sheet9'[Master]),

[Sales amount],

, DESC,

DENSE

)

where [sales amount] is a measure as this--

Sales amount = SUM('Sheet9'[Sales])

Re: RANKX doesnt work as It should

techies — Sat, 17 May 2025 20:08:24 GMT

Hi lucie_raboch please check this measure for grouping

Master Group =

VAR CurrentRank =

RANKX(

ALL('Sheet9'[Master]),

[Sales amount],

, DESC,

DENSE

)

RETURN

IF(CurrentRank <= 20, SELECTEDVALUE('Sheet9'[Master]), "Others")

Re: RANKX doesnt work as It should

lucie_raboch — Sun, 18 May 2025 06:39:05 GMT

Hi,

Ranking is working now, but I need to remove Master from the table to see only top 20 rows and Others and if I remove Master Column then Master Group is BLANK.

Also how can I sort according to the Rank?

Re: RANKX doesnt work as It should

techies — Sun, 18 May 2025 08:08:49 GMT

Hi lucie_raboch does creating a calculated table and using its columns in a table visual work for you?

like this

TopMasterTable =

ADDCOLUMNS(

SUMMARIZE('Sheet9', 'Sheet9'[Master]),

"Sales", [Sales amount],

"Sales Rank", RANKX(

ALL('Sheet9'[Master]),

[Sales amount],

DESC,

DENSE

"MasterGroup",

VAR CurrentRank =

RANKX(

ALL('Sheet9'[Master]),

[Sales amount],

DESC,

DENSE

)

RETURN

IF(CurrentRank <= 20, 'Sheet9'[Master], "Others")

)

Re: RANKX doesnt work as It should

lucie_raboch — Sun, 18 May 2025 10:21:55 GMT

This is my PowerBI file: https://drive.google.com/file/d/1HagYTmRAdT6SFQd1HXw7CM10jwrFFOcy/view?usp=sharing

Can you create this table in this file and share back please?

Because it's not working properly and I don't know why.

Re: RANKX doesnt work as It should

techies — Sun, 18 May 2025 15:40:53 GMT

Hi lucie_raboch based on my understanding on the data model, here is the calculated table , please test it if it works, not able to upload pbix file

TopMasterTable =

VAR BaseTable =

SUMMARIZE(

'Master_Unique',

'Master_Unique'[Master]

)

VAR SalesWithValues =

ADDCOLUMNS(

BaseTable,

"Sales",

CALCULATE(

SUM('FactVarMetrics'[Value]),

'DimSce'[Scenario] = "Budget",

'DimSce'[Metric] = "Gross",

TREATAS(VALUES('Master_Unique'[Master]), 'DimProducts'[Master])

)

VAR WithRank =

ADDCOLUMNS(

SalesWithValues,

"Sales Rank",

RANKX(

SalesWithValues,

[Sales],

DESC,

DENSE

)

RETURN

ADDCOLUMNS(

WithRank,

"MasterGroup",

IF([Sales Rank] <= 20, [Master], "Others")

)

Re: RANKX doesnt work as It should

lucie_raboch — Mon, 19 May 2025 07:54:03 GMT

Wow, yes, It is working now! Thank you for this solution.