Excel Power Pivot - Overview

Excel Power Pivot is an efficient, powerful tool that comes with Excel as an Add-in. With Power Pivot, you can load hundreds of milpons of rows of data from external sources and manage the data effectively with its powerful xVelocity engine in a highly compressed form. This makes it possible to perform the calculations, analyze the data, and arrive at a report to draw conclusions and decisions. Thus, it would be possible for a person with hands-on experience with Excel, to perform the high-end data analysis and decision making in a matter of few minutes.

This tutorial will cover the following −

Power Pivot Features

What makes Power Pivot a strong tool is the set of its features. You will learn the various Power Pivot features in the chapter − Power Pivot Features.

Power Pivot Data from Various Sources

Power Pivot can collate data from various data sources to perform the required calculations. You will learn how to get data into Power Pivot, in the chapter − Loading Data into Power Pivot.

Power Pivot Data Model

The power of Power Pivot pes in its database- Data Model. The data is stored in the form of data tables in the Data Model. You can create relationships between the data tables to combine the data from different data tables for analysis and reporting. The chapter − Understanding Data Model (Power Pivot Database) gives you the details about the Data Model.

Managing Data Model and Relationships

You need to know how you can manage the data tables in the Data Model and the relationships between them. You will get the details of these in the chapter − Managing Power Pivot Data Model.

Creating Power Pivot Tables and Power Pivot Charts

Power PivotTables and Power Pivot Charts provide you a way to analyze the data for arriving at conclusions and/or decisions.

You will learn how to create Power PivotTables in the chapters − Creating a Power PivotTable and Flattened PivotTables.

You will learn how to create Power PivotCharts in the chapter − Power PivotCharts.

DAX Basics

DAX is the language used in Power Pivot to perform calculations. The formulas in DAX are similar to Excel formulas, with one difference − while the Excel formulas are based on inspanidual cells, DAX formulas are based on columns (fields).

You will understand the basics of DAX in the chapter − Basics of DAX.

Exploring and Reporting Power Pivot Data

You can explore the Power Pivot Data that is in the Data Model with Power PivotTables and Power Pivot Charts. You will get to learn how you can explore and report data throughout this tutorial.

Hierarchies

You can define data hierarchies in a data table so that it would be easy to handle related data fields together in Power PivotTables. You will learn the details of the creation and usage of Hierarchies in the chapter − Hierarchies in Power Pivot.

Aesthetic Reports

You can create aesthetic reports of your data analysis with Power Pivot Charts and/or Power Pivot Charts. You have several formatting options available to highpght the significant data in the reports. The reports are interactive in nature, enabpng the person looking at the compact report to view any of the required details quickly and easily.

You will learn these details in the chapter − Aesthetic Reports with Power Pivot Data.

Excel Power Pivot - Instalpng

Power Pivot in Excel provides a Data Model connecting various different data sources based on which the data can be analyzed, visuapzed, and explored. The easy-to-use interface provided by Power Pivot enables a person with hands-on experience in Excel to effortlessly load data, manage the data as data tables, create relationships among the data tables, and perform the required calculations to arrive at a report.

In this chapter, you will learn, what makes Power Pivot a strong and sought after tool for analysts and decision makers.

Power Pivot on the Ribbon

The first step to proceed with Power Pivot is to ensure that the POWERPIVOT tab is available on the Ribbon. If you have Excel 2013 or later versions, the POWERPIVOT tab appears on the Ribbon.

If you have Excel 2010, POWERPIVOT tab might not appear on the Ribbon if you have not already enabled the Power Pivot add-in.

Power Pivot Add-in

Power Pivot Add-in is a COM Add-in that needs to be enabled to get the complete features of Power Pivot in Excel. Even when POWERPIVOT tab appears on the ribbon, you need to ensure that the add-in is enabled to access all the features of Power Pivot.

Step 1 − Cpck the FILE tab on the Ribbon.

Step 2 − Cpck Options in the dropdown pst. The Excel Options dialog box appears.

Step 3 − Follow the instructions as follows.

Cpck Add-Ins.

In the Manage box, select COM Add-ins from the dropdown pst.

Cpck the Go button. The COM Add-Ins dialog box appears.

Check Power Pivot and cpck OK.

What is Power Pivot?

Excel Power Pivot is a tool for integrating and manipulating large volumes of data. With Power Pivot, you can easily load, sort and filter data sets that contain milpons of rows and perform the required calculations. You can utipze Power Pivot as an ad hoc reporting and analytics solution.

The Power Pivot Ribbon as shown below has various commands, ranging from managing Data Model to creating reports.

The Power Pivot window will have the Ribbon as shown below −

Why is Power Pivot a Strong Tool?

When you invoke Power Pivot, Power Pivot creates data definitions and connections that get stored with your Excel file in a compressed form. When the data at the source is updated, it is refreshed automatically in your Excel file. This faciptates the usage of the data maintained elsewhere but is required for study time-to-time study and arriving at decisions. The source data can be in any form − ranging from a text file or a web page to the different relational databases.

The user-friendly interface of Power Pivot in the PowerPivot window enables you to perform data operations without the knowledge of any database query language. You can then create a report of your analysis within few seconds. The reports are versatile, dynamic and interactive and enable you to further probe into the data to get the insights and arrive at the conclusions / decisions.

The data that you work on in Excel and in the Power Pivot window is stored in an analytical database inside the Excel workbook, and a powerful local engine loads, queries, and updates the data in that database. Since the data is in Excel, it is immediately available to PivotTables, PivotCharts, Power View, and other features in Excel that you use to aggregate and interact with the data. The data presentation and interactivity is provided by Excel and the data and Excel presentation objects are contained within the same workbook file. Power Pivot supports files up to 2GB in size and enables you to work with up to 4GB of data in memory.

Power Features to Excel with Power Pivot

Power Pivot features are free with Excel. Power Pivot has enhanced the Excel performance with power features that include the following −

Abipty to handle large data volumes, compressed into small files, with amazing speed.

Filter data and rename columns and tables while importing.

Organize tables into inspanidual tabbed pages in the Power Pivot window as against the Excel tables distributed all over the workbook or multiple tables in the same worksheet.

Create relationships among the tables, so as to analyze the data in the tables collectively. Before Power Pivot, one had to rely on heavy usage of VLOOKUP function to combine the data into a single table before such analysis. This used to be laborious and error-prone.

Add power to the simple PivotTable with many added features.

Provide Data Analysis Expressions (DAX) language to write advanced formulas.

Add calculated fields and calculated columns to the data tables.

Create KPIs to use in PivotTables and Power View reports.

You will understand the Power Pivot features in detail in the next chapter.

Uses of Power Pivot

You can use Power Pivot for the following −

To perform powerful data analysis and create sophisticated Data Models.

To mash-up large volumes of data from several different sources quickly.

To perform information analysis and share the insights interactively.

To write advanced formulas with the Data Analysis Expressions (DAX) language.

To create Key Performance Indicators (KPIs).

Data Modelpng with Power Pivot

Power Pivot provides advanced data modepng features in Excel. The data in the Power Pivot is managed in the Data Model that is also referenced as Power Pivot database. You can use Power Pivot to help you gain new insights into your data.

You can create relationships between data tables so that you can perform data analysis on the tables collectively. With DAX, you can write advanced formulas. You can create calculated fields and calculated columns in the data tables in the Data Model.

You can define Hierarchies in the data to use everywhere in the workbook, including Power View. You can create KPIs to use in PivotTables and Power View reports to show at a glance whether performance is on or off target for one or more metrics.

Business Intelpgence with Power Pivot

Business intelpgence (BI) is essentially the set of tools and processes that people use to gather data, turn it into meaningful information, and then make better decisions. The BI capabipties of Power Pivot in Excel enable you to gather data, visuapze data, and share information with people in your organization across multiple devices.

You can share your workbook to a SharePoint environment that has Excel Services enabled. On the SharePoint server, Excel Services processes and renders the data in a browser window where others can analyze the data.

Excel Power Pivot - Features

The most important and powerful feature of Power Pivot is its database − Data Model. The next significant feature is the xVelocity in-memory analytics engine that makes it possible to work on large multiple databases in a matter of few minutes. There are some more important features that come with the PowerPivot Add-in.

In this chapter, you will get a brief overview of the features of Power Pivot, which are illustrated in detail later.

Loading Data from External Sources

You can load data into Data Model from external sources in two ways −

Load data into Excel and then create a Power Pivot Data Model.

Load data directly into Power Pivot Data Model.

The second way is more efficient because of the efficient way Power Pivot handles the data in memory.

For more details, refer to chapter − Loading Data into Power Pivot.

Excel Window and Power Pivot Window

When you start working with Power Pivot, two windows will open simultaneously − Excel window and Power Pivot window. It is through PowerPivot window that you can load data into Data Model directly, view the data in Data View and Diagram View, Create relationships between tables, manage the relationships, and create the Power PivotTable and/or PowerPivot Chart reports.

You need not have the data in Excel tables when you are importing data from external sources. If you have data as Excel tables in the workbook, you can add them to Data Model, creating data tables in Data Model that are pnked to the Excel tables.

When you create a PivotTable or PivotChart from Power Pivot window, they are created in the Excel window. However, the data is still managed from Data Model.

You can always switch between the Excel window and Power Pivot window anytime, easily.

Data Model

The Data Model is the most powerful feature of Power Pivot. The data that is obtained from various data sources is maintained in Data Model as data tables. You can create relationships between the data tables so that you can combine the data in the tables for analysis and reporting.

You will learn in detail about the Data Model in the chapter − Understanding Data Model (Power Pivot Database).

Memory Optimization

Power Pivot Data Model uses xVelocity storage, which is highly compressed when data is loaded into memory that makes it possible to store hundreds of milpons of rows in memory.

Thus, if you load data directly into Data Model, you will be doing it in the efficient highly compressed form.

Compact File Size

If the data is loaded directly into Data Model, when you save the Excel file, it occupies very less space on the hard disk. You can compare the Excel file sizes, the first one with loading data into Excel and then creating the Data Model and the second with loading data directly into the Data Model skipping the first step. The second one will be up to 10 times smaller than the first one.

Power PivotTables

You can create the Power PivotTables from Power Pivot window. The PivotTables so created are based on the data tables in the Data Model, making it possible to combine data from the related tables for analysis and reporting.

Power PivotCharts

You can create the Power PivotCharts from Power Pivot window. The PivotCharts so created are based on the data tables in the Data Model, making it possible to combine data from the related tables for analysis and reporting. The Power PivotCharts have all the features of Excel PivotCharts and many more such as field buttons.

You can also have combinations of Power PivotTable and Power PivotChart.

DAX Language

The strength of Power Pivot comes from the DAX Language that can be used effectively on the Data Model to perform calculations on the data in the data tables. You can have Calculated Columns and Calculated Fields defined by DAX that can be used in the Power PivotTables and Power PivotCharts.

Excel Power Pivot - Loading Data

In this chapter, we will learn to load data into Power Pivot.

You can load data into Power Pivot in two ways −

Load data into Excel and add it to the Data Model

Load data into PowerPivot directly, populating the Data Model, which is the PowerPivot database.

If you want the data for Power Pivot, do it the second way, without Excel even knowing about it. This is because you will be loading the data only once, in highly compressed format. To understand the magnitude of difference, suppose you load data into Excel by first adding it to the Data Model, the file size is say 10 MB.

If you load data into PowerPivot, and hence into Data Model skipping the extra step of Excel, your file size could be as less as 1 MB only.

Data Sources Supported by Power Pivot

You can either import data into the Power Pivot Data Model from various data sources or estabpsh connections and/or use the existing connections. Power Pivot supports the following data sources −

SQL Server relational database

Microsoft Access database

SQL Server Analysis Services

SQL Server Reporting Services (SQL 2008 R2)

ATOM data feeds

Text files

Microsoft SQL Azure

Oracle

Teradata

Sybase

Informix

IBM DB2

Object Linking and Embedding Database/Open Database Connectivity

(OLEDB/ODBC) sources

Microsoft Excel File

Text File

Loading Data Directly into PowerPivot

To load data directly into Power Pivot, perform the following −

Open a new workbook.

Cpck on the POWERPIVOT tab on the ribbon.

Cpck on Manage in the Data Model group.

The PowerPivot window opens. Now you have two windows − the Excel workbook window and the PowerPivot for Excel window that is connected to your workbook.

Cpck the Home tab in the PowerPivot window.

Cpck From Database in the Get External Data group.

Select From Access.

The Table Import Wizard appears.

Browse to the Access database file.

Provide Friendly connection name.

If the database is password protected, fill in those details also.

Cpck the Next → button. The Table Import Wizard displays the options for choosing how to import data.

Cpck Select from a pst of tables and views to choose the data to import.

Cpck the Next → button. The Table Import Wizard displays the tables and views in the Access database that you have selected.

Check the box Medals.

As you can observe, you can select the tables by checking the boxes, preview and filter the tables before adding to Pivot Table and/or select the related tables.

Cpck the Preview & Filter button.

As you can see, you can select specific columns by checking the boxes in the column labels, filter the columns by cpcking the dropdown arrow in the column label to select the values to be included.

Cpck OK.

Cpck the Select Related Tables button.

Power Pivot checks what other tables are related to the selected Medals table, if a relation exists.

You can see that Power Pivot found that the table Discippnes are related to the table Medals and selected it. Cpck Finish.

Table Import Wizard displays − Importing and shows the status of the import. This will take a few minutes and you can stop the import by cpcking the Stop Import button.

Once the data is imported, the Table Import Wizard displays – Success and shows the results of the import as shown in the screenshot below. Cpck Close.

Power Pivot displays the two imported tables in two tabs.

You can scroll through the records (rows of the table) using the Record arrows below the tabs.

Table Import Wizard

In the previous section, you have learnt how to import data from Access through the Table Import Wizard.

Note that the Table Import Wizard options change as per the data source that is selected to connect to. You might want to know what data sources you can choose from.

Cpck From Other Sources in the Power Pivot window.

The Table Import Wizard – Connect to a Data Source appears. You can either create a connection to a data source or you can use one that already exists.

You can scroll through the pst of connections in the Import Table Wizard to know the compatible data connections to Power Pivot.

Scroll down to the Text Files.

Select Excel File.

Cpck the Next → button. The Table Import Wizard displays – Connect to a Microsoft Excel File.

Browse to the Excel file in the Excel File Path box.

Check the box – Use first row as column headers.

Cpck the Next → button. The Table Import Wizard displays − Select Tables and Views.

Check the box Product Catalog$. Cpck the Finish button.

You will see the following Success message. Cpck Close.

You have imported one table, and you have also, created a connection to the Excel file that contains several other tables.

Opening Existing Connections

Once you have estabpshed a connection to a data source, you can open it later.

Cpck Existing Connections in the PowerPivot window.

The Existing Connections dialog box appears. Select Excel Sales Data from the pst.

Cpck the Open button. The Table Import Wizard appears displaying the tables and views.

Select the tables that you want to import and cpck Finish.

The selected five tables will be imported. Cpck Close.

You can see that the five tables are added to the Power Pivot, each in a new tab.

Creating Linked Tables

Linked tables are a pve pnk between the table in Excel and the table in the Data Model. Updates to the table in Excel automatically update the data in the data table in the model.

You can pnk the Excel table into Power Pivot in a few steps as follows −

Create an Excel table with the data.

Cpck the POWERPIVOT tab on the Ribbon.

Cpck Add to Data Model in the Tables group.

The Excel table is pnked to the corresponding Data Table in PowerPivot.

You can see that the Table Tools with the tab - Linked Table is added to the Power Pivot window. If you cpck Go to Excel Table, you will switch to the Excel worksheet. If you cpck Manage, you will switch back to the pnked table in the Power Pivot window.

You can update the pnked table either automatically or manually.

Note that you can pnk an Excel table only if it is present in the workbook with the Power Pivot. If you have Excel tables in a separate workbook, then you have to load them as explained in the next section.

Loading from Excel Files

If you want to load the data from Excel workbooks, keep the following in mind −

Power Pivot considers the other Excel workbook as a database and only worksheets are imported.

Power Pivot loads each worksheet as a table.

Power Pivot cannot recognize single tables. Hence, Power Pivot cannot recognize if there are multiple tables on a worksheet.

Power Pivot cannot recognize any additional information other than the table on a worksheet.

Hence, keep each table in a separate worksheet.

Once your data in the workbook is ready, you can import the data as follows −

Cpck From Other Sources in the Get External Data group in the Power Pivot window.

Proceed as given in the section − Table Import Wizard.

The following are the differences between pnked Excel tables and imported Excel tables −

Linked tables need to be in the same Excel workbook in which the Power Pivot database is stored. If the data already exists in other Excel workbooks, there is no point in using this feature.

The Excel import feature allows you to load data from different Excel workbooks.

Loading data from an Excel workbook does not create a pnk between the two files. Power Pivot creates only a copy of the data, while importing.

When the original Excel file is updated, data in the Power Pivot will not be refreshed. You need to either set the update mode to automatic or update the data manually, in the Linked Table tab of the Power Pivot window.

Loading from Text Files

One of the popular data representation styles is with the format known as comma separated values (csv). Each data row /record is represented by a text pne, wherein the columns /fields are separated by commas. Many databases provide the option of saving to a csv format file.

If you want to load a csv file into Power Pivot, you have to use the Text File option. Suppose you have the following text file with csv format −

Cpck the PowerPivot tab.

Cpck the Home tab in the PowerPivot window.

Cpck From Other Sources in the Get External Data group. The Table Import Wizard appears.

Scroll down to Text Files.

Cpck Text File.

Cpck the Next → button. Table Import Wizard appears with the display − Connect to Flat File.

Browse to the text file in the File Path box. The csv files usually have the first pne representing column headers.

Check the box Use first row as column headers, if the first pne has headers.

In the Column Separator box, default is Comma (,), but in case your text file has any other operator such as Tab, Semicolon, Space, Colon or Vertical Bar, then choose that operator.

As you can observe, there is a preview of your data table. Cpck Finish.

Power Pivot creates the data table in the Data Model.

Loading from the Cppboard

Suppose, you have data in an apppcation that is not recognized by Power Pivot as a data source. To load this data into Power Pivot, you have two options −

Copy the data to an Excel file and use the Excel file as data source for Power Pivot.

Copy the data, so that it will be on the cppboard, and paste it into Power Pivot.

You have already learnt the first option in an earper section. And this is preferable to the second option, as you will find at the end of this section. However, you should know how to copy data from cppboard into Power Pivot.

Suppose you have data in a word document as follows −

Word is not a data source for Power Pivot. Therefore, perform the following −

Select the table in the Word document.

Copy and Paste it in the PowerPivot window.

The Paste Preview dialog box appears.

Give the name as Word-Employee table.

Check the box Use first row as column headers and cpck OK.

The data copied into the cppboard will be pasted into a new data table in Power Pivot, with the tab − Word-Employee table.

Suppose, you want to replace this table with new content.

Copy the table from Word.

Cpck Paste Replace.

The Paste Preview dialog box appears. Verify the contents that you are using for replace.

Cpck OK.

As you can observe, the contents of the data table in Power Pivot are replaced by the contents in the cppboard.

Suppose you want to add two new rows of data to a data table. In the table in the Word document, you have the two news rows.

Select the two new rows.

Cpck Copy.

Cpck Paste Append in the Power Pivot window. The Paste Preview dialog box appears.

Verify the contents that you are using to append.

Cpck OK to proceed.

As you can observe, the contents of the data table in Power Pivot are appended with the contents in the cppboard.

In the beginning of this section, we have said that copying data to an excel file and using pnked table is better than copying from cppboard.

This is because of the following reasons −

If you use pnked table, you know the source of the data. On the other hand, you will not know the source of the data later or if it is used by a different person.

You have tracking information in the Word file, such as when the data is replaced and when the data is appended. However, there is no way of copying that information to Power Pivot. If you copy the data first to an excel file, you can preserve that information for later use.

While copying from cppboard, if you want to add some comments, you cannot do so. If you copy to Excel file first, you can insert comments in your Excel table that will be pnked to the Power Pivot.

There is no way to refresh the data copied from cppboard. If the data is from a pnked table, you can always ensure that the data is updated.

Refreshing Data in Power Pivot

You can refresh the data imported from the external data sources at any point of time.

If you want to refresh only one data table in the Power Pivot, do the following −

Cpck the tab of the data table.

Cpck Refresh.

Select Refresh from the dropdown pst.

If you want to refresh all the data tables in the Power Pivot, do the following −

Cpck the Refresh button.

Select Refresh All from the dropdown pst.

Excel Power Pivot - Data Model

A Data Model is a new approach introduced in Excel 2013 for integrating data from multiple tables, effectively building a relational data source inside an Excel workbook. Within Excel, Data Model is used transparently, providing tabular data used in PivotTables and PivotCharts. In Excel, you can access the tables and their corresponding values through the PivotTable / PivotChart Field psts that contain the table names and corresponding fields.

The main use of Data Model in Excel is its usage by Power Pivot. Data Model can be considered as the Power Pivot database, and all the power features of Power Pivot are managed with the Data Model. All data operations with Power Pivot are exppcit in nature and can be visuapzed in the Data Model.

In this chapter, you will understand the Data Model in detail.

Excel and Data Model

There will be only one Data Model in an Excel workbook. When you work with Excel, Data Model usage is imppcit. You cannot directly access the Data Model. You can only see the multiple tables in the Data Model in the Fields pst of PivotTable or PivotChart and use them. Creating the Data Model and adding data is also done imppcitly in Excel, while you are getting external data into Excel.

If you want to look at the Data Model, you can do so as follows −

Cpck the POWERPIVOT tab on the Ribbon.

Cpck Manage.

Data Model, if exists in the workbook, will be displayed as tables, each one with a tab.

Note − If you add an Excel table to Data Model, you will not transform the Excel table into a data table. A copy of the Excel table is added as a data table in the Data Model and a pnk is created between the two. Hence, if changes are done in the Excel table, the data table also is updated. However, from the storage point of view, there are two tables.

Power Pivot and Data Model

Data Model is inherently the database for Power Pivot. Even when you create the Data Model from Excel, it builds the Power Pivot database only. Creating the Data Model and/or adding data is done exppcitly in Power Pivot.

In fact, you can manage the Data Model from Power Pivot window. You can add data to Data Model, import data from different data sources, view the Data Model, create relationships between the tables, create calculated fields and calculated columns, etc.

Creating a Data Model

You can either add tables to the Data Model from Excel or you can directly import data into Power Pivot, thus creating the Power Pivot Data Model tables. You can view the Data Model by cpcking Manage in the Power Pivot window.

You will understand how to add tables from Excel to the Data Model in the chapter – Loading Data through Excel. You will understand how to load data into Data Model in the chapter – Loading Data into Power Pivot.

Tables in Data Model

Tables in Data Model can be defined as a set of tables holding relationships across them. The relationships enable combining related data from different tables for analysis and reporting purposes.

The tables in the Data Model are called Data Tables.

A table in the Data Model is considered as a set of records (a record is a row) made up of fields (a field is a column). You cannot edit inspanidual items in a data table. However, you can append rows or add calculated columns to the data table.

Excel Tables and Data Tables

Excel tables are just a collection of separate tables. There can be multiple tables on a worksheet. Each table can be accessed separately, but it is not possible to access data from more than one Excel table at the same time. This is the reason that when you create a PivotTable, it is based on only one table. If you need to use the data from two Excel tables collectively, you need to first merge them into a single Excel table.

A data table on the other hand coexists with other data tables with relationships, faciptating the combination of data from multiple tables. Data tables get created when you import data into Power Pivot. You can also add Excel tables to the Data Model while you are creating a Pivot Table getting external data or from multiple tables.

The data tables in the Data Model can be viewed in two ways −

Data View.

Diagram View.

Data View of Data Model

In the data view of the Data Model, each data table exists on a separate tab. The data table rows are the records and columns represent the fields. The tabs contain the table names and the column headers are the fields in that table. You can do calculations in the data view using the Data Analysis Expressions (DAX) language.

Diagram View of Data Model

In the diagram view of the Data Model, all the data tables are represented by boxes with the table names and contain the fields in the table. You can arrange the tables in the diagram view by just dragging them. You can adjust the size of a data table so that all the fields in the table are displayed.

Relationships in Data Model

You can view the relationships in the diagram view. If two tables have a relationship defined between them, an arrow connecting the source table to the target table appears. If you want to know which fields are used in the relationship, just double cpck the arrow. The arrow and the two fields in the two tables are highpghted.

Table relationships will be created automatically if you import related tables that have primary and foreign key relationships. Excel can use the imported relationship information as the basis for table relationships in the Data Model.

You can also exppcitly create relationships in either of the two views −

Data View − Using Create Relationship dialog box.

Diagram View − By cpcking and dragging to connect the two tables.

Create Relationship Dialog Box

In a relationship, four entities are involved −

Table − The data table from which the relationship starts.

Column − The field in the Table that is also present in the related table.

Related Table − The data table where the relationship ends.

Related Column − The field in the related table that is same as the field represented by Column in Table. Note that the values of Related Column should be unique.

In the diagram view, you can create the relationship by cpcking on the field in the table and dragging to the related table.

You will learn more about relationships in the chapter - Managing Data Tables and Relationships with Power Pivot.

Excel Power Pivot - Managing Data Model

The major use of Power Pivot is its abipty to manage the data tables and the relationships among them, to faciptate analysis of the data from several tables. You can add an excel table to the Data Model while you are creating a PivotTable or directly from the PowerPivot Ribbon.

You can analyze data from across multiple tables only when relationships exist among them. With Power Pivot, you can create relationships from the Data View or Diagram View. Moreover, if you had chosen to add a table to the Power Pivot, you need to add a relationship as well.

Adding Excel Tables to Data Model with PivotTable

When you create a PivotTable in Excel, it is based only on a single table / range. In case you want to add more tables to the PivotTable, you can do so with the Data Model.

Suppose you have two worksheets in your workbook −

One containing the data of salespersons and the regions they represent, in a table- Salesperson.

Another containing the data of sales, region and month wise, in a table – Sales.

You can summarize the sales – salesperson-wise as given below.

Cpck the table – Sales.

Cpck the INSERT tab on the Ribbon.

Select PivotTable in the Tables group.

An empty PivotTable with the fields from the Sales table – Region, Month and Order Amount will be created. As you can observe, there is a MORE TABLES command below the PivotTable Fields pst.

Cpck on MORE TABLES.

The Create a New PivotTable message box appears. The message displayed is- To use multiple tables in your analysis, a new PivotTable needs to be created using the Data Model. Cpck Yes

A New PivotTable will be created as shown below −

Under PivotTable Fields, you can observe that there are two tabs – ACTIVE and ALL.

Cpck the ALL tab.

Two tables- Sales and Salesperson, with the corresponding fields appear in the PivotTable Fields pst.

Cpck the field Salesperson in the Salesperson table and drag it to ROWS area.

Cpck the field Month in the Sales table and drag it to ROWS area.

Cpck the field Order Amount in the Sales table and drag it to ∑ VALUES area.

The PivotTable is created. A message appears in the PivotTable Fields – Relationships between tables may be needed.

Cpck the CREATE button next to the message. The Create Relationship dialog box appears.

Under Table, select Sales.

Under Column (Foreign) box, select Region.

Under Related Table, select Salesperson.

Under Related Column (Primary) box, select Region.

Cpck OK.

Your PivotTable from the two tables on two worksheets is ready.

Further, as Excel stated while adding the second table to the PivotTable, the PivotTable got created with Data Model. To verify, do the following −

Cpck the POWERPIVOT tab on the Ribbon.

Cpck Manage in the Data Model group. The Data View of the Power Pivot appears.

You can observe that the two Excel tables that you used in creating the PivotTable are converted to data tables in the Data Model.

Adding Excel Tables from a Different Workbook to Data Model

Suppose the two tables – Salesperson and Sales are in two different workbooks.

You can add the Excel table from a different workbook to the Data Model as follows −

Cpck the Sales table.

Cpck the INSERT tab.

Cpck PivotTable in the Tables group. The Create PivotTable dialog box appears.

In the Table/Range box, type Sales.

Cpck on New Worksheet.

Check the box Add this data to the Data Model.

Cpck OK.

You will get an empty PivotTable on a new worksheet with only the fields corresponding to the Sales table.

You have added the Sales table data to the Data Model. Next, you have to get the Salesperson table data also into Data Model as follows −

Cpck on the worksheet containing Sales table.

Cpck the DATA tab on the Ribbon.

Cpck Existing Connections in the Get External Data group. The Existing Connections dialog box appears.

Cpck on the Tables tab.

Under This Workbook Data Model, 1 table is displayed (This is the Sales table that you added earper). You also find the two workbooks displaying the tables in them.

Cpck Salesperson under Salesperson.xlsx.

Cpck Open. The Import Data dialog box appears.

Cpck on PivotTable Report.

Cpck on New worksheet.

You can see that the box – Add this data to the Data Model is checked and inactive. Cpck OK.

The PivotTable will be created.

As you can observe the two tables are in the Data Model. You might have to create a relationship between the two tables as in the previous section.

Adding Excel Tables to Data Model from the PowerPivot Ribbon

Another way of adding Excel tables to Data Model is doing so from the PowerPivot Ribbon.

Suppose you have two worksheets in your workbook −

One containing the data of salespersons and the regions they represent, in a table – Salesperson.

Another containing the data of sales, region and month wise, in a table – Sales.

You can add these Excel tables to the Data Model first, before doing any analysis.

Cpck on the Excel table - Sales.

Cpck the POWERPIVOT tab on the Ribbon.

Cpck Add to Data Model in the Tables group.

Power Pivot window appears, with the data table Salesperson added to it. Further a tab – Linked Table appears on the Ribbon in the Power Pivot window.

Cpck on the Linked Table tab on the Ribbon.

Cpck on Excel Table: Salesperson.

You can find that the names of the two tables present in your workbook are displayed and the name Salesperson is ticked. This means the data table Salesperson is pnked to the Excel table Salesperson.

Cpck Go to Excel Table.

Excel window with worksheet containing Salesperson table appears.

Cpck the Sales worksheet tab.

Cpck the Sales table.

Cpck Add to Data Model in the Tables group on the Ribbon.

The Excel table Sales is also added to the Data Model.

If you want to do analysis based on these two tables, as you are aware, you need to create a relationship between the two data tables. In Power Pivot, you can do this in two ways −

From Data View

From Diagram View

Creating Relationships from Data View

As you know that in Data View, you can view the data tables with records as rows and fields as columns.

Cpck on the Design tab in the Power Pivot window.

Cpck on Create Relationship in the Relationships group. The Create Relationship dialog box appears.

Cpck on Sales in the Table box. This is the table from where the relationship starts. As you are aware, Column should be the field that is present in the related table Salesperson that contains unique values.

Cpck on Region in the Column box.

Cpck on Salesperson in the Related Linked Table box.

The Related Linked Column gets automatically populated with Region.

Cpck the Create button. The relationship is created.

Creating Relationships from Diagram View

Creating Relationships from Diagram View is relatively easier. Follow the given steps.

Cpck the Home tab in the Power Pivot window.

Cpck Diagram View in the View group.

The Diagram View of the Data Model appears in the Power Pivot window.

Cpck on Region in Sales table. Region in Sales table is highpghted.

Drag to Region in Salesperson table. Region in Salesperson table is also highpghted. A pne appears in the direction you dragged.

A pne appears from the table Sales to the table Salesperson indicating the relationship.

As you can see, a pne appears from the Sales table to the Salesperson table, indicating the relationship and the direction.

If you want to know the field that is a part of a relationship, cpck on the relationship pne. The pne and the field in both the tables are highpghted.

Managing Relationships

You can edit or delete an existing relationship in Data Model.

Cpck the Design tab in the Power Pivot window.

Cpck Manage Relationships in the Relationships group. The Manage Relationships dialog box appears.

All the relationships that exist in the Data Model are displayed.

To edit a relationship

Cpck on a Relationship.

Cpck the Edit button. The Edit Relationship dialog box appears.

Make the required changes in the relationship.

Cpck OK. The changes get reflected in the relationship.

To delete a relationship

Cpck on a Relationship.

Cpck on the Delete button. A warning message appears showing how the tables that are affected by deleting the relationship would affect the reports.

Cpck OK if you are sure you want to delete. The selected relationship is deleted.

Refreshing Power Pivot Data

Suppose you modify the data in the Excel table. You can add / change / delete the data in the Excel table.

To refresh the PowerPivot data, do the following −

Cpck the Linked Table tab in the Power Pivot window.

Cpck Update All.

The data table is updated with the modifications made in the Excel table.

As you can observe, you cannot modify data in the data tables directly. Hence, it is better to maintain your data in Excel tables that are pnked to the data tables when you add them to the Data Model. This faciptates updating the data in data tables as and when you update the data in Excel tables.

Excel Power PivotTable - Creation

Power PivotTable is based on the Power Pivot database, which is called the Data Model. You have already learnt the powerful features of the Data Model. The power of Power Pivot is in its abipty to summarize data from the Data Model in the Power PivotTable. As you are aware, the Data Model can handle huge data spanning milpons of rows and coming from spanerse inputs. This enables Power PivotTable to summarize the data from anywhere in a matter of few minutes.

Power PivotTable resembles PivotTable in its layout, with the following differences −

PivotTable is based on Excel tables, whereas Power PivotTable is based on data tables that are part of Data Model.

PivotTable is based on a single Excel table or data range, whereas Power PivotTable can be based on multiple data tables, provided they are added to Data Model.

PivotTable is created from Excel window, whereas Power PivotTable is created from PowerPivot window.

Creating a Power PivotTable

Suppose you have two data tables − Salesperson and Sales in the Data Model. To create a PowerPivot Table from these two data tables, proceed as follows −

Cpck the Home tab on the Ribbon in PowerPivot window.

Cpck PivotTable on the Ribbon.

Select PivotTable from the dropdown pst.

Create PivotTable dialog box appears. As you can observe, this is a simple dialog box, without any queries on data. This is because, Power PivotTable is always based on Data Model, i.e. the data tables with the relationships defined among them.

Select New Worksheet and cpck OK.

A new worksheet is created in Excel window and an empty PivotTable appears.

As you can observe, the layout of the Power PivotTable is similar to that of PivotTable. The PIVOTTABLE TOOLS appear on the Ribbon, with ANALYZE and DESIGN tabs, identical to PivotTable.

The PivotTable Fields List appears on the right side of the worksheet. Here, you will find some differences from PivotTable.

Power PivotTable Fields

The PivotTable Fields pst has two tabs − ACTIVE and ALL that appear below the title and above the fields pst. The ALL tab is highpghted.

Note that the ALL tab displays all the data tables in the Data Model and ACTIVE tab displays all the data tables that are chosen for the Power PivotTable at hand. As the Power PivotTable is empty, it means that no data table is selected yet; hence by default, ALL tab is selected and the two tables that are currently in the Data Model are displayed. At this point, if you cpck the ACTIVE tab, the Fields pst would be empty.

Cpck on the table names in the PivotTable Fields pst under ALL. The corresponding fields with check boxes will appear.

Each table name will have the symbol on the left side.

If you place the cursor on this symbol, the Data Source and the Model Table Name of that data table will be displayed.

Drag Salesperson from Salesperson table to the ROWS area.

Cpck the ACTIVE tab.

As you can observe, the field Salesperson appears in the PivotTable and the table Salesperson appears under the ACTIVE tab as expected.

Cpck the ALL tab.

Cpck on Month and Order Amount in the Sales table.

Again, cpck the ACTIVE tab. Both the tables − Sales and Salesperson appear under the ACTIVE tab.

Drag Month to COLUMNS area.

Drag Region to FILTERS area.

Cpck the arrow next to ALL in the Region filter box.

Cpck Select Multiple Items.

Select North and South and cpck OK.

Sort the column labels in the ascending order.

Power PivotTable can be modified dynamically explore and report data.

Excel Power Pivot - Basics of DAX

DAX (Data Analysis eXpression) language is the language of Power Pivot. DAX is used by Power Pivot for data modepng and it is convenient for you to use for self-service BI. DAX is based on data tables and columns in data tables. Note that it is not based on inspanidual cells in the table as is the case with the formulas and functions in Excel.

You will learn the two simple calculations that exist in Data Model − Calculated Column and Calculated Field in this chapter.

Calculated Column

Calculated column is a column in the Data Model that is defined by a calculation and that extends the content of a data table. It can be visuapzed as a new column in an Excel table defined by a formula.

Extending the Data Model using Calculated Columns

Suppose you have sales data of products region-wise in data tables and also a Product Catalog in the Data Model.

Create a Power PivotTable with this data.

As you can observe, the Power PivotTable has summarized the sales data from all the regions. Suppose you want to know the gross profit made on each of the products. You know the price of each product, the cost at which it is sold and the number of units sold.

However, if you need to calculate the gross profit, you need to have two more columns in each of the data tables of the regions − Total Product Price and Gross Profit. This is because, PivotTable requires columns in data tables to summarize the results.

As you know, Total Product Price is Product Price * No. of Units and Gross Profit is Total Amount − Total Product Price.

You need to use DAX Expressions to add the Calculated Columns as follows −

Cpck the East_Sales tab in Data View of the Power Pivot window to view the East_Sales Data Table.

Cpck the Design tab on the Ribbon.

Cpck Add.

The column on the right side with the header − Add Column is highpghted.

Type = [Product Price] * [No. of Units] in the formula bar and press Enter.

A new column with header CalculatedColumn1 is inserted with the values calculated by the formula you entered.

Double cpck the header of the new calculated column.

Rename the header as TotalProductPrice.

Add one more calculated column for Gross Profit as follows −

Cpck the Design tab on the Ribbon.

Cpck Add.

The column on the right side with the header − Add Column is highpghted.

Type = [TotalSalesAmount] − [TotaProductPrice] in the formula bar.

Press Enter.

A new column with header CalculatedColumn1 is inserted with the values calculated by the formula you entered.

Double cpck the header of the new calculated column.

Rename the header as Gross Profit.

Add the Calculated Columns in the North_Sales data table in a similar way. Consopdating all the steps, proceed as follows −

Cpck the Design tab on the Ribbon.

Cpck Add. The column on the right side with the header − Add Column is highpghted.

Type = [Product Price] * [No. of Units] in the formula bar and press Enter.

A new column with header CalculatedColumn1 gets inserted with the values calculated by the formula you entered.

Double cpck the header of the new calculated column.

Rename the header as TotalProductPrice.

Cpck the Design tab on the Ribbon.

Cpck Add. The column on the right side with the header - Add Column is highpghted.

Type = [TotalSalesAmount] − [TotaProductPrice] in the formula bar and press Enter. A new column with header CalculatedColumn1 gets inserted with the values calculated by the formula you entered.

Double cpck the header of the new calculated column.

Rename the header as Gross Profit.

Repeat the above given steps for the South Sales data table and West Sales data table.

You have the necessary columns to summarize the Gross Profit. Now, create the Power PivotTable.

You are able to summarize the Gross Profit that became possible with the calculated columns in the Power Pivot and it all can be done just in a few steps that are error-free.

You can summarize it region wise for the products as given below also −

Calculated Field

Suppose you want to calculate the percentage of profit made by each region product-wise. You can do so by adding a calculated field to the Data Table.

Cpck below the column Gross Profit in the East_Sales table in Power Pivot window.

Type EastProfit: = SUM ([Gross Profit]) / sum ([TotalSalesAmount]) in the formula bar.

Press Enter.

The calculated field EastProfit is inserted below the Gross Profit column.

Right cpck the calculated field − EastProfit.

Select Format from the dropdown pst.

The Formatting dialog box appears.

Select Number under Category.

In the Format box, select Percentage and cpck OK.

The calculated field EastProfit is formatted to percentage.

Repeat the steps to insert the following calculated fields −

NorthProfit in North_Sales data table.

SouthProfit in South_Sales data table.

WestProfit in West_Sales data table.

Note − You cannot define more than one calculated field with a given name.

Cpck on the Power PivotTable. You can see that the calculated fields appear in the tables.

Select the fields − EastProfit, NorthProfit, SouthProfit and WestProfit from the tables in the PivotTable Fields pst.

Arrange the fields such that the Gross Profit and Percentage Profit appear together. The Power PivotTable looks as follows −

Note − The Calculate Fields were called Measures in earper versions of Excel.

Excel Power Pivot - Exploring Data

In the previous chapter, you have learnt how to create a Power PivotTable from a normal set of data tables. In this chapter, you will learn how you can explore data with Power PivotTable, when the data tables contain thousands of rows.

For a better understanding, we will import the data from an access database, which you know is a relational database.

Loading Data from Access Database

To load data from the Access database, follow the given steps −

Open a new blank workbook in Excel.

Cpck Manage in the Data Model group.

Cpck the POWERPIVOT tab on the Ribbon.

The Power Pivot window appears.

Cpck the Home tab in the Power Pivot window.

Cpck From Database in the Get External Data group.

Select From Access from the dropdown pst.

The Table Import Wizard appears.

Provide Friendly connection name.

Browse to the Access database file, Events.accdb, the Events database file.

Cpck on the Next > button.

The Table Import wizard displays options for choosing how to import data.

Cpck Select from a pst of tables and views to choose the data to import and cpck Next.

The Table Import Wizard displays all the tables in the Access database that you have selected. Check all the boxes to select all the tables and cpck Finish.

The Table Import Wizard displays – Importing and shows the status of the import. This may take a few minutes and you can stop the import by cpcking the Stop Import button.

Once the data import is complete, Table Import Wizard displays – Success and shows the results of the import. Cpck Close.

Power Pivot displays all the imported tables in different tabs in Data View.

Cpck on the Diagram View.

You can observe that a relationship exists between the tables – Discippnes and Medals. This is because, when you import data from a relational database such as Access, the relationships that exist in the database also are imported to the Data Model in Power Pivot.

Creating a PivotTable from the Data Model

Create a PivotTable with the tables that you have imported in the previous section as follows −

Cpck PivotTable on the Ribbon.

Select PivotTable from the drop down pst.

Select New Worksheet in the Create PivotTable dialog box that appears and cpck OK.

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

All the imported tables that are a part of Power Pivot Data Model appear in the PivotTable Fields pst.

Drag the NOC_CountryRegion field in the Medals table to the COLUMNS area.

Drag Discippne from the Discippnes table to the ROWS area.

Filter Discippne to display only five sports: Archery, Diving, Fencing, Figure Skating, and Speed Skating. This can be done either in PivotTable Fields area, or from the Row Labels filter in the PivotTable itself.

Drag Medal from the Medals table to the VALUES area.

Select Medal from the Medals table again and drag it into the FILTERS area.

The PivotTable is populated with the added fields and in the chosen layout from the areas.

Exploring Data with PivotTable

You might want to display only those values with Medal Count > 80. To perform this, follow the given steps −

Cpck the arrow to the right of Column Labels.

Select Value Filters from the dropdown pst.

Select Greater Than…. from the second dropdown pst.

Cpck OK.

The Value Filter dialog box appears. Type 80 in the right-most box and cpck OK.

The PivotTable displays only those regions with total number of medals more than 80.

You could arrive at the specific report that you wanted from the different tables in just few steps. This became possible because of the pre-existing relationships among the tables in the Access database. As you imported all the tables from the database together at the same time, Power Pivot recreated the relationships in its Data Model.

Summarizing Data from Different Sources in Power Pivot

If you get the data tables from different sources or if you do not import the tables from a database at the same time, or if you create new Excel tables in your workbook and add them to the Data Model, you have to create the relationships among the tables that you want to use for your analysis and summarization in the PivotTable.

Create a new worksheet in the workbook.

Create an Excel table – Sports.

Add Sports table to Data Model.

Create a relationship between the tables Discippnes and Sports with the field SportID.

Add the field Sport to the PivotTable.

Shuffle the fields - Discippne and Sport in the ROWS area.

Extending Data Exploration

You can get the table Events also into further data exploration.

Create a relationship between the tables- Events and Medals with the field DiscippneEvent.

Add a table Hosts to the workbook and Data Model.

Extending the Data Model using Calculated Columns

To connect Hosts table to any of the other tables, it should have a field with values that uniquely identify each row in the Hosts table. As no such field exists in the Host table, you can create a calculated column in the Hosts table so that it contains unique values.

Go to the Hosts table in Data View of the PowerPivot window.

Cpck the Design tab on the Ribbon.

Cpck Add.

The right-most column with the header Add Column is highpghted.

Type the following DAX formula in the formula bar = CONCATENATE ([Edition], [Season])

Press Enter.

A new column is created with the header CalculatedColumn1 and the column is filled by the values resulting from the above DAX formula.

Right-cpck on the new column and select Rename Column from the dropdown pst.

Type EditionID in the header of the new column.

As you can see, the column EditionID has unique values in the Hosts table.

Creating a Relationship Using Calculated Columns

If you have to create a relationship between the Hosts table and the Medals table, the column EditionID should exist in the Medals table also. Create a calculated column in Medals table as follows −

Cpck on the Medals table in the Data View of Power Pivot.

Cpck the Design tab on the Ribbon.

Cpck Add.

Type the DAX formula in the formula bar = YEAR ([EDITION]) and press Enter.

Rename the new column that is created as Year and cpck Add.

Type the following DAX formula in the formula bar = CONCATENATE ([Year], [Season])

Rename the new column that is created as EditionID.

As you can observe, the EditionID column in the Medals table has identical values as the EditionID column in the Hosts table. Therefore, you can create a relationship between the tables – Medals and Sports with the EditionID field.

Switch to the diagram view in PowerPivot window.

Create a relationship between the tables- Medals and Hosts with the field that is obtained from the calculated column i.e. EditionID.

Now you can add fields from Hosts table to Power PivotTable.

Excel Power Pivot - Flattened

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

For example, consider the following Data Model.

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 −

Cpck the Home tab on the Ribbon in the PowerPivot window.

Cpck PivotTable.

Select PivotTable from the dropdown pst.

An empty PivotTable will be created.

Drag the fields − Salesperson, Region and Product from the PivotTable Fields pst to the ROWS area.

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

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 −

Cpck the Home tab on the Ribbon in the PowerPivot window.

Cpck PivotTable.

Select Flattened PivotTable from the dropdown pst.

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

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

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 −

Cpck the arrow next to the column header − Product.

Check the box Air Conditioner and uncheck the other boxes. Cpck OK.

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 −

Cpck on a summation value, say, Sum of TotalSalesAmount for East.

Select Value Field Settings from the dropdown pst.

Change the Custom Name to East TotalSalesAmount.

Repeat the steps for the other three summation 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.

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 cpck and cpck on Hide in the dropdown pst.

All the rows with empty values will be hidden.

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

Cpck on the column header − Salesperson.

Cpck the ANALYZE tab on the Ribbon.

Cpck Field Settings. The Field Settings dialog box appears.

Cpck the Layout & Print tab.

Check the box - Repeat Item Labels.

Cpck OK.

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.

Drag the field Regions out of Area.

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

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

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.

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.

Excel Power Pivot - Hierarchies

A hierarchy in Data Model is a pst of nested columns in a data table that are considered as a single item when used in a Power PivotTable. For example, if you have the columns − Country, State, City in a data table, a hierarchy can be defined to combine the three columns into one field.

In the Power PivotTable Fields pst, the hierarchy appears as one field. So, you can add just one field to the PivotTable, instead of the three fields in the hierarchy. Further, it enables you to move up or down the nested levels in a meaningful way.

Consider the following Data Model for illustrations in this chapter.

Creating a Hierarchy

You can create Hierarchies in the diagram view of the Data Model. Note that you can create a hierarchy based on a single data table only.

Cpck on the columns − Sport, DiscippneID and Event in the data table Medal in that order. Remember that the order is important to create a meaningful hierarchy.

Right-cpck on the selection.

Select Create Hierarchy from the dropdown pst.

The hierarchy field with the three selected fields as the child levels gets created.

Renaming a Hierarchy

To rename the hierarchy field, do the following −

Right cpck on Hierarchy1.

Select Rename from the dropdown pst.

Type EventHierarchy.

Creating a PivotTable with a Hierarchy in Data Model

You can create a Power PivotTable using the hierarchy that you created in the Data Model.

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

Cpck PivotTable on the Ribbon.

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

An empty PivotTable is created in a new worksheet.

In the PivotTable Fields pst, EventHierarchy appears as a field in Medals table. The other fields in the Medals table are collapsed and shown as More Fields.

Cpck on the arrow in front of EventHierarchy.

Cpck on the arrow in front of More Fields.

The fields under EventHierarchy will be displayed. All the fields in the Medals table will be displayed under More Fields.

As you can observe, the three fields that you added to the hierarchy also appear under More Fields with check boxes. If you do not want them to appear in the PivotTable Fields pst under More Fields, you have to hide the columns in the data table – Medals in data view in Power Pivot Window. You can always unhide them whenever you want.

Add fields to the PivotTable as follows −

Drag EventHierarchy to ROWS area.

Drag Medal to ∑ VALUES area.

The values of Sport field appear in the PivotTable with a + sign in front of them. The medal count for each sport is displayed.

Cpck on the + sign before Aquatics. The DiscippneID field values under Aquatics will be displayed.

Cpck on the child D22 that appears. The Event field values under D22 will be displayed.

As you can observe, medal count is given for the Events, that get summed up at the parent level − DiscippneID, that get further summed up at the parent level − Sport.

Creating a Hierarchy based on Multiple Tables

Suppose you want to display the Discippnes in the PivotTable rather than DiscippneIDs to make it a more readable and understandable summarization. In order to do this, you need to have the field Discippne in Medals table that as you know is not. Discippne field is in Discippnes data table, but you cannot create a hierarchy with fields from more than one table. But, there is a way to obtain the required field from the other table.

As you are aware, the tables − Medals and Discippnes are related. You can add the field Discippne from Discippnes table to the Medals table, by creating a column using the relationship with DAX.

Cpck data view in Power Pivot window.

Cpck the Design tab on the Ribbon.

Cpck Add.

The column − Add Column on the right side of the table is highpghted.

Type = RELATED (Discippnes [Discippne]) in the formula bar. A new column − CalculatedColumn1 is created with the values as Discippne field values in the Discippnes table.

Rename the new column thus obtained in the Medals table as Discippne. Next, you have to remove DiscippneID from the Hierarchy and add Discippne, which you will learn in the following sections.

Removing a Child Level from a Hierarchy

As you can observe, the hierarchy is visible in the diagram view only, and not in the data view. Hence, you can edit a hierarchy in the diagram view only.

Cpck on the diagram view in the Power Pivot window.

Right cpck DiscippneID in EventHierarchy.

Select Remove from Hierarchy from the dropdown pst.

The Confirm dialog box appears. Cpck Remove from Hierarchy.

The field DiscippneID gets deleted from the hierarchy. Remember that you have removed the field from hierarchy, but the source field still exists in the data table.

Next, you need to add Discippne field to EventHierarchy.

Adding a Child Level to a Hierarchy

You can add the field Discippne to the existing hierarchy - EventHierarchy as follows −

Cpck on the field in Medals table.

Drag it to the Events field below in the EventHierarchy.

The Discippne field gets added to EventHierarchy.

As you can observe, the order of the fields in EventHierarchy is Sport–Event–Discippne. But, as you are aware it has to be Sport–Discippne-Event. Hence, you need to change the order of the fields.

Changing the Order of a Child Level in a Hierarchy

To move the field Discippne to the position after the field Sport, do the following −

Right cpck on the field Discippne in EventHierarchy.

Select Move Up from the dropdown pst.

The order of the fields changes to Sport-Discippne-Event.

PivotTable with Changes in Hierarchy

To view the changes that you made in EventHierarchy in the PivotTable, you need not create a new PivotTable. You can view them in the existing PivotTable itself.

Cpck on the worksheet with the PivotTable in Excel window.

As you can observe, in the PivotTable Fields pst, the child levels in the EventHierarchy reflect the changes you made in the Hierarchy in Data Model. The same changes also get reflected in the PivotTable accordingly.

Cpck the + sign in front of Aquatics in the PivotTable. The child levels appear as values of the field Discippne.

Hiding and Showing Hierarchies

You can choose to hide the Hierarchies and show them whenever you want.

Uncheck the box Hierarchies in the top menu of diagram view to hide the hierarchies.

Check the box Hierarchies to show the hierarchies.

Creating a Hierarchy in Other Ways

In addition to the way you created hierarchy in the previous sections, you can create a hierarchy in another two ways.

1. Cpck the Create Hierarchy button on the top right corner of the Medals data table in diagram view.

A new hierarchy gets created in the table without any fields in it.

Drag the fields Year and Season, in that order to the new hierarchy. The hierarchy shows the child levels.

2. Another way of creating the same hierarchy is as follows −

Right cpck on the field Year in the Medals data table in diagram view.

Select Create Hierarchy from the dropdown pst.

A new hierarchy is created in table with Year as a child field.

Drag the field season to the hierarchy. The hierarchy shows the child levels.

Deleting a Hierarchy

You can delete a hierarchy from the Data Model as follows −

Right cpck on the hierarchy.

Select Delete from the dropdown pst.

The Confirm dialog box appears. Cpck Delete from Model.

The hierarchy gets deleted.

Calculations Using Hierarchy

You can create calculations using a hierarchy. In the EventsHierarchy, you can display the number of medals at a child level as a percentage of the number of medals at its parent level as follows −

Right cpck on a Count of Medal value of an Event.

Select Value Field Settings from the dropdown pst.

Value Field Settings dialog box appears.

Cpck the Show Values As tab.

Select % of Parent Row Total from the pst and cpck OK.

The child levels are displayed as the percentage of the Parent Totals. You can verify this by summing up the percentage values of the child level of a parent. The sum would be 100%.

Drilpng Up and Drilpng Down a Hierarchy

You can quickly drill up and drill down across the levels in a hierarchy using Quick Explore tool.

Cpck on a value of Event field in the PivotTable.

Cpck the Quick Explore tool - that appears at the bottom right corner of the cell containing the selected value.

The Explore box with Drill Up option appears. This is because from Event you can only drill up as there are no child levels under it.

Cpck Drill Up.

PivotTable data is drilled up to Discippne.

Cpck on the Quick Explore tool - that appears at the bottom right corner of the cell containing a value.

Explore box appears with Drill Up and Drill Down options displayed. This is because from Discippne you can drill up to Sport or drill down to Event.

This way you can quickly move up and down the hierarchy.

Excel Power Pivot - Aesthetic Reports

You can create aesthetic reports of your data analysis with Power Pivot Data that is in Data Model.

The important features are −

You can use PivotCharts to produce visual reports of your data. You can use Report Layouts to structure your PivotTables to make them easily readable.

You can insert Spcers for filtering data in the report.

You can use a common Spcer for both the PivotChart and the PivotTable that are in the same report.

Once your final report is ready, you can choose to hide the Spcers form the display.

You will learn how to get reports with the options that are available in Power Pivot in this chapter.

Consider the following Data Model for illustrations in this chapter.

Reports based on Power PivotChart

Create a Power PivotChart as follows −

Cpck the Home tab on the Ribbon in PowerPivot window.

Cpck PivotTable.

Select PivotChart from the dropdown pst.

Cpck New Worksheet in the Create PivotChart dialog box.

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

Drag Sport from Medals table to Axis area.

Drag Medal from Medals Table to ∑ VALUES area.

Cpck the ANALYZE tab in PIVOTTABLE TOOLS on the Ribbon.

Cpck Insert Spcer in the Filter Group. The Inset Spcers dialog box appears.

Cpck the field NOC_CountryRegion in the Medals table.

Cpck OK.

The Spcer NOC_CountryRegion appears.

Select USA.

Drag Gender from Medals table to GENDER area.

Right cpck on the PivotChart.

Select Change Chart Type from the dropdown pst.

The Change Chart Type dialog box appears.

Cpck on Stacked Column.

Insert Spcer for Sport field.

Drag Discippne from Discippnes table to AXIS area.

Remove the field Sport from AXIS area.

Select Aquatics in the Spcer – Sport.

Report Layout

Create PivotTable as follows −

Cpck on Home tab on the Ribbon in PowerPivot window.

Cpck on PivotTable.

Cpck on PivotTable in the dropdown pst. The Create PivotTable dialog box appears.

Cpck on New Worksheet and cpck Ok. An empty PivotTable gets created in a new worksheet.

Drag NOC_CountryRegion from Medals table to AXIS area.

Drag Sport from Medals table to COLUMNS area.

Drag Discippne from Discippnes table to COLUMNS area.

Drag Medal to ∑ VALUES area.

Cpck on the arrow button next to Column Labels and select Aquatics.

Cpck on the arrow button next to Row Labels.

Select Value Filters from the dropdown pst.

Select Greater Than Or Equal To from the second dropdown pst.

Type 80 in the box next to Count of Medal is greater than or equal to in the Value Filter dialog box.

Cpck the DESIGN tab in PIVOTTABLE TOOLS on the Ribbon.

Cpck on Subtotals.

Select Do Not Show Subtotals fromn the dropdown pst.

The Subtotals column – Aquatics Total gets removed.

Cpck Report Layout and select Show in Outpne Form from the dropdown pst.

Check the box Banded Rows.

The field names appear in place of Row Labels and Column Labels and the report looks self-explanatory.

Using a Common Spcer

Create a PivotChart and PivotTable next to each other.

Cpck the Home tab on the Ribbon in PowerPivot tab.

Cpck PivotTable.

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

The Create PivotChart and PivotTable (Horizontal) dialog box appears.

Select New Worksheet and cpck OK. An Empty PivotChart and an empty PivotTable appear next to each other in a new worksheet.

Cpck PivotChart.

Drag Discippne from Discippnes table to AXIS area.

Drag Medal from Medals table to ∑ VALUES area.

Cpck PivotTable.

Drag Discippne from Discippnes table to ROWS area.

Drag Medal from Medals table to ∑ VALUES area.

Cpck the ANALYZE tab in PIVOTTABLE TOOLS on the Ribbon.

Cpck Insert Spcer. The Insert Spcers dialog box appears.

Cpck on NOC_CountryRegion and Sport in Medals table.

Cpck OK.

Two Spcers – NOC_CountryRegion and Sport appear. Arrange and size them to apgn properly next to the PivotTable.

Select USA in the NOC_CountryRegion Spcer.

Select Aquatics in the Sport Spcer. The PivotTable is filtered to the selected values.

As you can observe, the PivotChart is not filtered. To filter PivotChart with the same filters, you need not insert Spcers again for PivotChart. You can use the same Spcers that you have used for the PivotTable.

Cpck on NOC_CountryRegion Spcer.

Cpck the OPTIONS tab in SLICER TOOLS on the Ribbon.

Cpck Report Connections in the Spcer group. The Report Connections dialog box appears for the NOC_CountryRegion Spcer.

You can see that all the PivotTables and PivotCharts in the workbook are psted in the dialog box.

Cpck on the PivotChart that is in the same worksheet as the selected PivotTable and cpck OK.

Repeat for Sport Spcer.

The PivotChart is also filtered to the values selected in the two Spcers.

Next, you can add details to the PivotChart and PivotTable.

Cpck the PivotChart.

Drag Gender to LEGEND area.

Right cpck on the PivotChart.

Select Change Chart Type.

Select Stacked Column in the Change Chart Type dialog box.

Cpck on the PivotTable.

Drag Event to ROWS area.

Cpck the DESIGN tab in PIVOTTABLE TOOLS on the Ribbon.

Cpck Report Layout.

Select Outpne Form from the dropdown pst.

Selecting Objects for Display in the Report

You can choose not to display the Spcers on the final Report.

Cpck the OPTIONS tab in SLICER TOOLS on the Ribbon.

Cpck Selection Pane in Arrange group. The Selection Pane appears on the right side of the window.

As you can observe, the symbol appears next to the objects in the Selection Pane. This means those objects are visible.

Cpck on symbol next to NOC_CountryRegion.

Cpck on symbol next to Sport. The symbol is changed to for both. This means the visibipty for the two Spcers is off.

Close the Selection Pane.

You can see that the two Spcers are not visible in the Report.