cancel
Showing results for
Did you mean:
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 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

1 ACCEPTED SOLUTION
Super User

@rogerdea

I checked the file, you need to insert the location and the location group from the areas table to the visual:

``````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 🙂

Did I answer your question? Mark my post as a solution! and hit thumbs up
6 REPLIES 6
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]))
)``````

________________________

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 🙂

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

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

Super User

@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 🙂

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

Thanks very much here's a link to the pbx example im using:

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.

Super User

@rogerdea

I checked the file, you need to insert the location and the location group from the areas table to the visual:

``````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 🙂

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

Regards
Amine Jerbi

and you can follow me on

Announcements

#### Power BI T-Shirt Design Challenge 2023

Submit your creative T-shirt design ideas starting March 7 through March 21, 2023.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors