Forum Discussion
Helper IIRANKX - breaking ties
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 |
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()/100Rank_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,
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] ) )
4 Replies
- DataInsights
Super User
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 ) ) 
JK-1Thank 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
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] ) )
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()/100Rank_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