Ranking by two criteria

[lpimentel]

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.

[Anonymous]

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:

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]

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