Forum Discussion
Rankx Issue: Resulting in Incorrect Figures
I am trying to create a ranking measure to show customer code is ranked from highest to lowest sales where there is a growth is sales as compared to last year. I have used following measure-
VAR FltrTbl = FILTER(ALLSELECTED( DimCustomer[Customer Code]), [SalesGrowth%]>0)
VAR RANKING = RANKX ( FltrTbl, [Total Sales], , , Dense )
RETURN RANKINGHowever, the result I'm getting is not only including degrowth customers but also repeating the rank when theirs clearly no tie in total sales. I'm sure filter is working fine as I tested the results of filter.
RANKX?
Output-
| Customer Code | Total Sales | SalesGrowth% |
| 1102447 | 1,14,97,25,098.61 | -6% |
| 1102569 | 64,91,05,763.98 | 34% |
| 1102957 | 33,78,84,256.88 | -10% |
| 1119845 | 15,37,06,068.66 | -21% |
| 1529872 | 11,80,84,481.60 | 100% |
| 1484754 | 10,86,79,942.96 | 17% |
| 1219568 | 11,15,19,404.94 | 1% |
| 1354515 | 12,05,17,619.38 | -11% |
| 1102842 | 11,03,74,704.53 | 52% |
Hi, peterpan Thanks for asking question.
Seems like you have some issues with DAX. FltrTbl contains only customers with positive growth.
However, when the visual is on a customer with negative growth, RANKX still takes that customer’s [Total Sales] and ranks it against the positive-growth set , so you still get a rank instead of BLANK. That’s why rows like SalesGrowth% = -6% show a rank.
You can try this dax, at first it will filter all positive growth items then do the ranking
Rank Among Positives := IF( [SalesGrowth%] > 0, RANKX( FILTER( ALLSELECTED(DimCustomer[Customer Code]), [SalesGrowth%] > 0 ), [Total Sales], , DESC, DENSE ) )Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!
2 Replies
Hi, peterpan Thanks for asking question.
Seems like you have some issues with DAX. FltrTbl contains only customers with positive growth.
However, when the visual is on a customer with negative growth, RANKX still takes that customer’s [Total Sales] and ranks it against the positive-growth set , so you still get a rank instead of BLANK. That’s why rows like SalesGrowth% = -6% show a rank.
You can try this dax, at first it will filter all positive growth items then do the ranking
Rank Among Positives := IF( [SalesGrowth%] > 0, RANKX( FILTER( ALLSELECTED(DimCustomer[Customer Code]), [SalesGrowth%] > 0 ), [Total Sales], , DESC, DENSE ) )Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!
Hi,
Try this measure
=if([SalesGrowth%]<0,blank(),rank(dense,filter(all(dimcustomer[Customer code]),[SalesGrowth%]>0),orderby([Total sales],desc)))
Hope this helps.
