Rankx and sort

[maurcoll]

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

Any ideas in how to deal with this?

Region	Store	Sales	Rank
North	A	10%	1
North	B	20%	2
South	A	30%	3

[Sahir_Maharaj · Accepted solution]

Hello @maurcoll,

Can you please try this DAX:

Store Ranking % sales (ASC) =
RANKX(
    FILTER(ALL(Region[Store]), [Measure for % Sales] > 0),
    [% Sales], , ASC, Dense
)

[maurcoll]

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