Using RANKX to rank Top N aggregate measures

[warrenpoh]

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 

[Anonymous · Accepted solution]

@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

[lalthan]

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]))) 

[warrenpoh]

@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 · Accepted solution]

@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]

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 · Accepted solution]

@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

[Anonymous]

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

[warrenpoh]

@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.