Forum Discussion
Solved
Build a rating considering the duplicate values
Help please build the rating with DAX. There is a table with three columns.In the value there are duplicates: Week Company Value 33 Company A 44 33 Company B 44 33 Company C ...
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, 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.