Power Pivot in Excel

Power Pivot in Excel

Power Pivot:

Power Pivot is a feature of Microsoft Excel. It is available as an add-in in Excel 2010, 2013 in separate downloads, and as an add-in included with the Excel 2016 program. Power Pivot extends a local instance of Microsoft Analysis Services Tabular that is embedded directly into an Excel Workbook. Power Pivot: Powerful data analysis and data modeling in ExcelPower Pivot is an Excel add-in you can use to perform powerful data analysis and create sophisticated data models.

How to get Power Pivot Addin?

  1. Go to File > Options > Add-Ins.
  2. In the Manage box, click COM Add-insGo.
  3. Check the Microsoft Office Power Pivot box, and then click OK. If you have other versions of the Power Pivot add-in installed, those versions are also listed in the COM Add-ins list. Be sure to select the Power Pivot add-in for Excel. Following is the figure below:
figure 1.0

Let’s understand each feature available over Power Pivot ribbon tab one by one: Point to be noticed Power Pivot can be performed over a Data Table, hence we must have our data table to be added as part of Data Model.

Add to Data Model: Create data in a sheet and click on “Add to Data Model” button a popup comes as shown in figure 1.1 below asking to select data range and having option to identify whether data has header or not:

figure 1.1

Click OK and Power Pivot Analysis interface would be launched as a separate window as shown below:

figure 1.2

Above is the Power Pivot Analysis Window where you can perform separate analysis over the data.

Manage:

When you select Manage, the Power Pivot window appears, which is where you can view and manage the data model, add calculations, establish relationships, and see elements of your Power Pivot data model. A data model is a collection of tables or other data, often with established relationships among them. The following image shows the Power Pivot window with a table displayed. You can bring data from external sources as shown below:

figure 1.3

Direct Relationships:

The Power Pivot window can also establish, and graphically represent, relationships between the data included in the model. By selecting the Diagram view icon from the bottom right side of the Power Pivot window, you can see the existing relationships in the Power Pivot data model. The following image shows the Power Pivot window in Diagram view.

figure 1.4

Measures:

Measures (also known as calculated fields) are one of the most powerful features in Power Pivot. Measures are easy to create, such as those using a standard aggregation such as SUM or AVERAGE, and created by using the AutoSum feature in the Power Pivot window, or implicit measures created in Excel by simply dragging a field into the Values area.

figure 1.5

In Below example I am putting a measure named as “myMeasure” on Table2:

figure 1.6

KPIs:

Key performance indicators (KPIs) are visual measures of performance. … The KPI gauges the performance of the value, defined by a Base measure (also known as a calculated field in Power Pivot in Excel 2013), against a Target value, also defined by a measure or by an absolute value.

Based on my measure the KPI is:

figure 1.7

Settings:

With the help of settings tab we can set global configurations for Power Pivot as shown below:

figure 1.8
figure 1.9

Next >> Power View Reports in Excel

Leave a Reply

Your email address will not be published.