Forum Discussion
Build a rating considering the duplicate values
Sorry, stupid sorting order. This will do:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZW0lFyzs8tSMyrVHAEsk1MlGJ10MSdcIg7A9mGBgaYEi5AtilU3ATNAjMs4iALjLCIgyywwCLuAlYPlTBFs8DQ0MgYU8YJ4lYsepwhhmGRcYHLxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Week = _t, Company = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week", Int64.Type}, {"Company", type text}, {"Value", Int64.Type}}), #"Sorted Rows2" = Table.Buffer(Table.Sort(#"Changed Type",{{"Value", Order.Descending}, {"Company", Order.Descending}})), #"Grouped Rows" = Table.Group(#"Sorted Rows2", {"Week"}, {{"MyRank", each Table.AddIndexColumn(_, "MyRank",1,1), type table}}), #"Expanded MyRank" = Table.ExpandTableColumn(#"Grouped Rows", "MyRank", {"Company", "Value", "MyRank"}, {"Company", "Value", "MyRank.1"}), #"Sorted Rows1" = Table.Sort(#"Expanded MyRank",{{"Week", Order.Ascending}, {"MyRank.1", Order.Descending}}) in #"Sorted Rows1"
ImkeF, can u help please with this formula?
Hi Anthony,
I'm not aware of a possiblity where RANK (in DAX) would return different values for the same amount.
But you can achieve the result you've given here as a static table using M in the query editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZW0lFyzs8tSMyrVHAEsk1MlGJ10MSdcIg7A9mGBgaYEi5AtilU3ATNAjMs4iALjLCIgyywwCLuAlYPlTBFs8DQ0MgYU8YJ4lYsepwhhmGRcYHLxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Week = _t, Company = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Week", Int64.Type}, {"Company", type text}, {"Value", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Company", Order.Descending}, {"Value", Order.Descending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Week"}, {{"MyRank", each Table.AddIndexColumn(_, "MyRank",1,1), type table}}),
#"Expanded MyRank" = Table.ExpandTableColumn(#"Grouped Rows", "MyRank", {"Company", "Value", "MyRank"}, {"Company", "Value", "MyRank.1"}),
#"Sorted Rows1" = Table.Sort(#"Expanded MyRank",{{"Week", Order.Ascending}, {"MyRank.1", Order.Descending}})
in
#"Sorted Rows1"
RANKX will return the same ranking for the same value, this is by design and it is the correct behavior. If you want the ranking to be different, then you need to write a measure that takes into account other columns to guarantee that the ranking is unique. How do you decide which of the two identical values should be ranked first? It is up to you to take this decision and, if it does not really matter, you can use the company ID to differentiate between them.
Of course, the result will not be accurate, but if this is what you need, you only need to change the measure to take the company ID into account.
ImkeF, Thank you for your help =), but the result is not correct:
WeekCompanyValueMyRank.1
33 Company A 44 4 33 Company B 44 3 33 Company C 100 2 33 Company D 50 1 34 Company A 60 4 34 Company B 20 3 34 Company C 80 2 34 Company D 200 1 35 Company A 1123 4 35 Company B 1000 3 35 Company C 2000 2 35 Company D 2000 1 33-th week, max value has Company "C". But in the ranking it takes the second place =(
Sorry, stupid sorting order. This will do:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZW0lFyzs8tSMyrVHAEsk1MlGJ10MSdcIg7A9mGBgaYEi5AtilU3ATNAjMs4iALjLCIgyywwCLuAlYPlTBFs8DQ0MgYU8YJ4lYsepwhhmGRcYHLxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Week = _t, Company = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week", Int64.Type}, {"Company", type text}, {"Value", Int64.Type}}), #"Sorted Rows2" = Table.Buffer(Table.Sort(#"Changed Type",{{"Value", Order.Descending}, {"Company", Order.Descending}})), #"Grouped Rows" = Table.Group(#"Sorted Rows2", {"Week"}, {{"MyRank", each Table.AddIndexColumn(_, "MyRank",1,1), type table}}), #"Expanded MyRank" = Table.ExpandTableColumn(#"Grouped Rows", "MyRank", {"Company", "Value", "MyRank"}, {"Company", "Value", "MyRank.1"}), #"Sorted Rows1" = Table.Sort(#"Expanded MyRank",{{"Week", Order.Ascending}, {"MyRank.1", Order.Descending}}) in #"Sorted Rows1"Thanks for the help, it works. Now I will try the second set-up - use the in-ID idifier in the measure.
