PowerPivot for the Data Analyst: Microsoft Excel 2010, Portable Documents

ANALYZE HUGE AMOUNTS OF BUSINESS DATA FASTER, MORE EASILY, AND MORE ACCURATELY!   Use Microsoft’s free PowerPivot add-in for Excel 2010 to analyze immense amounts of data from any source, perform state-of-the-art business analysis far more easily, and make better decisions in less time!   Simple, step-by-step instructions walk you through installing PowerPivot, importing data, using PivotTables with PowerPivot, using super-powerful DAX functions and measures, reporting to print or SharePoint, and a whole lot more.   Whatever your Excel data analysis experience, this book will help you use PowerPivot to get the right answers, right now—without IT’s help! Import and integrate data from spreadsheets, SQL Server, Access, Oracle, text files, Atom data feeds, and other sources Analyze multiple tables together, without complicated VLOOKUPs Format, sort, and filter data in the PowerPivot window Add calculated columns using new DAX functions Create powerful reports from PowerPivot data—and format them so executives instantly get your point Discover PivotTable tricks that work even better in PowerPivot Control multiple PivotTable elements on one worksheet, with one set of Excel 2010 Slicers Use DAX Measures to quickly perform tasks that were difficult or impossible with Calculated Fields Compare today’s sales to yesterday’s–or to sales from the parallel period last fiscal year. Use Named Sets to prepare asymmetric reports, show actuals for past months and plan for future months.

Table of Contents:
Introduction .................................................................................................... 1                 “There’s Row 20 Million” ................................................................................................................ 2                 “The PivotTable Field List Has Fields from Both Tables” ................................................................. 2                                 Now, I Love VLOOKUPs ............................................................................................................. 3                                 “How Much Will This Cost? Well, the Client Is Free” ................................................................. 4                                 This Book Was Pitched Eight Hours Later ................................................................................. 4                 There Could Be Five Titles for This Book .......................................................................................... 4                 Who Are Those Other People in the Room? .................................................................................... 5                                 This Book Is For The Excel People.............................................................................................. 6                 How This Book Is Organized ............................................................................................................ 7                 Conventions Used in This Book ....................................................................................................... 7                                 Text Conventions ...................................................................................................................... 7                                 Special Elements ....................................................................................................................... 8                                 Cross References ....................................................................................................................... 8 1 Downloading and Installing PowerPivot .................................................... 9                 System Requirements ..................................................................................................................... 9                                 32 Bit or 64 Bit? ........................................................................................................................ 9                                 Not Excel Starter Edition ......................................................................................................... 11                                 Not Excel Web Apps ................................................................................................................ 11                 Installing PowerPivot.................................................................................................................... 11                                 The Many PowerPivot Tabs .................................................................................................... 11                                 Ribbon Tabs in the PowerPivot Application ............................................................................ 12                 Uninstalling PowerPivot ............................................................................................................... 16                                 Next Steps .............................................................................................................................. 16 2 The Promise of PowerPivot .........................................................................17                 Preparing Your Data for PowerPivot ............................................................................................. 17                 Getting Your Data into PowerPivot ............................................................................................... 18                                 Decide on a Sequence for Importing ....................................................................................... 19                                 Import a Text File ................................................................................................................... 19                                 Add Excel Data by Copying and Pasting .................................................................................. 22                 Define Relationships ..................................................................................................................... 23                 Add Calculated Columns Using DAX .............................................................................................. 25                 Build a Pivot Table ........................................................................................................................ 26                 World-Class Data Compression ..................................................................................................... 31                 Asymmetric Reporting with PowerPivot ....................................................................................... 33                                 Next Steps .............................................................................................................................. 34 3 Why Wouldn’t I Build Every Future Pivot Table in PowerPivot? .....................37                 Great Reasons to Use PowerPivot ................................................................................................. 38                                 Create One Pivot Table from Multiple Tables .......................................................................... 38                                 Use Massive Data Volumes ..................................................................................................... 39                                 Fit More Data into Memory .................................................................................................... 39                                 Use Named Sets to Build Asymmetric Pivot Tables ................................................................. 39                                 Join Four Pivot Tables Together Using a Single Set of Slicers .................................................. 40                                 PowerPivot Slicer AutoLayout Runs Circles Around Regular Excel Slicers................................ 41                                 PowerPivot Allows for Standalone Pivot Charts ...................................................................... 42                                 Convert Your PowerPivot Pivot Table to Formulas.................................................................. 43                                 Measures Created by DAX Run Circles Around Calculated Fields ............................................. 43                 The Downside of PowerPivot ........................................................................................................ 44                                 You Lose Undo ........................................................................................................................ 44                                 PowerPivot Is Not Smart Enough to Sort Jan, Feb, Mar, Apr ................................................... 44                                 It’s Hard to Change the Calculation in the Pivot Table ............................................................ 46                                 You Cannot Create PowerPivot Pivot Tables with VBA ........................................................... 49                                 You Cannot Edit a Single Cell in the PowerPivot Window....................................................... 49                                 GetPivotData Is Harder to Use with PowerPivot .............................................................. 49                                 Show Items with No Data Is Grayed Out ................................................................................. 51                                 Calculated Fields and Calculated Items Are Grayed Out .......................................................... 52                                 You Cannot Double-Click to Drill Through............................................................................... 52                                 Grouping Does Not Work with PowerPivot ............................................................................. 53                                 Certain On-Worksheet Typing Adjustments Do Not Work in PowerPivot ............................... 55                                 Greatest Pivot Table Trick of All Time: Show Pages Does Not Work ........................................ 57                                 Other Minor Annoyances ........................................................................................................ 58                 Bottom Line ................................................................................................................................. 58                                 Next Steps .............................................................................................................................. 60 4 Getting Your Data into PowerPivot .............................................................61                 Getting Excel Data into PowerPivot .............................................................................................. 61                                 Converting Your Data to a Table and Linking ......................................................................... 62                                 Add Excel Data by Copying and Pasting .................................................................................. 67                                 Adding Excel Data by Importing ............................................................................................. 76                 Importing Data from SQL Server ................................................................................................... 79                 Importing a Text File ..................................................................................................................... 80                 Importing from Atom Data Feeds ................................................................................................. 83                 Importing from Other Sources ...................................................................................................... 83                                 Next Steps .............................................................................................................................. 84 5 Creating and Managing Relationships .........................................................85                 Trying to Autodetect Relationships ............................................................................................... 85                 Manually Defining a Relationship ................................................................................................. 88                 Calculating Between Tables .......................................................................................................... 89                 Defining a Difficult Relationship ................................................................................................... 91                                 Unwinding a Lookup Table ..................................................................................................... 91                 Building a Concatenated Key Relationship ................................................................................... 95                 Is This Harder Than a VLOOKUP? .................................................................................................. 98                 Questions About Relationships ..................................................................................................... 98                                 Next Steps ............................................................................................................................ 100 6 Using Data Sheet View .............................................................................101                 Working with Data in the PowerPivot Window .......................................................................... 101                                 Applying Numeric Formatting .............................................................................................. 102                                 Sorting Data in the PowerPivot Window .............................................................................. 104                                 Filtering in the PowerPivot Window..................................................................................... 104                                 Rearranging Columns ........................................................................................................... 106                                 Hiding Columns at Two Levels .............................................................................................. 108                                 Using PowerPivot Undo and Redo ........................................................................................ 109                                 Deleting Columns ................................................................................................................. 109                                 Using the Context Menu ....................................................................................................... 109                 Adding New Columns Using DAX Formulas................................................................................. 110                                 Operators in the DAX Language ............................................................................................ 110                                 Building Formulas in the PowerPivot Grid ............................................................................ 111                 DAX Function Reference.............................................................................................................. 112                                 Date and Time Functions ...................................................................................................... 112                                 Using YEARFRAC to Calculate Elapsed Time........................................................................ 119                                 Using TIMEVALUE to Convert Text Times to Real Times ..................................................... 123                                 Examples of Math and Trigonometry Functions ................................................................... 123                                 Examples of Text Functions .................................................................................................. 130                                 Examples of Text Functions .................................................................................................. 132                                 Examples of Logical Functions .............................................................................................. 139                                 Examples of Information Functions ...................................................................................... 142                 Grabbing Values from a Related Table ........................................................................................ 142                                 Using One Value from a Related Table .................................................................................. 143                                 Getting Multiple Values from a Related Table ...................................................................... 143                                 Filtering Multiple Values from a Related Table ..................................................................... 144                 Using the Recursive Functions .................................................................................................... 145                 Using Other Functions................................................................................................................. 146                                 Next Steps ............................................................................................................................ 147 7 Building Pivot Tables ...............................................................................149                 Elements of a Pivot Table............................................................................................................ 149                                 Arranging Field Headings to Build a Report .......................................................................... 150                                 Using the PowerPivot Field List to Create Reports ................................................................ 150                 Building a Pivot Table ................................................................................................................. 152                                 A Look at the Underlying Data .............................................................................................. 152                                 Defining the Pivot Table ....................................................................................................... 153                                 Using the Report Filter .......................................................................................................... 159                                 Report Filters Versus Slicers .................................................................................................. 161                 Explanation of Column B ............................................................................................................ 162                                 Returning the Column Labels to Sanity ................................................................................ 164                                 New Trick with Column Labels.............................................................................................. 164                                 Is There a Way to Permanently Sack the Compact Layout? .................................................. 165                 Two Important Rules with Pivot Tables ...................................................................................... 166                                 Pivot Tables Do Not Recalculate When Underlying Data Changes ........................................ 166                                 You Cannot Move or Change Part of a Pivot Table ................................................................ 167                 Working with Pivot Charts .......................................................................................................... 169                 Behind the Scenes with PowerPivot Field List and Add-In.......................................................... 172                                 Next Steps ............................................................................................................................ 173 8 Cool Tricks Native to Pivot Tables ..............................................................175                 Applying Sorting Rules to Pivot Tables ....................................................................................... 175                                 Presenting Customers with the Largest Sales at the Top ...................................................... 175                                 Adding a Custom List to Control Sort Order .......................................................................... 179                 Showing the Top Five Customers ................................................................................................ 182                                 Notes About the Top 10 Filter ............................................................................................... 183                 Changing the Calculation in the Pivot Table ............................................................................... 184                                 Easiest Way to Force a Count ................................................................................................ 184                                 Using Sum, Count, Min, Max, or Average ............................................................................. 185                                 Changing the Show Values as Drop-Down ........................................................................... 187                                 Base Fields and Base Items ................................................................................................... 188                 Pivot Table Formatting ............................................................................................................... 190                                 Change the Numeric Formatting for a Field .......................................................................... 190                                 Formatting Changes on the Design Tab ................................................................................ 192                                 Not Enough Styles? Multiply by 20 ....................................................................................... 194                 Applying Data Visualizations and Sparklines .............................................................................. 195                                 Next Steps ............................................................................................................................ 197 9 Cool Tricks New with PowerPivot ..............................................................199                 Building a Report with Two Pivot Charts .................................................................................... 200                                 Chart Formatting Changes .................................................................................................... 202                 Adding Slicers and Understanding Slicer AutoLayout ................................................................. 203                                 Cannot Directly Change the Size of Slicers ............................................................................ 204                                 Controlling the Size of the Bounding Rectangle ................................................................... 206                                 Strategy for Dealing with AutoLayout of Slicers ................................................................... 208                 Adding a Pivot Chart to an Existing Layout ................................................................................. 208                                 Hooking the New Pivot Chart Up to the Existing Slicers........................................................ 210                                 Moving the Pivot Table to a Back Worksheet ....................................................................... 211                 Adding a Pivot Table to an Existing Layout ................................................................................. 212                                 Can the PowerPivot Layout Be Skipped Entirely? ................................................................. 213                                 Next Steps ............................................................................................................................ 215 10 Using DAX for Aggregate Functions ...........................................................217                 DAX Measures Are Calculated Fields for the Values Area of a Pivot Table ................................... 218                                 Five of the Six Pivot Table Drop Zones Are Filter Fields! ........................................................ 219                                 DAX Measures Respect the Home Table Filters ..................................................................... 220                 Generate a Count Distinct ........................................................................................................... 220                                 Using the DISTINCT Function ............................................................................................ 220                                 Using COUNTROWS as a Wrapper Function .......................................................................... 221                                 Entering a DAX Measure ....................................................................................................... 222                                 DAX Measures Are Calculated Only on Demand .................................................................... 225                                 DAX Measures Can Reference Other DAX Measures .............................................................. 225                 Using Other DAX Functions That Respect Filters ......................................................................... 226                 Denominators Frequently Need to Ignore the Filters .................................................................. 228                                 DAX Calculate Function Is Like the Excel SUMIFS Function ................................................... 228                                 In DAX, a Filter Might Give You More Rows Than You Started With! .................................... 230                                 ALL Function Says to Ignore All Existing Filters ................................................................... 231                                 CALCULATE Is So Powerful, There Is a Shortcut .................................................................. 234                 Using the FILTER Function ....................................................................................................... 234                 The Double Negative of AllExcept ......................................................................................... 237                 Other DAX Functions ................................................................................................................... 238                                 Next Steps ............................................................................................................................ 240 11 Using DAX for Date Magic .........................................................................241                 Using Time Intelligence Functions .............................................................................................. 242                                 Fiscal Quarters and Calendar Quarters .................................................................................. 244                                 Using Period-to-Date Calculations ........................................................................................ 245                                 Comparing Today’s Sales to Yesterday ................................................................................. 245                                 Comparing Today’s Sales to One Year Ago ............................................................................ 245                                 Reporting Sales for the Full Month ....................................................................................... 246                                 Calculating Sales for the Previous or Next Month, Quarter, Year .......................................... 247                                 Sales for the Last 30 Days ..................................................................................................... 247                 Using Date Functions for Data Reported at a Monthly Level ....................................................... 248                                 Use Care with ParallelPeriod ...................................................................................... 248                                 Opening and Closing Balances .............................................................................................. 249                 Skip the CALCULATE Function in Three Cases ......................................................................... 249                                 Next Steps ............................................................................................................................ 250 12 Named Sets, GetPivotData, and Cube Formulas .........................................251                 Defining Territories with Named Sets ......................................................................................... 251                                 Correcting the Grand Total Row in Named Sets .................................................................... 254                 Using Named Sets for Asymmetric Reporting ............................................................................. 256                 Preserving Report Formatting Using GetPivotData .............................................................. 258                                 Producing a Perfectly Formatted Shell Report ...................................................................... 259                                 Evaluating the Formula Built by Excel .................................................................................. 261                 Converting Live Pivot Table to Cube Formulas ............................................................................ 263                                 Customizing the Formatted Report ...................................................................................... 265                                 Next Steps ............................................................................................................................ 266 13 Final Formatting: Making the Report Not Look Like Excel ...........................267                 Charts Should Have Less Ink, More Information ......................................................................... 267                                 Component Charts Make Great Pie Charts ............................................................................ 268                                 Time Series Charts Should Be Columns or Lines .................................................................... 268                                 Category Charts Make Great Bar Charts ................................................................................ 269                                 Use Descriptive Titles ............................................................................................................ 269                                 Single-Series Column and Bar Charts Do Not Need Legends! ................................................ 271                                 Reduce the Number of Zeros on the Values Axis ................................................................... 271                                 Slicers Make the On-Chart Controls Obsolete ........................................................................ 272                                 Replace Pie Chart Legends with Labels ................................................................................. 273                                 Gridlines, Tick Marks, Axis, and Column Widths ................................................................... 273                 Trying to Tame the Slicers........................................................................................................... 276                                 Change the Slicer Color ......................................................................................................... 278                 Remove Excel Interface Elements ............................................................................................... 279                                 Hide the Gridlines ................................................................................................................. 279                                 Hide Other Interface Elements .............................................................................................. 279                                 Hide the PowerPivot Field List .............................................................................................. 280                 Making a Report Look Like a Dashboard ..................................................................................... 280                                 Change the Background Color .............................................................................................. 280                                 Contrast Color and Title in Row 1.......................................................................................... 280                                 Minimize the Ribbon ............................................................................................................ 281                                 Micro-Adjust the Zoom Slider ............................................................................................... 281                                 Add a Row of Color at the Bottom of the Dashboard ............................................................ 281                                 Hide the Cell Pointer Behind a Slicer..................................................................................... 281                 Adding a Picture as a Top Banner ............................................................................................... 282                                 Next Steps ............................................................................................................................ 284 14 Upgrading to PowerPivot Server ...............................................................285                 Requirements to Run the Server Version of PowerPivot ............................................................. 285                 Benefits of the PowerPivot Server .............................................................................................. 286                 How the Report Looks in the Server ............................................................................................ 287                 The Report Gallery Is Slick ........................................................................................................... 288                 Why the IT Department Will Embrace PowerPivot ..................................................................... 291                 Mistakes to Avoid When Publishing Reports to SharePoint ........................................................ 292                                 Always Add Interactivity ....................................................................................................... 293                                 Always Select Cell A1 Before Saving a Workbook ................................................................. 293                                 Pictures Will Not Render on the Server ................................................................................. 293                                 Hide All but the Main Worksheet.......................................................................................... 293                                 Whatever Is Not Hidden in Excel Shows Up in SharePoint .................................................... 294                                 Next Steps ............................................................................................................................ 294 Appendix A: More Resources .........................................................................295