Forum Discussion
Hide zero values in pivot table
Hi,
I'm trying to control whether salary deductions and invoices match. I have pivot table set up like so:
Company A Company B
Person A 0
Person B 100
Person C 100 -100
Person A has a salary deduction in company A and a invoice for the same amount issued to the same company. Person B has had been debited by invoice, but a salary deduction has not been amde. Person Cs salary deduction was made in company B, but his invoice is issued to Company A.
I wish to hide Person A, since he checks out. However when I set the filters to hide all zeroes, my pivot table hides Person C as well since the sum across the companies totals zero.
Any ideas?
It seems that this cannot be done unless you change the source data by adding a helper field to tell the pivot table that a specific person has already paid.
Please find the attached file.
Hi Ludvig,
Try to drag the value/salary field to the Filter area in the pivot table task pane, then filter all values except the zero, and select Show Multiple Items, and then hit OK.
Please find the attached file to see this.
Regards
Haytham
- Hi Haytham. Thank you for your response.
I must have been unclear on one point.
The data will contain the list of people invoiced and the list of people with deductions.
Therefore the data will look like this:
Person A Company A 100
Person A Company A -100
Person B Company A 100
Person C Company A 100
Person C Company B -100
There are no zeroes in the data. The table is supposed to sum the values for each person, and hide all people where the deduction and the invoice sums to zero in the same column.It seems that this cannot be done unless you change the source data by adding a helper field to tell the pivot table that a specific person has already paid.
Please find the attached file.
