cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
naveenbobbala3 Frequent Visitor
Frequent Visitor

Group ranking

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. 

 

Capture1.PNG

 

 

 

InkedCapture_LI.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Group ranking

Hi @naveenbobbala3 ,

 

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"

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User
Super User

Re: Group ranking

@naveenbobbala3  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.



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





naveenbobbala3 Frequent Visitor
Frequent Visitor

Re: Group ranking

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

Community Support Team
Community Support Team

Re: Group ranking

Hi @naveenbobbala3 ,

 

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"

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
naveenbobbala3 Frequent Visitor
Frequent Visitor

Re: Group ranking

Hi @v-frfei-msft 

 

thank you for sharing pbix file and M language code 

 

Regards

naveen