MS365 Pivot table column contains data but is displayed as 0

copperwasher

Board Regular
Joined
Mar 1, 2016
Messages
84
Office Version
  1. 365
Platform
  1. Windows
Hi there,

I have a basic pivot table set up to send statements to our business partners.
The only filter is the company name of our partner.

The job ref should display the job ref number/letters as per the sheet it is connected to (Range).
Some of the company job data is displayed perfectly, others, the filled cell data is displayed as a 0.

Ex1 pivot table working, job refs displayed correctly as per the data source sheet.
Co.
partner1
Row Labelsjob refsSum of £ jobSum of paid bankedBalance Owed
2022£0.00
Dec
07/12/2022259992£552.00
28/12/2022260907£600.00£1,752.00
2023
Jan
03/01/2023265307£1,200.00£600.00
Grand Total786206£2,352.00£2,352.00



Ex2, same data source (Range), the job refs are in the cells (range), but displayed as 0 here, as well as the correct data!!!
Co.partner2
Row Labelsjob refsSum of £ jobSum of paid bankedBalance Owed
2022-£4,218.00
Dec
08/12/20220£252.00
12/12/20220£504.00
19/12/20220£600.00
2023
Jan
09/01/20230£558.00
16/01/20230£600.00£1,104.00
Feb
27/02/20230£696.00
Mar
20/03/202323115006£624.00£1,158.00
21/03/202323125504£600.00
29/03/20230£342.00
Apr
13/04/202323147740£624.00
14/04/202323147868£480.00
17/04/202323147811£600.00
Grand Total115683929£6,480.00£2,262.00

All of the values in the 'job ref's' are filled with the relevant job ref details, but some of the cells have been replaced with zeros...

Also, the grand total column, for the job ref, is displaying a total of 115683929, which is not required.
How do I blank this cell?

Any help would be greatly appreciated.

My thanks in advance.
Gary
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
It looks like the job refs field should be a row field, not a value field.
 
Upvote 0
It looks like the job refs field should be a row field, not a value field.
Hi RoryA

Thank you for replying...
Row field... how does that explain that some of the selected partner companies job ref data works perfectly, yet others do not?
Yes, the source range is the same, and so are the pivot table settings for the job refs column.
I am a bit confused...

What is odd, is I share this file via Google Drive, and after I opened this file online, the pivot table had issues when opening again on the desktop.

Prior to this mornings google drive session, the pivot table was working perfectly !!!

Any thoughts??
 
Upvote 0
It suggests some of the ref numbers are text (so they sum to 0) and the others are numbers.

Job numbers are not data that you should be aggregating, so they should not be value fields (hence the fact you don’t want a total of the job numbers).
 
Upvote 0
It suggests some of the ref numbers are text (so they sum to 0) and the others are numbers.

Job numbers are not data that you should be aggregating, so they should not be value fields (hence the fact you don’t want a total of the job numbers).
I have checked the source column again...
All data in the source column is set as a number...

I have also checked random cells in the column, they are all set as numbers.

I have an impression (from google search) that my file is corrupted!!!
As some of the partner filters work as intended, and others dont, even though they use the same column (range)
 
Upvote 0
What do you mean by “set as numbers”? Cell formats do not necessarily reflect cell contents.
If you remove any manual cell alignment, do some numbers appear left aligned and some right aligned?
 
Upvote 1
I worked it out...
Well, I know why the data in the Pivot Table column is 0

The source table column contains cell data with numbers (that works), but some cells contain text and numbers, hence the zero

So, I think I need to understand how to concatenate ???
 
Upvote 0
As I said before, that field should be a row or column field, not value field, because you are not doing calculations on it.
 
Upvote 1
If i move the job ref to the rows field, I get this layout...
Which does not work for multiple job refs, as its too long

Co.xxxxxx
Row LabelsSum of £ jobSum of paid bankedBalance/owed
2023£1,170.00-£1,170.00
Mar
15-Mar
BHL-202623-1£540.00
Apr
03-Apr
BHL 206091-1£630.00
Grand Total£1,170.00

Ideally the job ref data needs to be displayed in its own column... is this possible (when the source cell contains numbers and text in some cases ??
 
Upvote 1
Yes, you can choose to display any/all fields in tabular layout rather than compact/outline form. Just right click the field above the job ref field and choose field settings, then on the layout tab choose to show item labels in tabular form.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

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
Back
Top