Sign in or start a free trial to avail of this feature.
9. Comparing Sales to Averages
In this lesson, we will create a measure calculating by how much each sales person is above or below the average revenue and create a chart that can be used to evaluate performance at a glance.
Creating a chart measuring differences from average
- Having calculated the average revenue per sales person, we can easily create a measure determining the difference from average for each sales person
- Creating a chart of this measure creates an intuitive visualisation of which sales people are generating above and below average amounts of revenue
- We can also add conditional formatting to make the distinction clearer
- Unfortunately, some of these formatting changes are not preserved when the file is saved, due to a known issue with Excel
- Alt H, S, O – Sort PivotTable from largest to smallest
- Alt B, F – Create new measure
- Alt H, S, S – Sort PivotChart from smallest to largest
In the previous lesson, we created a combo chart that displayed the revenue generated by each salesperson as well as the average revenue generated by all salespeople. In this lesson, we'll create a chart that displays the difference between the average revenue amounts and the revenue generated by individual salespeople.
Our first step is to create a new pivot table and put it on a new sheet.
We'll navigate to the sales table and add sales person, revenue, and the average sales person revenue measure.
In the previous lesson, we excluded the salespeople Marcus and Stefani on the grounds that their low revenue figures were not representative of the company as a whole. We'll be consistent and remove them from this pivot table.
We'll select the arrow at the top of the column and unselect Marcus and Stefani.
We excluded these salespeople when we created the average sales measure so we don't need to make any further changes.
Our pivot table now shows the revenue for each salesperson and the average of these revenue figures.
We'll sort the table by revenue by selecting a cell in the revenue column and sorting with Alt + H + S + O.
Remember, our goal is to create a chart that displays the difference between revenue generated by individual salespeople and the average. In order to accomplish this, we'll need to create a measure that calculates the difference between revenue and the average revenue for each salesperson.
We'll create a new measure and call it difference from average revenue.
The formula for this measure will be the sum of revenue minus the average salesperson revenue measure we created previously.
We'll format the measure as currency with US Dollar sign and select OK to create it.
We can see that this column is showing us how much each salesperson revenue is above or below the average.
Recall that before we filtered out the two salespeople, there were only four salespeople with below average revenue figures.
There are now only four salespeople with above average revenue figures.
It's clear that the two sales people we'd removed were having a significant impact on the average revenue.
Let's create a pivot chart so we can gain a visual understanding of this difference.
We'll select any cell in the pivot table, insert a pivot chart, select a bar chart and press OK.
The chart has three bars but we only want to look at one of them.
We'll navigate to the values area and remove both sum of revenue and average revenue.
Our pivot chart now shows the amount by which each salesperson is above or below the average revenue.
This chart makes more sense if it's ordered so we'll select the series, and sort with Alt + H + S + S.
There are a few more changes we can make to increase the effectiveness of this graph.
We currently can't see all the salesperson names so we'll right click the vertical axis and select Format Access.
We'll scroll down through the options to Labels and set the Label Position as low.
Finally, we'll add some conditional formatting.
We'll right click one of the bars, and select Format Data Series.
We'll then select Fill and set it to Solid Fill.
We'll also check the Invert if Negative option and then set the two colors as green and red.
We can now see that any salesperson with above average revenue will have a green bar and any sales person with below average revenue will have a red bar.
Note that while we compared salespeople in these last two lessons this technique could also be applied to products, regions, or other categories.
As such, you'll likely come across may situations where this technique could be applied to gain greater insight into your data.