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. About MrExcel Library: Every book in the MrExcel Library pinpoints a specific set of crucial Excel tasks and presents focused skills and examples for performing them rapidly and effectively. Selected by Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will • Dramatically increase your productivity–saving you 50 hours a year, or more • Present proven, creative strategies for solving real-world problems • Show you how to get great results, no matter how much data you have • Help you avoid critical mistakes that even experienced users make
Author(s): Bill Jelen
Edition: 1
Year: 2010
Language: English
Pages: 336
Contents......Page 4
Introduction......Page 16
“The PivotTable Field List Has Fields from Both Tables”......Page 17
Now, I Love VLOOKUPs......Page 18
There Could Be Five Titles for This Book......Page 19
Who Are Those Other People in the Room?......Page 20
This Book Is For The Excel People......Page 21
Text Conventions......Page 22
Cross References......Page 23
32 Bit or 64 Bit?......Page 24
The Many PowerPivot Tabs......Page 26
Ribbon Tabs in the PowerPivot Application......Page 27
Next Steps......Page 31
Preparing Your Data for PowerPivot......Page 32
Getting Your Data into PowerPivot......Page 33
Import a Text File......Page 34
Add Excel Data by Copying and Pasting......Page 37
Define Relationships......Page 38
Add Calculated Columns Using DAX......Page 40
Build a Pivot Table......Page 41
World-Class Data Compression......Page 46
Asymmetric Reporting with PowerPivot......Page 48
Next Steps......Page 49
3 Why Wouldn’t I Build Every Future Pivot Table in PowerPivot?......Page 52
Create One Pivot Table from Multiple Tables......Page 53
Use Named Sets to Build Asymmetric Pivot Tables......Page 54
Join Four Pivot Tables Together Using a Single Set of Slicers......Page 55
PowerPivot Slicer AutoLayout Runs Circles Around Regular Excel Slicers......Page 56
PowerPivot Allows for Standalone Pivot Charts......Page 57
Measures Created by DAX Run Circles Around Calculated Fields......Page 58
PowerPivot Is Not Smart Enough to Sort Jan, Feb, Mar, Apr......Page 59
It’s Hard to Change the Calculation in the Pivot Table......Page 61
GetPivotData Is Harder to Use with PowerPivot......Page 64
Show Items with No Data Is Grayed Out......Page 66
You Cannot Double-Click to Drill Through......Page 67
Grouping Does Not Work with PowerPivot......Page 68
Certain On-Worksheet Typing Adjustments Do Not Work in PowerPivot......Page 70
Greatest Pivot Table Trick of All Time: Show Pages Does Not Work......Page 72
Bottom Line......Page 73
Next Steps......Page 75
Getting Excel Data into PowerPivot......Page 76
Converting Your Data to a Table and Linking......Page 77
Add Excel Data by Copying and Pasting......Page 82
Adding Excel Data by Importing......Page 91
Importing Data from SQL Server......Page 94
Importing a Text File......Page 95
Importing from Other Sources......Page 98
Next Steps......Page 99
Trying to Autodetect Relationships......Page 100
Manually Defining a Relationship......Page 103
Calculating Between Tables......Page 104
Unwinding a Lookup Table......Page 106
Building a Concatenated Key Relationship......Page 110
Questions About Relationships......Page 113
Next Steps......Page 115
Working with Data in the PowerPivot Window......Page 116
Applying Numeric Formatting......Page 117
Filtering in the PowerPivot Window......Page 119
Rearranging Columns......Page 121
Hiding Columns at Two Levels......Page 123
Using the Context Menu......Page 124
Operators in the DAX Language......Page 125
Building Formulas in the PowerPivot Grid......Page 126
Date and Time Functions......Page 127
Using YEARFRAC to Calculate Elapsed Time......Page 134
Examples of Math and Trigonometry Functions......Page 138
Examples of Text Functions......Page 145
Examples of Text Functions......Page 147
Examples of Logical Functions......Page 154
Grabbing Values from a Related Table......Page 157
Getting Multiple Values from a Related Table......Page 158
Filtering Multiple Values from a Related Table......Page 159
Using the Recursive Functions......Page 160
Using Other Functions......Page 161
Next Steps......Page 162
Elements of a Pivot Table......Page 164
Using the PowerPivot Field List to Create Reports......Page 165
A Look at the Underlying Data......Page 167
Defining the Pivot Table......Page 168
Using the Report Filter......Page 174
Report Filters Versus Slicers......Page 176
Explanation of Column B......Page 177
New Trick with Column Labels......Page 179
Is There a Way to Permanently Sack the Compact Layout?......Page 180
Pivot Tables Do Not Recalculate When Underlying Data Changes......Page 181
You Cannot Move or Change Part of a Pivot Table......Page 182
Working with Pivot Charts......Page 184
Behind the Scenes with PowerPivot Field List and Add-In......Page 187
Next Steps......Page 188
Presenting Customers with the Largest Sales at the Top......Page 190
Adding a Custom List to Control Sort Order......Page 194
Showing the Top Five Customers......Page 197
Notes About the Top 10 Filter......Page 198
Easiest Way to Force a Count......Page 199
Using Sum, Count, Min, Max, or Average......Page 200
Changing the Show Values as Drop-Down......Page 202
Base Fields and Base Items......Page 203
Change the Numeric Formatting for a Field......Page 205
Formatting Changes on the Design Tab......Page 207
Not Enough Styles? Multiply by 20......Page 209
Applying Data Visualizations and Sparklines......Page 210
Next Steps......Page 212
9 Cool Tricks New with PowerPivot......Page 214
Building a Report with Two Pivot Charts......Page 215
Chart Formatting Changes......Page 217
Adding Slicers and Understanding Slicer AutoLayout......Page 218
Cannot Directly Change the Size of Slicers......Page 219
Controlling the Size of the Bounding Rectangle......Page 221
Adding a Pivot Chart to an Existing Layout......Page 223
Hooking the New Pivot Chart Up to the Existing Slicers......Page 225
Moving the Pivot Table to a Back Worksheet......Page 226
Adding a Pivot Table to an Existing Layout......Page 227
Can the PowerPivot Layout Be Skipped Entirely?......Page 228
Next Steps......Page 230
10 Using DAX for Aggregate Functions......Page 232
DAX Measures Are Calculated Fields for the Values Area of a Pivot Table......Page 233
Five of the Six Pivot Table Drop Zones Are Filter Fields!......Page 234
Using the DISTINCT Function......Page 235
Using COUNTROWS as a Wrapper Function......Page 236
Entering a DAX Measure......Page 237
DAX Measures Can Reference Other DAX Measures......Page 240
Using Other DAX Functions That Respect Filters......Page 241
DAX Calculate Function Is Like the Excel SUMIFS Function......Page 243
In DAX, a Filter Might Give You More Rows Than You Started With!......Page 245
ALL Function Says to Ignore All Existing Filters......Page 246
Using the FILTER Function......Page 249
The Double Negative of AllExcept......Page 252
Other DAX Functions......Page 253
Next Steps......Page 255
11 Using DAX for Date Magic......Page 256
Using Time Intelligence Functions......Page 257
Fiscal Quarters and Calendar Quarters......Page 259
Comparing Today’s Sales to One Year Ago......Page 260
Reporting Sales for the Full Month......Page 261
Sales for the Last 30 Days......Page 262
Use Care with ParallelPeriod......Page 263
Skip the CALCULATE Function in Three Cases......Page 264
Next Steps......Page 265
Defining Territories with Named Sets......Page 266
Correcting the Grand Total Row in Named Sets......Page 269
Using Named Sets for Asymmetric Reporting......Page 271
Preserving Report Formatting Using GetPivotData......Page 273
Producing a Perfectly Formatted Shell Report......Page 274
Evaluating the Formula Built by Excel......Page 276
Converting Live Pivot Table to Cube Formulas......Page 278
Customizing the Formatted Report......Page 280
Next Steps......Page 281
Charts Should Have Less Ink, More Information......Page 282
Time Series Charts Should Be Columns or Lines......Page 283
Use Descriptive Titles......Page 284
Reduce the Number of Zeros on the Values Axis......Page 286
Slicers Make the On-Chart Controls Obsolete......Page 287
Gridlines, Tick Marks, Axis, and Column Widths......Page 288
Trying to Tame the Slicers......Page 291
Change the Slicer Color......Page 293
Hide Other Interface Elements......Page 294
Contrast Color and Title in Row 1......Page 295
Hide the Cell Pointer Behind a Slicer......Page 296
Adding a Picture as a Top Banner......Page 297
Next Steps......Page 299
Requirements to Run the Server Version of PowerPivot......Page 300
Benefits of the PowerPivot Server......Page 301
How the Report Looks in the Server......Page 302
The Report Gallery Is Slick......Page 303
Why the IT Department Will Embrace PowerPivot......Page 306
Mistakes to Avoid When Publishing Reports to SharePoint......Page 307
Hide All but the Main Worksheet......Page 308
Next Steps......Page 309
PowerPivot Team Blog......Page 310
Kasper de Jonge’s Business Intelligence Blog......Page 311
Add-Ins for PowerPivot......Page 312
B......Page 314
C......Page 315
D......Page 316
E......Page 317
F......Page 318
J-K-L......Page 319
M......Page 320
P......Page 321
Q......Page 322
R......Page 323
T......Page 324
W......Page 325
X-Y-Z......Page 326
