Index Match Ranking top 3

[rschaudhr]

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 up in the 1st column. 20 is the second largest number will show up in 2nd column and so on for the 3rd column. The excel sheet is using formula of index match and large. Is there a way to do this in Power BI. 

Thanks in advance 

[Ashish_Mathur]

Hi,

This will depend upon how the raw data is structured.  Share the link from where i can download your PBI file.

[mahoneypat · Accepted solution]

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

[rschaudhr]

Hi Pat,

You solution looks good. I incorporated the solution on my pbi file. It shows some gaps inbetween. I have provided my snapshot of the issue. Would you know what the issue and solution would be?

[rschaudhr]

Thanks in advance.

[rschaudhr]

I will accept this solution because it did address my original question. However, it did not solve the next ask. I will see if I can find that solution.

Thank you for your help.