Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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 |
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |