Forum Discussion
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 |
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.
6 Replies
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.