Ranking With a Slicer

[Tucker62]

Hi,

I have a pivot table that lists 67 stores by their Region and State and respective sales for each store. I am using slicers to filter the data by Region and State. I also have a formula within the table that ranks the stores by their sales. So, when all slicers are unfiltered the results give me the stores ranked on a national level from 1 to 67. However, when using the slicer to filter on a specific state, the ranking formula re-ranks the stores within the state and does not retain their nationally ranked number. How can I retain the stores national ranking when using the slicer?

Thanks,

 

[SpillerBD]

The Slicer I believe will always slice/filter the data before it reaches the Pivot Table.
You can apply an Auto Filter.
However, the ranking may be better to be calculated in the source table instead.

Check out a Dueling Excel episode https://www.youtube.com/watch?v=QkOwkbdaruQ

 

[DataMike]

Is your desired output the regionally filtered rankings that have a column with the national ranking as well? The logical steps seem to be:
1. Generate the national rankings
2. Generate the filtered rankings (should be changed dynamically)
3. Join the national ranking column with the new filtered rankings - joined by store name/id presumably

In excel, you could probably do this with some combination of formulas using VLOOKUP. However, if this is a professional use case, check out Parabola (https://parabola.io/) - it would only take a couple minutes to set up those steps, and then it's automated going forward.

 

[Tucker62]

Thanks for the reply, but I'm still uncertain. As you see below, the Area ranking column ranks each District within it's respective Area based on the Top2 % column, which is one ranking I need, but I can't get the National ranking column to ranking each District on a National level from 1-67 based on the Top2 % column.

	Area	District	Area Ranking	National Ranking	Top2 % Ranking	Total Surveys	% of Total Surveys Received
	CAPITAL METRO	GREATER SOUTH CAROLINA	1	1	100.00%	58	1.09%
		NORTHERN VIRGINIA	2	2	97.44%	39	0.73%
		MID-CAROLINAS	3	3	95.12%	41	0.77%
		CAPITAL	4	4	93.88%	49	0.92%
		BALTIMORE	5	5	92.77%	83	1.56%
		GREENSBORO	6	6	92.73%	55	1.03%
		ATLANTA	7	7	92.59%	81	1.52%
		RICHMOND	8	8	91.30%	69	1.30%
	EASTERN	CENTRAL PENNSYLVANIA	1	1	98.52%	270	5.07%
		SOUTH JERSEY	2	2	95.87%	121	2.27%
		TENNESSEE	3	3	95.62%	137	2.57%
		APPALACHIAN	4	4	94.29%	70	1.31%

<colgroup><col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1184;"> <col width="175" style="width: 131pt; mso-width-source: userset; mso-width-alt: 5600;"> <col width="175" style="width: 131pt; mso-width-source: userset; mso-width-alt: 5600;"> <col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2656;"> <col width="106" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3392;"> <col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3072;"> <col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 2688;"> <col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 3552;"> <tbody> </tbody>

 

[SpillerBD]

Add a new Field to your Source Data for "Nation"
Add that field to your Pivot Table as the lowest Label item.
Right Click on a "National Ranking" Item and set the Parent Item to the new "Nation" field.

 

[Tucker62]

I added a column to my source data for Nation, but what populates that column of data? Thanks for your patience

CCC:	Nation	Area	District	Site
723580095		WESTERN	COLO./WYOMING	DENVER
581000417		PACIFIC	SIERRA COASTAL	VAN NUYS
3066450567		WESTERN	CENTRAL PLAINS	OMAHA
4371400951		NORTHEAST	CONNECTICUT VALLEY	PROVIDENCE
1187450624		SOUTHERN	SOUTH FLORIDA	STUART
502940816		PACIFIC	SIERRA COASTAL	ARCADIA

<colgroup><col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3008;" span="2"> <col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3200;"> <col width="218" style="width: 164pt; mso-width-source: userset; mso-width-alt: 6976;"> <col width="199" style="width: 149pt; mso-width-source: userset; mso-width-alt: 6368;"> <tbody> </tbody>

 

[SpillerBD]

Any non blank value. Like "USA"

 

[Tucker62]

I tried that also using Nationally...and here were the results

CCC:	National	Area	District	Site
723580095	Nationally	WESTERN	COLO./WYOMING	DENVER
581000417	Nationally	PACIFIC	SIERRA COASTAL	VAN NUYS
3066450567	Nationally	WESTERN	CENTRAL PLAINS	OMAHA
4371400951	Nationally	NORTHEAST	CONNECTICUT VALLEY	PROVIDENCE

<colgroup><col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3008;" span="2"> <col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3200;"> <col width="218" style="width: 164pt; mso-width-source: userset; mso-width-alt: 6976;"> <col width="199" style="width: 149pt; mso-width-source: userset; mso-width-alt: 6368;"> <tbody> </tbody>

	Area	District	National	Area Ranking	National Ranking	Top2 % Ranking	Total Surveys
	CAPITAL METRO	GREATER SOUTH CAROLINA	Nationally	1	1	100.00%	58
		NORTHERN VIRGINIA	Nationally	2	1	97.44%	39
		MID-CAROLINAS	Nationally	3	1	95.12%	41
		CAPITAL	Nationally	4	1	93.88%	49
		BALTIMORE	Nationally	5	1	92.77%	83
		GREENSBORO	Nationally	6	1	92.73%	55
		ATLANTA	Nationally	7	1	92.59%	81
		RICHMOND	Nationally	8	1	91.30%	69
	EASTERN	CENTRAL PENNSYLVANIA	Nationally	1	1	98.52%	270
		SOUTH JERSEY	Nationally	2	1	95.87%	121
		TENNESSEE	Nationally	3	1	95.62%	137
		APPALACHIAN	Nationally	4	1	94.29%	70
		PHILADELPHIA METRO	Nationally	5	1	93.98%	83
		OHIO VALLEY	Nationally	6	1	92.81%	167
		NORTHERN OHIO	Nationally	7	1	92.03%	138
		WESTERN PENNSYLVANIA	Nationally	8	1	91.89%	185
		WESTERN NEW YORK	Nationally	9	1	90.72%	97
		KENTUCKIANA	Nationally	10	1	88.04%	92

<colgroup><col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1184;"> <col width="175" style="width: 131pt; mso-width-source: userset; mso-width-alt: 5600;"> <col width="175" style="width: 131pt; mso-width-source: userset; mso-width-alt: 5600;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2208;"> <col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2656;"> <col width="106" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3392;"> <col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3072;"> <col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 2688;"> <tbody> </tbody>

 

[SpillerBD]

Add a new Field to your Source Data for "Nation"
Add that field to your Pivot Table as the lowest Label item.
Right Click on a "National Ranking" Item and set the Parent Item to the new "Nation" field.

I swear. I thought I had it right one way but....
Try Right Click on a "National Ranking" Item and set the Parent Item to the new "Area" field.

You can retain the National ranking when the Advanced Filter is applied to the Table, but not the Slicer.