8. Visualizing and Manipulating Averages

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Overview

When evaluating performance, we often want to compare individuals to an average. In this lesson, we will create a chart of revenue by sales people and use DAX to create a dynamic average line.

Summary

Calculating Average Revenue by Sales Person

  • To calculate the average revenue generated per salesperson, we divide the total revenue amount by the number of salespeople in the dataset
  • We use ALL to see all salespeople in the dataset
  • This measure is constant for every salesperson, so we can add it to a chart as a target line

Removing two salespeople

  • In this situation, we decide to remove two salespeople whose low revenue was significantly affecting the overall average
  • We do this by adding simple additional conditions to the calculate function
  • If taking a step like this, ensure you can provide a business justification for it

Keyboard Shortcuts

  • Alt H, S, O – Sort chart from largest to smallest
  • Alt B, F – Create a new measure
  • Alt JC, C – Change chart type

Transcript

In the next two lessons, we'll see how to create charts that allow you to compare data to average values.

We'll see how you could easily identify which salespeople are generating above average or below average amounts of revenue. Note that you could also apply this technique to products, regions, or any other category of data in your dataset.

In this lesson, our goal is to create a combo chart showing the revenue for each salesperson, as well as the average revenue generated per salesperson. Let's create a simple pivot chart of revenue by salesperson.

We'll put the chart on a new sheet, navigate to the sales table, and add revenue and salesperson.

Next, we'll add a constant line to this chart, showing the average revenue generated by salesperson. We'll do this with a measure.

We'll create the new measure, call it average salesperson revenue, and enter a calculate function.

The expression will be the sum of revenue divided by a distinct count of the salesperson column.

The filter condition will be the all function applied to the salesperson column.

We want this measure to be constant, which is why we're using the all function on the salesperson column.

We'll close the formula, format the measure as currency with the symbol set to dollar sign, and select okay.

Let's now add this measure to the chart.

Note that it gets added as another set of bars.

We'll right-click any column in the new series, then select change chart type.

We'll select combo chart, change the average revenue series to align, and press okay.

At this point, we'll sort the original series by revenue, by selecting any bar, and sorting with Alt, H, S, O.

Let's also add a data label for a single point on the average line to see the value.

We'll right-click the data point for the salesperson Mitel, and select add data label.

We'll then drag the data label above the line, so we can see it better.

We now have a reasonable column chart showing us revenue for each salesperson and the average revenue per salesperson.

On average, each salesperson is generating approximately 1.49 million dollars.

We can use this chart to see which salespeople are generating above average revenue, and which are generating below average revenue.

However, there are a few issues with this chart.

First, we can see that there are two salespeople, Stephanie and Barcus, who have generated significantly less revenue than the others.

Because there are only 15 salespeople in the dataset, this appears to distort the average sales figure.

Currently, there are only two other salespeople below the average, which is fewer than we would expect.

Let's assume there are specific circumstances relating to Stephanie and Barcus, which make their sales roles different from the other salespeople.

For example, maybe they only work part-time.

We'll assume we can justify removing them from our analysis. We'll do this by modifying the measure we created.

Let's select manage measures, and edit the average salesperson revenue measure.

Currently, we calculate the average revenue for all salespeople.

We'll add new filter conditions to the calculate function, that ensure that the calculation leaves out Stephanie and Barcus. Previously, we've only used one filter condition in our calculate functions. However, you can use multiple conditions if needed.

We'll add a comma after the all function, to add a new condition.

The first new condition will be that salesperson is not equal to Barcus.

The second new condition will be that salesperson is not equal to Stephanie.

The calculate function is now determining the average sales for all salespeople, except Stephanie and Barcus.

We'll press okay to accept the change to measure.

We can see the average revenue line has moved up, however the bars for Stephanie and Barcus are still present.

We'll delete these bars by selecting the salesperson filter in the bottom left of the chart, and un-selecting Barcus and Stephanie.

Now our chart is accurate.

This chart makes it easy to identify which salespeople generate above average or below average revenue, and to compare salespeople to each other by their total revenue.

However, it's not particularly easy to measure how much each salesperson is above or below the average revenue figure.

To solve this problem, we'll need to create a new bar chart.

We'll address this in the next lesson, by creating a chart that compares the difference between revenue generated by each salesperson and the overall average.

Excel Excel for Business Analytics Learning Plan
Practical Applications of DAX

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial