Loss of number formatting in Pivot Table

[Avra]

I am working in Microsoft Office 365.

ZeoRelief 5 Proto Build BOM 2020 07 19 V1.xlsx
	A	B	C	D	E	F	G	H	I	J
3	Part Number	Purchase Description	Supplier	OEM	OEM Part Number	Cost	On Hand	BldQty	Purchase Requirements	Extended
4	010-001	Cord, power, 18 AWG, 5-15P, 9.84 ft	Digi-Key	Qualtek	312010-01	$7.00	4.00	5	1	$ 7.00
5	035-001	Switch, pushbutton, SPST, 3A, 250V	Digi-Key	TE Connectivity	AV1911PB12T5Q04	$26.50	6.00	5	-1	$ -
6	040-001	Fuse, glass, 6.3A, 125VAC, 5X20MM	Digi-Key	Schurter	7010.351	$2.89	12.00	5	-7	$ -
7	040-002	Fuse, drwr for pwr mod f'grip, 2pl	Digi-Key	Schurter	4301.1014.04	$9.55	6.00	5	-1	$ -
8	055-002	Sensor, probe, temperature, thermistor, NTC, -20°C - 85°C, 10kΩ	Digi-Key	TDK Electronics, Inc.	B57504K0103A009	$4.93	32.00	25	-7	$ -
9	056-001	Temperature element, Peltier, 40 x 4.0 mm, 6.0A	Digi-Key	CP60440	102-1678-ND	$23.05	20.00	15	-5	$ -
10	056-002	Temperature element, heater, cartridge, 8mm x 120mm, 110V, 250W	Amazon	Twidec	B07PNDL7SD	$9.99	5.00	5	0	$ -
11	065-001	Plug, male, pwr ent mod rcpt, 2W fuse, 8A, 125-250V	Digi-Key	Schurter	DD11.0114.1110	$10.71	6.00	5	-1	$ -
12	075-003	Power supply, AC/DC, external wall mount, 5V, 13W	Digi-Key	CUI Inc	SWI12-5-N-P5R	$13.32	5.00	5	0	$ -
13	075-005	Power supply, sw, 12V, 25A, 300W	Mouser	Mean Well	SPV-300-12	$79.23	5.00	5	0	$ -
14	076-002	Heat Sink Compound - High Density	Digi-Key	Chip Quick Inc	TC1-10G	$8.95	8.00	30	22	1.969
MRP

I have a pivot table that brings dollar amounts over from one of my queries. The problem I am running into is that some of my values are coming over as text even though the source data, the PivotTable Field (in the ∑Values area) and the Query data are all formatted as Accounting . What causes this error and how do I fix it?

 

[jasonb75]

the PivotTable Field (in the ∑Values area) and the Query data are all formatted as Accounting .

They might be formatted as accounting but that doesn't necessarily mean that they are in accounting format. Applying formats to data that starts as text does nothing, it is still text.
Try going to the source and changing the format to date, if the dollar amounts change to random dates then that tells you that they are valid accounting format. If they stay as dollar amounts then they are text strings.

 

[Avra]

@jasonb75 I reformatted everything as you suggested. Everything was and is back to accounting format. I had no text strings anywhere.

 

[jasonb75]

If the amounts didn't change to dates when you changed the format then that means that they are text strings.

$ 7.00 in proper accounting format would change to 01/07/1900 when you format it as date.

Selecting the column, then using text to columns 'might' fix it. On the first screen of the text to columns options, choose 'delimited'. On the second screen uncheck all of the boxes, click Next, then Finish.

If that doesn't work then it usually means hidden / non standard characters from the data source, this is quite common with data imported from web pages.

 

[Avra]

After checking everything you suggested, I went back to the beginning and verified the formatting of every column in all the tables, queries, and connections related to this pivot table. All were correct, which led me to revisit the measure I had created. I deleted it and tried to bring the column of data back in without a measure. This worked. I suspect that all of the format checking solved the problem allowing me to no longer need a measure. Format, format, format... Thanks @jasonb75