Calculated Field in Pivot

[sheridan60]

Hello,

I am new to Pivot Tables so please bear with me. I am trying to determine monthly costs for each job, represented by a WO#. My timesheet data is downloaded from Replicon but it does not contain the rates. I have the rates in a separate table. I have created a Pivot and joined the two tables. Now, I can see the number of hours each employee has logged for each job and I can see their rate but I need to calculate the total cost based on that employees rate. I tried to create a calculated field but it is disabled. I read that calc fields are available for all types of data except OLAP source data. I don't know what OLAP data is, but don't think my data is OLAP. Any ideas?

Unfiltered Results

Filtered using a slicer

[#2]

@sheridan60 How are you importing the source data?  Does the download from Replicon go directly into a pivot table or does it first go into a table from which you are creating the pivot?  

In the helper table for rates you created, is it possible to pull information (Hours) from the Replicon datasource and calculate the total cost there (bypassing the need for a calculated item)? 

Dexter 

[#3]

@DexterG_III I am provided with a file from HR/Payroll. Apparently this is my only option. I had to join the two tables myself. I just figured out that I can use VLOOKUP and add the total for each line item to the spreadsheet I was given. I now have a total per work order per entry, but cannot get a total per work order overall. I'm not sure a pivot table is gonna work for me.

[#4]

@sheridan60 While Vlookup will pull a value from a single cell (the first match in the lookup table - a single workorder entry), using sumif will pull values from several cells.  Using sumif should allow you to pull the total by workorder rather than workorder entry.  

[#5]

Thanks. I'll work on that.

[#6]

Can I use sumif/sumifs if I have 3000 different identifiers, without entering detail for each one?

[#7]

@sheridan60 As long as you have the common keys in both tables, yes.  For example, this formula uses both username and Workorder Number.  It's just one formula which, if entered in a table object, will spill to all rows in that table.  

=SUMIFS(Table3[Hours],Table3[Workorder],[@Workorder],Table3[Username],[@Username])