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
TOC, 9780789743152, 5/14/10
