Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Rank Top Senders = RANKX ( FILTER ( ALL ( UserMessages ); UserMessages[Direction] = "Outbound" ); CALCULATE ( SUM ( UserMessages[Messages] ); ALLEXCEPT ( UserMessages; UserMessages[User] ) ); ; DESC; DENSE )
replace ; with ,
Rank Top Senders = RANKX ( FILTER ( ALL ( UserMessages ); UserMessages[Direction] = "Outbound" ); CALCULATE ( SUM ( UserMessages[Messages] ); ALLEXCEPT ( UserMessages; UserMessages[User] ) ); ; DESC; DENSE )
replace ; with ,
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:
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 |
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])
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
You are welcome.
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |