Rankx with filter

[ChrisWilliams]

I'm working on a RANKX function that involves a filter.  I reviewed this answer, but it did not seem to help my scenario.

I have a table called "UserMessages", and a simplified version is shown below:

Basically, I want to rank users as "Top Senders" or "Top Receivers".  When they are a Top Sender, we are ranking based on filtering the Direction column to "Outbound".

I created a message called "Rank Top Senders" and it looks like this:

Rank Top Senders = RANKX(FILTER(ALL(UserMessages), UserMessages[Direction] = "Outbound"), SUM(UserMessages[Messages]),,DESC,Dense)

However, it doesn't appear to rank correctly, because each user is shown with a rank of 1

I've tried several variations, but if someone can point me in the right direction I'd appreciate it.

[Vvelarde · Accepted solution]

hi @ChrisWilliams

Rank Top Senders =
RANKX (
    FILTER ( ALL ( UserMessages ); UserMessages[Direction] = "Outbound" );
    CALCULATE (
        SUM ( UserMessages[Messages] );
        ALLEXCEPT ( UserMessages; UserMessages[User] )
    );
    ;
    DESC;
    DENSE
)

replace ; with ,

[Anonymous]

Hi Chris,

I have been searching for help on my challenge and run into this post. I tried to modify your solution to my situation but it is not working somehow. My situation is a little bit different.

A simplified table is below. What I want is to rank the Customer Name by Sales BUT EXCLUDING all the Customers with Blank names (let's call them anyomymous customers). 

The formula I came up after modelling yours is:

Rank Customer By Sales= RANKX(

filter(all(Table[Customer Name]),NOT(ISBLANK(Table[Customer Name]))),

CALCULATE(sum(Sales), ALLEXCEPT(Customer Name])),,DESC,Dense)

But it is still including the Anonymous customers. 

Thanks for help. 

PBISean

Customer Name	Sales	Sales Order Num
A	800	1
B	700	2
C	600	3
D	500	4
E	400	5
	300	6
F	200	7
G	100	8
A	80	9
B	70	10
C	60	11
D	50	12
E	40	13
	30	14
F	20	15
G	10	16

[Ashish_Mathur]

Hi,

You may drag Customer Name to the row labels and then in the Visual level filters, uncheck the blank in the Customer Name field.  Write these measures

Total Sales = SUM(Data[Sales])

Rank of selected Customers = RANKX(ALLSELECTED(Data[Customer Name]),[Total sales])

Hope this helps.

[Anonymous]

Ashish_Mathur,

Thanks for your help. It works on my mock data, but not working on my real data set which has 1.3M lines. Not sure why. I will spend some time tomorrow trying to trouble shoot. I just want to thank you first. 

PBISean

[Ashish_Mathur]

You are welcome.

[Vvelarde]

@Anonymous

Hi, a simple way is using a measure to Rank (Basic) and a Visual Level Filter to exclude Blanks.

If you have problems with the filter you can create a new measure

MeasuretoexcludeAnonymus=Len(Table1[ClientName])

and use it in the visual level filter to exclude the 0.

Regards

Victor

[ChrisWilliams]

 Thank you @Vvelarde, that works perfectly.  I'll have to study up on AllExcept... I haven't seen that used in Ranking before but it works well.