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've been avoiding the use of RANKX because on previously looking at it, it's behaviour seemed so complicated as to make it unusable.
I have a basic table that has a column for department revenue and I'd simply like to show the top 10 items in the table based on that revenue. The measure I have is:
Rank of Opportunity by Dept1 Revenue = RANKX(ALL(Opportunities[OpportunityId]),[Total Dept1 Revenue])
This displays fine on a visual if all I have on that visual is the Measure, the Opportunity ID, and the Dept1 Revenue.
As soon as I add the Company Name to the visual, it breaks the Ranking.
I've told rankx to rank based on opportunity ID which is unique for each opportunity in order to avoid any grouping. Adding the Company Name doesn't change the value of Total Dept1 Revenue.
Clearly this is probably because the rank isn't specifically based on the Dept1 Revenue column but across all columns? How can I force RANKX to rank a row based only on a single column?
Any assistance greatly appreciated.
Thanks.
Solved! Go to Solution.
Hi @dparkinson,
How can I force RANKX to rank a row based only on a single column?
If I understand you correctly, the formula below should work in your scenario.
Rank of Opportunity by Dept1 Revenue = RANKX ( ALL ( Opportunities[OpportunityId] ), CALCULATE ( [Total Dept1 Revenue], ALLEXCEPT ( Opportunities, Opportunities[OpportunityId] ) ) )
Regards
Hi @dparkinson,
How can I force RANKX to rank a row based only on a single column?
If I understand you correctly, the formula below should work in your scenario.
Rank of Opportunity by Dept1 Revenue = RANKX ( ALL ( Opportunities[OpportunityId] ), CALCULATE ( [Total Dept1 Revenue], ALLEXCEPT ( Opportunities, Opportunities[OpportunityId] ) ) )
Regards
Been trying to replicate this but can't seem to. Can you provide sample/mock data?
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 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |