Building on the previous problem, another pivot table element we didn’t explain is a calculated field. This is usually a new numerical variable built from numerical variables that can be summarized in the Values area. It acts somewhat like a new column in the spreadsheet data, but there is an important difference. Again, it is easiest to learn from an example. Open the file Elecmart Sales.xlsx and follow the instructions below.
a. Create a new column in the data, Cost Per Item, which is Total Cost divided by Items Ordered.
Then create a pivot table and find the average of Cost Per Item, broken down by Region. You should find averages such as $50.41 for the MidWest. Explain exactly how this value was calculated. Would such an average be of much interest to a manager at Elecmart? Why or why not?
b. Select any average in the pivot table and then select Calculated Field from the Formulas dropdown list on the PivotTable Tools Options ribbon. This will open a dialog box. Enter CF_CostPerItem in the name box enter the formula = TotalCost/ItemsOrdered, and click on OK. You should now see a new column in the pivot table, Sum of CF_CostPerItem, with different values than in the Average of CostPerItem column. For example, the new value for the MidWest should be $46.47. Do some investigation to understand how this “sum” was calculated. From a manager’s point of view, does it make any sense?