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,

warrenpoh's avatar
warrenpoh
Frequent Visitor
9 years ago

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.

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
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,