Excel PowerPivot Average Calculation

Question

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.

Answer 1

Zam, this should be quite easy. You just need to create a new calculated field that calculates the average for ALL sales rep.

Let me walk you through it:

I used your data table and then added it to my PowerPivot (Excel 2013). Then I created those calculated measures:

1. Sales Average:

=AVERAGE(SalesData[SalesGP])

2. Sales Average ALL -- this will calculate the average for ALL rows in the table and will be used in other calculations. Notice I used the first calculated field as the first parameter to make this flexible:

=CALCULATE([Sales Amount Average],ALL(SalesData))

3. Sales Comparison to Average. I wasn't sure what is your goal, but I made this one a bit more complex as I wanted to display the performance in percentage:

=IF([Sales Amount Average] > 0, [Sales Amount Average] / [Sales Average ALL] -1)

Basically, what this does is that first it checks if their an existing calculation for a sales representative, if so then it divides Sales Average for a given sales representative by the average sale amount for ALL sales representatives. And then I subtract 1 so the performance can be easily grasped just by looking at the percentages.

To make it easy-to-understand, I decided to use bar charts in conditional formatting instead of stand-alone pivotchart -- I believe it does exactly what you need:

In the picture above, the first table represents your data. The second table is the actual powerpivot table and shows what I have described.

Hope this helps!

EDIT

I didn't want to make things over-complicated, but should you want to remove the percentage total from Grand Totals row, use this calculation instead for the percentage comparison:

=IF(HASONEVALUE(SalesData[SalesRep]),
    IF([Sales Amount Average] > 0, 
        [Sales Amount Average] / [Sales Average ALL] -1),
    BLANK()
)

EDIT - ADDED AVERAGE COMPARISON FOR WORKGROUPS

To calculate the performance per workgroup instead of ALL sales representative, add those two measures:

4. Sales Average per Workgroup

=CALCULATE(AVERAGE(SalesData[SalesGP]),ALL(SalesData[SalesRep]))

This will calculate the average sale per workgroup (don't get confused with using SalesRep in ALL function, it's related to filter context).

5. Sales Diff to Average per Workgroup

=IF(HASONEVALUE(SalesData[SalesRep]),IF([Sales Amount Average] > 0, [Sales Amount Average] - [Sales Average per Workgroup]),BLANK())

This simply calculates the difference between average sale of given sales rep and the average sale per workgroup. The result could then look like this:

I have uploaded the source file to my public Dropbox folder.