PowerPivot Alchemy: Patterns and Techniques for Excel
June 2014
Covers a Number of Techniques to Make You a Power Pivot Sorcerer
PowerPivot Alchemy is far less 'techie' than other PowerPivot books...all you have to do is follow the pattern, and you will have something awesome to show for your time and attention.
Add to Cart: $9.99 »- 223 Pages
- Publisher: Holy Macro! Books
- ISBN: 978-1-61547-021-1
- PDF ISBN: 978-1-61547-214-7
PowerPivot is an amazing new add-in for Excel 2013 that allows business intelligence pros to process large amounts data. Although it is simple to use in a demo environment, analysts are likely to run into real-life scenarios that are difficult to solve. This guide, dedicated solely to the PowerPivot tool, provides techniques and solutions to real-world problems, including showing the Top N customers by using slicer filters, comparing budget to actuals, drilling across data instead of through data, and joining data from two different sources in a single analysis.
I first saw Power Pivot in 2009. It was amazing. I quickly learned enough to do a demo to wow people from Peoria to Peru. But a 10-minute demo requires far less skill than actually using the product every day. As I wrote PowerPivot for the Data Analyst, I found myself always going back to Rob Collie, who was still a PM on the Power Pivot team, to try to figure out various quirks of Power Pivot.
...
The book you’re looking at right now is far less techie than some of the PowerPivot books that have hit the data stream. For instance, Rob doesn’t force you to understand how the EARLIER() function works in DAX in order to do something cool with Power Pivot. Yes, you will see the EARLIER() function in this book, but all you have to do is follow the pattern, and you will have something awesome to show for your time and attention."
More from the book:
Broadly speaking, Power Pivot is a numbers-producing machine: Raw data goes in, your formulas and relationships digest it, and magically useful numbers come out. Those numbers are often metrics on a business, and that’s a beautiful thing; very often, those metrics are being “seen” for the first time in the history of the business. Quite often, before a business adopts Power Pivot, it is forced to operate without metrics that are, in hindsight, quite clearly critical. This is a lot like a doctor suddenly having access to patient information such as temperature, pulse rate, and blood pressure—after lacking that information for most of a career. Such a shift is transformational.
So, Power Pivot produces numbers—incredibly important numbers that quite often have never before existed. It’s empowering stuff for sure.
There is a natural tendency among “numbers” people to view the freshly pressed numbers as the final destination. Resist that temptation! In order for numbers to have an impact,..."
- Dedications v
- Acknowledgements vii
- Foreword ix
- Introduction xi
-
Chapter 1: Dashboard Tricks and Visualization Techniques 1
- Adding a “Last Refreshed Date” Readout 1
- Normalizing Your Measures to First/Average/Max Values for Charts 4
- Bubbling Up Exceptions with “Sarah Problem” 10
- Ranks and Exceptions That Bubble Up to Subtotals 12
-
Chapter 2: Slicers: The Gateway to Interactivity 23
- “Initializing” Slicers That Contain Too Many Values to Scroll 26
- Wingdings and Other Symbolic Fonts in Slicers 28
- Using Macros to Change Slicer Fonts 40
- A Simple Trick for Combatting “Stale” Slicers 42
- User-Friendly Report Sorting with Slicers 46
- Adding State Alpha Sorting to the Sort-by-Slicer Trick 50
- Conditional Formatting Controlled via Slicers 60
- Catching Slicer Selections in a Formula 69
-
Chapter 3: Conquering Common Calculated Column Conundrums 75
- An Excel Pro’s Primer on Calculated Columns 75
- Subtotaling Calc Columns and the EARLIER() Function 76
- Referencing the Previous Row and Similar Calcs 79
- Referencing Rows “Within Range” of the Current Row 80
- Totaling Data Table Values in Lookup Tables 90
- CONTAINSX: Finding Matching Values in Two Tables 92
- CONTAINSX Revisited: What Is the Match? 94
- Common Calendar Conundrums 97
- Custom Calendar Conundrums 100
-
Chapter 4: Modeling and Portable Formulas 103
- Integrating Data of Different “Grains” 109
- A Mystifying and Awesome Solution for the Many-to-Many Problem 117
- Calculating Same-Store Sales in Power Pivot 120
- Same-Store Sales Continued: Using Store Open/Close Dates 123
- Campaign Analysis: Seasonally Adjusted Measures 128
- A/B Campaign Analysis with Start and End Date Slicers 137
- Customers and Website Visitors: Percentage Who Return 145
- Calculating Average Customer Age 151
- Moving Averages, Sums, etc 157
- Moving Averages Controlled by a Slicer 160
-
Chapter 5: Power Query 171
- Combining Multiple Worksheets or Workbooks into a Single Power Pivot Table 171
- Using Power Query to Merge CSV Files 176
- Using Power Query to “Unpivot” a Table 186
- Using Power Query to Create a Lookup Table from a Data Table 190
- Creating a Calendar Table: Advanced Usage of Power Query 193
- Chapter 6: Power View 199
- How to Use Power View 199
- Chapter 7: Power Map 221
