cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
warrenpoh Frequent Visitor
Frequent Visitor

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
mridul Member
Member

Re: Using RANKX to rank Top N aggregate measures

@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

View solution in original post

Moderator v-sihou-msft
Moderator

Re: Using RANKX to rank Top N aggregate measures

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

 

Rank = RANKX(ALL(Table1[Name]),CALCULATE(SUM(Table1[Sales]),Table1[IsSold]="T",ALLEXCEPT(Table1,Table1[Name])))

Capture66.PNG

 

Regards,

View solution in original post

7 REPLIES 7
mridul Member
Member

Re: Using RANKX to rank Top N aggregate measures

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

lalthan Regular Visitor
Regular Visitor

Re: Using RANKX to rank Top N aggregate measures

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 Frequent Visitor
Frequent Visitor

Re: Using RANKX to rank Top N aggregate measures

@mridul 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.
warrenpoh Frequent Visitor
Frequent Visitor

Re: Using RANKX to rank Top N aggregate measures

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

PavelR Established Member
Established Member

Re: Using RANKX to rank Top N aggregate measures

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

Highlighted
mridul Member
Member

Re: Using RANKX to rank Top N aggregate measures

@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

View solution in original post

Moderator v-sihou-msft
Moderator

Re: Using RANKX to rank Top N aggregate measures

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

 

Rank = RANKX(ALL(Table1[Name]),CALCULATE(SUM(Table1[Sales]),Table1[IsSold]="T",ALLEXCEPT(Table1,Table1[Name])))

Capture66.PNG

 

Regards,

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors