Introduction to PowerPivot
So far you have learned various techniques and advanced concepts when it comes to using formulas (and VBA) to build dashboards. Let me ask you one question.
What are some of the limitations of formula-driven (or VBA-driven) dashboard development?
Go ahead and think about it.
For all its glory and power, it turns out that formula-driven dashboard development suffers from a few key limitations, such as the following:
Although it is possible to answer these questions with Excel formulas, these formulas tend to be long, complicated, and often tricky to modify when business requirements change.
If you seem to be nodding along while reading the previous reasons, then you are going to love PowerPivot. This new technology (well, it has been around for five years now, but you could argue that for a majority of Excel users, PowerPivot is still new) overcomes many of the limitations of Excel and can truly transform Excel into a powerful business intelligence application.
What Is PowerPivot?
PowerPivot is an Excel add-in from Microsoft. It is part of the Power BI family of tools. PowerPivot is designed to help you (data analysts, managers, report creators, data nerds, and so on) answer complex questions about your data with ease. In a nutshell, the process for using PowerPivot to go from raw data to insights is like this:
A Note About How to Get PowerPivot
PowerPivot is compatible with Excel 2010 and newer for Windows.
Note If you don’t see the PowerPivot add-in, that means your version of Excel 2013/2016 does not have PowerPivot. You may have to upgrade your Excel version to the Professional Plus package.
For more about PowerPivot compatibility, availability, and installation, please visit the official PowerPivot web site at http://bit.ly/1MfBdSl.
What to Expect from This Chapter
Because PowerPivot is a vast technology, it is not possible to cover it at any length in one chapter. So, in this chapter, I will limit the discussion to the following topics:
Getting Started with PowerPivot
First, download and install PowerPivot for your respective version of Excel. For more, refer to http://bit.ly/1MfBdSl. Once you install PowerPivot, you should see a new tab, called PowerPivot, in Excel (see Figure 24-1). This is your gateway into the world of PowerPivot.
Figure 24-1. The PowerPivot tab
You can now create PowerPivot tables (they are like regular pivot tables but can do so much more).
But first, you need to load some data into PowerPivot. After all, PowerPivot needs data to do analysis.
Loading Sample Data into PowerPivot
PowerPivot accepts various data sources. You can load data from almost any database and from text files, CSV files, Excel workbooks, SQL Server Analysis Services, tables in current workbook, and connections created in Power Query.
The process for loading data into PowerPivot will differ based on the type of connection. In this chapter, you will learn how to load data from Excel tables into PowerPivot. For other types of data, refer to the “The Last Word” section at the end of this chapter or visit http://bit.ly/1MfBdSl.
Imagine you have three tables in Excel, containing customer, product, and sales data, as shown in Figures 24-2, 24-3, and 24-4.
Figure 24-2. Customer data
Figure 24-3. Product data
Figure 24-4. Sales data
To load these tables into PowerPivot, follow these steps:
This loads the table to the PowerPivot data model (more on this later) and opens the PowerPivot window.
Figure 24-5. Adding tables to the data model through the PowerPivot tab
What Is the PowerPivot Data Model?
For PowerPivot to process your data, analyze it, and calculate the numbers you want, it must construct a data model. This consists of nothing but the following:
A key difference between the data model introduced in Chapter 21 and the data model used by PowerPivot is that when you use PowerPivot, the relationship-building process happens in PowerPivot. For more about the data modeling capabilities of Excel 2013, refer to Chapter 21.
Let’s Enter the World of PowerPivot
Now that you have added three tables to the workbook’s data model, let’s get into the world of PowerPivot and start exploring.
Click the Manage button on the PowerPivot tab. This opens the PowerPivot window, which looks like Figure 24-6.
Figure 24-6. The PowerPivot window, explained
The PowerPivot window offers a lot of features and functionalities. Because the aim for this chapter is to provide a brief overview of what you can do with PowerPivot, let’s skip the discussion of these features. You can review the features in detail at the URL provided earlier in the “Getting Started with PowerPivot” section.
As a first step, now that the data is in PowerPivot, let’s set up relationships between tables. Click Diagram View on the Home tab of the PowerPivot window (see Figure 24-7).
Figure 24-7. The Diagram View option in the View group on the Home tab in PowerPivot
This will open the diagram view where each table is represented by a box (with the columns of the table listed as separate items in the box), as shown in Figure 24-8.
Figure 24-8. Diagram view of tables, PowerPivot window
To set up the relationships between tables, follow these steps:
Done! Your relationships are now set up.
Creating Your First PowerPivot Table
Now that you have loaded some data and created the necessary relationships, let’s create a PowerPivot table so that you can see what this is all about.
To create a PowerPivot table, click PivotTable on the Home tab of the PowerPivot window (see Figure 24-9). This opens the Create PivotTable dialog box and prompts you for the location of the PowerPivot table. By default the new PowerPivot table will be inserted in a new worksheet. Click OK.
Figure 24-9. Inserting a pivot table from a PowerPivot window
At this point, the PivotTable Fields list will show all three tables (if you see only one table, switch to the ALL view from the ACTIVE view). Please refer to Figure 24-10.
Figure 24-10. The PowerPivotTable Fields list shows all the tables in your data model
You can mix and match any fields from any of the tables to create a combined pivot report. For example, see the “Quantity Breakdown PowerPivot by Product Category & Customer Gender” PowerPivot table, as shown in Figure 24-11.
Figure 24-11. Example PowerPivot table, quantity by category and gender
Since you are familiar with this process (refer to Chapter 21), let’s talk about the Power part of PowerPivot.
The Real Power of PowerPivot: DAX Formulas
When you drag a field to the values area of the pivot table, by default Excel will either sum or count that field. Using the pivot table calculation options, you can calculate a few other summaries such as average, minimum, maximum, and so on.
In other words, there are only a few ways in which you can summarize (or analyze) data with regular pivot tables.
PowerPivot gives you Data Analysis Expressions (DAX) formulas, which you can use to calculate a lot of different numbers easily. Here are a few sample numbers that can be easily calculated in PowerPivot using DAX formulas:
Think of DAX formulas as a mix of Excel formulas, pivot tables, pixie dust, and your favorite superhero (for me it’s the Flash).
Let’s Create Your First-Ever DAX Formula Measure in PowerPivot
To truly appreciate what DAX formulas can do, you need to create a few of them and see how they work. Let’s start by calculating something that is useful for business managers but tricky to calculate with regular Excel formulas.
Finding the right combination of buttons to press on an espresso machine to get the most awesome coffee ever!
Of course, I am kidding. PowerPivot can’t tell you how to make the perfect cup of coffee. Not yet.
Instead, let’s calculate something that is truly useful for business managers: finding the distinct customer count.
Let’s say you are looking at a monthly sales report and wondering how many customers bought from you in this month. Although you had 6,000 transactions, that doesn’t mean you had 6,000 customers. Because a few customers might have made multiple purchases, the customer count could be less. But how do you calculate it?
The Process for Creating a DAX Formula to Calculate Distinct Customer Count
To create a new measure, you will be using the Calculations area of the PowerPivot tab in Excel.
Figure 24-12. Blank pivot table with categories and genders
Figure 24-13. Inserting the calculated field from the PowerPivot tab
Figure 24-14. DISTINCTCOUNT() PowerPivot measure, explained
At this stage, PowerPivot calculates the distinct count for each of the category and gender combinations and loads them into the pivot table. The output looks like Figure 24-15.
Figure 24-15. Distinct count of customers by category and gender, PowerPivot example report
DISTINCTCOUNT(), I LIKE THE SOUND OF IT
DISTINCTCOUNT() is one of the hundreds of formulas available in PowerPivot to calculate what you want. As the name suggests, DISTINCTCOUNT counts how many unique (or distinct) values are present in a table column. In this case, DISCINCTCOUNT is looking at the Customer ID column of the sales table and finding the count of customers.
Oh! Wait a second, I never told PowerPivot how to calculate DISTINCTCOUNT for Gender=Female, Category=Biscuits. That’s right. When you create a calculated field in PowerPivot, you just have to specify the abstract or business definition of it. Then PowerPivot will figure out how to calculate these numbers for every scenario. To facilitate this, PowerPivot uses a concept called a filter context.
Let’s take a closer look at the distinct count pivot table and narrow it down to the first number; there are 11 distinct customers where Gender=Female and Category=Biscuits (see Figure 24-16).
Figure 24-16. Understanding PowerPivot filter contexts
In that situation, the filter context is nothing but Gender=Female and Category=Biscuits. Once PowerPivot determines the filter context for each cell of the pivot table, it will calculate the measure (distinct customer count) for only that filter context.
Everything you add to a PowerPivot table will have a role in defining the filter context.
The filter context helps PowerPivot narrow down to the subset of data on which it calculates the measures.
MEASURES VS. CALCULATED FIELDS
Both of these refer to the same idea. In Excel 2010 PowerPivot, they were called measures. In Excel 2013 PowerPivot, they were called calculated fields. Who knows what Microsoft will call them in Excel 2016? In this chapter, I will use both words so that you get used to them.
Let’s Create a Few More DAX Measures
Now that you are familiar with the process of creating DAX measures (or calculated fields), let’s create a few more of them for the data model.
To create these measures, use Calculated Fields New Calculated Field from the PowerPivot tab.
You will create measures for calculating Total Quantity and Average Quantity per Customer using simple DAX formulas.
The total quantity is nothing but the sum of the Quantity column in the Sales table. The measure definition looks like this:
You can see this in Figure 24-17.
Figure 24-17. Adding the Total Quantity measure using the SUM DAX formula
Since you know both the total quantity and the customer count, you can calculate the average quantity per customer. This is defined as follows:
Figure 24-18 shows the Average Quantity per Customer measure.
Figure 24-18. Adding the Average Quantity per Customer DAX measure
THAT’S RIGHT, POWERPIVOT MEASURES ARE REUSABLE
Once you create a few measures, you can use them in constructing other measures.
Think of each measure as a LEGO block. Once you have a bunch of them, you can creatively mix them to come up with a new kinds of measures.
Example PowerPivot Report: Top Five Products Based on Average Quantity per Customer
Now that you have the Quantity per Customer measure, you can use it to find the top five products based on how much customers buy them. Essentially, you are trying to create something like Figure 24-19.
Figure 24-19. Example PowerPivot report with top five products
To create this PowerPivot report, follow these steps:
Figure 24-20. Quantity per Customer for all products
Now that product names are sorted by Quantity per Customer, let’s filter down to the top five products alone.
Figure 24-21. Sorting the pivot table by Quantity per Customer measure
Figure 24-22. Filtering top five products using the Top 10 value filter
Your pivot report with the top five products based on quantity per customer is ready.
Figure 24-23. Filter criteria for top five products
PowerPivot and Excel Dashboards
PowerPivot is like a powerful processing engine for analyzing data. Since dashboards often involve analyzing huge amounts of data, PowerPivot fits naturally into this world. Here is how you can use PowerPivot to speed up your dashboard development:
Figure 24-24 shows an example dashboard that depicts product performance based on the dataset introduced at the start of this chapter.
Figure 24-24. Example Product Performance Dashboard made with PowerPivot, explained
Explaining the construction of this dashboard is beyond the scope for this discussion on PowerPivot. If you are feeling curious, you may refer to this chapter’s workbook, Chapter24-pp01-examples.xlsx, and learn by breaking it apart.
The Last Word
I am sure you are feeling curious about PowerPivot and its possibilities. If so, please check out the following resources: