Chapter 14: Using Pivot Tables

In This Chapter

• Using pivot tables as your data model

• Creating and modifying a pivot table

• Customizing pivot table fields, formats, and functions

• Filtering data using Pivot Table views

In Chapter 11, we discuss using a data model as the foundation for your dashboards and reports. This data model helps you to organize your information into three logical layers: data, analysis, and presentation. As you discover in this chapter, pivot tables lend themselves nicely to this data model concept. With pivot tables, you can build data models that are easy to set up and that can then be updated with a simple press of a button. So you can spend less time maintaining your dashboards and reports and more time doing other things. No utility in Excel enables you to achieve a more efficient data model than a pivot table.

Introducing the Pivot Table

A pivot table is a tool that allows you to create an interactive view of your source data (commonly referred to as a pivot table report). A pivot table can help transform endless rows and columns of numbers into a meaningful presentation of data. You can easily create groupings of summary items — for example, combine Northern Region totals with Western Region totals, filter that data using a variety of views, and insert special formulas that perform new calculations.

Pivot tables get their namesake from your ability to interactively drag and drop fields within the pivot table to dynamically change (or pivot) the perspective, giving you an entirely new view using the same source data. You can then display subtotals and interactively drill down to any level of detail that you want. Note that the data itself doesn’t change and is not connected to the pivot table. The reason a pivot table is so well suited to a dashboard is that you can quickly update the view of your pivot table by changing the source data that it points to. This allows you to set up both your analysis and presentation layers at one time. You can then simply press a button to update your presentation.

Anatomy of a pivot table

A pivot table is composed of four areas: Values, Row Labels, Column Labels, and Filters. The data you place in these areas defines both the use and presentation of the data in your pivot table. We now discuss the function of each of these four areas.

Values area

The Values area allows you to calculate and count the source data. In Figure 14-1, it is the large rectangular area below and to the right of the column and row headings. In this example, the Values area contains a sum of the values in the Sales Amount field.

The data fields that you drag and drop here are typically those that you want to measure — fields, such as the sum of revenue, a count of the units, or an average of the prices.

Figure 14-1: The Values area of a pivot table calculates and counts the data.

Row Labels area

The Row Labels area is shown in Figure 14-2. Dragging a data field into the Row Labels area displays the unique values from that field down the rows of the left side of the pivot table. The Row Labels area typically has at least one field, although it’s possible to have no fields.

The types of data fields that you drop here include those that you want to group and categorize, such as products, names, and locations.

Column Labels area

The Column Labels area contains headings that stretch across the top of columns in the pivot table, as you can see in Figure 14-3. In this example, the Column Labels area contains the list of unique business segments.

Figure 14-2: The Row Labels area of a pivot table gives you a row-oriented perspective.

Figure 14-3: The Column Labels area of a pivot table gives you a column-oriented perspective.

Placing a data field into the Column Labels area displays the unique values from that field in a column-oriented perspective. The Column Labels area is ideal for creating a data matrix or showing trends over time.

Filter area

At the top of the pivot table, the Filter area is an optional set of one or more drop-down controls. In Figure 14-4, the Filter area contains the Region field, and the pivot table is set to show all regions.

Placing data fields into the Filter area allows you to change the views for the entire pivot table based on your selection. The types of data fields that you drop here include those that you want to isolate and focus on — for example, region, line of business, and employees.

Figure 14-4: The Filter area allows you to easily apply filters to your pivot table, focusing on specific data items.

Creating the basic pivot table

Now that you have a good understanding of the structure of a pivot table, follow these steps to create your first pivot table.

You can find the example file for this chapter on this book’s companion website at www.wiley.com/go/exceldr in the workbook named Chapter 14 Samples.xlsx.

1. In the Chapter 14 sample file, go to the tab called Sample Data and click any single cell inside the source data (the table you’ll use to feed the pivot table).

2. Click the Insert tab on the Ribbon.

Find the PivotTable icon, as shown in Figure 14-5.

3. From the drop-down list under the PivotTable icon, select PivotTable.

Figure 14-5: Start a pivot table by clicking the PivotTable icon found on the Insert tab.

This opens the Create PivotTable dialog box, as shown in Figure 14-6.

4. Specify the location of your source data.

5. Specify the worksheet where you want to put the pivot table.

In Figure 14-6, note that the default location for a new pivot table is New Worksheet. This means your pivot table will be placed in a new worksheet within the current workbook. To change this, select the Existing Worksheet option and specify the worksheet in which you want to place the pivot table.

Figure 14-6: The Create PivotTable dialog box.

6. Click OK.

At this point, you have an empty pivot table report on a new worksheet.

Laying out the pivot table

Next to the empty pivot table, you see the PivotTable Fields List dialog box, as shown in Figure 14-7.

You can add the fields you need into the pivot table by dragging and dropping the field names to one of the four areas found in the PivotTable Fields List — Filters, Columns, Rows, and Values.

If clicking the pivot table doesn’t activate the PivotTable Fields List dialog box, you can manually activate it by right-clicking anywhere inside the pivot table and selecting Show Field List. Alternatively, you can go to the Ribbon, click Option, and then select Field List in the Show group.

Now before you start dropping fields into the various areas, ask yourself two questions: “What am I measuring?” and “How do I want to see it?” The answers to these questions will help guide you in determining which fields go where.

For your first pivot table example, you want to measure the dollar sales by market. This tells you that you need to work with the Sales Amount field and the Market field.

Figure 14-7: The PivotTable Fields List dialog box.

How do you want to view that? You want markets to go down the left side of the report and the sales amount to be calculated next to each market. You need to add the Market field to the Row Labels area and the Sales Amount field to the Values area.

1. In the fields list, select the Market field (see Figure 14-8).

Now that you have regions in your pivot table, it’s time to add the dollar sales.

Figure 14-8: Select the Market field to add it to the fields selector list.

2. In the fields selector area, select the Sales Amount field (see Figure 14-9).

Figure 14-9: Add the Sales Amount field.

Placing a check next to any field that is non-numeric (text or date) automatically places that field into the Row Labels area of the pivot table. Placing a check next to any field that is numeric automatically places that field in the Values area of the pivot table.

One more thing: When you add new fields, you may find it difficult to see all the fields in the box for each area. You can expand the PivotTable Fields List dialog box by clicking and dragging the borders of the dialog box to avoid that problem.

As you can see, you have just analyzed the sales for each market in just nine steps! That’s an amazing feat, considering you start with over 60,000 rows of data. With a little formatting, this modest pivot table can become the starting point for a dashboard or report.

Modifying the pivot table

Now here’s the wonderful thing about pivot tables. For your data model, you can add as many analysis layers as possible by changing or rearranging the fields in your source data table. Say that you want to show the dollar sales each market earned by business segment. Because your pivot table already contains the Market and Sales Amount fields, all you have to add is the Business Segment field.

So simply click anywhere on your pivot table to reactivate the PivotTable Fields List dialog box and then select the Business Segment field. Figure 14-10 illustrates what your pivot table now looks like.

If clicking the pivot table doesn’t activate the PivotTable Fields List dialog box, you can manually activate it by right-clicking anywhere inside the pivot table and selecting Show Field List.

Figure 14-10: Adding a new analysis layer to your data model is as easy as selecting another field.

What if this layout doesn’t work for you? Maybe you want to see business segments listed at the top of the pivot table results. No problem. Simply drag the Business Segment field from the Row Labels area to the Column Labels area. As you can see in Figure 14-11, this instantly restructures the pivot table to your specifications.

Figure 14-11: Your business segments are now column-oriented.

Changing the pivot table view

Often you’re asked to produce reports for one particular region, market, product, and so on. Instead of working hours and hours building separate pivot tables for every possible scenario, you can leverage pivot tables to help create multiple views of the same data. For example, you can do so by creating a region filter in your pivot table.

Click anywhere on your pivot table to reactivate the PivotTable Fields List dialog box and then drag the Region field to the Filter area. This adds a drop-down control to your pivot table, as shown in Figure 14-12. You can then use this control to view one particular region at a time.

Figure 14-12: Add the Region field to view data for a specific geographic area.

Updating your pivot table

As time goes by, your data may change and grow with newly added rows and columns. You use the Refresh command to update your pivot table with these changes. To do so, simply right-click inside the pivot table and select Refresh, as demonstrated in Figure 14-13.

Figure 14-13: Use the Refresh command to update the data in your pivot table.

Sometimes, the source data that feeds your pivot table changes in structure. For example, you may want to add or delete rows or columns from your data table. These types of changes then affect the range of your data source, not just a few data items in the table.

In this case, a simple update of your pivot table data won’t do. You have to update the range that is captured by the pivot table. Here’s how:

1. Click anywhere inside your pivot table to activate the PivotTable Tools context tab in the Ribbon.

2. Click the Analyze tab.

3. Click the Change Data Source button, as demonstrated in Figure 14-14.

4. Change the range selection to include any new rows or columns (see Figure 14-15).

5. Click OK.

Figure 14-14: Changing the data range that feeds your pivot table.

Figure 14-15: Select the new range that feeds your pivot table.

Customizing Your Pivot Table

The pivot tables you create often need to be tweaked in order to get the look and feel that you’re looking for. In this section, we cover some of the ways that you can customize your pivot tables to suit your dashboard’s needs.

Changing the pivot table layout

Excel 2013 gives you a choice in the layout of your data in a pivot table. The three layouts, shown side by side in Figure 14-16, are the Compact Form, Outline Form, and Tabular Form. Although no layout stands out as being better than the others, most people prefer using the Tabular Form layout because it seems easiest to read, and it’s the layout that most people who have seen pivot tables in the past are used to.

Figure 14-16: The three layouts for a pivot table report.

The layout you choose not only affects the look and feel of your reporting mechanisms but also it may affect the way you build and interact with any dashboard models based on your pivot tables.

Changing the layout of a pivot table is easy. Follow these steps:

1. Click anywhere inside your pivot table to activate the PivotTable Tools context tab in the Ribbon.

2. Select the Design tab on the Ribbon.

3. Click the Report Layout icon and choose the layout you like (see Figure 14-17).

Figure 14-17: Changing the layout for your pivot table.

Renaming the fields

Notice that every field in your pivot table has a name. The fields in the row, column, and filter areas inherit their names from the data labels in your source data. For example, the fields in the Values area are given a name, such as Sum of Sales Amount.

Now, you might prefer the name Total Sales instead of the unattractive default name, like Sum of Sales Amount. In this situation, the ability to change your field name is handy. To change a field name, perform the following steps:

1. Right-click any value within the target field.

For example, if you want to change the name of the field Sum of Sales Amount, you right-click any value under that field.

2. Select Value Field Settings (see Figure 14-18).

This opens the Value Field Settings dialog box.

3. Type the new name in the Custom Name box (see Figure 14-19).

4. Click OK.

Figure 14-18: Right-click any value in the target field to select the Value Field Settings option.

Figure 14-19: Use the Custom Name box to change the name.

If you use the same name of the data label that you specified in your source data, you receive an error. In this example, if you try to rename the Sum of Sales Amount field as Sales Amount, you do get an error message. To get around this, you can add a space to the end of any field name. Excel considers Sales Amount (followed by a space) to be different from Sales Amount. This way, you can use the name that you want, and no one will notice any difference.

Formatting numbers

You can format numbers in a pivot table to fit your needs (such as currency, percent, or number). For example, you can control the numeric formatting of a field using the Value Field Settings dialog box. Here’s how:

1. Right-click any value within the target field.

For example, if you want to change the format of the values in the Sales Amount field, right-click any value under that field.

2. To display the Select Value Field Settings dialog box, select Value Field Settings.

3. To display the Format Cells dialog box, click Number Format.

4. Indicate the number format you desire, just as you normally would on your worksheet.

5. Click OK.

After you set a new format for a field, the applied formatting will persist even if you refresh or rearrange your pivot table.

Changing summary calculations

When you create your pivot table, Excel, by default, summarizes your data by either counting or summing the items. Instead of Sum or Count, you may want to choose other functions, such as Average, Min, Max, and so on. In all, 11 options are available, including:

→ Sum: Adds all numeric data.

→ Count: Counts all data items within a given field, including numeric-, text-, and date-formatted cells.

→ Average: Calculates an average for the target data items.

→ Max: Displays the largest value in the target data items.

→ Min: Displays the smallest value in the target data items.

→ Product: Multiplies all target data items together.

→ Count Nums: Counts only the numeric cells in the target data items.

→ StdDevP and StdDev: Calculates the standard deviation for the target data items. Use StdDevP if your data source contains the complete population. Use StdDev if your data source contains a sample of the population.

→ VarP and Var: Calculates the statistical variance for the target data items. Use VarP if your data contains a complete population. If your data contains only a sampling of the complete population, use Var to estimate the variance.

To change the summary calculation for any given field, perform the following steps:

1. Right-click any value within the target field.

2. To display the Value Field Settings dialog box, select Value Field Settings.

3. Select the type of calculation you want to use from the list of calculations (see Figure 14-20).

4. Click OK.

Figure 14-20: Change the type of calculation used for a field.

Did you know that a single blank cell causes Excel to count instead of sum? That’s right. If all the cells in a column contain numeric data, Excel chooses Sum. If just one cell is either blank or contains text, Excel chooses Count. Be sure to pay attention to the fields that you place into the Values area of the pivot table. If the field name starts with Count Of, Excel’s counting the items in the field instead of summing the values.

Suppressing subtotals

Notice that each time you add a field to your pivot table, Excel adds a subtotal for that field. There may be, however, times when the inclusion of subtotals either doesn’t make sense or just hinders a clear view of your pivot table report. For example, Figure 14-21 shows a pivot table where the subtotals inundate the report with totals that serve only to hide the real data you’re trying to report.

Figure 14-21: Subtotals sometimes muddle the data you’re trying to show.

Removing all subtotals at one time

You can remove all subtotals at once by performing these steps:

1. To activate the PivotTable Tools context tab on the Ribbon, click anywhere inside your pivot table.

2. Click the Design tab.

3. Select the Subtotals icon and select Do Not Show Subtotals (see Figure 14-22).

Figure 14-22: Use the Do Not Show Subtotals option to remove all subtotals at once.

As you can see in Figure 14-23, the same report without subtotals is much more pleasant to review.

Figure 14-23: The same report without subtotals.

Removing the subtotals for only one field

Maybe you want to remove the subtotals for only one field? In such a case, you can perform the following steps:

1. Right-click any value within the target field.

2. To display the Field Settings dialog box, select Field Settings.

3. Select None under the Subtotals options (see Figure 14-24).

4. Click OK.

Figure 14-24: Select the None option to remove subtotals for one field.

Removing grand totals

You may want to remove the Grand Totals field from your pivot table.

1. Right-click anywhere on your pivot table.

2. To display the Options dialog box, select PivotTable Options.

3. Click the Totals & Filters tab.

4. Deselect Show Grand Totals for Rows.

5. Deselect Show Grand Totals for Columns.

6. Click the OK button to confirm your change.

Hiding and showing data items

A pivot table summarizes and displays all the information in your source data. There may, however, be situations when you want to inhibit certain data items from being included in your pivot table summary. In these situations, you can choose to hide a data item.

In terms of pivot tables, hiding doesn’t just mean preventing the data item from displaying on the dashboard; hiding a data item also prevents it from being factored into the summary calculations.

The pivot table in Figure 14-25 shows sales amounts for all Business Segments by Market. In this example, however, you want to show totals without taking sales from the Bikes segment into consideration. In other words, you want to hide the Bikes segment.

Figure 14-25: You want to remove Bikes from this analysis.

To hide the Bikes Business Segment, in the Business Segment drop-down list, deselect Bikes (see Figure 14-26).

Figure 14-26: Removing the check from the Bike items hides the Bikes segment.

After clicking OK, the pivot table instantly recalculates, leaving out the Bikes segment. As you can see in Figure 14-27, the Market total sales now reflect the sales without Bikes.

Also note in Figure 14-27 that the filter icon next to Business Segment gives you a visual indicator that a filter is being applied.

Figure 14-27: Segment analysis without the Bikes segment.

You can just as quickly reinstate all hidden data items for the field. Simply click the Business Segment drop-down list and choose Select All (see Figure 14-28).

Figure 14-28: Placing a check next to Select All forces all data items in that field to become unhidden.

Hiding or showing items without data

By default, your pivot table shows only data items that have data. This may cause unintended problems for your data.

Look at Figure 14-29, which shows a pivot table with the SalesPeriod field in the Row Labels area and the Region field in the Filter area. Note that the Region field is set to (All), and every sales period appears in the report.

Figure 14-29: All sales periods are showing.

If you display only Europe in the filter area, only a portion of all the sales periods now show (see Figure 14-30).

Figure 14-30: Filtering for the Europe region causes some of the sales periods to not display.

But displaying only those items with data could cause trouble if we plan on using this pivot table as the source for your charts or other dashboard components. With that in mind, it isn’t ideal if half the year disappears each time a customer selects Europe.

Here’s how you can prevent Excel from hiding pivot items without data:

1. Right-click any value within the target field.

In this example, the target field is the SalesPeriod field.

2. To display the Field Settings dialog box, select Field Settings.

3. Click the Layout & Print tab in the Field Settings dialog box.

4. Select Show Items with No Data (see Figure 14-31).

5. Click OK.

Figure 14-31: Select the Show Items with No Data option to display all data items.

As you can see in Figure 14-32, after you select the Show Items with No Data option, all the sales periods appear whether the selected region had sales that period or not.

Figure 14-32: All sales periods display even if there is no data.

Now that you’re confident that the structure of the pivot table is locked, you can use it as the source for all charts and other components in your dashboard.

When you show items with no data, you will see plenty of empty cells. Excel gives you the option of replacing empty cells with a value of your own (such as 0 or n/a). This will give your customers a clear indication that there is truly no data for the items that show empty. To replace empty cells with your own value, right-click your pivot table and select PivotTable Options. In the PivotTable Options dialog box, you see a For Empty Cells Show setting. Simply enter the value you want to show instead of empty cells.

Sorting your pivot table

By default, items in each pivot field are sorted in ascending sequence based on the item name. Excel gives you the freedom to change the sort order of the items in your pivot table.

Like many actions that you can perform in Excel, lots of different ways exist to sort data within a pivot table. The easiest way, and the way that we use the most, is to apply the sort directly in the pivot table. Here’s how:

1. Right-click any value within the target field (the field you need to sort).

In the example shown in Figure 14-33, you want to sort by Sales Amount.

2. Select Sort and then select the sort direction.

Figure 14-33: Applying a sort to a pivot table field.

The changes take effect immediately and persist while you work with your pivot table.

Examples of Filtering Your Data

At this point in your exploration of pivot tables, you know enough to start creating your own pivot table and specifying unique views. In this section, we share a few ways we like to view the data. Although you could specify these views by hand, using the pivot table feature saves you hours of work and allows you to more easily update and maintain your information.

Producing top and bottom views

You’ll often find that people are interested in the top and bottom measurement of things — for example, the top 50 customers, the bottom 5 sales reps, the top 10 products. Although you may think this is because they have the attention span of a four-year-old, there’s a more logical reason for focusing on the outliers.

Effective dashboards and reports are often about showing actionable data. If you, as a manager, know which accounts are the bottom ten revenue-generating accounts, you could apply your effort and resources in building up those accounts. Because you most likely wouldn’t have the resources to focus on all accounts, viewing a manageable subset of accounts would be more useful.

Luckily, pivot tables make it easy to filter your data for the top five, the bottom ten, or any conceivable combination of top or bottom records. Here’s an example.

Imagine that in your company, the Accessories Business Segment is a high-margin business — you make the most profit for each dollar of sales in the Accessories segment. To increase sales, your manager wants to focus on the 50 customers who spend the least amount of money on Accessories. He obviously wants to spend his time and resources on getting those customers to buy more accessories. Here’s what to do:

1. Build a pivot table with Business Segment in the Filter area, Customer in the Row Labels area, and Sales Amount in the Values area (see Figure 14-34). For cosmetic value, change the layout to Tabular Form.

Figure 14-34: Build this pivot table to start.

2. Right-click any customer name in the Customer field, select Filter, and then select Top 10 (see Figure 14-35).

Figure 14-35: Select the Top 10 filter option.

3. In the Top 10 Filter dialog box (see Figure 14-36), define the view you’re looking for.

In this example, you want the Bottom 50 Items (Customers), as defined by the Sum of Sales Amount field.

Figure 14-36: Specify the filter you want to apply.

4. Click OK.

5. In the Filter area, click the drop-down list for the Business Segment field and select Accessories (see Figure 14-37).

Figure 14-37: Filter your pivot table report to show Accessories.

At this point, you have exactly what you need — the 50 customers who spend the least amount of money on accessories. You can go a step further and format the report a bit by sorting on the Sum of Sales Amount and applying a currency format to the numbers (see Figure 14-38).

Figure 14-38: Your final report.

Note that because you built this view using a pivot table, you can now filter according to any new field. For example, you can add the Market field to the Filter area to get the 50 United Kingdom customers who spend the least amount of money on accessories. This, my friends, is the power of using pivot tables for the basis of your dashboards and reports. Continue to play around with the Top 10 filter option to see what kind of reports you can come up with (see Figure 14-39).

Figure 14-39: You can easily adapt this report to produce any combination of views.

You may notice that in Figure 14-39, the bottom 50 view is showing only 23 records. This is because there are fewer than 50 customers in the United Kingdom market that have accessories sales. Because I asked for the bottom 50, Excel shows up to 50 accounts; but fewer if there are fewer than 50. If there’s a tie for any rank in the bottom 50, Excel shows you all the tied records.

You can remove the applied filters in your pivot tables by taking these actions:

1. Click anywhere inside your pivot table to activate the PivotTable Tools context tab in the Ribbon.

2. Click the Analyze tab.

3. Select the Clear icon and select Clear Filters, as shown in Figure 14-40.

Figure 14-40: Select Clear Filters to remove the applied filters in a field.

Creating views by month, quarter, and year

Raw transactional data is rarely aggregated by month, quarter, or year for you. This type of data is often captured by the day. However, people often want reports by month or quarters instead of detail by day. Fortunately, pivot tables make it easy to group date fields into various time dimensions. Here’s how:

1. Build a pivot table with Sales Date in the Row Labels area and Sales Amount in the Values area, similar to the one in Figure 14-41.

Figure 14-41: Build this pivot table to start.

2. Right-click any date and select Group, as shown in Figure 14-42.

The Grouping dialog box appears, as shown in Figure 14-43.

3. Select the time dimensions that you want.

In this example, you can select Months, Quarters, and Years.

4. Click OK.

Figure 14-42: Select the Group option.

Figure 14-43: Select the time dimensions that suit your needs.

Here are several interesting things to note about the resulting pivot table. First, notice that Quarters and Years have been added to your field list. Keep in mind that your source data hasn’t changed to include these new fields; instead, these fields are now part of your pivot table. Another interesting thing to note is that, by default, the Years and Quarters fields are automatically added next to the original date field in the pivot table layout, as shown in Figure 14-44.

Figure 14-44: Your pivot table is now grouped by Years and Quarters.

After your date field is grouped, you can use each added time grouping just as you would any other field in your pivot table. For instance, in Figure 14-45, I moved the Years and Quarters fields to the Column area and filtered on 2011.

Figure 14-45: You can use your newly created time dimensions just like a normal pivot field.

Creating a percent distribution view

A percent distribution (or percent contribution) view allows you to see how much of the total is made up of a specific data item. This view is useful when you’re trying to measure the general impact of a particular item.

The pivot table, as shown in Figure 14-46, gives you a view into the percent of sales that comes from each business segment. Here, you can tell that bikes make up 81 percent of Canada’s sales, whereas only 77 percent of France’s sales come from bikes.

Figure 14-46: This view shows percent of total for the row.

You’ll also notice in Figure 14-46 that this view was created by selecting the % of Row Total option in the Value Field Settings dialog box. Here are the steps to create this type of view:

1. Right-click any value within the target field.

For example, if you want to change the settings for the Sales Amount field, right-click any value under that field.

2. Select Show Values As.

3. Select % of Row Total.

The pivot table in Figure 14-47 gives you a view into the percent of sales that comes from each market. Here, you have the same type of view, but this time, you use the % of Column Total option.

Figure 14-47: This view shows percent of total for the column.

Again, remember that because you built these views in a pivot table, you have the flexibility to slice the data by region, bring in new fields, rearrange data, and most importantly, refresh this view when new data comes in.

Creating a YTD totals view

Sometimes, it’s useful to capture a running totals view to analyze the movement of numbers on a year-to-date (YTD) basis. Figure 14-48 illustrates a pivot table that shows a running total of revenue by month for each year. In this view, you can see where the YTD sales stand at any given month in each year. For example, you can see that in August 2010 revenues were about a million dollars lower than the same point in 2009.

Figure 14-48: This view shows a running total of sales for each month.

In the sample data for this chapter, you don’t see Months and Years. You have to create them by grouping the SalesDate field. Feel free to review the “Creating views by month, quarter, and year” section earlier in this chapter to find out how.

To create this type of view, follow these steps:

1. Right-click any value within the target field.

For example, if you want to change the settings for the Sales Amount field, right-click any value under that field.

2. Select Value Field Settings.

The Value Field Settings dialog box appears.

3. Click the Show Values As tab.

4. Select Running Total In from the drop-down list.

5. In the Base Field list, select the field that you want the running totals to be calculated against.

In most cases, this will be a time series such as, in this example, the SalesDate field.

6. Click OK.

Creating a month-over-month variance view

Another commonly requested view is a month-over-month variance. How did this month’s sales compare to last month’s sales? The best way to create these types of views is to show the raw number and the percent variance together.

In that light, you can start creating this view by building a pivot table similar to the one shown in Figure 14-49. Notice that you bring in the Sales Amount field twice. One of these remains untouched, showing the raw data. The other is changed to show the month-over-month variance.

Figure 14-49: Build a pivot table that contains the Sum of Sales Amount twice.

Figure 14-50 illustrates the settings that convert the second Sum of Sales Amount field into a month-over-month variance calculation.

Figure 14-50: Configure the second Sum of Sales Amount field to show month-over-month variance.

As you can see, after applying these settings, the pivot table gives you a nice view of raw sales dollars and the variance over last month. You can obviously change the field names (see the “Renaming the fields” section earlier in this chapter) to reflect the appropriate labels for each column.

In the sample data for this chapter, you don’t see Months and Years. You have to create them by grouping the SalesDate field. Feel free to review the section, “Creating views by month, quarter, and year,” earlier in this chapter to find out how.

To create the view in Figure 14-50, follow these steps:

1. Right-click any value within the target field.

In this case, the target field is the second Sum of Sales Amount field.

2. Select Value Field Settings.

The Value Field Settings dialog box appears.

3. Click the Show Values As tab.

4. Select % Difference From in the drop-down list.

5. In the Base Field list, select the field that you want the running totals to be calculated against.

In most cases, this is a time series like, in this example, the SalesDate field.

6. In the Base Item list, select the item you want to compare against when calculating the percent variance. In this example, you want to calculate each month’s variance to the previous month. Therefore, select the (previous) item.