PowerPivot for the Data Analyst: Microsoft Excel 2010

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