Forum Discussion
Solved
Rankx and sort
Good Afternoon
I am using the following formula to rank store sales, by store and region.
Store Ranking % sales =
RANKX ( ALL(Region[Store]),
[% Sales], , ASC, Dense)
Store Ranking % sales =
RANKX ( ALL(Region[Store]),
[% Sales], , Desc , Dense)
I have a table of results for regions which i then filter down to stores within the region.
The issue i have is that when i use asc, the ranking starts from 2, when i use desc the ranking starts from 1.
The reason i have both calculations is that for some types of sales i want to use ascending instead of descending
I have a table of results for regions which i then filter down to stores within the region.
The issue i have is that when i use asc, the ranking starts from 2, when i use desc the ranking starts from 1.
The reason i have both calculations is that for some types of sales i want to use ascending instead of descending
Any ideas in how to deal with this?
| Region | Store | Sales | Rank |
| North | A | 10% | 1 |
| North | B | 20% | 2 |
| South | A | 30% | 3 |
Hello maurcoll,
Can you please try this DAX:
Store Ranking % sales (ASC) = RANKX( FILTER(ALL(Region[Store]), [Measure for % Sales] > 0), [% Sales], , ASC, Dense )
2 Replies
Hello maurcoll,
Can you please try this DAX:
Store Ranking % sales (ASC) = RANKX( FILTER(ALL(Region[Store]), [Measure for % Sales] > 0), [% Sales], , ASC, Dense )Good morning,
That is working for the majoirty it is only when i have a store with 0 sales that it is giving this store and the next store a ranking of 1, when all stores have sales it is working perfectly. Ideally i would like the 0% to be 1 and then the next store to show as 2. I have tried changing from skip to dense but this does not seem to make any difference. Thank you for your help
Store % Sales Ranking A 1% 1 B 0% 1 C 1.5% 2
