I am having some difficulty determining how to produce a calculation of averages that can be plotted on a PivotChart.
Specifically, I wish to compare a Sales Rep's performance (gross profit by month/year) against all other reps (using an average) who are in a comparable role (the same workgroup) for a given period.
Let's just say the data structure is as follows:
SaleID SaleLocation SaleType SalesRep SaleDate WorkGroup SalesGP
1 Retail1 Car John A 01/01/2014 Sales $301
2 HQ Bike John A 01/01/2014 Sales $200
3 Retail1 Car Sam L 02/01/2014 Sales $1300
4 Retail2 Plane Sam L 02/01/2014 Sales $72
5 Retail2 Plane Vince T 03/01/2014 Admin $55
6 Retail2 Bike John A 04/01/2014 Sales $39
7 HQ Car Vince T 05/01/2014 Admin $2154
....etc
In the excel data model I've added calculated fields (that use a lookup table) for the sale date so that sales can be plotted by Month or Year (eg. =YEAR([SaleDate]) and =MONTH([SaleDate]))
As an example, let's say I want to plot someone's GP (Gross Profit) for a period of time:
My question is this......
How can I calculate an "average gross profit" that I can plot on the PivotChart? This "average gross profit" should be the average of all sales for the same period for the same workgroup.
In the example above, in the PivotChart I am wanting to plot an "average" series which plots the average GP by month for all SalesReps that are in the same Workgroup as John A ("Sales").
If my request isn't clear enough please let me know and I'll do my best to expand.