Forum Discussion

warrenpoh's avatar
warrenpoh
Frequent Visitor
9 years ago
Solved

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 hav...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    warrenpoh : I would suggest to calculate TotalSales as 

     

    CALCULATE(SUM(Rank_Table[Sales]),Rank_Table[IsSold]="T",ALLEXCEPT(Rank_Table,Rank_Table[Staff Member ])) , add additional filter for StaffMember.

    RankSales mentioned by PavelR  works well. Thanks

  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    warrenpoh

     

    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,

Anonymous's avatar
Anonymous
Not applicable
9 years ago

warrenpoh : Can you share the sample data , with the required result in additional column . Thanks

warrenpoh's avatar
warrenpoh
Frequent Visitor
9 years ago
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.