About This Book

This book is a little different from most books already out there on Power Pivot. It doesn't cover all the features of Power Pivot, nor does it cover the DAX language extensively. Many books before this one have already done those things well. Two good examples are Bill Jelen's PowerPivot for the Data Analyst and Rob Collie's DAX Formulas for PowerPivot.

This book is intended as a very practical book to help you get started on a Power Pivot journey that will bring your Excel and data analysis skills to the next level. This book follows Jim, a business user who is very familiar with Excel, on his journey to create a financial dashboard and complementary reports in Microsoft Excel. The journey starts with Jim finding out what information his organization needs in order to understand the current rhythm of its business. He then gathers that information and shapes it into a dashboard, in which he must determine the best ways to visualize information. As you follow Jim on this journey, you will use Power Pivot and DAX formulas to solve several very common business calculations, like year-to-date revenue, variance to target, and year-over-year growth.

You will also learn to create reports in Excel and Microsoft Power View to allow Jim's business to dive deeper into the numbers. Then you'll see how to share those workbooks to SharePoint and Office 365 Power BI.

In many places, this book dives deeper in subjects like the Power Pivot engine, DAX formulas, and Excel and dashboard design tips and tricks. Most of this book applies to both Excel 2010 and Excel 2013. However, Chapter 5 applies only to Excel 2013 because it's about Power View, which is not available in Excel 2010.

I hope you will find this book very useful in creating dashboards that provide insights into data, and I'm looking forward to seeing you out there in the Power Pivot community. You can find me at my blog, http://www.powerpivotblog.com, or on Twitter, at @kjonge.

What Is Business Intelligence?

Before you get hands-on with Excel, it's important to look at why the tools discussed in this book even exist.

Business intelligence (BI) has traditionally been used as an umbrella term to refer to software and practice that should lead to better insights and decisions for an organization. Instead of making decisions based on gut feeling, an organization can base its decisions on actual facts it visualizes by using business applications. Many Excel professionals are likely to think, "Hey, that's what I'm doing every day, but I don't give it a fancy name!"

BI gained traction in the 1990s, when companies started creating and collecting more and more data but couldn't get the information into the hands of the business users to create insights and make decisions based on that information.

Building BI solutions has traditionally been the territory of IT organizations and consulting firms. It has often resulted in very heavy-weight and expensive projects; these highly curated and complex systems have brought together a lot of information from all over a company into a data warehouse.

A data warehouse collects data from all over a company and consolidates it into what many think of as "the single version of the truth." An IT organization may want all data to flow through the BI system to make sure it's consistent and non-redundant, in order to gain "correct" insights.

To make the data in a data warehouse actionable, organizations have often created cubes on top of the data warehouses. They have optimized these cubes to gain fast access to the data for doing quick analytics on large amounts of data. Organizations have created canned reports based on these cubes in order for users to get insight into the data. In the 2000s, Excel improved this situation, making PivotTables available, so users could drag and drop data from a cube straight into Excel.

Today, the stream of information that flows through an organization comes not just from BI systems but also from the number-one BI tool in the world: Excel. Users from the business side of an organization — not from the IT side — create Excel reports. These reports often bypass a BI solution completely or mash up data from the data warehouse with additional data retrieved elsewhere. This often causes IT and business users to clash because IT folks want the data to come from their BI solution, but the business cannot wait for IT to provide that information. The world does not stand around and wait for the data to become available. Events happen all the time, and it is often crucial for an organization to react quickly.

As the pace of the world has increased and as more and more data has become available to organizations, CFOs and other stakeholders in organizations have wanted to get insights into data faster and faster. BI traditionally was set to create insights through long projects, but that type of system makes it hard to quickly get insights into the data. When the recent financial crisis hit, the business world had to make many cutbacks, especially in the IT space. So at the same time that IT departments are expected to provide more insights and provide oversight over the data, they now have fewer resources to consolidate larger amounts of data.

But an organization doesn't need to rely on just its IT department for data. An army of Excel users in any business knows the data inside out, and they are very proficient at creating reports and using data to gain insights. What if Excel users and IT could work together to serve the information needs of the organization and use each other's strengths instead of competing? This was exactly the idea that started the self-service revolution in 2006 at the Microsoft campus in Redmond. At that time, Microsoft began an incubation project called Gemini, named for the constellation. The twins in this project are IT and business users, working together.

The Self-Service Revolution: Power Pivot

Microsoft started its BI journey in 1994 by creating the very successful product Microsoft SQL Server Analysis Services (SSAS), which is designed for developers with an IT background. It is the bestselling analytical database engine in the industry. The idea behind Gemini was to shape the world-leading BI product SSAS into something that fits in Excel and can be used by Excel professionals. The Gemini incubation team aimed to determine whether it would be possible to empower Excel professionals and at the same time have them work together with IT. The team wanted to figure out how to put more business intelligence into the hands of the business users and allow them to "self-service" the information.

The Gemini team determined that it needed to create a product with a few radical features:

• The ability to work with massive amounts of data: Since SSAS hit the market in 1994, a lot has changed in the IT industry....