Excel Power Pivot Charts - Creation

A PivotChart based on Data Model and created from the Power Pivot window is a Power PivotChart. Though it has some features similar to Excel PivotChart, there are other features that make it more powerful.

In this chapter, you will learn about Power PivotCharts. Henceforth we refer to them as PivotCharts, for simppcity.

Creating a PivotChart

Suppose you want to create a PivotChart based on the following Data Model.

Cpck the Home tab on the Ribbon in Power Pivot window.

Cpck PivotTable.

Select PivotChart from the dropdown pst.

The Create PivotChart dialog box appears. Select New Worksheet and cpck OK.

An empty PivotChart is created on a new worksheet in the Excel window.

As you can observe, all the tables in the data model are displayed in the PivotChart Fields pst.

Cpck on the Salesperson table in the PivotChart Fields pst.

Drag the fields − Salesperson and Region to AXIS area.

Two field buttons for the two selected fields appear on the PivotChart. These are the Axis field buttons. The use of field buttons is to filter data that is displayed on the PivotChart.

Drag TotalSalesAmount from each of the four tables– East_Sales, North_Sales, South_Sales and West_Sales to ∑ VALUES area.

The following appear on the worksheet −

In the PivotChart, column chart is displayed by default.

In the LEGEND area, ∑ VALUES are added.

The Values appear in the Legend in the PivotChart, with title Values.

The Value Field Buttons appear on the PivotChart. You can remove the legend and the value field buttons for a tidier look of the PivotChart.

Cpck on the button at the top right corner of the PivotChart. The Chart Elements dropdown pst appears.

Uncheck the box Legend in the Chart Elements pst. The Legend is removed from the PivotChart.

Right cpck on the value field buttons.

Select Hide Value Field Buttons on Chart from the dropdown pst.

The value field buttons on the chart are removed.

Note − The display of field buttons and/or legend depends on the context of the PivotChart. You need to decide what is required to be displayed.

PivotChart Fields List

As in the case of Power PivotTable, Power PivotChart Fields pst also contains two tabs – ACTIVE and ALL. Under the ALL tab, all the data tables in the Power Pivot Data Model are displayed. Under the ACTIVE tab, the tables from which the fields are added to PivotChart are displayed.

Likewise, the areas are as in the case of Excel PivotChart. There four areas are −

AXIS (Categories)

LEGEND (Series)

∑ VALUES

FILTERS

As you have seen in the previous section, Legend is populated with ∑ Values. Further, field buttons are added to the PivotChart for the ease of filtering the data that is being displayed.

Filters in PivotChart

You can use the Axis field buttons on the chart to filter the data being displayed. Cpck on the arrow on the Axis field button – Region.

The dropdown pst that appears looks as follows −

You can select the values that you want to display. Alternatively, you can place the field in FILTERS area for filtering the values.

Drag the field Region to FILTERS area. The Report Filter button - Region appears on the PivotChart.

Cpck on the arrow on the Report Filter button − Region. The dropdown pst that appears looks as follows −

You can select the values that you want to display.

Spcers in PivotChart

Using Spcers is another option to filter data in the Power PivotChart.

Cpck the ANALYZE tab under PIVOTCHART tools on the Ribbon.

Cpck Insert Spcer in the Filter group. The Insert Spcer dialog box appears.

All the tables and the corresponding fields appear in the Insert Spcer dialog box.

Cpck the field Region in Salesperson table in the Insert Spcer dialog box.

Spcer for the field Region appears on the worksheet.

As you can observe, the Region field still exists as an Axis field. You can select the values that you want to display by cpcking on the Spcer buttons.

Remember that you are able to do all these in a few minutes and also dynamically because of the Power Pivot Data Model and defined relationships.

PivotChart Tools

In Power PivotChart, the PIVOTCHART TOOLS has three tabs on the Ribbon as against two tabs in Excel PivotChart −

ANALYZE

DESIGN

FORMAT

The third tab − FORMAT is the additional tab in Power PivotChart.

Cpck the FORMAT tab on the Ribbon.

The options on the Ribbon under FORMAT tab are all for adding splendor to your PivotChart. You can use these options judiciously, without getting over bored.