Pivot calculated fields - dropping needed fields without dropping calculations

Hi,

You can add a Calculated Field. You can do this by selecting the "Reg" or "OTP" under an employee name in the Pivot Table field.
Under Pivot Table options select "Fields, Items, & Sets", then select "Calculated Item". Create a new Formula in the "Time Coding" Field such as "=OTP/REG". Give it a name of your choosing. Select "Add".

Then filter out the REG and OTP to only leave your new formula.
In the Values field of the Pivot Table field controls select the "Sum of Hours", change the "Show Values As" to "No Calculation".
Change the "Number Format" to Percentage. Then OK.

The Pivot Table should then display the data you are requesting.

See attached.

Hope this make sense and is helpful.

Cheers

Thanks-- It does work-- is there a way to get it to total on the far right? also no way to rename it back to OTP versus formula is there? Seems when i type it in it recognizes that as the filtered item and pull is the wrong calculation.

Hi,

Glad to hear that it worked for you.

Yes, you can show Total to the right. Have to filter out the (blank) from dates.

You can create a formula with the name "OTP ". (notice the space after the "P"). This will distinguish it from the original.

See attached.

Cheers

Ok I need to be able to view this another way.

Within each site, I need to have the OTP within that site for the week ending date as well as against all the sites together. Only for Nonexempt employees and I want to be able to filter on scope (already in the filter)

So for an example:

For OTP % within the site
Fort Washington on the OT% Within Site for 2/4/17 this should calculate

2/4/17

29 OTP/ 287 total hours= 10.1%

I also want it to be able to calculate % based on total hours for all sites. So in Fort Washington would under 2/4 would be 287/3751 = 7.7%

It would be great if in the total’s at the end of each date range it could be the total of that site for the OT% within a site

Same goes for the % based on total for all sites.

These could also be two separate pivots and would like to be able to filter on scope (already in filter)

I am not following your example.

In the spreadsheet the data for For Washington on 2/4/17 is 7 OTP and 126 Reg.

I am not able to figure out what you are wanting.

Ok i realize i wasn't that clear. So I would like to be able to see if its possible to get % of OTP hours within the site itself and overall for each scope.

So Fort Washington, 2/4 looking at 50 Scope: OTP % for that scope within that site would be 5/287. Can I have that calculate by choosing scope in the above filter? I would want it to change for each scope that a user chooses. Also-- Is there a way to keep REG in the calculations but drop it from the report for viewing?

OK. For something like that you will have to get down into the weeds yourself. I think that once you establish a filter in a Pivot Table it will return values as they pertain to those filters.

You will have to get into the weeds with personalized formulas. Essentially you will have to identify the various criteria that will return the value 5 (Date, Scope, Non-Exempt, OTP, Hours) and divide it by the criteria that will return the 287 (Date, Reg, Hours, Non-Exempt).

You can setup your field, setup some filter pull down cells and use a SUMIFS formula divided by another SUMIFS formula.

Attached is an example using your data, in the "Exp" Sheet.

Hope this makes sense and is helpful.

Cheers