cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Regular Visitor

Re: issue with RANKX

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

Highlighted
Microsoft
Microsoft

Re: issue with RANKX

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
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors