Rank by one attribute when there is more than one attribute in a table

[Anonymous]

Hi Everyone,

I have a table visual with Manager in column 1, Region in column 2, and store in column 3.  Manager is from database #1 while Region & store are from database #2 (these tables are joined).  

I am trying to rank P% by Manager for what is selected in the slicers.  If I use RANKX(allselected(Manager[Mgr]), [P%]), it works fine if ONLY Manager (column 1) is in the table visual.  However, when region and store are added (columns 2 & 3) to the visual, it gives me what appears to be non-sensical ranking numbers. 

How do I rank based on Manager while also having region and store in the visual?

Thanks,

[Anonymous · Accepted solution]

Hi,

Thanks to everyone who took the time to either post or email me with suggestions - it is much appreciated!  The correct solution came from Oxenskiold:

RANKX(all(db#1[Mgr]),CALCULATE([PVR%],all(db#2[Region])),,,Dense)

...then it was just a matter of re-arranging the matrix to get the correct presentation of the ranking based on what fields I wanted to include.

Thanks.

[v-huizhn-msft]

Hi @Anonymous,

Congratulation to you have resolved your issue, please mark the correspoding reply as answer. So others can find solution clearly.

Best Regards,
Angelia

[MattAllington]

Based on my understanding of your design, allselected is not the right formula. Try using all instead. 

[austinsense]

And maybe not just ALL(Manager[Mgr]) but maybe ALL(Manager) instead, since you've put everything on the same table.