Notice in Figure 2 that columns J, L, N, and O have been hidden. The drawback to this approach is that while the Total column is correct, extra columns will appear in the pivot table. Apply the same settings as with E-W, but choose % Difference From. This will show a calculation of East minus West, as shown in Figure 2.įor the Sum of Revenue3 field, use a name of E/W.
In the Base Field list box, choose Region. In the first drop-down below Show Values As, choose Difference From. In the Value Field Settings dialog, click on the Show Values As tab. Select each of those headings and click on Value Field Settings in the PivotTable Analyze tab of the ribbon.įor Sum of Revenue, simply rename the field to “Total.”įor the Sum of Revenue2, use a name of E-W. The three fields in the Values pane will initially be called Sum of Revenue, Sum of Revenue2, and Sum of Revenue3. The tile initially appears below Region, so you need to drag the tile to appear above Region. When you have multiple fields in the Values area of a pivot table, a virtual field tile called ∑ Values appears in the PivotTable Fields pane. In this case, create a pivot table and drag the Revenue column to the Values area three times. If you need to show East to West as a ratio, you could use the same steps to build =East/West or even =(East-West)/East.Ī different approach is to use the Value Field Settings to change the calculation in the pivot table. The calculated item approach is easy and flexible. The solution? Right-click on the Grand Total heading and choose Remove Grand Total. While 105,787 and 57,220 should total to 163,007, the Grand Total shown in cell J5 includes the extra 48,567 from cell I5. For an unknown reason, the Grand Total column treats the new calculated item as if it should be added to the other items along the dimension. This is always a problem with calculated items. Look closely at the Grand Total column in Figure 1. Click OK to add the item to the pivot table (see Figure 1). Double-click West to insert it into the formula. Double-click East to insert it into the formula. This brings up East and West in the Items list box. In the Fields list box, Region is already selected. You need to click in the box and backspace to remove the zero.
The Formula box always starts out with a formula of = 0. Since the column is relatively narrow, I went with “E-W” as the field name instead of “East minus West.”
In the Insert Calculated Item dialog, give the new item a name.
Had you chosen a field that contains a product name, you would be inserting a new calculated item along the product dimension.įrom the PivotTable Analyze tab, open the drop-down menu for Fields, Items, & Sets. The selected cell tells Excel that your new calculated item belongs to the Region dimension. Before starting to add a calculated item, you should choose either the East or West cell in the pivot table column headings. This will add columns for both East and West to the report. Because East and West are two items within the single Region column, you would want to create a new calculated item to do any comparisons between the two.Ĭreate a pivot table with Product in the Rows area, Region in the Columns area, and Revenue in the Values area. Calculated items are used when the calculations are within a single pivot table field. Calculated fields are used when performing calculations involving other fields in the pivot table. Pivot tables offer two similar-sounding features: calculated fields and calculated items. The question involves finding out the differences by region for each product. The spreadsheet contains columns for Product, Region, Sales Rep, and Revenue. The example involves data on sales by region.
0 kommentar(er)
