Adding vlues in a column based on another column and then rank them

[Vish24]

Hello,

I want to make a table with adding values for one column: for example,

A               B

test         20

test         30

test1      10

test2      5

test1      6

test4      12

test2     15

So I have 2 columns with names coming multiple times and their sales. I want to make a table with unique list of names and adding thier sales and then rank them based on sales. So final result something like this

 A               B        C(Rank)

test         50            1

test1      16            3

test2      20            2

test4      12           4

Thank you so much in advace!

[mwegener · Accepted solution]

Hi @Vish24 ,

check this out

Table 2 =
VAR sumTable =
    SUMMARIZE ( 'Table', 'Table'[A], "B", SUM ( 'Table'[B] ) )
RETURN
    ADDCOLUMNS ( sumTable, "C", RANKX ( sumTable, [B] ) )

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[Vish24]

I have done the ranking correctly. But now I have to show the ranking only for the selected person. I have list of 200 people who has done multiple projets and my rank is based on the number of projects. Now I need to make slicer with names of all 200people and show the ranking only for the selected person. Is there any way if I can hide the ranking for rest or I can only show ranking for selected person.

Thank you!

[Vish24]

Thank you so much! It worked!

[Tad17]

Hey @Vish24 

My understanding is that you are trying to do this in the original query. If so follow the steps below:

1. Right-click the query in Query Editor and create a reference.

2. In the new reference table remove all other columns other that column A (from your example)

3. Select remove duplicates.

4. Create a "sumif" column using the solution provided in this post: https://community.powerbi.com/t5/Desktop/SUMIF-in-Query-Editor-on-Power-BI-Desktop/td-p/135150

5. You can then set the default sort for each column to be the summed column in ascening order.

alternatively you can simply create a rankx function like in this article: https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

[Vish24]

Thank you! but I am working on desktop and I have made many columns here which I am not able to see if I am working in powerbi query.

[Tad17]

Hey @Vish24 

Just to clarify, are you wanting to add this ranking/sorting column to a Power BI Table Visual or to the data table itself?

[Vish24]

Data Table

[Tad17]

@Vish24 

If that is the case then you have to do this in the Query Editor. You cannot remove dupliactes in the data view in desktop. If the columns needing to be affected are all measures instead of calculated columns then similarly you will have to use SumX and RankX measures to accomplish your goal.

[Vish24]

@mwegenerif you could respond on this. Its so urgent. Thank you!