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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JOKA
Advocate I
Advocate I

RANKX in Card

Hi!

I´m having trouble with a Rankx measure when displaying on a card. 

I have a rank of sales by user: Rank = RANKX(ALL(TABLE[User]);[Sales];;DESC;Dense)

If I use Rank in a table all users have the correct rank value, it works fine.

okrank.png

 

 

 

 

 

 

 

 

 

I also need to have a card, so that when I select the user from the table the card shows me the rank value. The problem is that in a couple of cases it shows me the wrong value. For example in this case, I click on row #3 in the table but the card shows me #4:

rank.png

 

 

 

 

 

Does anybody know what am I doing wrong?

1 ACCEPTED SOLUTION

In case there´s someone else with the same problem. There´s something wrong when you try to rank decimals. To solve the problem we had to change the measure in the overall rank to integer.


View solution in original post

21 REPLIES 21
karma123
Helper II
Helper II

Any update on this? Still having the same issue

Anonymous
Not applicable

Hello!

I found this in other forum and it works!

= RANKX(ALL(Products), SUMX(RELATEDTABLE(InternetSales), [SalesAmount]))

Thank you!

karma123
Helper II
Helper II

I have identified that the rank is missing Rank number 5 in the card visual it just goes straight to 6

In the table visual the rank is working fine, it is only in the Card visual where the rank is incorrect.

I'm sorry, i don't have any more ideas. The formula that i provided worked for my data.

 

I did spend several weeks at the time trying to resolve it and tried so many things that i can't actually remember what the thing was that made it work in the end. It was a year ago! 🙂 

 

 

oh ok thanks anyway for your help! 

 

I will keep trying

karma123
Helper II
Helper II

karma123_1-1670266086962.png

 

 

On the Card visual I have filtered for a client but I am getting the rank as 9 but on the table it is showing the rank as 8.

 

The formulae used is rankx(all(client),value(gross premium)

 

I have tried using Allselected in the formula but this just gives a rank as 1

 

Would really appreciate any solutions,

 

Thanks, 

 

Hi, 

 

It's been a while since i looked at this, but i believe we found a formula that we were happy to use. 

 

Rank = RANKX(ALL(Your dataset[what you want to rank]),Your dataset [measure/metric you want to rank by)
 
Let me know if this works for you?
 
Thanks
 
Kathryn

Hi, 

 

thanks for this, I tried this 

Rank = RANKX(ALL(Your dataset[what you want to rank]),Your dataset [measure/metric you want to rank by) but am still getting an error where the rank in the card is giving me a rank higher than it should be 
 
thanks, 
 
 

There was also something about multiplying by 100,000 but i can't for the life of me remember why we had to do that. 
karma123
Helper II
Helper II

Did this get resolved? I am also having the same issue

kathrynhmoss
Helper I
Helper I

Hi Guys, 

 

I am having a very similar problem but the suggestions above do not seem to have resolved my issue. I too am working with decimal numbers. I cannot change the data type for this dataset as the decimal numbers is integral to the dataset and the workings out. 

 

Here's the issue that i'm having.... (picture below)

The table with rankings on the left is correct - the ranking issue is the one that has a red ring around it (the table is being filtered by a subject. 

 

The card on the right (circled) should say 14, but it is saying 15. It is reading the same rank formula as the table, it is also being filtered by a subject, but it has an additional filter on it for Institution. 

 

This only happens for a couple of subjects - the vast majority of the other subjects have the correct ranking in the card.

 

Any ideas?? Would be most grateful if anyone has any! 

 

Thanks! 

 

Rank issue.JPG

 

 

 

Anonymous
Not applicable

Hi @JOKA ,

 

You can create the following two measures to display rank:

 

Normal Rank = 

RANKX(ALLSELECTED(TABLE[User]),CALCULATE(SUM([Sales]),,DESC,Dense)

 

Overall Rank = RANKX(ALL(TABLE[User]),CALCULATE(SUM([Sales]),,DESC,Dense)

 

Then try putting the Normal Rank in a table visual and then Overall Rank Measure in the Card Visualization. Now if you will slice and click on, let's say Rank 3 record in the table, the card will show Rank 3 as well.

 

Hope this helps. 

 

**Please mark this as answer if it solves your problem. Thanks

Yes, it is working. Great.

Thank you.

Thank you @Anonymous. I tried this but it doesn´t work. I believe there´s something else. I tried the same solution that you gave me in another dataset and it works perfect, but in this one it doesn´t. The only difference that I can see is that there are negative values in the Sales mesasure in this one, could that be the problem?

Anonymous
Not applicable

I don't think it is because of the negative sales figures. To test this quickly is to wrap the ABS function on Sales and see if this works as expected. If not, any chance you could attached the PBIX file in here with removing the sensitive bits. I believe it may be because of the filters/slicers that are passing through which can be viewed by clicking on Edit Interactions and hovering on the card visual.

 

Thanks

Ashish

Hi @Anonymous ! I still can´t solve this problem. I tried your recommendation in another dataset and it works perfect. But in this one it doesn´t.

I am sharing the link to the .pbix file: https://drive.google.com/file/d/155T88JrH1mciasgj3Dcx_7ZHwZwxV842/view?usp=sharing

Hope you could help me. I would be very grateful!

 

In case there´s someone else with the same problem. There´s something wrong when you try to rank decimals. To solve the problem we had to change the measure in the overall rank to integer.


Hours. Tons of hours. That's what it took to finally find this post. And guess what - after three years, MSFT still hasn't fixed this comical issue. Had to set Sales $ column in "advanced editor" to be an integer like you said (Int64.Type), and magically my RankX calc that was ranking brands would finally give the right rank at all times when isolated to a single brand in a card. It's like the BI and Excel teams have never talked.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors