MS365 Pivot table column contains data but is displayed as 0

[copperwasher]

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 Labels	job refs	Sum of £ job	Sum of paid banked	Balance Owed
2022				£0.00
Dec
07/12/2022	259992	£552.00
28/12/2022	260907	£600.00	£1,752.00
2023
Jan
03/01/2023	265307	£1,200.00	£600.00
Grand Total	786206	£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 Labels	job refs	Sum of £ job	Sum of paid banked	Balance Owed
2022				-£4,218.00
Dec
08/12/2022	0	£252.00
12/12/2022	0	£504.00
19/12/2022	0	£600.00
2023
Jan
09/01/2023	0	£558.00
16/01/2023	0	£600.00	£1,104.00
Feb
27/02/2023	0	£696.00
Mar
20/03/2023	23115006	£624.00	£1,158.00
21/03/2023	23125504	£600.00
29/03/2023	0	£342.00
Apr
13/04/2023	23147740	£624.00
14/04/2023	23147868	£480.00
17/04/2023	23147811	£600.00
Grand Total	115683929	£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

 

[RoryA]

It looks like the job refs field should be a row field, not a value field.

 

[copperwasher]

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??

 

[RoryA]

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).

 

[copperwasher]

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)

 

[RoryA]

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?

 

[copperwasher]

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 ???

 

[RoryA]

As I said before, that field should be a row or column field, not value field, because you are not doing calculations on it.

 

[copperwasher]

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 Labels	Sum of £ job	Sum of paid banked		Balance/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 ??

 

[RoryA]

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.