Pivot table errors

[Bshira]

I'm trying to make a Pivot table that will sum up filtered data and the filtered data in the pivot table doesn't match the source data with the same filters applied to it. I have two other pivot tables on the same sheet doing the same thing with different filters and those pivot tables have no issues getting the correct sums. This table however seems to be adding 5% to the sum of the source data. I've checked the precision and formatting and everything is the same.

 

[Bshira]

I'm trying to make a Pivot table that will sum up filtered data and the filtered data in the pivot table doesn't match the source data with the same filters applied to it. I have two other pivot tables on the same sheet doing the same thing with different filters and those pivot tables have no issues getting the correct sums. This table however seems to be adding 5% to the sum of the source data. I've checked the precision and formatting and everything is the same.

here is a mini sheet

bookeeping sheet.xlsx
	A	B	C	D	E	F	G	H	I
5	Date	Name of Job - Description	Amount	Tax Zone	Gen / Sub	Gross on Tax Return	Tax Due	Date Paid	CHK #
57	5/4/2023	City Tax - Big Horn	$ 139,106.41	City Tax	Gen.	$ 132,558.04	$ 6,871.86	5/4/2023	10852
61	5/18/2023	City Tax - MALC	$ 82,000.00	City Tax	Gen.	$ 78,139.89	$ 4,050.80	5/18/2023
84	Total		$ 221,106.41			$ 210,697.93	$ 10,922.66
85
86
87
88
89
90
91			Tax Zone	No Tax
92			Gen / Sub	(Multiple Items)
93
94			Row Labels	Sum of Amount
95			May	$ 174,272.50
96			Grand Total	$ 174,272.50
97
98			Tax Zone	County Tax
99
100			Row Labels	Sum of Amount	Sum of Tax Due
101			May	$123,787.62	$4,505.87	$ (6,416.79)
102			Grand Total	$123,787.62	$4,505.87
103
104			Tax Zone	City Tax
105
106			Row Labels	Sum of Amount	Sum of Tax Due
107			May	$221,106.41	$10,922.66
108			Grand Total	$221,106.41	$10,922.66
Sales Tax 9-01-21 to current

Cell Formulas
Range	Formula
F57,F61	F57	=IF([@[Gen / Sub]]="Sub", [@Amount], IF([@[Gen / Sub]]="REF", "Non Taxable Refund", IF([@[Gen / Sub]]="Agremnt", "Not taxable Agreement", IF([@[Tax Zone]]="City Tax",ABS((C57/1.0494)*0.0494-C57), IF([@[Tax Zone]]="County Tax", ABS((C57/1.0364)*0.0364-C57), "Error1")))))
G57,G61	G57	=IF([@[Gen / Sub]]="Sub","Non-Taxed Subcontract", IF([@[Gen / Sub]] = "REF","Non-Taxed Refund",IF([@[Gen / Sub]]="Agremnt","Non-Taxed Agreement", IF([@[Gen / Sub]]="Gen.", IF([@[Tax Zone]]="County Tax",([@Amount]*0.03445), IF([@[Tax Zone]]="City Tax",([@Amount]*0.0494),"Error1"))))))
F84	F84	=SUBTOTAL(109,[Gross on Tax Return])
G84	G84	=SUBTOTAL(109,[Tax Due])
C84	C84	=SUBTOTAL(109,[Amount])
F101	F101	=GETPIVOTDATA("Sum of Tax Due",$C$100)-Deposits[[#Totals],[Tax Due]]

 

[Alex Blakenburg]

Can you try double clicking on the 4505.87 ?
It should create a new sheet with the lines making up that number which might provide some insights.
Before doing that maybe right click the pivot and do a refresh just in case that’s the issue.

 

[Bshira]

Can you try double clicking on the 4505.87 ?
It should create a new sheet with the lines making up that number which might provide some insights.
Before doing that maybe right click the pivot and do a refresh just in case that’s the issue.

Refreshing seemed to work on that, Much appreciated. I did try a refresh initially but on all the tables and it was still giving me that 5% greater on Friday. Maybe it was because it was a Friday

 

[Alex Blakenburg]

You're welcome. Glad that worked for you.
A couple of things that would influence the refresh are, are you using Power Query in which case do you have background refresh turned off ? and are all the Pivots using the same table as the source, in which case we could look into whether they are using the same cache (although refresh all should still refresh them all)