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,

PavelR's avatar
PavelR
Solution Specialist
9 years ago

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

Anonymous's avatar
Anonymous
Not applicable
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