We are going to continue with pivot tables in this chapter to explore some advanced features. An identical data set to the previous pivot table chapter will be used with a couple of additions: Money Spent and Quantity Purchased columns.
Use this workbook for the chapter.
The first question we want to answer in this chapter will reuse the pivot table from the previous chapter, and we want to know the percentages of products purchased in three locations. We will begin by naming the data set for quick reference and inserting a pivot table in a new spreadsheet with Type of Fishing Preferred in the columns field, Store in the rows field, and Store in the values field.
The initial pivot table in Figure 26.2 does not present the percentage data we need in this scenario. Instead of manually producing percentages using functions, we can have the pivot table do it automatically by modifying the value settings.
The pivot table will now show the percentage values across the table instead of the previous count totals. The percentages are based on the grand total count of 1,500 from Figure 26.2. The percent of grand total is useful to understand the company's total sales, but what if we want to know the percentages of sales by store or by-product? We could change the Show values as setting to % of Column Total to view sales by store for a geographic perspective, or % of Row Total to view sales by preferred bait for a product perspective.
If we want to view the data of our new pivot table in a chart, we can create a pivot chart based on the table. For this scenario, we'll insert a simple clustered column chart.
A notable difference with this chart is that it includes a button for selecting filters. Filters can be added by dropping additional chart items to a relevant field. For example, we could add Type of Fishing Preferred to the Legend (Series) field to include a filter for types of fishing methods.
If we have three pivot charts to present percentages of grand total, column total, and row total individually, we may also want the ability to filter the data for all three at once. Slicers can be created to do just that.
The newly created slicer(s) will only work on the initially selected chart, but we can modify the slicer to work for multiple charts that use the same data.
Due to slicers working as filters, the built-in filters for each chart become redundant. These filters can be removed by selecting the chart, going to the PivotChart Analyze tab, pressing Field Buttons, and selecting Hide All.
Additionally, to select multiple options of a slicer filter, pressing the CTRL key before selecting a second or more options as necessary.
In another scenario, we need to know the average cost customers paid per unit. To calculate the cost, we'll create a new pivot table using Money Spent and Quantity Purchased in the Values field. Additionally, Store will be added to the Rows field. The resulting pivot table will display the total money spent at each store and the amount of product purchased.
Note: The values need to be formatted appropriately with a comma separator and relevant currency symbol by using the Number Format button in the Value Field Settings menu.
Now we need to find the average cost per product unit. We can do this manually by dividing the money spent by the quantity purchased, but we want to include it automatically in the pivot table. We'll do this by adding a field.
Note: The new field's settings will need to be adjusted to format the value correctly as currency with two decimal places.
We can extend the results into types of fishing preferred and gender to identify average costs by product and gender. In Figure 26.10, we can see female customers bought fewer spinners for a higher price per unit cost in Ammon, Idaho.
This content is provided to you freely by BYU-I Books.
Access it online or download it at https://books.byui.edu/bus_115_business_app/advanced_pivot_table.