Forum Discussion
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
All stores being displayed in rank order
Stores in the North Region - I am looking for "Apple Dealership" to display with a rank of 6 instead of 1.
3 Replies
- Greg_Deckler
Community Champion
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
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:
Date Dealer Name Orders Sales PMA Reimbursement Margin Pre Reimbursement Margin Post Reimburesement Non-Program Conversions Conversions Opportunities OEM Sales Non-Program OEM Program Promotions Part Group Number Part Group PMA Program Number PMA Program Part Number AM OEM Salvaged Reman LKQ Region District Dealer Code State City 43100 American Dealership 8 $1,979.00 $158.32 $277.06 $435.38 $39.58 1643 2464 $336.43 $1,187.40 $692.65 $98.95 13 Suspention 6 Reward PMA 37994 1848 739 296 246 74 North 1 481657 ME Portland 42948 Liberty Dealership 23 $309.00 $24.72 $33.99 $58.71 $6.18 192 266 $117.42 $185.40 $108.15 $15.45 8 Engine 6 Reward PMA 17734 200 80 32 27 8 South 1 125364 FL Jacksonville 42835 Eagle Dealership 12 $1,214.00 $97.12 $230.66 $327.78 $12.14 838 1089 $376.34 $728.40 $424.90 $60.70 11 Ignition 5 Impact PMA 40782 817 327 131 109 33 East 1 398618 NC Charlotte 42899 Airport Dealership 23 $403.00 $32.24 $68.51 $100.75 $12.09 266 351 $137.02 $241.80 $141.05 $20.15 3 Belts & Cooling 3 Country PMA 56784 263 105 42 35 11 West 1 239303 CA Los Angeles 42876 Jim's Dealership 9 $1,210.00 $96.80 $242.00 $338.80 $24.20 895 1074 $314.60 $726.00 $423.50 $60.50 2 Air & Fuel Delivery 4 Royal PMA 11071 806 322 129 107 32 South West 1 483921 NM Santa Fe 43042 Lichty Dealership 6 $660.00 $52.80 $105.60 $158.40 $6.60 429 528 $231.00 $396.00 $231.00 $33.00 12 Steering 2 City PMA 32764 396 158 63 53 16 South East 1 830173 SC Charelston 42821 Garlow Dealership 15 $895.00 $71.60 $107.40 $179.00 $8.95 635 934 $259.55 $537.00 $313.25 $44.75 15 Transfer Case 5 Impact PMA 52414 701 280 112 93 28 North West 1 821287 WA Seattle 42887 Apple Dealership 23 $1,043.00 $83.44 $312.90 $396.34 $10.43 845 1183 $198.17 $625.80 $365.05 $52.15 6 Driveline & Axels 2 City PMA 23379 887 355 142 118 35 North 2 952495 MA Salem - Greg_Deckler
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.
