Pivot table averaging issue/question

[Stephen.R]

I have a complaints spreadsheet which records complaints over the year by customer, week number/ date, type of product, sub type, the type of complaint, and whether it has been accepted or rejected.
The current pivot table works absolutely fine - see attached showing all complaints for type UHT.

As you can see there are 4 types of complaints in that week - Appearance, Packaging, Packaging (Leak), Texture.
The column this is pulling from is a free field text so over the year the same type of complaint can be written in different ways.
To improve this it has been set to a list.
However as this was a free text field there were sometimes as many as 3 separate complaints on the same product -
e.g. Blown, Mould, Packaging (cap) etc.
As such two other columns were added so that all complaint 'types/ reasons' related to the complaint could be recorded.

I thought this would be a simple addition to the pivot table of the two other columns but instead it sub totals them and counts them as each individual occurrence of the complaints against said product type that week:
e.g. Packaging - Appearance - (blank)

Any help would be appreciated, its driving me round the bend.

 

[Stephen.R]

If someone can point me in the right direction even that would be brilliant too, just everything I search for seems to lead to the wrong thing.

 

[GlennUK]

Your original question is hard to follow - in what way does it subtotal the two other columns, and in what way does that not give you what you want?

 

[Stephen.R]

Your original question is hard to follow - in what way does it subtotal the two other columns, and in what way does that not give you what you want?

Hi Gennn,
Apologies for the confusion. I have attached a link to a stripped out version.
If you look at the second tab you can see the pivot table that pulls through all complaints.
However as the information is pulled from three columns rather than summing all instances of a type of complaint with each sub category of product it separates that count out to each column

Mock Limited Customer Complaints 2022 WIP.xlsx

 

[GlennUK]

If you mean that you want the 3 Reason Code Descriptions to be counted as if they are one field, then transform the table into a list before generating the PivotTable.