Need help with RANKX by Customer

[Shelley]

I'm trying to Rank Customer IDs by the Total Hours Delivered to them. Here's the formula I'm trying:

Rank Hours = RANKX(ALL('CustomerTable'[CustomerID]), 'ServiceTicketTable'[Total Hours Delivered])

This results in all customers with a rank of 1.

1 Cust A 8 hours

1 Cust B .5 hours

1 Cust C 3 hours

What the heck am I missing?

[Ashish_Mathur]

Hi,

Your formula is correct.  Share the link from where i can download your PBI file.

[Shelley · Accepted solution]

Thanks for your confirmation on my formula.

I spent hours trying to get this to work with the relationship between customer and hours through a Link Table. I found this awesome post and the line that says "... with the filtering context of these two tables being related to each other (there’s a relationship between Projects and Budget thru ProjIDs)." Aha! I connected this customer list directly to the hours table, with the RANKX formula in the customer table and it worked! Then, I thought about the Link table and when I put the RANKX formula in the Link Table it also worked because the Link Table has a direct connection to the Hours table! Both formulas work even using an inactive relationship as long as the customer table is directly linked to the hours table.

The only thing I changed in my formula from above was adding descending order (the higher the hours, the better the rank):

Rank Hours = RANKX(ALL('CustomerTable'[CustomerID]), 'ServiceTicketTable'[Total Hours Delivered] , , DESC )

[Shelley]

I wish I could, I really need and appreciate the help, but my company doesn't allow me to share data. It would take me a long time to mock it up with fake data. Unless you have any easy way to do this?

[Rfranca]

hi,  @Shelley

Is 'ServiceTicketTable' [Total Hours Delivered] formatted as numeric?

[Shelley]

It was format "General" I changed it to "Decimal Number", but nothing changed in the results.

[RolandsP]

Formula should be:
Rank Hours = RANKX('CustomerTable'[CustomerID], 'ServiceTicketTable'[Total Hours Delivered])

[Shelley]

Thanks for the help. When I try this, I receive an error that says "A single value for Customer ID cannot be determined." This is odd because there are no duplicate Customer IDs in this table.

[RolandsP]

Oh, it is actually even more simple:

Rank Hours = RANKX('CustomerTable', 'ServiceTicketTable'[Total Hours Delivered])

This should work