Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rogerdea
Helper IV
Helper IV

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 desired.JPG

Rank overall is just the rank descending based upon Total Score.  The Location Rank is the Rank within each Location_Group.

 

Data is :.

 

LocationLocation_GroupScore
BirminghamMidlands2.6
ManchesterNorth3.1
TorquaySouth2.1
BirminghamMidlands1.3
ManchesterNorth1.4
TorquaySouth2.9
BirminghamMidlands3.6
ManchesterNorth1.2
TorquaySouth4
BirminghamMidlands2.1
ManchesterNorth1.8
TorquaySouth3.1
SolihullMidlands1.6
LeedsNorth2.1
PlymouthSouth3.6
SolihullMidlands2.1
LeedsNorth0.5
PlymouthSouth1.1
SolihullMidlands2.5
LeedsNorth3.1
PlymouthSouth0.3
BradfordNorth2.5
CoventryMidlands1.6
PlymouthSouth1.6
BradfordNorth2.9
CoventryMidlands3.4
PlymouthSouth5.1
BradfordNorth2.6
CoventryMidlands0.9

 

Thanks

Roger

1 ACCEPTED 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

 

Fowmy_0-1610798819071.png

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 🙂


Website YouTube  LinkedIn

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

6 REPLIES 6
Fowmy
Super User
Super User

@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]))
)

 

Fowmy_0-1610633188243.png

 

 

________________________

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 🙂


Website YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


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 🙂


Website YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


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

 

Fowmy_0-1610798819071.png

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 🙂


Website YouTube  LinkedIn

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

aj1973
Community Champion
Community Champion

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.