| |
| |
| Introduction | |
| |
| |
| "There's Row 20 Million" | |
| |
| |
| "The PivotTable Field List Has Fields from Both Tables" | |
| |
| |
| Now, I Love VLOOKUPs | |
| |
| |
| "How Much Will This Cost? Well, the Client Is Free" | |
| |
| |
| This Book Was Pitched Eight Hours Later | |
| |
| |
| There Could Be Five Titles for This Book | |
| |
| |
| WhoAreThose Other People in the Room? | |
| |
| |
| This Book Is For The Excel People | |
| |
| |
| How This Book Is Organized | |
| |
| |
| Conventions Used in This Book | |
| |
| |
| Text Conventions | |
| |
| |
| Special Elements | |
| |
| |
| Cross References | |
| |
| |
| |
| Downloading and Installing PowerPivot | |
| |
| |
| System Requirements | |
| |
| |
| 32 Bit or 64 Bit? | |
| |
| |
| Not Excel Starter Edition | |
| |
| |
| Not Excel Web Apps | |
| |
| |
| Installing PowerPivot | |
| |
| |
| The Many PowerPivot Tabs | |
| |
| |
| Ribbon Tabs in the PowerPivot Application | |
| |
| |
| Uninstalling PowerPivot | |
| |
| |
| Next Steps | |
| |
| |
| |
| The Promise of PowerPivot | |
| |
| |
| Preparing Your Data for PowerPivot | |
| |
| |
| Getting Your Data into PowerPivot | |
| |
| |
| Decide on a Sequence for Importing | |
| |
| |
| Import a Text File | |
| |
| |
| Add Excel Data by Copying and Pasting | |
| |
| |
| Define Relationships | |
| |
| |
| Add Calculated Columns Using DAX | |
| |
| |
| Build a Pivot Table | |
| |
| |
| World-Class Data Compression | |
| |
| |
| Asymmetric Reporting with PowerPivot | |
| |
| |
| Next Steps | |
| |
| |
| |
| Why Wouldn't I Build Every Future Pivot Table in PowerPivot? | |
| |
| |
| Great Reasons to Use PowerPivot | |
| |
| |
| Create One Pivot Table from Multiple Tables | |
| |
| |
| Use Massive Data Volumes | |
| |
| |
| Fit More Data into Memory | |
| |
| |
| Use Named Sets to Build Asymmetric Pivot Tables | |
| |
| |
| Join Four Pivot Tables Together Using a Single Set of Slicers | |
| |
| |
| PowerPivot Slicer AutoLayout Runs Circles Around Regular Excel Slicers | |
| |
| |
| PowerPivot Allows for Standalone Pivot Charts | |
| |
| |
| Convert Your PowerPivot Pivot Table to Formulas | |
| |
| |
| Measures Created by DAX Run Circles Around Calculated Fields | |
| |
| |
| The Downside of PowerPivot | |
| |
| |
| You Lose Undo | |
| |
| |
| PowerPivot Is Not Smart Enough to Sort Jan, Feb, Mar, Apr | |
| |
| |
| It's Hard to Change the Calculation in the Pivot Table | |
| |
| |
| You Cannot Create PowerPivot Pivot Tables with VBA | |
| |
| |
| You Cannot Edit a Single Cell in the PowerPivot Window | |
| |
| |
| GetPivotData Is Harder to Use with PowerPivot | |
| |
| |
| Show Items with No Data Is Grayed Out | |
| |
| |
| Calculated Fields and Calculated Items Are Grayed Out | |
| |
| |
| You Cannot Double-Click to Drill Through | |
| |
| |
| Grouping Does Not Work with PowerPivot | |
| |
| |
| Certain On-Worksheet Typing Adjustments Do Not Work in PowerPivot | |
| |
| |
| Greatest Pivot Table Trick of All Time: Show Pages Does Not Work | |
| |
| |
| Other Minor Annoyances | |
| |
| |
| Bottom Line | |
| |
| |
| Next Steps | |
| |
| |
| |
| Getting Your Data into PowerPivot | |
| |
| |
| Getting Excel Data into PowerPivot | |
| |
| |
| Converting Your Data to a Table and Linking | |
| |
| |
| Add Excel Data by Copying and Pasting | |
| |
| |
| Adding Excel Data by Importing | |
| |
| |
| Importing Data from SQL Server | |
| |
| |
| Importing a Text File | |
| |
| |
| Importing from Atom Data Feeds | |
| |
| |
| Importing from Other Sources | |
| |
| |
| Next Steps | |
| |
| |
| |
| Creating and Managing Relationships | |
| |
| |
| Trying to Autodetect Relationships | |
| |
| |
| Manually Defining a Relationship | |
| |
| |
| Calculating Between Tables | |
| |
| |
| Defining a Difficult Relationship | |
| |
| |
| Unwinding a Lookup Table | |
| |
| |
| Building a Concatenated Key Relationship | |
| |
| |
| Is This Harder Than a VLOOKUP? | |
| |
| |
| Questions About Relationships | |
| |
| |
| Next Steps | |
| |
| |
| |
| Using Data Sheet View | |
| |
| |
| Working with Data in the PowerPivot Window | |
| |
| |
| Applying Numeric Formatting | |
| |
| |
| Sorting Data in the PowerPivot Window | |
| |
| |
| Filtering in the PowerPivot Window | |
| |
| |
| Rearranging Columns | |
| |
| |
| Hiding Columns at Two Levels | |
| |
| |
| Using PowerPivot Undo and Redo | |
| |
| |
| Deleting Columns | |
| |
| |
| Using the Context Menu | |
| |
| |
| Adding New Columns Using DAX Formulas | |
| |
| |
| Operators in the DAX Language | |
| |
| |
| Building Formulas in the PowerPivot Grid | |
| |
| |
| DAX Function Reference | |
| |
| |
| Date and Time Functions | |
| |
| |
| Using YEARFRAC to Calculate Elapsed Time | |
| |
| |
| Using TIMEVALUE to Convert Text Times to Real Times | |
| |
| |
| Examples of Math and Trigonometry Functions | |
| |
| |
| Examples of Text Functions | |
| |
| |
| Examples of Text Functions | |
| |
| |
| Examples of Logical Functions | |
| |
| |
| Examples of Information Functions | |
| |
| |
| Grabbing Values from a Related Table | |
| |
| |
| Using One Value from a Related Table | |
| |
| |
| Getting Multiple Values from a Related Table | |
| |
| |
| Filtering Multiple Values from a Related Table | |
| |
| |
| Using the Recursive Functions | |
| |
| |
| Using Other Functions | |
| |
| |
| Next Steps | |
| |
| |
| |
| Building Pivot Tables | |
| |
| |
| Elements of a Pivot Table | |
| |
| |
| Arranging Field Headings to Build a Report | |
| |
| |
| Using the PowerPivot Field List to Create Reports | |
| |
| |
| Building a Pivot Table | |
| |
| |
| A Look at the Underlying Data | |
| |
| |
| Defining the Pivot Table | |
| |
| |
| Using the Report Filter | |
| |
| |
| Report Filters Versus Slicers | |
| |
| |
| Explanation of Column B | |
| |
| |
| Returning the Column Labels to Sanity | |
| |
| |
| New Trick with Column Labels | |
| |
| |
| Is There a Way to Permanently Sack the Compact Layout? | |
| |
| |
| Two Important Rules with Pivot Tables | |
| |
| |
| Pivot Tables Do Not Recalculate When Underlying Data Changes | |
| |
| |
| You Cannot Move or Change Part of a Pivot Table | |
| |
| |
| Working with Pivot Charts | |
| |
| |
| Behind the Scenes with PowerPivot Field List and Add-In | |
| |
| |
| Next Steps | |
| |
| |
| |
| Cool Tricks Native to Pivot Tables | |
| |
| |
| Applying Sorting Rules to Pivot Tables | |
| |
| |
| Presenting Customers with the Largest Sales at the Top | |
| |
| |
| Adding a Custom List to Control Sort Order | |
| |
| |
| Showing the Top Five Customers | |
| |
| |
| Notes About the Top 10 Filter | |
| |
| |
| Changing the Calculation in the Pivot Table | |
| |
| |
| Easiest Way to Force a Count | |
| |
| |
| Using Sum, Count, Min, Max, or Average | |
| |
| |
| Changing the Show Values as Drop-Down | |
| |
| |
| Base Fields and Base Items | |
| |
| |
| Pivot Table Formatting | |
| |
| |
| Change the Numeric Formatting for a Field | |
| |
| |
| Formatting Changes on the Design Tab | |
| |
| |
| Not Enough Styles? Multiply by 20 | |
| |
| |
| Applying Data Visualizations and Sparklines | |
| |
| |
| Next Steps | |
| |
| |
| |
| Cool Tricks New with PowerPivot | |
| |
| |
| Building a Report with Two Pivot Charts | |
| |
| |
| Chart Formatting Changes | |
| |
| |
| Adding Slicers and Understanding Slicer AutoLayout | |
| |
| |
| Cannot Directly Change the Size of Slicers | |
| |
| |
| Controlling the Size of the Bounding Rectangle | |
| |
| |
| Strategy for Dealing with AutoLayout of Slicers | |
| |
| |
| Adding a Pivot Chart to an Existing Layout | |
| |
| |
| Hooking the New Pivot Chart Up to the Existing Slicers | |
| |
| |
| Moving the Pivot Table to a Back Worksheet | |
| |
| |
| Adding a Pivot Table to an Existing Layout | |
| |
| |
| Can the PowerPivot Layout Be Skipped Entirely? | |
| |
| |
| Next Steps | |
| |
| |
| |
| Using DAX for Aggregate Functions | |
| |
| |
| DAX Measures Are Calculated Fields for the Values Area of a Pivot Table | |
| |
| |
| Five of the Six Pivot Table Drop Zones Are Filter Fields! | |
| |
| |
| DAX Measures Respect the Home Table Filters | |
| |
| |
| Generate a Count Distinct | |
| |
| |
| Using the DISTINCT Function | |
| |
| |
| Using COUNTROWS as a Wrapper Function | |
| |
| |
| Entering a DAX Measure | |
| |
| |
| DAX Measures Are Calculated Only on Demand | |
| |
| |
| DAX Measures Can Reference Other DAX Measures | |
| |
| |
| Using Other DAX Functions That Respect Filters | |
| |
| |
| Denominators Frequently Need to Ignore the Filters | |
| |
| |
| DAX Calculate Function Is Like the Excel SUMIFS Function | |
| |
| |
| In DAX, a Filter Might Give You More Rows Than You Started With! | |
| |
| |
| ALL Function Says to Ignore All Existing Filters | |
| |
| |
| CALCULATE Is So Powerful, There Is a Shortcut | |
| |
| |
| Using the FILTER Function | |
| |
| |
| The Double Negative of AllExcept | |
| |
| |
| Other DAX Functions | |
| |
| |
| Next Steps | |
| |
| |
| |
| Using DAX for Date Magic | |
| |
| |
| Using Time Intelligence Functions | |
| |
| |
| Fiscal Quarters and Calendar Quarters | |
| |
| |
| Using Period-to-Date Calculations | |
| |
| |
| Comparing Today's Sales to Yesterday | |
| |
| |
| Comparing Today's Sales to One Year Ago | |
| |
| |
| Reporting Sales for the Full Month | |
| |
| |
| Calculating Sales for the Previous or Next Month, Quarter, Year | |
| |
| |
| Sales for the Last 30 Days | |
| |
| |
| Using Date Functions for Data Reported at a Monthly Level | |
| |
| |
| Use Care with ParallelPeriod | |
| |
| |
| Opening and Closing Balances | |
| |
| |
| Skip the CALCULATE Function in Three Cases | |
| |
| |
| Next Steps | |
| |
| |
| |
| Named Sets, GetPivotData, and Cube Formulas | |
| |
| |
| Defining Territories with Named Sets | |
| |
| |
| Correcting the Grand Total Row in Named Sets | |
| |
| |
| Using Named Sets for Asymmetric Reporting | |
| |
| |
| Preserving Report Formatting Using GetPivotData | |
| |
| |
| Producing a Perfectly Formatted Shell Report | |
| |
| |
| Evaluating the Formula Built by Excel | |
| |
| |
| Converting Live Pivot Table to Cube Formulas | |
| |
| |
| Customizing the Formatted Report | |
| |
| |
| Next Steps | |
| |
| |
| |
| Final Formatting: Making the Report Not Look Like Excel | |
| |
| |
| Charts Should Have Less Ink, More Information | |
| |
| |
| Component Charts Make Great Pie Charts | |
| |
| |
| Time Series Charts Should Be Columns or Lines | |
| |
| |
| Category Charts Make Great Bar Charts | |
| |
| |
| Use Descriptive Titles | |
| |
| |
| Single-Series Column and Bar Charts Do Not Need Legends! | |
| |
| |
| Reduce the Number of Zeros on the Values Axis | |
| |
| |
| Slicers Make the On-Chart Controls Obsolete | |
| |
| |
| Replace Pie Chart Legends with Labels | |
| |
| |
| Gridlines, Tick Marks, Axis, and Column Widths | |
| |
| |
| Trying to Tame the Slicers | |
| |
| |
| Change the Slicer Color | |
| |
| |
| Remove Excel Interface Elements | |
| |
| |
| Hide the Gridlines | |
| |
| |
| Hide Other Interface Elements | |
| |
| |
| Hide the PowerPivot Field List | |
| |
| |
| Making a Report Look Like a Dashboard | |
| |
| |
| Change the Background Color | |
| |
| |
| Contrast Color and Title in Row 1 | |
| |
| |
| Minimize the Ribbon | |
| |
| |
| Micro-Adjust the Zoom Slider | |
| |
| |
| Add a Row of Color at the Bottom of the Dashboard | |
| |
| |
| Hide the Cell Pointer Behind a Slicer | |
| |
| |
| Adding a Picture as a Top Banner | |
| |
| |
| Next Steps | |
| |
| |
| |
| Upgrading to PowerPivot Server | |
| |
| |
| Requirements to Run the Server Version of PowerPivot | |
| |
| |
| Benefits of the PowerPivot Server | |
| |
| |
| How the Report Looks in the Server | |
| |
| |
| The Report Gallery Is Slick | |
| |
| |
| Why the IT Department Will Embrace PowerPivot | |
| |
| |
| Mistakes to Avoid When Publishing Reports to SharePoint | |
| |
| |
| Always Add Interactivity | |
| |
| |
| Always Select Cell A1 Before Saving a Workbook | |
| |
| |
| Pictures Will Not Render on the Server | |
| |
| |
| Hide All but the Main Worksheet | |
| |
| |
| Whatever Is Not Hidden in Excel Shows Up in SharePoint | |
| |
| |
| Next Steps | |
| |
| |
| |
| More Resources | |
| |
| |
| TOC, 9780789743152, 5/14/10 | |