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