Pivot Table Help

rgillson3

Board Regular
Joined
Oct 3, 2013
Messages
81
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?
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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

Board Regular
Joined
Oct 3, 2013
Messages
81
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>
 
Last edited by a moderator:

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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).
 
Last edited:

rgillson3

Board Regular
Joined
Oct 3, 2013
Messages
81

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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.

<b>Unknown</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">FY-Per</td><td style=";">INVOICE NUMBER</td><td style=";">Sum of Invoice BALANCE</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">F17-P02</td><td style="text-align: right;;">2069444</td><td style="text-align: right;;">200</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;">2072360</td><td style="text-align: right;;">317.09</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;">2072361</td><td style="text-align: right;background-color: #FFFF00;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;">2072362</td><td style="text-align: right;;">317.09</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;">2072363</td><td style="text-align: right;;">317.09</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;">2072364</td><td style="text-align: right;;">317.09</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;">2072365</td><td style="text-align: right;;">317.09</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;">2072366</td><td style="text-align: right;;">317.09</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;">2072367</td><td style="text-align: right;;">317.09</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;">2072368</td><td style="text-align: right;;">317.09</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;">2072369</td><td style="text-align: right;;">317.09</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;">2072370</td><td style="text-align: right;;">317.1</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">F17-P02 Total</td><td style="text-align: right;;"></td><td style="text-align: right;;">3370.91</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />
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

Board Regular
Joined
Oct 3, 2013
Messages
81

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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

Board Regular
Joined
Oct 3, 2013
Messages
81
Code:
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 NUMBERFY-PerTIME FRAMEDate of Voucher / PaymentTotal
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-P122034797 (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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top