Forum Discussion

rschaudhr's avatar
rschaudhr
Resolver II
5 years ago
Solved

Index Match Ranking top 3

I would like to rank the top three values from each row and place it in the 1st, 2nd and 3rd row. In the figure below it shows for row 1 (for example), D has the highest value of 90 and it will show ...
  • mahoneypat's avatar
    5 years ago

    Here is one way to do this.

     

    1.  Add an index and unpivot your data in the query editor.  I mocked up some data to demonstrate.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNlGK1YkGkhAeSNQQLGIIloOJxsYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Row", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Row"}, "Attribute", "Value")
    in
    #"Unpivoted Other Columns"

     

    I called the table "AD"

     

    2.  Make a second DAX table to hold your Rank values with an expression like this

     

    RankTable = GENERATESERIES(1,3,1)
     
    3.  Make this measure
     

    Letter At Rank =
    VAR vThisRank =
        SELECTEDVALUE ( RankTable[Value] )
    VAR vSummary =
        ADDCOLUMNS (
            VALUES ( AD[Attribute] ),
            "cRank",
                RANKX (
                    ALLSELECTED ( AD[Attribute] ),
                    CALCULATE (
                        SUM ( AD[Value] )
                    )
                )
        )
    RETURN
        MINX (
            FILTER (
                vSummary,
                [cRank] = vThisRank
            ),
            AD[Attribute]
        )

     

    4.  Make a matrix visual with the RankValues on columns, the AD[Attribute] column on rows and the measure on values to get this result.

     
     
     
     
     

     

    Pat