24.7 PivotCharts

To insert a PivotChart, return to Manage within Power Pivot, click the arrow underneath the PivotTable button, and select PivotChart. Next, head to the Analyze tab and click Field List to begin adding data to the chart. Let’s create a chart that shows which categories are the most profitable relative to the other categories in our data. So, add our ProductType hierarchy to rows and the Gross Profit from the OrderFact table to the values.

By default, we see a column chart that shows the sum of the gross profit. While we can see the total gross profit by category and some relative comparisons, let’s change our chart type to a pie chart so that we can see the percentage of gross profit attributable to each category. You can select the type of chart shown by right-clicking on the chart and then selecting Change Chart Type and making a selection from the available options.

To enhance the clarity of interpretation of your chart, add data labels by right-clicking and selecting Add Data Labels. Then, to clarify the data labels even more, right-click on a label and select Format Data Labels. In our example, we’ve added the relative percentage and also the category name. Continue to enhance the clarity of the overall chart by giving it an effective title and styling. Don’t be afraid to remove any details that may distract from the message you’d like to convey with the chart. For instance, here, we’ve removed the legend since that same information has now been added to the chart.

Just as we added slicers to our PivotTable, we can also add slicers to filter the data shown in a PivotChart. Under the Analyze tab, select Insert Slicer. Select the fields you’d like to filter the data by and arrange them appropriately. Here, we’ve added a timeline for the Order Date and changed the grouping to quarter so that we can quickly switch quarters and see which categories amounted to the most gross profit.