- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Rankx all return 1
First time using the rankx. Trying to use the rankx function to return employee sales rank but somehow all return 1.
table 1 is the simple sales table. hoping to return result like table 2.
My sales rank = rankx(allselected(sales[employee number]), sum(sales[sales amount]))
Can anyone see what is wrong here? thanks.
employee number | sales amount | sales date |
100801 | 1 | 02/04/16 |
100801 | 2 | 03/05/16 |
100801 | 4 | 06/05/16 |
100806 | 2 | 01/04/16 |
100806 | 1 | 01/05/15 |
100806 | 1 | 01/05/16 |
100806 | 4 | 03/04/16 |
100806 | 3 | 08/05/16 |
100807 | 4 | 02/05/15 |
crew employee number | sales amount | sales rank |
100801 | 7 | 2 |
100806 | 11 | 1 |
100807 | 4 | 3 |
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Anonymous CALCULATE() converts a row context to a filter context. Without it the row context at each step in the iteration doesn't work properly with the filter context coming from the ALLSELECTED(). You get an implicit CALCULATE when you reference an existing measure like this:
Total Sales = SUM(sales[sales amount])
Sales Rank = RANKX (ALLSELECTED(sales[employee number]), [Total Sales])
...but when you spell out the expression instead of using a measure you also have to spell out the invisible CALCULATE yourself. The above formula is identical to your
Sales Rank = RANKX( ALLSELECTED(sales[employee number]), CALCULATE( SUM(sales[sales amount]))
This article might help to explain it.
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ended up with this. It worked but still no idea why?
sales rank = rankx(allselected(sales[employee number]), calculate(sum(sales[sales amount])))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Anonymous CALCULATE() converts a row context to a filter context. Without it the row context at each step in the iteration doesn't work properly with the filter context coming from the ALLSELECTED(). You get an implicit CALCULATE when you reference an existing measure like this:
Total Sales = SUM(sales[sales amount])
Sales Rank = RANKX (ALLSELECTED(sales[employee number]), [Total Sales])
...but when you spell out the expression instead of using a measure you also have to spell out the invisible CALCULATE yourself. The above formula is identical to your
Sales Rank = RANKX( ALLSELECTED(sales[employee number]), CALCULATE( SUM(sales[sales amount]))
This article might help to explain it.
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I am trying the same stuff but instead of SUM I am trying to get Rank by order of the date but it returns all 1s
Rank = RANKX(ALLSELECTED(Query1[UserName]),CALCULATE(MAX(Query1[StartDate])))
Would appreciate any wisdom here..
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@PowerBIArtistdid you enter this as a measure or a column?
OK I did some testing with my own data. That ALLSELECTED thing was a specific case for the other person's presentation of data. If you're just going to drop a big list of people and rank them by date, just use ALL(Query1) instead of ALLSELCTED(Query1[UserName]). ALLSELECTED forces each user to be their own set of data to be ranked individually against themselves instead of everyone else, so among all the users that are that one user, they are all rank 1.
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I still do not understand how does it work. I want to rank project name base on project value.
I have rankx=rankx(All(Merge1[Project Name]),sum(Merge1[TOtal project construction Value]))
but rank returns 1
Please help
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@KHorseman Absolutely spot on! The link is also very useful. An eye opening lesson for Dax. Thanks.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
05-22-2024 01:44 AM | |||
Anonymous
| 01-18-2018 09:38 AM | ||
04-16-2024 10:05 AM | |||
03-07-2023 03:48 PM | |||
08-03-2018 05:49 PM |
User | Count |
---|---|
104 | |
75 | |
43 | |
39 | |
32 |
User | Count |
---|---|
168 | |
90 | |
65 | |
46 | |
44 |