Forum Discussion
RANKX- Rank By User
Hi Everyone-
I am new to DAX and I have read a handful of blogs on RANKX, but I still can't figure out what I am doing wrong on the below scenario:
I want to create a column that ranks user's opportunities by the revenue (Owner Opportunity Rank). Here are the desired results:
| _ownerid_value | Expected Annual Revenue | Opportunity Name | Owner Opportunity Rank |
| aaaa-1 | $ 100.00 | Opp1 | 2 |
| aaaa-1 | $ 200.00 | Opp2 | 1 |
| aaaa-2 | $ 400.00 | Opp3 | 2 |
| aaaa-2 | $ 500.00 | Opp4 | 1 |
Here is my code:
I created a measurement:
TotalRevenue = SUM(opportunities[Expected Annual Revenue])
I created the desired column
Owner Opportunity Rank = RANKX (ALL(opportunities[_ownerid_value]), [TotalRevenue])
The issue is my "Owner Opportunity Rank" column returns all 1's.
Any help is appreciated!
How about this?
Owner Opportunity Rank = RANKX ( ALL ( opportunities[Opportunity Name] ), CALCULATE ( SUM ( opportunities[Expected Annual Revenue] ), ALLEXCEPT ( opportunities, opportunities[_ownerid_value], opportunities[Opportunity Name] ) ) )EDIT: You want to Rank the Opportunity Names for each Owner!
I think that's what you meant? :smileyhappy:
5 Replies
You could try this approach. It might work so let me know how you get on
This Col Might work = CALCULATE( COUNTROWS('Opportunities'), FILTER( ALL('Opportunities'), 'Opportunities'[_ownerid_value]=EARLIER('Opportunities'[_ownerid_value]) && 'Opportunities'[Expected Annual Revenue] < EARLIER('Opportunities'[Expected Annual Revenue]) ))+1Your RANK formula works as a MEASURE not a COLUMN
If you want a column in the Data View - this should work
Owner Opportunity Rank = RANKX ( ALL ( opportunities[_ownerid_value] ), CALCULATE ( SUM ( opportunities[Expected Annual Revenue] ), ALLEXCEPT ( opportunities, opportunities[_ownerid_value] ) ) )Good Luck! :smileyhappy:
Thanks Sean for the quick reply.
I think this is on the right track, but I believe this is ranking the owner himself. I am looking for the opportunity rank by the user. The rank should be determined by expected revenue. So I am looking for the "Owner Opportunity Rank" column here..
_ownerid_value Expected Annual Revenue Opportunity Name Owner Opportunity Rank aaaa-1 $ 100.00 Opp1 2 aaaa-1 $ 200.00 Opp2 1 aaaa-2 $ 400.00 Opp3 2 aaaa-2 $ 500.00 Opp4 1 Owner = aaaa-1 has two opportunites. Opp2 has a greater revenue than Opp1 so the Onwer Opportunity Rank = 1 for Opp2 and so forth... I apologize if that is not clear in my first post.
I when I read the RANKX documentation, I feel like this should be the column:
Owner Opportunity Rank = RANKX (
ALL ( opportunities[_ownerid_value] ),
opportunities[Expected Annual Revenue]
)But I still get all 1's...
How about this?
Owner Opportunity Rank = RANKX ( ALL ( opportunities[Opportunity Name] ), CALCULATE ( SUM ( opportunities[Expected Annual Revenue] ), ALLEXCEPT ( opportunities, opportunities[_ownerid_value], opportunities[Opportunity Name] ) ) )EDIT: You want to Rank the Opportunity Names for each Owner!
I think that's what you meant? :smileyhappy:
