RANKX Returning 1 or failing

[Anonymous]

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!

[Thejeswar]

Hi @Anonymous ,

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,

[Anonymous]

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

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

[Thejeswar]

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

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!!

[Anonymous]

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? 

[Thejeswar]

Hi @Anonymous ,

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,