Excel Pivot Table - Percentages with common denominator

Question

I have a database with error codes in it from a production line. Something like this

Code - Line - Date - ErrorQty E1 - Line 2 - 01.01.2001 - 7

E1 - Line 1 - 01.01.2001 - 1

E2 - Line 1 - 01.01.2001 - 2

E2 - Line 2 - 01.01.2001 - 3

E1 - Line 1 - 02.01.2001 - 7

E2 - Line 1 - 02.01.2001 - 4

E1 - Line 2 - 02.01.2001 - 5

E2 - Line 2 - 02.01.2001 - 8

For each Line / Day combination I have a total. Lets say 100 each day.

I need a format where the pivot can calculate the correct percentage even if I group/consolidate the data to say all errors or all lines or months.

At the moment I am only able to have it at the code/line/day configuration, otherwise my totals also sum up and I get a kind of average percentage.

For example line 2 on 02.01.2001 I have 5 and 8 and a total production of 100. So I have E1 = 5% and E2 = 8% -- Total = 13%, but in my current configuration I duplicate the total so I get 6.5%

Any thoughts?

Addition:

So you can see the duplicated Totals. This is the total for the Date/Line

Data Source

Pivot Field

Update

Now I finally have the Rep to add a hyperlink, here is an example file.

In the Pivot for Line 1 it has a total of 0,28% - but this should be 5,01% (the summation of the percentages). I know this needs re-configuring to work but I cannot work out how

So it needs to add up the percentages for the grouping of error codes, but calculate cumulatively for all other factors.

Example file on GoogleDrive

You need to show your current configuration. Also, if your "ErrorQty" is a percent (or if your "total" is always 100, and ErrorQty is a count), then just SUM them. If it is not a percent, then how are you calculating the percent from the data you supply? — Ron Rosenfeld
@witchchild As pnuts wrote, add a column in your source. There are issues with pivot tables, consolidations, and calculated fields that are best dealt with that way. I understand that there is a Power Pivot option in Excel 2010+ that may deal with this problem, but I have 2007 so cannot advise. — Ron Rosenfeld
@witchchild The way your data table is set up, since the Totals cells do not relate to the individual line, it's not clear to me how that would fit into a Pivot. I still think the best option would be to add a column calculated as errors/totals for each line, and use that in your Pivot. — Ron Rosenfeld

guitarthrower guitarthrower · Accepted Answer · 2014-06-06T14:38:02

In the Values section of the PivotTable pane, click on the item you want as a percentage and select Value Field Settings. Select whether you want the percentage based on Sum, Count, or some other method, then click on the Show Values As tab on the form.

The drop down here will give you a lot of summarizing options. Some examples

% of Grand Total: Provides the item's % of all items in the Pivot Table, regardless of grouping
% of Column Total: Performs item's % of all items in the same column of the Pivot Table, regardless of grouping
% of Parent Row Total: (I think) Performs item's % of grouped items. (I think this is what you are looking for)

There are a lot of other options, and they are quite powerful, but will need some experimentation to make work with your data.

Excel Pivot Table - Percentages with common denominator

1 Answers