Calculated Field in PivotTable (or alternative solution to this problem)

[excelnoobee]

Hello all,

I’m having difficulty trying to figure out how to include (either in a Pivot Table, or in the underlying dataset) a calculation that is different for two separate values (2016 and 2017) in a field/column of a given dataset...so that I can graph this one field and show the correct numbers for the two different periods.

So, for all 2016 records (which are full-year 2016 results), and for a given set of data (filtered on: Business Group = Finance, in this case) the calculation (in a new computed field/column) must be “the sum of [Short Term Days Lost] column” divided by “the sum of [Average Employee Count] column” for any/all filtered data. I’ve shown this (calculation) in the top screenshot of the following image:

Name: PivotTable-Screenshot.jpg Views: 0 Size: 115.5 KB

For all Q1-2017 records, and within the same dataset, the calculation (in the same above-mentioned column) must be “the sum of [Short Term Days Lost] column” divided by “the sum of [Average Employee Count] column” * (53 / 13) for any/all filtered data. The latter part of this formula [* (53 / 13)] is what is different from the one for 2016 data (since the data for 2017 comprises just one Qtr, whereas the 2016 data is for a full year). I’ve shown this (calculation) in the bottom screenshot of the above image.

For Q2-2017 data (once it comes in), the above formula will have the 13 (number of weeks in one quarter) replaced with 27 (the number of weeks in two quarters..and so on, and so forth till the fourth quarter of 2017.
In my example screenshots, the calculations are created in cells B1 and D1 (C1 and E1 are just pasted values of the aforementioned formula cells)...but I really want them to be in the Pivot Table itself, since the Pivot Table is used to generate a chart that (should) show the correct numbers for 2016, and subsequently for each of the quarters of 2017.

I have tried creating calculated fields in the Pivot Table, as well as (helper columns) in the dataset, but neither of those are giving me the right numbers. Perhaps I’m doing something wrong, or I just don’t know how to do this correctly.

What I’m currently thinking of doing is to create two separate calculated fields in the pivot table, and since both these calculated fields will be included in the data/values area of the pivot, with each field displaying an incorrect number for either the 2016 or 2017 sections, I’m going to have to copy and paste (as values) the pivot table to a separate sheet and then delete the two incorrect values columns via VBA code. Messy, and perhaps unnecessary I’m thinking!

Going down a different path, I’ve also thought about creating smaller tables that use array formulas, but that’s not proving to be as flexible (nor easy to setup) for my dashboard selection controls, since I want to be able to allow the end-users to filter down a couple of levels in the organization structure (using filters), and then (using checkboxes) they should be able to cherry-pick which levels they’d like to see data for (in the graphs).

The following image shows the Dashboard controls I have setup to allow the user to both filter-down to the required data, and then select which org level numbers they'd like to see represented in the graphs:

Name: Dashboard.jpg Views: 0 Size: 155.1 KB

The following image shows how the PivotTable and connected PivotChart look/work. The green boxes show the correct numbers, whereas the red boxes show incorrect numbers (per the following calculated fields I've setup in the PivotTable:

Calculated Field calc2016 in PivotTable: =('Short Term Days Lost'/'Average Employee Count')
Calculated Field calc2017 in PivotTable: =('Short Term Days Lost'/'Average Employee Count') * (53 / 13)

Name: PivotAndGraph.jpg Views: 0 Size: 110.9 KB

Any other ideas/help/suggestions will be greatly appreciated.

Thanks.

[dflak]

I'll take a shot but I will need a good set of non-sensitive data and what you want the answers to look like. After I get the data I'll look more closely at the pictures above.

[excelnoobee]

dflak, thank you for the offer to help...much appreciated!

I will try and get you some non-sensitive data, and perhaps any additional (expected) output.