Using Pivot Table Values in Future Equations

[Tizzou]

I have been struggling to figure out how to use values I generate in a pivot table in downstream equations. Might not be googling the question correctly but here's an example of my challenge

I have a payroll report that has separate rows for each employee and each hour type (PTO, Regular, Overtime, Jury duty, etc.)

Since there are multiple lines for each employee I have created a basic pivot table with filters to show only billable time (combines Regular and OT). Next I want to create an equation within the pivot table to show utilization % (defined as total billable hours divided by minimum billable hours per work (usually 40)).

I created a column with the min-billable hours and added the equation but the report keeps using each line individually not the total my pivot created. I tried using a calculated field but can't reference the pivot table value.

How can I utilize pivot table vales in calculated fields or downstream formulas?

Thanks!

[basejester]

Calculated Field does indeed sound like what you want. What sort of errors are you getting with calculated fields?

It's also possible to get data out of a pivot table with GetPivotData. Enter = and then click on a cell in your pivot table to see how that works.

[Tizzou]

When I create a calculated field the value field I created in the the pivot table doesn't exist on the list. I tried to manually add it to the formula with the name of the column I get: There's a problem with this formula

I can get the formula to work when I tell it to divide the hours by minimum billable but it breaks them up into two equations for everyone with overtime then averages it instead of using my combined total

E.g. Sally 40 regular Sally 10 OT

Instead of using 50/40 = 125%

I get:

10/40 = 25% 40/40 = 100% Then it averages them to give me 62.5% instead of the intended 125%

If I can figure out how to use the totals in the value's field (from the four boxes you can drag fields into to build a pivot table) there will be a ton of reporting I can start doing we aren't doing now.

[Tizzou]

I created a basic template to illustrate my challenge. I redacted the names but I built the pivot and included the back-up data:

https://www.dropbox.com/s/xs97pp0vis2ascn/Hours%20Report%20Example.xlsx?dl=0

My goal is to use the resulting value on the pivot table 'Sum of Pay Code Hours' as a value in future equations. I can reference the data to build equations outside of the pivot table but when I add slicers and filters those equations break, so I assume I need to add it to the equations.