Rankx and Slicers

[OECvargoj]

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.

[Greg_Deckler]

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

[OECvargoj]

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.