Group ranking

[Anonymous]

Hi all, 

I am facing ranking issue problem, Please send me solution for my query

I have 3 columns in one table (CountryName , label, Riskrating), By using Riskrating i want to display rank (particular country) on new column. 

[PattemManohar]

@Anonymous  Try using "DENSE" option in your RankX formula then it will assign same rank for same riskrating values and will not skip the next rank number. Hope this is what you are looking for.  If not, then please post the sample data and expected output in copiable format.

[Anonymous]

Hi @PattemManohar 

please send me Dax funtion by using RankX.

I have data like this,

CountryName              Label                          Riskrating    
Afghanistan Brand       Management               15                

Afghanistan Brand       Business continuity      20                

Afghanistan Brand       Management1             20                

Afghanistan Brand       Management2             12                

Afghanistan Brand       Management3             12                

Albania                        management                20                

Albania                        management1              20                

Albania                        management 2             20                

Bangladesh                 Management                56                
Bangladesh                 Management1              56       

i want rank like this

CountryName              Label                          Riskrating     rank
Afghanistan Brand       Management               15                3

Afghanistan Brand       Business continuity      20                1

Afghanistan Brand       Management1             20                2

Afghanistan Brand       Management2             12                4

Afghanistan Brand       Management3             12                5

Albania                        management                20                1

Albania                        management1              20                2

Albania                        management 2             20                3

Bangladesh                 Management                56                1
Bangladesh                 Management1              56                2

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

We can insert an index by category in power query as below. Here is the M code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxLz0jMyywuScxTcCpKzEtR0lHyTcxLTE/NTc0rAXIMTZVidbCrcyotzsxLLS5WSM7PK8nMK80sqQSKGhng1IAw2JBYhUYgJxgRodAYSWFOElBd4qEFQKFcZL/AbMQqb6hASIWCEZISp8S89JzElNTiDAUUdyiAuKZmeNUYwtTEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CountryName = _t, label = _t, Riskrating = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Riskrating", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Riskrating", Order.Descending}, {"CountryName", Order.Descending}}),
    Partition = Table.Group(#"Sorted Rows", {"CountryName"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"label", "Riskrating", "Index"}, {"Partition.label", "Partition.Riskrating", "Partition.Index"})

in
    #"Expanded Partition"

For more details, please check the pbix as attached.

Regards,

Frank

[Anonymous]

Hi @v-frfei-msft 

thank you for sharing pbix file and M language code 

Regards

naveen