cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OECvargoj Frequent Visitor
Frequent Visitor

Rankx and Slicers

Hello everyone,

 

I am in need of some assistance regarding the rankx function. I have my rankx setup and working properly, however what I'm looking to do is keep the overall rank when slicing my data. In my example, I rank stores 1-54 based on Total Sales. What I want to do from there is slice by region, for example say the North Region. I slice by North Region and the rankings are then reordered based on only the stores in that region. If the North region contained overall store ranks of 6, 15 and 54 how would I go about getting 6, 15 and 54 to display instead of 1, 2, 3,4,5,6?

 

Rankx to rank all stores

National Sales Rank DAX.png

 

All stores being displayed in rank order

Apple Dealer North 6th Overall.png

Stores in the North Region - I am looking for "Apple Dealership" to display with a rank of 6 instead of 1.

Apple Dealer North 1st in Region.png

3 REPLIES 3
Super User
Super User

Re: Rankx and Slicers

Are your regions in another table? Can you post sample data, have just been doing a bunch of work with RANKX.

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

OECvargoj Frequent Visitor
Frequent Visitor

Re: Rankx and Slicers

Thanks for the quick reply Greg! Everything is in the same table. My original setup utilized seperate tables however I could not get the rankx function to work properly when pulling in items from more than one table. 

 

Sample Data:

DateDealer NameOrdersSalesPMA ReimbursementMargin Pre ReimbursementMargin Post ReimburesementNon-Program ConversionsConversionsOpportunitiesOEM SalesNon-ProgramOEM ProgramPromotionsPart Group NumberPart GroupPMA Program NumberPMA ProgramPart NumberAMOEMSalvagedRemanLKQRegionDistrictDealer CodeStateCity
43100American Dealership8$1,979.00$158.32$277.06$435.38$39.5816432464$336.43$1,187.40$692.65$98.9513Suspention6Reward PMA37994184873929624674North1481657MEPortland
42948Liberty Dealership23$309.00$24.72$33.99$58.71$6.18192266$117.42$185.40$108.15$15.458Engine6Reward PMA177342008032278South1125364FLJacksonville
42835Eagle Dealership12$1,214.00$97.12$230.66$327.78$12.148381089$376.34$728.40$424.90$60.7011Ignition5Impact PMA4078281732713110933East1398618NCCharlotte
42899Airport Dealership23$403.00$32.24$68.51$100.75$12.09266351$137.02$241.80$141.05$20.153Belts & Cooling3Country PMA56784263105423511West1239303CALos Angeles
42876Jim's Dealership9$1,210.00$96.80$242.00$338.80$24.208951074$314.60$726.00$423.50$60.502Air & Fuel Delivery4Royal PMA1107180632212910732South West1483921NMSanta Fe
43042Lichty Dealership6$660.00$52.80$105.60$158.40$6.60429528$231.00$396.00$231.00$33.0012Steering2City PMA32764396158635316South East1830173SCCharelston
42821Garlow Dealership15$895.00$71.60$107.40$179.00$8.95635934$259.55$537.00$313.25$44.7515Transfer Case5Impact PMA524147012801129328North West1821287WASeattle
42887Apple Dealership23$1,043.00$83.44$312.90$396.34$10.438451183$198.17$625.80$365.05$52.156Driveline & Axels2City PMA2337988735514211835North2952495MASalem
Super User
Super User

Re: Rankx and Slicers

Hmm, very strange. I recreated this and it seems to work perfectly. See attached PBIX, Table9 Page 2. There is some other junk you don't want, this is my working PBIX.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 108 members 1,394 guests
Please welcome our newest community members: