cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cudjh1 Regular Visitor
Regular Visitor

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

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,

1 ACCEPTED SOLUTION

Accepted Solutions
cudjh1 Regular Visitor
Regular Visitor

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

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.

4 REPLIES 4
Super User
Super User

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

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
austinsense Established Member
Established Member

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

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

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast Smiley Happy
cudjh1 Regular Visitor
Regular Visitor

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

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 Super Contributor
Super Contributor

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

Hi @cudjh1,

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

 

Best Regards,
Angelia