RANKX - breaking ties

[JK-1]

Hi, how can I make these 3 and 4 (as coloured in table example below), when the store priority is the same so it ties. Would I have to combine a general choice, such as Store Number, to allow an extra split to give out the full ranking number, I'd need to see 4 overall. I can't quite get to grips with the advice on Dense - and what may need to be combined to re-write the measure.

Thanks,

Rank Priority = RANKX(FILTER('Table 1','Table 1'[Postcode Area]=EARLIER('Table 1'[Postcode Area])),'Table 1'[Store Priority],,ASC)

[Table 1]
Store Number	Postcode Area	Store Priority		Rank
15	SR	1		1
27	SR	2		2
33	SR	3		3
2	SR	3		3

[v-yangliu-msft · Accepted solution]

Thanks for the reply from DataInsights , please allow me to add some more information:
Hi  @JK-1 ,

Here are the steps you can follow：

1. Power Query -- Add Column – Index Column – From 1.

2. Create calculated column.

Rand = RAND()/100

Rank_Number =
RANKX(
    FILTER(ALL('Table'),
    'Table'[Store Number]=EARLIER('Table'[Store Number])&&
    'Table'[Postcode Area]=EARLIER('Table'[Postcode Area])),
    [Store Priority] + [Index]/1000000,,ASC,Dense)

3. Result:

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-yangliu-msft · Accepted solution]

Thanks for the reply from DataInsights , please allow me to add some more information:
Hi  @JK-1 ,

Here are the steps you can follow：

1. Power Query -- Add Column – Index Column – From 1.

2. Create calculated column.

Rand = RAND()/100

Rank_Number =
RANKX(
    FILTER(ALL('Table'),
    'Table'[Store Number]=EARLIER('Table'[Store Number])&&
    'Table'[Postcode Area]=EARLIER('Table'[Postcode Area])),
    [Store Priority] + [Index]/1000000,,ASC,Dense)

3. Result:

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[JK-1]

@DataInsights 

Thank you for that. Works perfectly.

Slightly different, but related ..where there might be various floors -called Tenant in column below- is there a way to adapt it to Rank (against the exact Store Number) and for any tiebreaker again give each an individual ranking based on associated Store Priority. 

Store Number	Tenant	Postcode Area	Store Priority		Rank
2	B	SR	1		1
2	C	SR	1		2
2	A	SR	3		3
15	A	SR	1		1
15	B	SR	3		2
27	A	SR	2		1
33	A	SR	3		1

[DataInsights · Accepted solution]

@JK-1,

I added Tenant to the ALL and ORDERBY arguments, and added a PARTITIONBY argument for Store Number. The partition resets the rank numbering for each Store Number.

Rank Priority = 
RANK (
    DENSE,
    ALL ( 'Table 2'[Store Priority], 'Table 2'[Store Number], 'Table 2'[Tenant] ),
    ORDERBY ( 'Table 2'[Store Priority], ASC, 'Table 2'[Tenant], ASC, 'Table 2'[Store Number], DESC ),
    PARTITIONBY ( 'Table 2'[Store Number] )
)

[DataInsights]

@JK-1,

Try this calculated column using RANK.

Rank Priority = 
RANK (
    DENSE,
    ALL ( 'Table 1'[Store Priority], 'Table 1'[Store Number] ),
    ORDERBY ( 'Table 1'[Store Priority], ASC, 'Table 1'[Store Number], DESC )
)