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 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
Solved! Go to Solution.
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks very much here's a link to the pbx example im using:
https://drive.google.com/file/d/1wSKbEPxO0IUNLF6Cr_IJH8uimJUUNwN1/view?usp=sharing
You can see i only have one of your DAX examples working but theres repeating locations as well which i dont want.
Also what would be amazing is for these rankings to be able to recalculate whenever the filter (or any other i add in) are applied.
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @rogerdea
Please refer to this video
https://www.youtube.com/watch?v=aMbtfBzTtBc&feature=youtu.be
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |