The Manager’s Essential Guide to Filters, Pivoting and Totals

Last Updated: Tuesday, October 30, 2012 by

Getting a KPI dashboard that displays key information to the right people makes an incredible difference to the operating efficiency of any business. However before getting to a great dashboard you need to have your data in shape, and then know what you can do with it.

In this Post I am going to use a simple sales example to explain three principles that are often misunderstood and can be confusing. These three principles are:

  • Totalising (to Total Up the numerical values)
  • Filtering
  • Pivoting

Let’s imagine…..

…we have a sales team of 4 people, “Fred”, “Jane”, “John” and “Gail”

….and each sales person sells “Widget Machines” and installation of these machines.

In our business, our sales manager and other managers might have some sales questions:

1. What are our total sales this month compared to last?

2. What is our split of sales between Machine Sales and Installation?

3. What is the Trend of machine sales?

4. Who is our best sales person and how do they compare to the other guys?

Obviously there are many other views of the sales data that would be required, but the above four questions require our data to be “shaped” correctly, and once shaped correctly it’s possible to use it in many other ways.

Let’s try to make this example more visual, but adding a simple dashboard chart for each question:

1. What are our total sales this month compared to last?

 

2. What is our split of sales between Machine Sales and Installation?

 

3. What is the Trend of machine sales?

 

4. Who is our best sales person and how do they compare to the other guys?

 

 

If we are smart, we can use one set of data to answer all the questions.

Let us start with question 1, the “total sales”.

We could have our data in a very simple table like this with one row of data for every month.

Month

 Sales($)

Jan-12

123

Feb-12

234

Mar-12

456

...etc

 

This type of data is easy to plot on a chart, but this format of data is not very flexible when it comes to answering any of our other business questions. We need to consider a more flexible format, but with this more flexible format comes a little more complexity.

Instead of storing one row for every month, we need to store one row for every combination of “sales person” and “product type”, for example. Let’s take “John” as an example:

Month

 Sales Person

Product Type

Value

Jan-12

John

Machine

$123

Jan-12

John

Installation

$111

Feb-12

John

Machine

$345

Feb-12

John

Installation

$456

...etc

Note here that we now have a row for every month and every combination of product type. The “Value” column is the “sum” total of all sales of that product type.

Now let’s add “Jane” into the same table

Month

Sales Person

Product Type

Value

Jan-12

John

Machine

$123

Jan-12

John

Installation

$111

Jan-12

Jane

Machine

$323

Jan-12

Jane

Installation

$211

Feb-12

John

Machine

$345

Feb-12

John

Installation

$456

Feb-12

Jane

Machine

$245

Feb-12

Jane

Installation

$156

...etc

Now imagine repeating this for all sales staff. With all the data in a single table, this becomes our “core data table” from which we can find all our answers.

Data like this is a little more awkward to look at in this state, but is hugely powerful.

Totalising (Total Up by Month) the Columns

Returning to our first question, “total sales”, we simply need to “Total Up” the “Values” column by month. Target Dashboard does this automatically for you and allows you to plot a chart of the total sales month on month. It’s a little more tricky to do this in MS Excel, but very possible.

 

Categorised Data

In question two, we want to see a comparison of our different types of sale. As our data has now got “Categories” we can easily plot a category column again the Sales value column over a fixed date range. For example, let’s say we want a pie chart of the split of product sales for last quarter. We can now plot “Product type” V’s “Value” on a pie chart.

 

Filters

In question three (What is the Trend of machine sales?) we want to see just a subset of our core table. For this we need to throw away any data that is not “machine” sales.

Looking at our core table again,

Month

Sales Person

Product Type

Value

Jan-12

John

Machine

$123

Jan-12

John

Installation

$111

Jan-12

Jane

Machine

$323

Jan-12

Jane

Installation

$211

Feb-12

John

Machine

$345

Feb-12

John

Installation

$456

Feb-12

Jane

Machine

$245

Feb-12

Jane

Installation

$156

We now only want the highlighted data, so to get this we need to apply a filter:

Product Type = “machine”

Once you have done this the underlying data we would plot on our chart would be:

Month

Sales Person

Product Type

Value

Jan-12

John

Machine

$123

Jan-12

Jane

Machine

$323

Feb-12

John

Machine

$345

Feb-12

Jane

Machine

$245

...etc

We might then “Totalise” this data so we ignore the Salesman, thus ending up with:

Month

Value

Jan-12

$446

Feb-12

$590

...etc

Now with our filtered data, we can:

  • Create a time based chart showing the trend of machine sales
  • Create a gauge showing the latest machine sales values
  • Display a summary of tabular data just for machine sales

Target dashboard will do this all for you automatically.

If we chose not to “totalise” the data, we could apply more filters such as:

Product Type = “machine” AND Salesman=”John”

Again, with this we can chart trends, show gauges or create tabular summaries.

 

Pivoting

Perhaps the most confused and misunderstood element of data shaping is Pivoting.

Why do you want to pivot data?

The answer is – you want to pivot data when you want to make a comparison between two different categories of data.

In the examples above, we have shown how easy it is to filter the data to pull out just one category of the data. For example we can easily pull out “John” sales of “machines”. The process of filtering effectively pulls John’s data, but throws away all the other data. This means that with filtering it is not possible to compare one set of sales against another. For example, say you want to compare “John” sales to “Jane” sales, this is not possible with filtering and we need to pivot the data.

Pivoting the data allows us to make comparisons.

Pivoting data requires us to pick one Category column and one Numeric column from our data. Look again at our core table:

Month

Sales Person

Product Type

Value

Jan-12

John

Machine

$123

Jan-12

John

Installation

$111

Jan-12

Jane

Machine

$323

Jan-12

Jane

Installation

$211

Feb-12

John

Machine

$345

Feb-12

John

Installation

$456

Feb-12

Jane

Machine

$245

Feb-12

Jane

Installation

$156

...etc

For us to compare our sales people’s total sales against each other, we can pivot the columns

Sales Person V’s Value

Pivoting in Excel is a little tricky, see this youtube video on how to do it.

Target Dashboard will make your life easy and pivot your data in just 2 clicks. Once pivoted around “Sales Person and Value”, the underlying data we can chart looks like this:

Month

John

Jane

Fred

Gail

Jan-12

$245

$543

$123

$234

Feb-12

$701

$401

$123

$123

...etc

You can see that this has swapped our rows from columns and also totalled the sales. Now we can very easily create a chart that shows our sales guys compared to each other.

 

 

You can take this a stage further and add filters enabling you to compare Jane and John sales for Product Type= Machine.

In Target Dashboard when you pivot your data it also works out the averages, so if you wanted to compare your sales guys to the average sales per person you can. For a more indepth look at pivoting data, take a look at our online dashboard best practice guide.

 

Summary

If you look at one chart or indicator you want to add to your corporate dashboard in isolation, then you will find there may well be several ways to layout your raw data. However, when you consider that in most cases you will want to filter and compare data it makes sense to try to store your data in a format that can achieve all of your goals with minimal effort.

If you lay out data in a categorised format ie

Date Column, Text Category Column, Values Column

Then the dashboard and charting possibilities are endless.

You can

  • Total Up numeric columns to get overall totals by month
  • Use Filters, to pull a subset of data
  • Use a Pivot table when you want to make comparisons

To most people this can look a little complicated, but if you format your data as suggested then Target Dashboard makes filters easy and pivots automatic, saving you lots of headaches and time.