cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lpimentel
New Member

Ranking by two criteria

Hi people,

 

Im havin the following issue:

Im trying to rank securities by their time to zero the position (liquidity mesure) but i want to rank it by "PRAZO DE ZERAGEM" and by "CLASSE DE ATIVO".

What I mean is, i need a single column with rank starting in 1 to N-stocks, another rank starting in 1 to N-options, another starting in 1 to N-bonds etc...

I've tried using rankx but I'm not familiar with it and it has not worked.

 

zeragem.PNG

2 REPLIES 2
Anonymous
Not applicable

Here's the M script:

 

let

    // Source is your source table; here it's a manually created one
    // for demo purposes. You should replace this with your table.
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZExCoNAEEWvEra28K+uumcRi0hSCKLBmCK3j0SIzN8ZSCDle82+P9u2Di5z820d5sl12Yb5b1wSB+KKuCZuiGPyHoukyLMoWHBkKjgT3AkOBZd6LvVJ6Yb9PF2OQ37ICyoElYKCoOprirl8XaKsgcyB7IEMgiyCfHY/5IGNxEhXkEhVG6zL9Xx/LM9TP4zjn+x7TqqDritd17pudB11vf+K4mF4b/jC8MZUGFthjIWxFsZcGHu9sddre7sX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Time to Zero" = _t, #"Asset Class" = _t]),

    // Then I change the type of the two columns
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time to Zero", Int64.Type}, {"Asset Class", type text}}),


    // Need to sort the rows in order to get the ranks right.
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Asset Class", Order.Ascending}, {"Time to Zero", Order.Ascending}}),

    // This is needed to calculate the rank in the line below.
    #"Buffered Table" = Table.Buffer( #"Sorted Rows" ),

    // The calculation of rank.
    #"Rank" = Table.AddColumn(
        #"Sorted Rows", "Rank",
        (_) =>
            let
                TimeToZeroTable = Table.SelectColumns(
                    Table.SelectRows(
                        #"Buffered Table",
                        (r) => r[Asset Class] = _[Asset Class] and r[Time to Zero] <= _[Time to Zero]
                    ),
                    {"Time to Zero"}
                ),
                // If you want to have ranks with gaps, remove the List.Distinct function.
                rank = List.Count( List.Distinct( TimeToZeroTable[Time to Zero] ) )
            in
                rank
    )
in
    #"Rank"

And here's the result:

 

Ranking in Power Query.PNG

Just make the obvious changes to the script and you're done.

 

Truth be told, if you remove the line that starts with #"Sorted Rows" =, you'll also get what you need. You don't have to sort the table but if the table is really big, then it might be better to sort it.

 

Best

Darek

Anonymous
Not applicable

You should do such things in Power Query, not in DAX. This is, as much as I can see, a static ranking problem, not dynamic. So, please do yourself a favour and calculate this in Power Query.

Best
Darek

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors