Calculating Columns/Fields in PowerPivot

Good morning Excel Forum Gurus, experts and junkies,

I need your assistance. I would consider my skills with Excel to be good...not Godly but good! However, I have very little experience with PowerPivot and think this would be a better tool than creating several macros to create the report. I have uploaded the data I am using to create my report. The problem I am running into is creating some calculated columns. Some additional background on the data I have attached.

Bench Mark Data
So this is a table of production times based on batch size, product type and customer. The first Batch time column is if the tech works 1 device specific to that product type and customer then it will take that many minutes to complete. The second column is if the batch size contains 2 devices and then that time needs to be multiplied by 2, and the third column is if the batch size is 3 and the time in that column should be multiplied by 3 to get Expected Total Elapsed Time. Then the fourth column represents a batch size of 4 or more and then that time needs to be multiplied by the size of the batch to get Expect Total Elapsed Time.

Tech Time Report
So this is the raw data by tech with all of the time worked per batch of devices.

Tech Production Report
Is the PowerPivot table I am trying to create.

Currently, my pivot table includes by tech, the product worked, customer the product belongs to, the elapsed time per batch and then the batch size.

I want to be able to...

1. Calculate what the techs expected lapsed time should be with the criteria provided.

2. Calculate the percentage of actual time to expected time.

I will also be eventually adding a time clock portion to this pivot table to calculate utilization, but that is for a different day. I first want to get the above accomplished first and build from there.

Can ANYBODY help me out?!?!?!?!!?

Richard-

I reviewed the data from the Tech Time Report and need see additional issues with the elapsed time as you pointed out and need to review the reporting from the application that creates this report and determine where the glitch is. Lets ignore that for the time being...in the simple form, I have 2 data sheets. One consisting of production by tech with customer and device type information and the second being the standard to which tech production gets measured(Bench Mark Data). I want to be able to calculate the production a tech is doing relative to the bench mark...so if the bench mark is 15 minutes per device A for Customer 123 and tech 1 has a production time of 18 minutes per device A for Customer 123, then tech 1 could use additional coaching or need to see if they were working on something else at the same time. The bench times fluctuate based on the quantity of devices being worked per batch and the calculation would need to take that into consideration. Does that make it any easier?

I am open to other suggestions to calculate productivity...

I am going to have to review a little bit further but it looks like this was more the route I was trying to go. Is there any way to incorporate these types of calculations within PowerPivot?

OK...I get everything and I think I could probably work with this however I really need to calculate expected time based on batch size not by an average time of the batch sizes. So if a record has a batch size of 4+ and another record, with the same product and same customer,has a batch size of 2, the time gets counted accordingly...

ex...

Tech works on ItemId X and has 3 different batches they worked...batch 1 contained 2 devices, batch 2 contained 6 devices and batch 3 contained 3 devices, then batch one would be calculated using the value in column E on the bench marks worksheet for that ItemId, batch 2 would be calculated using the expected time from column G of the bench mark worksheet for that ItemId, and then batch 2 would be calculated using the time in column F for that ItemId.

What is the best way to do this? Is it even possible?

Richard-

How would I convert your formula of

Formula:

Please Login or Register  to view this content.

To a VBA code to enter into a specified column?

Thank you for your help!!!