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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
haosmark2
Regular Visitor

issue with RANKX

I have a strange issue, that I can't seem to replicate with basic dummy data. My RANKX refuses to rank customers in my actual workbook, it returns 1s or 2s for all. Basically I have account attributes table (lookup) with one row per account, and I have financials table (data), where there are multiple transactions for each customer; both tables are related based on the customer name. My DAX is as follows:

=RANKX(
  ALL( tAccountAttributes[CUSTOMER] ),
  [(CY) SALES]
)

(CY) SALES just a SUM of revenue

=CALCULATE(
  SUM(tFinancials[REVENUE])
)

 

however ranking is not working.

 

Here's what I see as the values for (CY) SALES and for RANK columns:

 

This is the view of the tables and their connection:

 

 

The strange thing is that when I built two basic tables with just a few rows of data, and connected them by ficticious customer name, rank worked without a problem. The problem workbook does have time slicers on it, but since (CY) SALES calculate without an issue, I don't understand why rank doesn't...

2 REPLIES 2
haosmark2
Regular Visitor

I tried replacing ALL, with ALLSELECTED and it works now... My pivot table did have director, customer name, customer category, service leader, and service manager as the rows, so somehow that was throwing off RANKX? I'm also confused on how DAX knows what I'm ranking now. I changed the query to this:

 

=RANKX(
  ALLSELECTED( tAccountAttributes ),
  [(CY) SALES]
)

however nowhere in the code do I tell it that I want to rank customer names and not something else. In other words if I have something like this:

 

DIRECTOR      CUSTOMER    SALES

FrankA5
FrankA1
FrankB10
FrankB1
FrankB2
JoeC15
JoeC5

somehow DAX knows to rank C as 1, B as 2, and A as 3; not all Frank's accounts as 1, and Joe's as 2. Yes once again, I can't replicate it with basic tables. If I use the actual example above I get the following result

uWIb9ZA

 

With the actual data (changed the data) I would see this, which is accurate ranking. Go figure...

67rkGCm

Hi @haosmark2 

 

It depends on the row context.I would suggest you have a look at below articles to understand RANKX Function.If you need further help,please share some data sample and expected output. You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Use of RANKX in POWER BI measures
How to use RANKX in DAX (Part 2 of 3 – Calculated Measures)
How to use RANKX in DAX (Part 1 of 3 – Calculated Columns)

 

How to Get Your Question Answered Quickly

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.