Complex data processing in pivot tables (averages)

Question

I'm plotting yield for products. I have two ways of classifying products, lets say these two ways are first as numbers: 1, 2, 3, 4, ect, and then as letters, a, b, c, d, e, ect. The letters are a subcategory of the numbers. So within products, I have many different letters within individual number groups:

1: a, b, c, e

2: c, d

3: a

For each product, I've calculated yield and plotted it in a pivot table. I've plotted the pivot table so that the "letters" are a subcategory of the "numbers" within the column labels, please see picture below (please note that in the picture the subcategory is labelled using numbers as well, the "letters" subcategory is just to try to explain with more contrast in this question)

This is all well and good, but I'd like to find an average of everything within the "numbers" umbrella category. However, there's one final problem to consider with the task: the amount of products that contribute to a yield average may be different per letter subcategory. I.e:

1 - a: 2 out of 2 products yielded = 1 (fractional yield)

1 - b: 2 out of 22 products yielded = 0.09 (fractional yield)

This leads to a problem while using pivot tables. Using the above yield fractions, if I was using the pivot tables's "average of yield" to work out the total yield for all the "1" group, it would calculate:

1 + 0.09 / 2 .... = 0.54 (fractional yield)

But we can see that this isn't a true representation of yield, as it should be:

(2 + 2) / (2 + 22) =

4 / 24 =

0.17 (fractional yield)

Can anyone work out a way of calculating true yield with pivot tables rather than the average of an average?

Answer 1

I got it!

I went here: https://support.office.com/en-nz/article/Calculate-values-in-a-PivotTable-report-697406b6-ee20-4a39-acea-8128b5e904b8

And adapted this section:

Create a formula in a PivotTable report
Add a calculated field        
1. Click the PivotTable report.
2. On the Options tab, in the Tools group, click Formulas, and then click Calculated Field.
3. In the Name box, type a name for the field.
4. In the Formula box, enter the formula for the field.
5. To use the data from another field in the formula, click the field in the Fields box, and then click Insert Field. For example, to calculate a 15% commission on each value in the Sales field, you could enter = Sales * 15%.
6. Click Add.

to fit my copy of Excel 2010 by doing the following:

Create a formula in a PivotTable report
Add a calculated field        
1. Click the PivotTable report.
2. On the Options tab, in the Calculations group, click "Fields, Items, and Sets", and then click Calculated Field.
4. In the Name box, type a name for the field.
5. In the Formula box, enter the formula for the field (=Yielded/Total).
6. To use the data from another field in the formula, click the field in the Fields box, and then click Insert Field. For example, to calculate a 15% commission on each value in the Sales field, you could enter = Sales * 15%.
7. Click Add.

This gives the correct answers you were looking for.