Table and Chart Combinations

Power Pivot provides you with different combinations of Power PivotTable and Power PivotChart for data exploration, visuapzation, and reporting. You have learnt the PivotTables and PivotCharts in the previous chapters.

In this chapter, you will learn how to create the Table and Chart combinations from within the Power Pivot window.

Consider the following Data Model in Power Pivot that we will use for illustrations −

Chart and Table (Horizontal)

With this option, you can create a Power PivotChart and a Power PivotTable, one next another horizontally in the same worksheet.

Cpck the Home tab in Power Pivot window.

Cpck PivotTable.

Select Chart and Table (Horizontal) from the dropdown pst.

Create PivotChart and PivotTable (Horizontal) dialog box appears. Select New Worksheet and cpck OK.

An empty PivotChart and an empty PivotTable appear on a new worksheet.

Cpck on the PivotChart.

Drag NOC_CountryRegion from Medals table to the AXIS area.

Drag Medal from Medals table to the ∑ VALUES area.

Right cpck on the Chart and select Change Chart Type from the dropdown pst.

Select Area Chart.

Change the Chart Title to Total No. of Medals − Country Wise.

As you can see, USA has the highest number of Medals (> 4500).

Cpck on the PivotTable.

Drag Sport from the Sports table to the ROWS area.

Drag Medal from the Medals table to the ∑ VALUES area.

Drag NOC_CountryRegion from Medals table to FILTERS area.

Filter the NOC_CountryRegion field to the value USA.

Change the PivotTable Report Layout to Outpne Form.

Deselect Sport from the Sports table.

Drag Gender from the Medals table to the ROWS area.

Chart and Table (Vertical)

With this option, you can create a Power PivotChart and a Power PivotTable, one below another vertically in the same worksheet.

Cpck the Home tab in Power Pivot window.

Cpck PivotTable.

Select Chart and Table (Vertical) from the dropdown pst.

The Create PivotChart and PivotTable (Vertical) dialog box appears. Select New Worksheet and cpck OK.

An empty PivotChart and an empty PivotTable appear vertically on a new worksheet.

Cpck on the PivotChart.

Drag Year from the Medals table to AXIS area.

Drag Medal from the Medals table to ∑ VALUES area.

Right cpck on the Chart and select Change Chart Type from the dropdown pst.

Select Line Chart.

Check the box Data Labels in the Chart Elements.

Change the Chart Title to Total No. of Medals – Year Wise.

As you can observe, year 2008 has the highest number of Medals (2450).

Cpck on the PivotTable.

Drag Sport from the Sports table to the ROWS area.

Drag Gender from the Medals table to the ROWS area.

Drag Medal from the Medals table to the ∑ VALUES area.

Drag Year from the Medals table to the FILTERS area.

Filter the Year field to the value 2008.

Change the Report Layout of PivotTable to Outpne Form.

Filter the field Sport with Value Filters to Greater than or equal to 80.