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
Vulgrim
New Member

RANKX Returning 1 or failing

I am using TOP N to display top customers that submitted tickets. Since some of the results match, I am getting more than the TOP 5 I am looking for. I found that RANKX is what I need to resolve this issue, but I cannot get it to work.

 

I have a table called 'CustomerCount' which has two columns, 'Customer' and 'Count'. 'Count' shows the number of tickets that customer put in for the month. I right click on the 'CustomerCount' table and select 'New Measure' and enter the following:


CustRANK = Rankx(ALL('CustomerCount'), Calculate (SUM('CustomerCount'[Count])))

 

It vailidates without error. Good, right? Next I go back to my report and just drag and drop the new measure onto the report, but it just display '1', no customers ranked. I KNOW I am misunderstanding how this should work. Can anyone PLEASE HELP!

5 REPLIES 5
Thejeswar
Resident Rockstar
Resident Rockstar

Hi @Vulgrim ,

The Measure that you are using should give the right RANK. It gives me the right RANK with the data I used. Not sure why in your case it is not working. Is it that you are using in your category some column which is used for joining with other tables?

 

You can also split the measure into two as shown below and try using it.

 

First create a measure that returns sum of customercount as shown below

 

measure1 = SUM('Table'[Count])

 

Then build a separate measure for RANK as shown below

 

CustRank = RANKX(ALL('Table'), 'Table'[measure1],,DESC)

 

 

 Regards,

 

I tried the two measures above, and put them on my report. This is what I got 

Vulgrim_0-1646589172029.png

The table I am using is not linked to any other tables. 

Hi,

If you use the measures separately, that is how they give you the values.

Measure repond to the Filter Contexts that they get from Row values. 

 

So use the measure with a row value and that should give you the right Rank.

For ex, below is the screenshot

Thejeswar_0-1646589935555.png

 

In the above ss, the first table shows RANK as 1 as in the one that you show. When I use the same measure in a table with a Brand Column, the RANK is displayed

Hope this clarifies your doubt!!

 

If this solves your problem, mark it as solution!! Appreciate a Kudo!!

That was very helpful! Thank you! I am seeing companies that have the same rank, is that normal? I assume they get the same rank because say Company 'A' enter 5 tickets and company 'B' entered 5 tickets, they would rank the same? 

 

Vulgrim_1-1646590460866.png

 

 

Hi @Vulgrim ,

Yes this can be expected.

 

When the value based on which your Rank measure works is same, the RANKs will be same.

When the next value come into picture, the RANK function will either SKIP rank or dense rank based on your input.

SKIP ranking is the default behavior.

For example, if there are two items with same rank, let's say 2 is the rank, then the next rank would be 4 as there are two items with Rank 2

 

Regards,

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