Good Afternoon Everyone,
I work at a manufacturing facility and am trying to organize the data for all of out Preventative maintenance items. Primarily, by calendar week how many were assigned and completed, and the percentage of how many were completed before their due date.
What I've done so far:
-Used Power Query to import data from excel sheets that I download from SAP
-added a couple of helper columns,
one with a 1 on every row(index),
one with a 1 if the PM was not completed before its finish date (Overdue)
one that shows the calendar week the PM is due
-Used Pivot tables to count assigned and completed PMs by work center
The "schedule attainment" calculation is the one I'm having trouble with.
I've tried
- Custom field in pivot table with the equation: 1-COUNT('Overdue_Y/N') /SUM(Index)
-this one seems to work on the "daily", but messes up when summarized into the calendar week, or month
I'm medium skilled with excel, but relatively new to query and pivot. Any advice is greatly appreciated.
Bookmarks