Forum Discussion
Using RANKX to rank Top N aggregate measures
Hi,
I have searched relatively hard for this - sorry if it's been covered already - any tips would be helpful.
I have a series of 34,000 transaction data for various timesheet entries. I have been using tables and visual level filters to provide metrics that show the sum that each person has billed year to date. I have been doing this via a:
Inv. = CALCULATE(sum(Transactions[Value]), Transactions[IsInvoiced] = TRUE) as a measure.
I would like to show on a graph the top 5 people in the group. I have tried to understand RANKX but am struggling.
I've tried: RANKX(ALLSELECTED(Transactions),Transactions[Inv.]) but all I get are 1's... I then found out that was wrong, so have tried:
RANKX(ALLSELECTED(Transactions),CALCULATE(SUM(Transactions[Value]))) ....Esentially trying to cut out the measure part - this results in the same.
RANKX(ALL(Transactions),CALCULATE(SUM(Transactions[Value]))) ...shows some form of ranking but mainly random non-sensical numbers.
Am on the right track with RANKX - there is a TOPN function but it seems to return tables which doesn't really help me.
I don't care that the ranking is in the table visualisation as I want to apply it as a visual filter onto the column graph.
Regards,
Warren
In this scenario, since your rank is based on each staff's sales total, you should use ALLEXCEPT() to calculate the total group on each staff as Anonymous suggested.
Rank = RANKX(ALL(Table1[Name]),CALCULATE(SUM(Table1[Sales]),Table1[IsSold]="T",ALLEXCEPT(Table1,Table1[Name])))
Regards,
7 Replies
warrenpoh : Can you share the sample data , with the required result in additional column . Thanks
- Anonymous what's the best format to share the data. Copy and paste picture into the forum. I've never done it before. The solution suggested by the other person did not work so still need help.
If you are ranking on people, then Transaction[People ID] has to be wrapped inside your all criteria and not the whole table
e.g. RANKX(ALL(Transactions[People ID]),CALCULATE(SUM(Transactions[Value])))
lalthan your method seems to work when the raw data is in the table, e.g.:
Staff Member Sales Measure
Warren 32 3
John 52 1
Dave 45 2
But I have data that looks like this: Anonymous is this ok to assist with?
Staff Member Date Sales IsSold
Warren 24/08/2016 12 T
John 23/08/2016 24 T
Dave 23/08/2016 10 T
Warren 25/08/2016 10 T
John 26/07/2016 15 T
Dave 27/07/2016 11 F
I want to display a table that shows the following. Note that Total Sales is a measure = CALCULATE(SUM(Table[SALES]), Table[IsSold] = TRUE)
Staff Member Total Sales Sales Rank
Warren 22 2
John 39 1
Dave 10 3
I hope tihs explains my issue more clearly. I feel like I'm almost there but not quite.
In this scenario, since your rank is based on each staff's sales total, you should use ALLEXCEPT() to calculate the total group on each staff as Anonymous suggested.
Rank = RANKX(ALL(Table1[Name]),CALCULATE(SUM(Table1[Sales]),Table1[IsSold]="T",ALLEXCEPT(Table1,Table1[Name])))
Regards,
