Microsoft Office Tutorials and References
In Depth Information
Examples of Filtering Your Data
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
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
monthover-month variance calculation.