Search code examples
excelpivot-table

Excel PivotTable Count field as % of another Count field

I am using Excel from Microsoft Office 365 ProPlus.

Here's a really simple data table.

enter image description here

I want to build a Pivot Table around it to look like this...

enter image description here

... except that the "what I want" column (which is the count of items in Column C divided by the count of items in Column B) should be a part of the pivot table.

I have tried all sorts of things using calculated fields, calculations on fields, etc., to add the "what I want" column and just cannot make it work.

Can anyone help?

Solution

  • Calculated Fields only operate on the Sum of the elements in the data tables. Wherever you see a Field Name in the formula for a Calculated Field, picture it as meaning the sum of all elements for that field (that match any other row/column criteria in the Pivot Table).

    Putting "= B / C" actually means "= SUM(B) / SUM(C)" for elements of columns B and C that fit that section of the Pivot Table.

    The only way to achieve your goal is with two helper columns:

    B Count: =COUNT([@B])

    C Count: =COUNT([@C])

    The sum of these columns then give you the count of columns B and C, so you can use these helper columns to give you what you want:

    Img

    The Data Field based on the Calculated Field then says "Sum of What U Want", but it will always just be the result of your calculation, even if you change how the field is summarized through Value Field Settings. You can manually rename the Data Field, but it still needs to be different to the Calculated Field name you chose earlier.