Pivot Table Help

[rgillson3]

I'm maintaining a list of vouchers, which I enter when the vouchers are issued and when the payment(s) are received.

I can adjust the filter to get the correct balance through a particular fiscal period.

But, I'm trying to generate a detailed list of invoices whose balances are not $0, with the "filtering". But, every time move the fields to show the invoice detail, the balance becomes incorrect.

Any ideas?

 

[Jerry Sullivan]

I'm not understanding what you mean by "But, every time move the fields to show the invoice detail, the balance becomes incorrect."

Please clarify, or better, post a screen shot showing your the organization of your source data and the layout of your pivot table.

 

[rgillson3]

I'm not understanding what you mean by "But, every time move the fields to show the invoice detail, the balance becomes incorrect."

Please clarify, or better, post a screen shot showing your the organization of your source data and the layout of your pivot table.

I'm unable to, or don't know how, to post screen shots. But, here is a copy of the PivotTable and part of the Table I'm trying to work with.

The only way I've found to show the invoice numbers, is to move the field "up" in the rows section. Then, the totals become all out of whack. I'd like to be able to filter the fiscal period, and show a list of the invoices with non-zero balances, along with their balance and a total.

	$6,861.00
Sum of Auth Amt/ (Amt Pd)
FY-Per	VOUCHER NUMBER	TIME FRAME	Date of Voucher / Payment	Total
				211,488.95
F10-P01				(9,367.50)
F10-P02				(7,590.00)
F10-P03				(10,337.50)
F10-P04				(13,915.00)
F10-P05				(11,505.00)
F10-P06				(17,870.00)
F10-P07				(6,305.00)
F10-P08				(7,362.50)
F10-P09				(24,462.50)
Grand Total				6,861.00

<tbody>
</tbody>

Invoice BALANCE	INVOICE NUMBER	Date-Ext	FY-Per
0.00	1557410
200.00	2069444	11/30/16	F17-P02
317.09	2072360	11/30/16	F17-P02
317.09	2072361	11/30/16	F17-P02
317.09	2072362	11/30/16	F17-P02
317.09	2072363	11/30/16	F17-P02
317.09	2072364	11/30/16	F17-P02
317.09	2072365	11/30/16	F17-P02
317.09	2072366	11/30/16	F17-P02
317.09	2072367	11/30/16	F17-P02
317.09	2072368	11/30/16	F17-P02
317.09	2072369	11/30/16	F17-P02
317.10	2072370	11/30/16	F17-P02
0.00
0.00	2072486	12/01/16	F17-P03
0.00
0.00	2069563	12/05/16	F17-P03
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00

<tbody>
</tbody>

 

[Jerry Sullivan]

Is Voucher Number in your Pivot Table the same field as Invoice Number in your data source?

Is "Sum of Auth Amt/ (Amt Pd)" in your Pivot Table summarizing data "Invoice Balance" in your data source?

Why aren't any values showing up on under Voucher Number (did that happen in posting, or are those blank in your PivotTable)?

Edit: ....or have you collapsed (grouped) the PivotTable by Fiscal Period before copying that image?

Edit 2: With your okay, I'll delete some rows from your Pivot Table image so it isn't so long to read (we just need a few rows to understand the organization).

 

[rgillson3]

Is Voucher Number in your Pivot Table the same field as Invoice Number in your data source?

Is "Sum of Auth Amt/ (Amt Pd)" in your Pivot Table summarizing data "Invoice Balance" in your data source?

Why aren't any values showing up on under Voucher Number (did that happen in posting, or are those blank in your PivotTable)?

Edit: ....or have you collapsed (grouped) the PivotTable by Fiscal Period before copying that image?

Edit 2: With your okay, I'll delete some rows from your Pivot Table image so it isn't so long to read (we just need a few rows to understand the organization).

Yes, the Voucher Number and Invoice Number are one in the same. I'm sorry. Apparently I forgot to change the other title.

The "Sum of Auth Amt/(Amt Pd)" appears to be the default title of the Pivot Table.

I do have the Voucher Numbers collapsed. Otherwise, the table shows the balance of each Voucher within each period, taking up 3479 rows.

The deletion is fine. I was having difficulty getting the information "loaded".

Again, my goal is a listing of the Voucher/Invoice numbers that I can filter down by period to show the correct total through that period.

 

[Jerry Sullivan]

Ok, I think I understand.

I mocked up this unfiltered PivotTable. Before creating the Pivot, I changed the balance of the highlighted invoice to 0, to demonstrate the filtering.

Unknown
	I	J	K
4	FY-Per	INVOICE NUMBER	Sum of Invoice BALANCE
5	F17-P02	2069444	200
6		2072360	317.09
7		2072361
8		2072362	317.09
9		2072363	317.09
10		2072364	317.09
11		2072365	317.09
12		2072366	317.09
13		2072367	317.09
14		2072368	317.09
15		2072369	317.09
16		2072370	317.1
17	F17-P02 Total		3370.91
Sheet1

To filter this PivotTable to hide the zero balance invoice:
Right-click on the Invoice Number field header > Filter > Value filters... >

You should see a dialog titled Value Filter (INVOICE NUMBER)
In the three fields under "Show items for which"....
select from dropdown "Sum of Invoice BALANCE"
select "does not equal"
enter 0

The PivotTable should now hide the row with the 0 balance invoice.

Assuming that works for you, on your previous attempts you might have missed the step of first selecting a cell in the Invoice Number field. If you right-clicked on a cell in the FY-Per field then did the same value filters step, the Pivot Table would only filter out rows in which the sum of the invoices for that FY-Per equal 0.

 

[rgillson3]

Ok, I think I understand.

I mocked up this unfiltered PivotTable. Before creating the Pivot, I changed the balance of the highlighted invoice to 0, to demonstrate the filtering.

Unknown

	I	J	K
4	FY-Per	INVOICE NUMBER	Sum of Invoice BALANCE
5	F17-P02	2069444	200
6		2072360	317.09
7		2072361
8		2072362	317.09
9		2072363	317.09
10		2072364	317.09
11		2072365	317.09
12		2072366	317.09
13		2072367	317.09
14		2072368	317.09
15		2072369	317.09
16		2072370	317.1
17	F17-P02 Total		3370.91

<tbody>
</tbody>

Sheet1​

To filter this PivotTable to hide the zero balance invoice:
Right-click on the Invoice Number field header > Filter > Value filters... >

You should see a dialog titled Value Filter (INVOICE NUMBER)
In the three fields under "Show items for which"....
select from dropdown "Sum of Invoice BALANCE"
select "does not equal"
enter 0

The PivotTable should now hide the row with the 0 balance invoice.

Assuming that works for you, on your previous attempts you might have missed the step of first selecting a cell in the Invoice Number field. If you right-clicked on a cell in the FY-Per field then did the same value filters step, the Pivot Table would only filter out rows in which the sum of the invoices for that FY-Per equal 0.

Ok, that's a BIG step towards exactly what I'm trying to accomplish. As I deselect FY-Per 03, 02 and 01, the balance changes accordingly, which is GREAT.

But, the Pivot Table is still maintaining all Invoices listed within their periods.

I've found that if I "move" the Invoice number field "up", the Pivot Table will show a more consolidate listing of only non-zero balance Invoices.

Is there a way to display the consolidated list, without doing the "move".

 

[Jerry Sullivan]

Could you post an image showing what you want the PivotTable to look like. I'm not following your description. The method you used to post the spreadsheet image is fine.

 

[rgillson3]

Could you post an image showing what you want the PivotTable to look like. I'm not following your description. The method you used to post the spreadsheet image is fine.

This is the best I can do.

This is leaps and bounds ahead of where I was, and I can definitely work with it.

But, I didn't know if there was a way to eliminate the need to "move" the field in order to get the condensed list, and still be able to filter by period.

	$11,234.00
Sum of Auth Amt/ (Amt Pd)
VOUCHER NUMBER	FY-Per	TIME FRAME	Date of Voucher / Payment	Total
1926393				0.00
1926394				0.00
1927342				0.00
1931059				0.00
1988397				(0.00)
2052237				340.00
2052238				440.00
2058304				260.00
2057484				301.00
2060211				800.00
2057380				(140.00)
2064393				85.00
2064394				85.00
2064395				85.00
2064396				85.00
2064397				85.00
2059624				140.00
2059729				140.00
2062321				232.53
2062322				232.53
2062323				232.53
2062324				232.53
2062325				232.53
2062326				232.53
2062327				232.53
2062328				232.53
2062329				232.53
2062330				232.53
2062331				232.53
2062332				232.53
2062333				232.53
2062334				232.53
2062335				232.58
2065040				240.00
2055977				800.00
2055978				800.00
2055979				800.00
2055980				800.00
2055981				800.00
2055982				800.00
Grand Total			11,234.00

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

Here is an example of how the Pivot Table looks before I move the field. It shows all of the invoices with activity within each period, regardless of whether activity in subsequent periods result in a zero balance.

F16-P12	2034797			(240.00)
	2050367			(60.00)
	2055856			(380.00)
	2052237			(220.00)
	2035987			(210.00)
	2055864			(800.00)
	2055865			(800.00)
	2055866			(800.00)
	2055867			(800.00)
	2055868			(800.00)
	2055869			(800.00)
	2042252			(630.00)
	2044208			(210.00)
	2048869			(140.00)
	2052637			(420.00)
	2046251			(735.00)
	2050518			(1,867.00)
	2050521			(1,867.00)
	2050526			(1,000.00)
	2050527			(1,000.00)
	2056908			(218.00)
	2056909			(218.00)
	2056910			(218.00)
	2056911			(218.00)
	2056912			(218.00)
	2056913			(218.00)
	2056914			(218.00)
	2056915			(218.00)
	2056916			(218.00)
	2056917			(218.00)
	2056918			(218.00)
	2056919			(218.00)
	2056920			(218.00)
	2056921			(218.00)
	2056922			(218.00)
	2056923			(218.00)
	2058304			(130.00)
	2057484			(813.25)
	2060211			580.00
	2057380			(140.00)
	2064393			85.00
	2064394			85.00
	2064395			85.00
	2064396			85.00
	2064397			85.00
	2059624			140.00
	2059729			140.00
	2062321			232.53
	2062322			232.53
	2062323			232.53
	2062324			232.53
	2062325			232.53
	2062326			232.53
	2062327			232.53
	2062328			232.53
	2062329			232.53
	2062330			232.53
	2062331			232.53
	2062332			232.53
	2062333			232.53
	2062334			232.53
	2062335			232.58
	2065040			240.00
	2055977			800.00
	2055978			800.00
	2055979			800.00
	2055980			800.00
	2055981			800.00
	2055982			800.00

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

 

[Jerry Sullivan]

Sorry, but I don't understand what isn't working on the second image that has a Row Label hierarchy of FY Period > Invoice. There are no invoices shown with 0 balances.

It shows all of the invoices with activity within each period, regardless of whether activity in subsequent periods result in a zero balance.

I've been assuming that your field labeled "Total" is the Invoice BALANCE field since that's the only data field in the sample source data you showed. Do you have additional data fields? It's a different problem if you are trying to filter based on a field that isn't in the PivotTable report.