Power Pivot in Data Analysis

Power Pivot is useful in data analysis when we need to analyze large datasets and combine data from multiple tables without using lookup formulas. It helps us build a Data Model, create relationships between tables, and write powerful calculations using DAX, so the data can be analyzed more easily. In Excel, Power Pivot is used to handle sales databases, customer records, product lists, employee data, and other large table-based data.

In this article, we will learn about Power Pivot in data analysis, its uses, and the main features used to build a Data Model and create measures in Excel.

What Is Power Pivot in Data Analysis?

Power Pivot is an Excel add-in used for data modeling. It allows us to import millions of rows from different sources, connect the tables using relationships, and create pivot tables from the combined data. It stores the data in a compressed, in-memory form called the Data Model, which is why it works faster than normal worksheets.

For example, if we have a Sales table, a Products table, and a Customers table, we can connect them using common columns such as Product ID and Customer ID. After that, we can create one pivot table that shows sales by product category and customer city, without using VLOOKUP at all.

Power Pivot vs Normal Pivot Table

Power Pivot vs Power Query

Sample Dataset Used in This Article

We will use three tables in the examples. Each table is stored as an Excel Table.

Table 1: Sales

Table 2: Products

Table 3: Customers

The Sales table is a fact table, which stores transactions. The Products and Customers tables are lookup tables (also called dimension tables), which store details.

How to Enable Power Pivot in Excel

Power Pivot is available in Excel 2013 and later, and in Microsoft 365, but it is not always visible by default. It may not be available in some versions, such as Home editions.

Steps

1. Go to File > Options > Add-ins. 
2. In the Manage box, select COM Add-ins and click Go. 
3. Check Microsoft Power Pivot for Excel and click OK. 
4. A new Power Pivot tab appears on the ribbon. 

What Is the Data Model?

The Data Model is a collection of related tables stored inside the Excel workbook. It is not visible in a worksheet, but it can be used by pivot tables, pivot charts, and Power View reports. Because it uses a compressed format, the file size stays smaller than it would be with the same data in worksheets.

How to Add Data to the Data Model

Method 1: From an Excel Table

Steps

1. Click on any cell inside the table. 
2. Go to the Power Pivot tab and click on Add to Data Model. 
3. The Power Pivot window opens with the table. 
4. Repeat the process for the other tables. 

Method 2: From Power Query

Steps

1. Load the cleaned query using Close & Load To. 
2. Select Only Create Connection and check Add this data to the Data Model. 
3. Click OK. 

Method 3: From External Sources

In the Power Pivot window, click on Get External Data to import data from SQL Server, Access, text files, and other sources.

The Power Pivot Window

Creating Relationships Between Tables

A relationship connects two tables using a common column. It allows a pivot table to use fields from both tables together.

Steps

1. In the Power Pivot window, click on Diagram View. 
2. Drag the Product ID column from the Sales table to the Product ID column in the Products table. 
3. Drag the Customer ID column from the Sales table to the Customer ID column in the Customers table. 

Output:
A line appears between each pair of tables. One end shows 1 and the other end shows an asterisk (*).

Explanation:
In the above example, we drag the common columns from the Sales table to the lookup tables. After that, Power Pivot creates a relationship between each pair. Finally, the 1 and * symbols show a one-to-many relationship, which means one product can appear in many sales rows.

Rules for Relationships

• The lookup table column must contain unique values, with no duplicates. 
• The two columns should have the same data type. 
• The column names do not have to be the same, but it is easier to use the same name. 
• Excel Tables should have clear names, because they appear in the relationship list. 
• An arrow in the diagram shows the direction in which filtering flows, from the lookup table to the fact table. 

Types of Relationships

Creating a Pivot Table from the Data Model

Steps

1. In the Power Pivot window, click on PivotTable on the Home tab, or in Excel go to Insert > PivotTable. 
2. Select Use this workbook's Data Model, and click OK. 
3. The field list shows all three tables. 
4. Drag Category from Products to Rows. 
5. Drag City from Customers to Columns. 
6. Drag Sales from the Sales table to Values. 

Output:

Explanation:
In the above example, we use fields from three different tables in one pivot table. After that, the relationships tell Excel how the tables are connected. Finally, it calculates the sales for each category and city without any VLOOKUP formula.

Introduction to DAX

DAX stands for Data Analysis Expressions. It is the formula language used in Power Pivot. It looks similar to Excel formulas, but it works on columns and tables instead of single cells.

DAX is used to create two main types of calculations.

Calculated Columns

A calculated column adds a new column to a table in the Data Model. The value is calculated for each row when the data is loaded or refreshed, and it is stored in the model.

Steps

1. In the Power Pivot window, open the Sales table in Data View. 
2. Click on the first cell in the Add Column column. 
3. Type the formula in the formula bar and press Enter. 

Example

=Sales[Sales] / Sales[Quantity]

Output:
A new column shows the price per unit. For Order ID 1, the result is 40000.

Explanation:
In the above example, we divide the Sales column by the Quantity column. After that, Power Pivot calculates the result for each row. Finally, the new column can be used in a pivot table like any other field.

Calculated Column Using RELATED

The RELATED function brings a value from a lookup table into the fact table, similar to VLOOKUP.

=RELATED(Products[Category])

Explanation:
In the above example, the function follows the relationship to the Products table. After that, it returns the category for each sales row. Finally, the Sales table gets a Category column without using a lookup formula.

Measures

A measure is a calculation that is not stored in the table. It is calculated when the pivot table is built, and its result changes with the filters, rows, and columns. Measures are usually better than calculated columns because they use less memory and respond to slicers.

Steps

1. In Excel, go to Power Pivot > Measures > New Measure. 
2. Select the table, such as Sales. 
3. Enter a measure name, such as Total Sales. 
4. Enter the formula, choose a number format, and click OK. 

Example

Total Sales := SUM(Sales[Sales])

Output:
The measure returns 344000 for all data. In a pivot table with Delhi selected, it returns 204000.

Explanation:
In the above example, we use the SUM function on the Sales column. After that, Power Pivot adds the values that are visible under the current filter. Finally, the same measure gives different results for different filters.

Common DAX Measures

Important DAX Functions

Aggregation Functions

SUM, AVERAGE, MIN, MAX, COUNT, COUNTROWS, and DISTINCTCOUNT work on a column or a table and return a single value.

CALCULATE Function

CALCULATE is the most important DAX function. It changes the filter conditions of a calculation.

Syntax

CALCULATE(expression, filter1, filter2, ...)

Example

Laptop Sales := CALCULATE(SUM(Sales[Sales]), Products[Product] = "Laptop")

Output:
The measure returns 200000, which is the total sales of laptops.

Explanation:
In the above example, we use CALCULATE with the SUM function. After that, we add a filter for the Laptop product. Finally, the measure adds only the sales rows that belong to laptops, even if the pivot table shows all products.

DIVIDE Function

The DIVIDE function divides two values and handles divide-by-zero errors safely.

Example

Sales % := DIVIDE([Laptop Sales], [Total Sales], 0)

Output:
The result is about 0.5814, which is 58.14% when formatted as a percentage.

Explanation:
In the above example, we divide the laptop sales by the total sales. After that, we use 0 as the alternate result if the total is zero. Finally, the measure shows the share of laptop sales without an error.

ALL Function

The ALL function removes filters from a table or column. It is useful for calculating a percentage of the grand total.

Example

% of All Sales := DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Customers)))

Explanation:
In the above example, the CALCULATE function ignores any filter on the Customers table. After that, it returns the total sales of all customers. Finally, the DIVIDE function shows the sales of the selected customers as a share of the overall total.

Iterator Functions (SUMX)

Functions that end with X, such as SUMX, AVERAGEX, and MAXX, work row by row and then combine the results.

Example

Total Revenue := SUMX(Sales, Sales[Quantity] * Sales[Sales])

Use these functions when a calculation needs more than one column in each row.

Text and Logical Functions

IF, SWITCH, AND, OR, CONCATENATE, LEFT, RIGHT, and FORMAT work in a similar way to the Excel functions with the same names. For example:

Sales Level := IF([Total Sales] >= 100000, "High", "Low")

Time Intelligence in DAX

Time intelligence functions calculate values such as year-to-date, month-to-date, and the same period in the previous year. They need a Date Table.

Creating a Date Table

Steps

1. Create a table with one row for every date, without gaps, covering all the dates in the data. 
2. Add it to the Data Model. 
3. Go to Design > Mark as Date Table in the Power Pivot window. 
4. Select the date column, and click OK. 
5. Create a relationship between the Date column of this table and the Date column of the Sales table. 

You can also use the DAX function CALENDAR or CALENDARAUTO to create the table.

Example

Date Table = CALENDAR(DATE(2026,1,1), DATE(2026,12,31))

Common Time Intelligence Measures

Output:
In a pivot table by month, Sales YTD shows 140000 for January, 216000 for February, and 344000 for March, because it adds the sales from the start of the year.

Explanation:
In the above example, we use TOTALYTD with the Date column. After that, Power Pivot adds the values from January 1 up to each month. Finally, the measure shows a running total for the year.

Creating KPIs

A KPI (Key Performance Indicator) compares an actual value with a target and shows the result with an icon.

Steps

1. Create a measure for the actual value, such as Total Sales. 
2. Create a measure or type a fixed number for the target, such as 400000. 
3. In the Power Pivot window, right-click on the measure and choose Create KPI. 
4. Select the target value, set the threshold limits, and choose an icon style. 
5. Click OK. 

Explanation:
In the above steps, we create a KPI from the Total Sales measure. After that, we compare it with the target. Finally, the pivot table shows a green, yellow, or red icon, depending on how close the sales are to the target.

Creating Hierarchies

A hierarchy groups fields into levels so that they can be used together in a pivot table, such as Year > Quarter > Month, or Category > Product.

Steps

1. In the Power Pivot window, open Diagram View. 
2. Right-click on a column in the table and choose Create Hierarchy. 
3. Drag the other columns into the hierarchy in the correct order. 
4. Rename the hierarchy. 

Explanation:
In the above steps, we place the columns in order inside one hierarchy. After that, the hierarchy appears as a single field in the pivot table. Finally, we can expand and collapse it to drill down to the details.

Using Slicers and Pivot Charts with the Data Model

Slicers, timelines, and pivot charts work the same way with the Data Model as with a normal pivot table. Because the tables are related, a slicer made from the Customers table, such as City, can filter the sales data and the product data together.

Explanation:
This is one of the biggest advantages of Power Pivot. A single slicer can control many pivot tables and charts in a dashboard, even when the fields come from different tables.

Hiding Fields and Managing the Model

• To hide technical columns, such as IDs, from the pivot table field list, right-click on the column in the Power Pivot window and choose Hide from Client Tools. 
• To refresh the model, go to Data > Refresh All. 
• To change the source or edit a table, use the Table Properties option in the Power Pivot window. 
• To check the structure of the model, use the Diagram View.