cancel
Showing results for
Did you mean:
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
Member

## Re: Using RANKX to rank Top N aggregate measures

@warrenpoh : I would suggest to calculate TotalSales as

RankSales mentioned by @PavelR  works well. Thanks

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

Regards,

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

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

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

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
Member

## Re: Using RANKX to rank Top N aggregate measures

@warrenpoh : I would suggest to calculate TotalSales as

RankSales mentioned by @PavelR  works well. Thanks

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

Regards,

Announcements

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

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

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