Forum Discussion
Using RANKX to rank Top N aggregate measures
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,
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,
Hi warrenpoh. Just use these two DAX calculations:
TOTALSALES = CALCULATE(SUM(TABLE[Sales]);TABLE[IsSold]="T")
RANKSALES = RANKX(ALL(TABLE[Staff Member]);[TOTALSALES])
Regards.
Pavel
