Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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...
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
Frank | A | 5 |
Frank | A | 1 |
Frank | B | 10 |
Frank | B | 1 |
Frank | B | 2 |
Joe | C | 15 |
Joe | C | 5 |
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
With the actual data (changed the data) I would see this, which is accurate ranking. Go figure...
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |