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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.