Excel Power Pivot - Flattened


Advertisements


When the data has many levels, sometimes it becomes cumbersome to read the PivotTable report.

For example, consider the following Data Model.

Read PivotTable

We will create a Power PivotTable and a Power Flattened PivotTable to get an understanding of the layouts.

Creating a PivotTable

You can create a Power PivotTable as follows −

  • Click the Home tab on the Ribbon in the PowerPivot window.
  • Click PivotTable.
  • Select PivotTable from the dropdown list.
Dropdown List

An empty PivotTable will be created.

  • Drag the fields – Salesperson, Region and Product from the PivotTable Fields list to the ROWS area.

  • Drag the field – TotalSalesAmount from the Tables – East, North, South and West to the ∑ VALUES area.

TotalSalesAmount

As you can see, it is a bit cumbersome read such a report. If the number of entries becomes more, the more difficult it will be.

Power Pivot provides a solution for a better representation of data with Flattened PivotTable.

Creating a Flattened PivotTable

You can create a Power Flattened PivotTable as follows −

  • Click the Home tab on the Ribbon in the PowerPivot window.

  • Click PivotTable.

  • Select Flattened PivotTable from the dropdown list.

Flattened PivotTable

Create Flattened PivotTable dialog box appears. Select New Worksheet and click OK.

Create Flattened PivotTable

As you can observe the data is flattened out in this PivotTable.

Data Flattened

Note − In this case Salesperson, Region and Product are in ROWS area only as in the previous case. However, in the PivotTable layout, these three fields are appearing as three columns.

Exploring Data in Flattened PivotTable

Suppose you want to summarize the sales data for the product – Air Conditioner. You can do it in a simple way with the Flattened PivotTable as follows −

  • Click the arrow next to the column header – Product.
  • Check the box Air Conditioner and uncheck the other boxes. Click OK.
Box Air Conditioner

The Flattened PivotTable is filtered to the Air Conditioner sales data.

You can make it look more flattened by dragging ∑ VALUES to ROWS area from the COLUMNS area.

Rename the custom names of the summation values in the ∑ VALUES area to make them more meaningful as follows −

  • Click on a summation value, say, Sum of TotalSalesAmount for East.
  • Select Value Field Settings from the dropdown list.
  • Change the Custom Name to East TotalSalesAmount.
  • Repeat the steps for the other three summation values.
Values

You can also summarize the number of units sold.

  • Drag No. of Units to the ∑ VALUES area from each of the tables – East_Sales, North_Sales, South_Sales and West_Sales.

  • Rename the values to East Total No. of Units, North Total No. of Units, South Total No. of Units and West Total No. of Units respectively.

Number of Units

As you can observe, in both of the above tables, there are rows with empty values, as each salesperson represents a single region and each region is represented only by a single salesperson.

  • Select the rows with empty values.
  • Right click and click on Hide in the dropdown list.

All the rows with empty values will be hidden.

 Empty Values

As you can observe, though the rows with empty values are not displayed, the information on the Salesperson also got hidden.

  • Click on the column header – Salesperson.

  • Click the ANALYZE tab on the Ribbon.

  • Click Field Settings. The Field Settings dialog box appears.

  • Click the Layout & Print tab.

  • Check the box - Repeat Item Labels.

  • Click OK.

Repeat Item Labels

As you can observe, the Salesperson information is displayed and the rows with empty values are hidden. Further, the column Region in the report is redundant, as the values in the Values column are self-explanatory.

Self-explanatory

Drag the field Regions out of Area.

Field Regions

Reverse the order of the fields – Salesperson and Product in the ROWS area.

Reverse Order

You have arrived at a concise report combining data from six tables in the Power Pivot.



Advertisements
E-Books Store