Hi all,
Let me describe what help I am looking for.
I want to create a Driver Performance dashboard around the data I have in the attachment.
So, I want to setup a page with a combo box that I would select a driver name, this in turn would display details of this driver on this page. One thing I want to do is compare the selected driver to other drivers on the same shift and compare average hours per Work order which is the WK Avg column.
I want to compare only the last 16 weeks of the data, so I am looking at how to create a "moving average" on my data as I add data every week.
I then want to chart all the drivers on the same shift and compare to the driver selected from the combo box for the past 16 weeks.
I hope this makes sense!
Hi James,
Thanks for sharing a sample file.This makes it a lot easier for us to help you.
I'd use a PivotTable and Slicers for this, assuming you have Excel 2010 or later. In the attached file you'll see an example. I've summed the Wk Avg. but you can change it to Average if you want to see the average of averages!
I've inserted Slicers for the Shift. Clicking on these will display the drivers for that shift type in the chart and PivotTable. There's also a Slicer for the Route, not sure if you need/want this though. You can add/remove Slicers as required.
I've added a helper column to your Data sheet to classify the data into the last 16 weeks or older. It returns a TRUE/FALSE result and I've used this field in the Filters area of the PivotTable and set it to show TRUE results only. When you add new data you just need to refresh the PivotTable and it will automatically include the new weeks and drop off any that are > 16 weeks ago.
I hope this helps point you in the right direction. Let me know if you have any questions.
Mynda
Hi Mynda,
Thanks for your reply and your example which I will review. Sorry I have taken so long to reply, I was pulled into another project and only just remembered I asked a question of this forum.
I will get back with you with any questions, again, thanks for the reply!!
Hi Mynda,
I have had sometime today to review your example and I like what you have come up with.
I am having issues getting to the result I want for the next step, please review the tab named "Analyze".
On the chart - blue line is the average of all drivers on this shift say "Day". The red line is the driver I select from the combobox who I want to compare that is on the same shift, I want to compare his performance to others.
Is there a way to do this on one sheet rather than two??
Thanks
Hi James,
Is there a reason why you're still using combo boxes and not Slicers to do the filtering?
Mynda
Hi James,
If you use Slicers you can use 2 PivotTables; one for the selected driver and one for the total average and plot your chart from a combination of the two. See example file attached.
You could achieve what you want from one PivotTable but it would mean more complicated formulas and it's simpler to do it the way I've provided in the sample file. In other words, there's nothing to be gained by having one PivotTable vs two.
Mynda
