Forum Discussion
Struggling with RANKX
Hi again
I'm struggling to get a simple rank working, but in addition to this want an additional ranking column to show the rank within another grouping. My sample pbx is attached, and this is the result i am trying to achieve:
Desired results with 2 x new rank measures:
Rank overall is just the rank descending based upon Total Score. The Location Rank is the Rank within each Location_Group.
Data is :.
| Location | Location_Group | Score |
| Birmingham | Midlands | 2.6 |
| Manchester | North | 3.1 |
| Torquay | South | 2.1 |
| Birmingham | Midlands | 1.3 |
| Manchester | North | 1.4 |
| Torquay | South | 2.9 |
| Birmingham | Midlands | 3.6 |
| Manchester | North | 1.2 |
| Torquay | South | 4 |
| Birmingham | Midlands | 2.1 |
| Manchester | North | 1.8 |
| Torquay | South | 3.1 |
| Solihull | Midlands | 1.6 |
| Leeds | North | 2.1 |
| Plymouth | South | 3.6 |
| Solihull | Midlands | 2.1 |
| Leeds | North | 0.5 |
| Plymouth | South | 1.1 |
| Solihull | Midlands | 2.5 |
| Leeds | North | 3.1 |
| Plymouth | South | 0.3 |
| Bradford | North | 2.5 |
| Coventry | Midlands | 1.6 |
| Plymouth | South | 1.6 |
| Bradford | North | 2.9 |
| Coventry | Midlands | 3.4 |
| Plymouth | South | 5.1 |
| Bradford | North | 2.6 |
| Coventry | Midlands | 0.9 |
Thanks
Roger
rogerdea
I checked the file, you need to insert the location and the location group from the areas table to the visual:You can download the file: HERE
Location Rank = RANKX( ALLSELECTED(Areas[Location]), CALCULATE(SUM(Scores[Score])) ) -------------------------------------------------------- Overall Rank = RANKX( ALLSELECTED(Areas[Location],Areas[Location_Group]), CALCULATE(SUM(Scores[Score])) )________________________
If my answer was helpful, please click Accept it as the solution to help other members find it useful
Click on the Thumbs-Up icon if you like this reply 🙂
6 Replies
rogerdea
Here are the two ranking measures:Overall Rank = RANKX( ALL(T9[Location],T9[Location_Group]), CALCULATE(SUM(T9[Score])) )location Rank = RANKX( ALL(T9[Location]), CALCULATE(SUM(T9[Score])) )________________________
If my answer was helpful, please click Accept it as the solution to help other members find it useful
Click on the Thumbs-Up icon if you like this reply 🙂
Thanks very much and i got the simpler DAX working however i've now realised in my real work rather than this example the [Location_Group] is stored in another table. And using this dax below gives me the error when trying to use the location_group in this other table:
"All column arguments of the ALL/ALLNOBLANKROW/ALLSELECTED/REMOVEFILTERS function must be from the same table."
So for the DAX:
RANKX =
ALL(T9[Location]),T9[Location_Group]),
CALCULATE(SUM(T9([Score]))
)
If T9[Location_Group]) was from another table in the powerBI how could this work or be written?
Thanks
rogerdea
What is the relationship between the tables? Can you share a dummy PBIX file that represents your actual model?________________________
If my answer was helpful, please click Accept it as the solution to help other members find it useful
Click on the Thumbs-Up icon if you like this reply 🙂
