Forum Discussion
Index Match Ranking top 3
- 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 measureLetter 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
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
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
- rschaudhr5 years agoResolver II
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?