Add a single column to a Pivot Table

Occasional Visitor
Hi
I am at the start of a learning curve with Pivot Tables. Excel in office 365

I would like to do things properly and not hack around with kludges.

I have a particular problem which will probably have a simple (obvious) answer, well to those in the know.

I wish to add a single column to a pivot table.

Before or after the GRAND TOTAL column.

But all that happens is that i add lots of columns, one after every date column!

each day column has the count of failed products for that date.

I have grouped them into weeks.

column A is the product fault code (text)

column B onwards are the quantity of failed products for that day
(count of the product serial number) (date/time field)

The last column is the GRAND TOTAL added by the Pivot Table.

So in the PT Boxes I have,
Filter Box: empty
Column Box: Failed Date
Rows Box: Fault Code
Values Box: count of serial numbers

I just want to add one column before or after the GRAND TOTAL

to show the weekly average of the product count for preceding weeks.

the number of column will increase as the year progresses.

if i use the average function to the right of the GRAND TOTAL column (outside the PT)

it works fine until a new column is added then the PT expands and overwrites it. As you would expect - it's outside the PT.
Also the range is fixed so I would have to amend it each week.

if i try to add a calculated field I get lots of columns one to the right of each of the original data columns (date columns).

the quantity per day is grouped into 7 days, but i have tried adding the calculated column with the columns un-grouped.

I just get more columns!

what am I doing wrong?

can you not add calculated fields if the columns are date columns? Because each column is a cout of...

Thanks for an insights.

Hattie
2 Replies

@Hattie_Dog 

I'm not a specialist in Power Query ... some also mean in general :)))

 

Here is the information from Microsoft that could possibly help you in your plans.

Add and rearrange fields in the Field List

https://support.microsoft.com/en-us/office/use-the-field-list-to-arrange-fields-in-a-pivottable-4398...

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

 

@Hattie_Dog 

It looks like you need to add data to data model (if your version of Excel supports it) creating the Pivot Table and add DAX measure which calculates % to average in previous week. Without sample file it's hard to say something more concrete.