Ranking by groups using Power query in Power BI
Hello Friends, Today we’ll be learning how to create ranking by groups using Power query in Power BI. Easy and quick tutorial to follow. Power BI is attached used in the tutorial.
Data
The data is looks like below with Team, Salesperson and the value. Here we want to create a column with ranking of Salespersons within the team
Group by
Now we have Group by the teams. Go to Transform > Group By. Select Team and operation as All Rows
Add Ranking column
Now go to Add Column > Custom Column and add the formula
=Table.AddIndexColumn([Count],"Rank",1,1)
Final Table creation
Removing the other 2 columns and expand the newly created custom column as shown in the image below and that’s done.
final table will look something like below
Keep visiting Analytics Tuts for more tutorials.
Thanks for reading! Comment your suggestions and queries.
I found your post interesting to read. Keep posting such blogs.
Hi !
The solution is interesting, but not able to se the rank column when expanding the grouped column.
You can follow the steps again and download the sample file used in tutorial.
Thanks
Wonderful find! Thanks for sharing…
Is it possible to do it without breaking the types of the columns?
How were you able to get the values in ascending order? For example, my row 2 is 275 units, while yours is 116. Thank you!!
I have followed the tutorial exactly and the ranking is not correct. You have probably forgotten a step in this tutorial.
Your sample file does not help and it does not have any power query code, so I don’t know what you did exactly.
Yes, I have figured it out. The tutorial is incorrect, you forgot to add a sorting step. I need to sort by Team and Value before grouping.
Thanks for pointing out, will add the step to the tutorial.
How do you sort the rank Z-A?
Thank you. Well explained. Worked a treat!
I ‘m not sure if this works when there are multiple lines with the same group by variables. It seems to multiply the numbe rof records.
Hi I came across similar issue at first, but when I followed a youtube video of the same method, and deleted Team and Count columns after the Final Table Creation step, it worked.
you need to sort values, you have to specify when and how
Available on the tutorial
try Table.AddRankColumn instead of Table.AddIndexColumn