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

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
Super User
Super User

Re: Ranking by two criteria

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
Super User
Super User

Re: Ranking by two criteria

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 118 members 1,491 guests
Please welcome our newest community members: