Struggling with RANKX

[rogerdea]

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

[Fowmy]

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

Website YouTube  LinkedIn

[rogerdea]

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

[Fowmy]

@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

[rogerdea]

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.

[Fowmy · 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

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

[aj1973]

Hi @rogerdea 

Please refer to this video

https://www.youtube.com/watch?v=aMbtfBzTtBc&feature=youtu.be