Reply
avatar user
Anonymous
Not applicable

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
100801203/05/16
100801406/05/16
100806201/04/16
100806101/05/15
100806101/05/16
100806403/04/16
100806308/05/16
100807402/05/15

 

crew employee number sales amount sales rank
   
10080172
100806111
10080743
1 ACCEPTED SOLUTION

@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!




View solution in original post

6 REPLIES 6
avatar user
Anonymous
Not applicable

Ended up with this. It worked but still no idea why?

 

sales rank = rankx(allselected(sales[employee number]), calculate(sum(sales[sales amount])))

@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!




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..

@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!




avatar user
Anonymous
Not applicable

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

avatar user
Anonymous
Not applicable

@KHorseman Absolutely spot on! The link is also very useful. An eye opening lesson for Dax. Thanks.

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

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.

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)