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.
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!
Solved! Go to Solution.
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?
Your 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!
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?
Yes- This is what I am looking for. I am not following how DAX is grouping by the _ownerid_value but now that I have the acceptable solution, I can play around with it. Much thanks to you!!
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]) ))+1
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |