Sir/Madam,
I have been racking my brains to try solve this issue.
Basically I have a table of data which I need to perform a calculation on. It is a summary calculation for the day based on 4 or 5 different entries that day.
This is an excerpt from my table.
Date Available Time Max Design Speed Total OEE pieces
22/01/2018 130 38 2340
22/01/2018 320 38 4050
22/01/2018 250 38 4150
22/01/2018 230 38 3740
For a particular machine (named Carrig) 4 orders were processed. I need to determine the overall performance of that machine for the full day using a calculation called "Overall Equipment Effectiveness - OEE". This calculation simply divides the total pieces manufactured by the "available time*max design speed". Therefore for the example shown this should be [14280/(38*930)] = 40.4%.
In the calculated field, the formula sums the #pieces manufactured and the available time, which is correct. However, I want it to average the max design speed but it seems to sum them also, giving a result which is a quarter of the correct answer (because there were 4 separate entries).
I have tried to use the following formula within the pivot as a calculate field but to no avail
='Total OEE Pieces' /('Available Time' *AVERAGE('Max Design Speed (PPM)' ))
Any assistance would be greatly appreciated as I am running out of hair to pull!!!
Regards,
jb
I have been racking my brains to try solve this issue.
Basically I have a table of data which I need to perform a calculation on. It is a summary calculation for the day based on 4 or 5 different entries that day.
This is an excerpt from my table.
Date Available Time Max Design Speed Total OEE pieces
22/01/2018 130 38 2340
22/01/2018 320 38 4050
22/01/2018 250 38 4150
22/01/2018 230 38 3740
For a particular machine (named Carrig) 4 orders were processed. I need to determine the overall performance of that machine for the full day using a calculation called "Overall Equipment Effectiveness - OEE". This calculation simply divides the total pieces manufactured by the "available time*max design speed". Therefore for the example shown this should be [14280/(38*930)] = 40.4%.
In the calculated field, the formula sums the #pieces manufactured and the available time, which is correct. However, I want it to average the max design speed but it seems to sum them also, giving a result which is a quarter of the correct answer (because there were 4 separate entries).
I have tried to use the following formula within the pivot as a calculate field but to no avail
='Total OEE Pieces' /('Available Time' *AVERAGE('Max Design Speed (PPM)' ))
Any assistance would be greatly appreciated as I am running out of hair to pull!!!
Regards,
jb