Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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. 

 

 

 

 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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

4 Replies

  • PattemManohar's avatar
    PattemManohar
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-frfei-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        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