Performance chart for employees

[Danish Jasnaik]

Hi All,

I want to create a performance chart for my team members to assess their performance weekly and monthly. We work in a clinical data management organisation where we work on MRNs on daily basis. Our productivity is calculated as number of MRNs done (eg. 50, 60) in a month and the quality of those MRNs (error rate should be <5% for every month). Can you guys help me with excel template or how can I make a simple yet self explanatory performance chart?

Thanks for going through my question.

[kersplash]

You need to first setup what data you need to capture to create the reports you want, eg. Unique Code, First Name, Last Name, Date/Time Started, Date/Time Completed, Quality, etc.

Create a simple table with those headers assigned, and some dummy data, then start playing with pivot tables and charts and see if it suits.

[Danish Jasnaik]

Hey Kersplash,

Thank you for replying promptly. I want the MRNs to be on Y axis and months on X-axis and the error rates to be on secondary Y axis (since they are in small decimals like 1.5, 3.5 etc). Could you provide me with some templates?

[JeteMc]

Hello Danish Jasnaik and Welcome to Excel Forum.
I feel as if we will need to see some desensitized sample data set up as kersplash explained in post #2.
To upload a sample spreadsheet (not a picture or pasted copy). click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

[Danish Jasnaik]

Hi JeteMc. Thank you so much for replying. I have attached the sample data set. In this the "AVERAGE" row I want to plot it on the secondary Y-Axis to show the team their growth (ups or down).

[kersplash]

Is this the sort of thing you want?

Capture.JPG

[Danish Jasnaik]

I want the numbers except the average ones in bar format and the ones which are in "average" row to be in line format on secondary Y-axis. I am sorry if I am not so clear.

[JeteMc]

Looking at the chart in post #7, the data is charted by month, however the averages being requested are per MRN. I can't see that as being very "self explanatory". Perhaps the following will be of use. The data is normalized using Power Query (Sheet2), which I believe is a free add on from Microsoft for the 2013 version. A pivot table is then produced from the normalized data and a pivot chart from the pivot table (Sheet3). A slicer allows the user to view the data by individual MRN, which I feel is a "self explanatory" way to visualize the data.
Let us know if you have any questions.

[Danish Jasnaik]

Hi JeteMc,

Thank you for the sample. Sorry for not being clear in what I want, just that I am new to working with excel in so much depth.

I'll work on the sample sent by you and will get back to you.

Once again thanks Kersplash and JeteMc for the help.