Category: excel Power Pivot

Excel Power Pivot – Aesthetic Reports ”; Previous Next 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 Slicers for filtering data in the report. You can use a common Slicer 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 Slicers 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 − Click the Home tab on the Ribbon in PowerPivot window. Click PivotTable. Select PivotChart from the dropdown list. Click 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. Click the ANALYZE tab in PIVOTTABLE TOOLS on the Ribbon. Click Insert Slicer in the Filter Group. The Inset Slicers dialog box appears. Click the field NOC_CountryRegion in the Medals table. Click OK. The Slicer NOC_CountryRegion appears. Select USA. Drag Gender from Medals table to GENDER area. Right click on the PivotChart. Select Change Chart Type from the dropdown list. The Change Chart Type dialog box appears. Click on Stacked Column. Insert Slicer for Sport field. Drag Discipline from Disciplines table to AXIS area. Remove the field Sport from AXIS area. Select Aquatics in the Slicer – Sport. Report Layout Create PivotTable as follows − Click on Home tab on the Ribbon in PowerPivot window. Click on PivotTable. Click on PivotTable in the dropdown list. The Create PivotTable dialog box appears. Click on New Worksheet and click 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 Discipline from Disciplines table to COLUMNS area. Drag Medal to ∑ VALUES area. Click on the arrow button next to Column Labels and select Aquatics. Click on the arrow button next to Row Labels. Select Value Filters from the dropdown list. Select Greater Than Or Equal To from the second dropdown list. Type 80 in the box next to Count of Medal is greater than or equal to in the Value Filter dialog box. Click the DESIGN tab in PIVOTTABLE TOOLS on the Ribbon. Click on Subtotals. Select Do Not Show Subtotals fromn the dropdown list. The Subtotals column – Aquatics Total gets removed. Click Report Layout and select Show in Outline Form from the dropdown list. 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 Slicer Create a PivotChart and PivotTable next to each other. Click the Home tab on the Ribbon in PowerPivot tab. Click PivotTable. Select Chart and Table (Horizontal) from the dropdown list. The Create PivotChart and PivotTable (Horizontal) dialog box appears. Select New Worksheet and click OK. An Empty PivotChart and an empty PivotTable appear next to each other in a new worksheet. Click PivotChart. Drag Discipline from Disciplines table to AXIS area. Drag Medal from Medals table to ∑ VALUES area. Click PivotTable. Drag Discipline from Disciplines table to ROWS area. Drag Medal from Medals table to ∑ VALUES area. Click the ANALYZE tab in PIVOTTABLE TOOLS on the Ribbon. Click Insert Slicer. The Insert Slicers dialog box appears. Click on NOC_CountryRegion and Sport in Medals table. Click OK. Two Slicers – NOC_CountryRegion and Sport appear. Arrange and size them to align properly next to the PivotTable. Select USA in the NOC_CountryRegion Slicer. Select Aquatics in the Sport Slicer. 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 Slicers again for PivotChart. You can use the same Slicers that you have used for the PivotTable. Click on NOC_CountryRegion Slicer. Click the OPTIONS tab in SLICER TOOLS on the Ribbon. Click Report Connections in the Slicer group. The Report Connections dialog box appears for the NOC_CountryRegion Slicer. You can see that all the PivotTables and PivotCharts in the workbook are listed in the dialog box. Click on the PivotChart that is in the same worksheet as the selected PivotTable and click OK. Repeat for Sport Slicer. The PivotChart is also filtered to the values selected in the two Slicers. Next, you can add details to the PivotChart and PivotTable. Click the PivotChart. Drag Gender to LEGEND area. Right click on the PivotChart. Select Change Chart Type. Select Stacked Column in the Change Chart Type dialog box. Click on the PivotTable. Drag Event to ROWS area. Click the DESIGN tab in PIVOTTABLE TOOLS on the Ribbon. Click Report Layout. Select Outline Form from the dropdown list. Selecting Objects for Display in the Report You can choose not to display the Slicers on the final Report. Click the OPTIONS tab in SLICER TOOLS on the Ribbon. Click 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. Click on symbol next to NOC_CountryRegion. Click on symbol next to Sport. The symbol is changed to for both. This means the visibility for the two Slicers is off. Close the Selection Pane. You can see that the two Slicers are not visible in the Report. Print Page Previous Next Advertisements ”;

Excel Power Pivot – Features ”; Previous Next 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 linked 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 millions 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. Print Page Previous Next Advertisements ”;

Table and Chart Combinations ”; Previous Next Power Pivot provides you with different combinations of Power PivotTable and Power PivotChart for data exploration, visualization, 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. Click the Home tab in Power Pivot window. Click PivotTable. Select Chart and Table (Horizontal) from the dropdown list. Create PivotChart and PivotTable (Horizontal) dialog box appears. Select New Worksheet and click OK. An empty PivotChart and an empty PivotTable appear on a new worksheet. Click on the PivotChart. Drag NOC_CountryRegion from Medals table to the AXIS area. Drag Medal from Medals table to the ∑ VALUES area. Right click on the Chart and select Change Chart Type from the dropdown list. 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). Click 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 Outline 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. Click the Home tab in Power Pivot window. Click PivotTable. Select Chart and Table (Vertical) from the dropdown list. The Create PivotChart and PivotTable (Vertical) dialog box appears. Select New Worksheet and click OK. An empty PivotChart and an empty PivotTable appear vertically on a new worksheet. Click on the PivotChart. Drag Year from the Medals table to AXIS area. Drag Medal from the Medals table to ∑ VALUES area. Right click on the Chart and select Change Chart Type from the dropdown list. 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). Click 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 Outline Form. Filter the field Sport with Value Filters to Greater than or equal to 80. Print Page Previous Next Advertisements ”;

Excel Power Pivot Tutorial PDF Version Quick Guide Resources Job Search Discussion Excel Power Pivot is an efficient, powerful tool that comes with Excel as an Add-in. With Power Pivot, you can load hundreds of millions 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. Audience This guide targets professionals with hands-on experience with Excel, to perform the highend data analysis and decision making in a matter of few minutes. Prerequisites Before you proceed with this tutorial, we are assuming that you are already aware about the basics of Excel. If you are not well aware of these concepts, then we will suggest you to go through our short tutorials on Excel charts and MS Access. Print Page Previous Next Advertisements ”;

Discuss Excel Power Pivot ”; Previous Next Excel Power Pivot is an efficient, powerful tool that comes with Excel as an Add-in. With Power Pivot, you can load hundreds of millions 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. Print Page Previous Next Advertisements ”;

Excel Power PivotTable – Creation ”; Previous Next 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 ability to summarize data from the Data Model in the Power PivotTable. As you are aware, the Data Model can handle huge data spanning millions of rows and coming from diverse 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 − Click the Home tab on the Ribbon in PowerPivot window. Click PivotTable on the Ribbon. Select PivotTable from the dropdown list. 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 click 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 list has two tabs − ACTIVE and ALL that appear below the title and above the fields list. The ALL tab is highlighted. 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 click the ACTIVE tab, the Fields list would be empty. Click on the table names in the PivotTable Fields list 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. Click 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. Click the ALL tab. Click on Month and Order Amount in the Sales table. Again, click the ACTIVE tab. Both the tables − Sales and Salesperson appear under the ACTIVE tab. Drag Month to COLUMNS area. Drag Region to FILTERS area. Click the arrow next to ALL in the Region filter box. Click Select Multiple Items. Select North and South and click OK. Sort the column labels in the ascending order. Power PivotTable can be modified dynamically explore and report data. Print Page Previous Next Advertisements ”;

Excel Power Pivot – Basics of DAX ”; Previous Next DAX (Data Analysis eXpression) language is the language of Power Pivot. DAX is used by Power Pivot for data modeling 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 individual 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 visualized 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 − Click the East_Sales tab in Data View of the Power Pivot window to view the East_Sales Data Table. Click the Design tab on the Ribbon. Click Add. The column on the right side with the header − Add Column is highlighted. 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 click the header of the new calculated column. Rename the header as TotalProductPrice. Add one more calculated column for Gross Profit as follows − Click the Design tab on the Ribbon. Click Add. The column on the right side with the header − Add Column is highlighted. 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 click 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. Consolidating all the steps, proceed as follows − Click the Design tab on the Ribbon. Click Add. The column on the right side with the header − Add Column is highlighted. 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 click the header of the new calculated column. Rename the header as TotalProductPrice. Click the Design tab on the Ribbon. Click Add. The column on the right side with the header – Add Column is highlighted. 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 click 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. Click 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 click the calculated field − EastProfit. Select Format from the dropdown list. The Formatting dialog box appears. Select Number under Category. In the Format box, select Percentage and click 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. Click 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 list. 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 earlier versions of Excel. Print Page Previous Next Advertisements ”;

Excel Power Pivot – Exploring Data ”; Previous Next 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. Click Manage in the Data Model group. Click the POWERPIVOT tab on the Ribbon. The Power Pivot window appears. Click the Home tab in the Power Pivot window. Click From Database in the Get External Data group. Select From Access from the dropdown list. The Table Import Wizard appears. Provide Friendly connection name. Browse to the Access database file, Events.accdb, the Events database file. Click on the Next > button. The Table Import wizard displays options for choosing how to import data. Click Select from a list of tables and views to choose the data to import and click 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 click 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 clicking the Stop Import button. Once the data import is complete, Table Import Wizard displays – Success and shows the results of the import. Click Close. Power Pivot displays all the imported tables in different tabs in Data View. Click on the Diagram View. You can observe that a relationship exists between the tables – Disciplines 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 − Click PivotTable on the Ribbon. Select PivotTable from the drop down list. Select New Worksheet in the Create PivotTable dialog box that appears and click 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 list. Drag the NOC_CountryRegion field in the Medals table to the COLUMNS area. Drag Discipline from the Disciplines table to the ROWS area. Filter Discipline 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 − Click the arrow to the right of Column Labels. Select Value Filters from the dropdown list. Select Greater Than…. from the second dropdown list. Click OK. The Value Filter dialog box appears. Type 80 in the right-most box and click 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 Disciplines and Sports with the field SportID. Add the field Sport to the PivotTable. Shuffle the fields – Discipline 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 DisciplineEvent. 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. Click the Design tab on the Ribbon. Click Add. The right-most column with the header Add Column is highlighted. 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-click on the new column and select Rename Column from the dropdown list. 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

Excel Power Pivot – Overview ”; Previous Next Excel Power Pivot is an efficient, powerful tool that comes with Excel as an Add-in. With Power Pivot, you can load hundreds of millions 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 lies 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 individual 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 highlight the significant data in the reports. The reports are interactive in nature, enabling 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. Print Page Previous Next Advertisements ”;

Excel Power Pivot – Installing ”; Previous Next Power Pivot in Excel provides a Data Model connecting various different data sources based on which the data can be analyzed, visualized, 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 − Click the FILE tab on the Ribbon. Step 2 − Click Options in the dropdown list. The Excel Options dialog box appears. Step 3 − Follow the instructions as follows. Click Add-Ins. In the Manage box, select COM Add-ins from the dropdown list. Click the Go button. The COM Add-Ins dialog box appears. Check Power Pivot and click 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 millions of rows and perform the required calculations. You can utilize 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 facilitates 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 − Ability to handle large data volumes, compressed into small files, with amazing speed. Filter data and rename columns and tables while importing. Organize tables into individual 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 Modelling with Power Pivot Power Pivot provides advanced data modeling 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 Intelligence with Power Pivot Business intelligence