cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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.

pbix on OneDrive

Thanks for any help!

1 ACCEPTED SOLUTION
Super User

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"```

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

6 REPLIES 6
Helper I

@ImkeF, can u help please with this formula?

Super User

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"```

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Helper I

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

Super User

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"```

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Helper I

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

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.

Alberto Ferrari - SQLBI

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors