Consolidate rows into one row using RANK()

[dmace]

I am trying to use partitions to consolidate the rows in my table into one row. 

The data is for candidates who have been referred by multiple people (up to 4). A candidate can have multiple rows containing data as such (there are more columns, however they aren't relevant to this question as they will be the same in all that candidates rows):

As you can see I am able to succesfully rank the rows by using a table function and RANK(). I wish for the data to look like:

Then I can remove the additional rows by filtering to only show Rank 1. I have successfully accomplished this task in Workday's PRISM, however due to the restrictions PRISM has on refreshes I wish to do this in PowerBI to ensure the data is up-to-date in real time. Any help or tips would be greatly appreciated, thanks.

[VijayP]

@dmace 

HAve you tried Calculate Function? 

[dmace]

I have but was unable to get it to work, could you give me any tips on how to use Calculate to accomplish this?

[VijayP]

@dmace 

Calculate (Average(column), filter(table name, rankcolumn = 1)), try this

[dmace]

The values are text so I don't believe Average will work.

[dmace · Accepted solution]