I'm using Sharepoint 2007 and sharepoint designer, and I am using multiple sharepoint lists that have the same column types (# required, # complete, % complete) that shows the training status for different departments in my organization. For example, I have List1 (Sales), with a row, Title: safety training, # required (number of people in that department): 10, # complete: 8, % complete: 80.
List1 goes into KPIList1 and is displayed on a KPI webpart on the Sales page in my site collection (Lets call it Dunder Mifflin).
List2 (Administration) is a similar list (#required: 4, # complete:2, % complete: 50) that is displayed on its own KPI webpart on the Administration page in the same site collection.
The endstate is an overall KPI on the main page that reflects the overall safety training status for the organization (in this case 65% complete). I want each department to be able to update their own training status and have that automatically update the Overall KPI.
What I've been trying to do is import the rows from List1 and List2 onto an "overall" sharepoint list (List3), so I can calculate an average of the two, either in the List3 sharepoint list or in a KPI list.
I tried going the data view route, but to my knowledge it won't let me calculate an average and I don't think I can display the data view in a KPI webpart. You can probably tell from reading this that I don't have much programming experience, so step-by-step directions and/or screenshots are very helpful.