free barcode generator source code in c#.net CREATING CALCULATIONS IN A PIVOT TABLE in Font

Maker PDF417 in Font CREATING CALCULATIONS IN A PIVOT TABLE

For this report, you want to compare each month s total to the total for the previous month and see the change as a percent difference. 1. In the pivot table, right-click one of the value cells. 2. In the context menu, click Summarize Data By, and then click More Options. 3. In the Value Field Settings dialog box, click the Show Values As tab. 4. From the drop-down list for Show Values As, select % Difference From. 5. In this report, you want to compare the totals between dates, so choose OrderDate as the Base field. 6. In this report, you want to compare each month s total to the previous month s total, so select (previous) as the base item (see Figure 9-8).

Figure 9-8. OrderDate with (previous) as base item 7. It will be easier to compare the values with no decimal places, so click Number Format, and set Decimal places to zero, and then click OK to close the Format Cells dialog box. 8. Click OK to close the Value Field Settings dialog box. The values in the pivot table change to show the difference for each month, from the previous month (see Figure 9-9). The revised pivot table shows the percentage change in sales each month, compared to the previous month. The row for the first date is empty, because there s no previous date to which this can be compared. From the revised pivot table, the sales manager can quickly see how the sales totals changed during 2007. For example: June sales in the East were 32 percent lower than May sales. August sales in the North were 40 percent higher than July sales. Total December sales were 41 percent higher than November sales.

You ve sent your report on how the sales totals change from one month to the next, and now the sales manager wants a report that shows how each region s sales totals accumulate over the year. Each month should show the year to date total for each region. In the pivot table, OrderDate is in the Row Labels area, filtered for 2007 dates; Region is in the Column Labels area; and Sum of TotalPrice is in the Values area. You ll keep the current pivot table layout and change the custom calculation so it creates a running total. 1. In the pivot table, right-click one of the value cells. 2. In the context menu, click Summarize Data By, and then click More Options. 3. In the Value Field Settings dialog box, click the Show Values As tab. 4. From the drop-down list for Show Values As, select Running Total In. 5. In this report, you want to create a running total based on the Order dates, so choose OrderDate as the base field. For a Running Total In custom calculation, no base item is required. 6. Click Number Format, and format the values as Number, with a thousands separator, and no decimal places. 7. Click OK to close the Format Cells dialog box, and then click OK to close the Value Field Settings dialog box.

The values in the pivot table change to show the running total for each month (see Figure 9-10).

Figure 9-10. Running totals for each region The revised pivot table shows the year-to-date total for each region for each month. In this report, the sales manager can quickly see how each region was going at any point in the year.

The running total in custom calculations is best suited for using a date field as the base field, Tip

although you could use another type of field. For example, if recording expenses for different projects, you could show a running total of expenses over different phases of the project.