Microsoft’s revolutionary Power Pivot is a tool that allows users to create and transform data into reports and dashboards in new and much more powerful ways using the most-used analytical tool in the world: Excel. This book, written by a member of Microsoft’s Power Pivot team, provides a practical step by step guide on creating a financial dashboard. The book covers in detail how to combine and shape the relevant data, build the dashboard in Excel, providing layout and design tips and tricks, prepare the model to work with fiscal dates, and show values used in many financial reports, including year-to-date, variance-to-target, percentage-of-total, and running totals reports. Accessibly written, this book offers readers a practical, real-world scenario and can be used as a day-to-day reference. Though the guide focuses on Power Pivot for Excel 2010, a chapter that discusses Power View—compatible with Excel 2013— and Power BI is also included. Kasper de Jonge is a senior program manager on the Analysis Services team at Microsoft, where he has worked developing features for Power Pivot and other Analysis Services products such as the Tabular model and Multidimensional cubes. He is a frequent speaker at conferences such as TechEd, SQLPASS, and SQLSaturday, and is the creator of PowerPivotBlog.com, one of the leading Power Pivot websites. He lives in the Seattle area. Dashboarding and Reporting with Power Pivot and Excel By Kasper de Jonge Holy Macro! Books Copyright © 2014 Kasper de Jonge All rights reserved. ISBN: 978-1-61547-027-3 Contents Acknowledgments, v, Preface, vii, 1- About This Book, 1, 2- Introduction to Dashboards and Reports, 7, 3- Collecting and Preparing the Data, 11, 4- Building a Dashboard in Excel, 45, 5- Building Interactive Reports with Excel and Power View, 99, 6- Sharing Dashboards and Reports Within an Organization, 141, Bibliography and Suggested Readings, 175, Index of Tips, 177, Index, 179, CHAPTER 1 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 a