Rearranging Columns in Pivot Table

[rokunation]

Hi I have the following data:

TESTMay 2022- Fill Rate COT FILE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	CUSTOMER NAME	INVOICE NUMBER	INVOICE DATE	LINE ABBREV	PART NUMBER	DESCRIPTION	QUANTITY SOLD	NET PRICE	EXTENDED PRICE	UNIT COST	ORIGIN	PO NUMBER	ATTENTION	Actual Date	STATUS	Identifier	Part Request ID	Request Time	ReqApprove	Time Diff (Req-REQApp)	Time Diff (Operational Hours) (Req-REQApp)	Approved Time	Ready Time	Closed Time	Request->Ready Calendar Time	Time Difference (Approve-->Ready)	Time Diff (Operational Hours) (Approve->Ready)	Fill/No Fill
2	CITY OF TORONTO -DISCO	946911	5182022	2RU	455538C1	AIR TANK CABLE	2	36.42	72.84	36.42	NAPA ACCESS GENERATED	194036 774198	STOCK	05/18/2022	STOCK	2RU455538C1194036 774198	151933	2022-05-18 1:28:17 PM	2022-05-18 1:28:58 PM	0:00:41	0:00:41	2022-05-18 1:29:40 PM	2022-05-18 1:29:51 PM	2022-05-18 1:40:48 PM	0:01:34	0:00:11	0:00:11	FILL
3	CITY OF TORONTO -DISCO	946911	5182022	2RU	3832996C1	AIR TANK	1	249.22	249.22	249.22	NAPA ACCESS GENERATED	194036 774198	STOCK	05/18/2022	STOCK	2RU3832996C1194036 774198	151849	2022-05-18 7:19:35 AM	2022-05-18 1:28:39 PM	6:09:04	6:09:04	2022-05-18 1:29:40 PM	2022-05-18 1:29:51 PM	2022-05-18 1:40:47 PM	6:10:16	0:00:11	0:00:11	FILL
4	CITY OF TORONTO -DISCO	946928	5202022	NWB	35220	TRICO ICE WINTER BLAD	2	12.3	24.6	12.3	NAPA ACCESS GENERATED	135181 773893	STOCK	05/20/2022	STOCK	NWB35220135181 773893	151735	2022-05-17 7:22:04 AM	2022-05-17 7:54:01 AM	0:31:57	0:31:57	2022-05-17 7:56:21 AM	2022-05-17 7:56:36 AM	2022-05-20 2:15:41 PM	0:34:32	0:00:15	0:00:15	FILL
5	CITY OF TORONTO -DISCO	946901	5182022	FLX	BF31L	BRAKE FLUID	1	7.75	7.75	7.75	NAPA ACCESS GENERATED	156142 773907	STOCK	05/18/2022	STOCK	FLXBF31L156142 773907	151727	2022-05-17 6:28:56 AM	2022-05-17 7:17:59 AM	0:49:03	0:47:59	2022-05-17 7:56:21 AM	2022-05-17 7:56:36 AM	2022-05-18 8:53:15 AM	1:27:40	0:00:15	0:00:15	FILL
6	CITY OF TORONTO -DISCO	946943	5252022	NGF	1748XD	HD LUBE FILTER	1	42.34	42.34	56.73	NAPA ACCESS GENERATED	188060 774791	STOCK	05/25/2022	STOCK	NGF1748XD188060 774791	152507	2022-05-25 10:24:59 AM	2022-05-25 10:55:33 AM	0:30:34	0:30:34	2022-05-25 11:37:00 AM	2022-05-25 11:37:15 AM	2022-05-25 2:14:21 PM	1:12:16	0:00:15	0:00:15	FILL
7	CITY OF TORONTO -DISCO	946943	5252022	9KM	T32	KROWN T32	1	6.07	6.07	6.07	NAPA ACCESS GENERATED	188060 774791	STOCK	05/25/2022	STOCK	9KMT32188060 774791	152509	2022-05-25 10:25:40 AM	2022-05-25 11:05:14 AM	0:39:34	0:39:34	2022-05-25 11:37:00 AM	2022-05-25 11:37:15 AM	2022-05-25 2:14:22 PM	1:11:35	0:00:15	0:00:15	FILL
8	CITY OF TORONTO -DISCO	946943	5252022	KFL	837	AIR INTAKE KLEEN	1	6.01	6.01	6.01	NAPA ACCESS GENERATED	188060 774791	STOCK	05/25/2022	STOCK	KFL837188060 774791	152508	2022-05-25 10:25:40 AM	2022-05-25 11:06:13 AM	0:40:33	0:40:33	2022-05-25 11:37:00 AM	2022-05-25 11:37:15 AM	2022-05-25 2:14:21 PM	1:11:35	0:00:15	0:00:15	FILL
9	CITY OF TORONTO -DISCO	946946	5252022	NGF	1748XD	HD LUBE FILTER	1	42.34	42.34	56.73	NAPA ACCESS GENERATED	176025 774383	STOCK	05/25/2022	STOCK	NGF1748XD176025 774383	152032	2022-05-19 8:18:21 AM	2022-05-19 10:28:32 AM	2:10:11	2:10:11	2022-05-19 10:54:54 AM	2022-05-19 10:55:09 AM	2022-05-25 2:18:04 PM	2:36:48	0:00:15	0:00:15	FILL
10	CITY OF TORONTO -DISCO	946944	5252022	2TC	7H3608	NUT	40	3.67	146.8	3.67	NAPA ACCESS GENERATED	333017 771164	STOCK	05/25/2022	STOCK	2TC7H3608333017 771164	152156	2022-05-20 5:11:29 AM	2022-05-25 11:34:36 AM	126:23:07	30:34:36	2022-05-25 11:37:00 AM	2022-05-25 11:37:15 AM	2022-05-25 2:15:56 PM	126:25:46	0:00:15	0:00:15	FILL
11	CITY OF TORONTO -DISCO	946944	5252022	2TC	2823655	BOLT	40	3.47	138.8	3.47	NAPA ACCESS GENERATED	333017 771164	STOCK	05/25/2022	STOCK	2TC2823655333017 771164	152157	2022-05-20 5:11:58 AM	2022-05-25 11:34:59 AM	126:23:01	30:34:59	2022-05-25 11:37:00 AM	2022-05-25 11:37:15 AM	2022-05-25 2:15:56 PM	126:25:17	0:00:15	0:00:15	FILL
12	CITY OF TORONTO -DISCO	946944	5252022	2TC	1984781	WASHER	1	8.75	8.75	8.75	NAPA ACCESS GENERATED	333017 771164	STOCK	05/25/2022	STOCK	2TC1984781333017 771164	152037	2022-05-19 8:24:11 AM	2022-05-25 11:34:02 AM	147:09:51	37:09:51	2022-05-25 11:37:00 AM	2022-05-25 11:37:15 AM	2022-05-25 2:15:57 PM	147:13:04	0:00:15	0:00:15	FILL
13	CITY OF TORONTO -DISCO	946944	5252022	2TC	8T6381	BOLT	1	20.3	20.3	20.3	NAPA ACCESS GENERATED	333017 771164	STOCK	05/25/2022	STOCK	2TC8T6381333017 771164	152036	2022-05-19 8:21:39 AM	2022-05-25 11:34:02 AM	147:12:23	37:12:23	2022-05-25 11:37:00 AM	2022-05-25 11:37:15 AM	2022-05-25 2:15:57 PM	147:15:36	0:00:15	0:00:15	FILL
14	CITY OF TORONTO -DISCO	946944	5252022	2TC	3012754	HOSE	1	78.77	78.77	78.77	NAPA ACCESS GENERATED	333017 771164	STOCK	05/25/2022	STOCK	2TC3012754333017 771164	151975	2022-05-19 5:14:03 AM	2022-05-25 11:35:14 AM	150:21:11	39:05:14	2022-05-25 11:37:00 AM	2022-05-25 11:37:15 AM	2022-05-25 2:15:58 PM	150:23:12	0:00:15	0:00:15	FILL
15	CITY OF TORONTO -DISCO	946858	5102022	KFL	472	GASKET CLEAR SILICONE	1	12.02	12.02	12.02	NAPA ACCESS GENERATED	135185 773559	STOCK	05/10/2022	STOCK	KFL472135185 773559	151091	2022-05-10 5:19:56 PM	2022-05-10 5:23:34 PM	0:03:38	0:03:38	2022-05-10 5:23:51 PM	2022-05-10 5:24:09 PM	2022-05-10 5:25:11 PM	0:04:13	0:00:18	0:00:18	FILL
16	CITY OF TORONTO -DISCO	946936	5242022	1YS	ASH1154	SHOCK	2	94.71	189.42	94.71	NAPA ACCESS GENERATED	135181 773893	6520948	05/24/2022	NON STOCK	1YSASH1154135181 773893	151996	2022-05-19 7:00:58 AM	2022-05-24 1:30:25 PM	126:29:27	31:59:27	2022-05-24 1:38:00 PM	2022-05-24 1:38:21 PM	2022-05-24 2:10:12 PM	126:37:23	0:00:21	0:00:21	NO FILL
Disco

Cell Formulas
Range	Formula
P2:P16	P2	=CONCATENATE(D2,E2,L2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q:Q	Cell Value	duplicates	text	NO
G1:G358	Cell Value	<0	text	NO

I want to show in my pivot table this type of data:

Book3
	C	D	E	F	G
5	Fill Rate	May 2022
6		TOTAL	FILL	NO FILL	FILL/TOTAL
7	Disco	357	293	64	82.07%
SUMMARY

Cell Formulas
Range	Formula
F7	F7	=D7-E7
G7	G7	=E7/D7

But I am getting this arrangement:

TESTMay 2022- Fill Rate COT FILE.xlsx
	A	B	C	D
3	CUSTOMER NAME	Fill/No Fill	Count of Fill/No Fill2	Count of CUSTOMER NAME
4	CITY OF TORONTO -DISCO	FILL	293	82.07%
5		NO FILL	64	17.93%
6	Grand Total		357	100.00%
Sheet4

Please advise.

 

[GlennUK]

Look at this and see if it covers what you want:

 

[rokunation]

I need the totals column as well. That’s the issue

 

[GlennUK]

I need the totals column as well. That’s the issue

Here is the same Pivot with Totals for rows switched on:

 

[rokunation]

Any way to show just one total…

Total of both full and no fill?

 

[rokunation]

So total of fill, no fill and total of both then the fill rate which is just fill/total.

 

[rokunation]

Can this be done by just showing the totals. then adding a calculated field with fill/total to get the fill rate?

 

[GlennUK]

I have 2 questions - why don't you just do this with formulas ... why do you have to use PivotTables? And, have you considered just hiding the columns of the PivotTable sheet that you do not want to see?

 

[SpillerBD]

Can this be done by just showing the totals. then adding a calculated field with fill/total to get the fill rate?

Cautiously, yes.
When you have a calculation that yields a percentage, at some totals/subtotals the numbers will not be accurate in certain groupings.
Some of the conversation would be better helped with an anonymised data set. It helps test and verify proposed solutions and keep terms you're using.