Build a rating considering the duplicate values

[Anthony007]

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.

pbix on OneDrive

Thanks for any help!

[Anthony007]

@ImkeF, can u help please with this formula?

[ImkeF]

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"

[Anthony007]

@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 =(

[ImkeF · Accepted solution]

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"

[Anthony007]

Thanks for the help, it works. Now I will try the second set-up - use the in-ID idifier in the measure.

[AlbertoFerrari]

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.