Excel Power Pivot Charts - Creation


Advertisements


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 simplicity.

Creating a PivotChart

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

Creating PivotChart
  • Click the Home tab on the Ribbon in Power Pivot window.
  • Click PivotTable.
  • Select PivotChart from the dropdown list.
Select PivotChart

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

Create PivotChart

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

Empty PivotChart

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

  • Click on the Salesperson table in the PivotChart Fields list.
  • 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.

Two Field Buttons

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

Drag TotalSalesAmount

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.

  • Click on the Plus button at the top right corner of the PivotChart. The Chart Elements dropdown list appears.

Chart Elements

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

Box Legend
  • Right click on the value field buttons.
  • Select Hide Value Field Buttons on Chart from the dropdown list.
Hide Value

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.

Value Field Buttons

PivotChart Fields List

As in the case of Power PivotTable, Power PivotChart Fields list 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.

PivotChart Fields List

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. Click on the arrow on the Axis field button – Region.

Axis Field Button

The dropdown list that appears looks as follows −

Dropdown List Appears

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.

Filters Area

Click on the arrow on the Report Filter button – Region. The dropdown list that appears looks as follows −

Report Filter

You can select the values that you want to display.

Slicers in PivotChart

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

  • Click the ANALYZE tab under PIVOTCHART tools on the Ribbon.

  • Click Insert Slicer in the Filter group. The Insert Slicer dialog box appears.

Insert Slicer

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

Click the field Region in Salesperson table in the Insert Slicer dialog box.

Insert Slicer Dialog Box

Slicer for the field Region appears on the worksheet.

Slicer

As you can observe, the Region field still exists as an Axis field. You can select the values that you want to display by clicking on the Slicer 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.

PivotChart Tools

Click the FORMAT tab on the Ribbon.

Format Tab

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.



Advertisements
E-Books Store