cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors