Forum Discussion
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 | 100 |
| 33 | Company D | 50 |
| 34 | Company A | 60 |
| 34 | Company B | 20 |
| 34 | Company C | 80 |
| 34 | Company D | 200 |
| 35 | Company A | 1123 |
| 35 | Company B | 1000 |
| 35 | Company C | 2000 |
| 35 | Company D | 2000 |
I need to build a rating for each company every week:
| Week | Company | Value | Rank |
| 33 | Company A | 44 | 4 |
| 33 | Company B | 44 | 3 |
| 33 | Company C | 100 | 1 |
| 33 | Company D | 50 | 2 |
| 34 | Company A | 60 | 3 |
| 34 | Company B | 20 | 4 |
| 34 | Company C | 80 | 2 |
| 34 | Company D | 200 | 1 |
| 35 | Company A | 1123 | 3 |
| 35 | Company B | 1000 | 4 |
| 35 | Company C | 2000 | 2 |
| 35 | Company D | 2000 | 1 |
In Power bi i used 2 formula:
Total value = SUM(Table1[Value])
Rank = RANKX(FILTER(ALLSELECTED(Table1[Company]);NOT(ISBLANK([Total value])));Table1[Total value];;DESC;Dense)
And have next result:
| Week | Company | Value | Rank |
| 33 | Company A | 44 | 3 |
| 33 | Company B | 44 | 3 |
| 33 | Company C | 100 | 1 |
| 33 | Company D | 50 | 2 |
| 34 | Company A | 60 | 3 |
| 34 | Company B | 20 | 4 |
| 34 | Company C | 80 | 2 |
| 34 | Company D | 200 | 1 |
| 35 | Company A | 1123 | 2 |
| 35 | Company B | 1000 | 3 |
| 35 | Company C | 2000 | 1 |
| 35 | Company D | 2000 | 1 |
The problem, that the same values have the same rating.
Thanks for any help!
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"
6 Replies
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.
