04352nam 2200577 450 991081481060332120230803221554.01-61547-214-2(CKB)2550000001317379(EBL)1712158(SSID)ssj0001328851(PQKBManifestationID)11728254(PQKBTitleCode)TC0001328851(PQKBWorkID)11292132(PQKB)10097162(MiAaPQ)EBC1712158(Au-PeEL)EBL1712158(CaPaEBR)ebr10884980(CaONFJC)MIL619071(OCoLC)881571318(EXLCZ)99255000000131737920140701h20142014 uy 0engur|n|---|||||txtccrPower Pivot alchemy patterns and techniques for Excel /Rob Collie and Bill Jelen ; cover design, Jocelyn Hellyer ; indexing, Nellie J. LiwamUniontown, Ohio :Holy Macro! Books,2014.©20141 online resource (257 p.)Includes index.1-61547-021-2 Cover; Title page; Copyright; Table of Contents; Dedications; A Note on Hyperlinks; Acknowledgements; Foreword; Introduction; Chapter 1: Dashboard Tricks and Visualization Techniques; Adding a "Last Refreshed Date" Readout; Normalizing Your Measures to First/Average/Max Values for Charts; Bubbling Up Exceptions with "Sarah Problem"; Ranks and Exceptions That Bubble Up to Subtotals; Chapter 2: Slicers: The Gateway to Interactivity; "Initializing" Slicers That Contain Too Many Values to Scroll; Wingdings and Other Symbolic Fonts in Slicers; Using Macros to Change Slicer FontsA Simple Trick for Combatting "Stale" SlicersUser-Friendly Report Sorting with Slicers; Adding State Alpha Sorting to the Sort-by-Slicer Trick; Conditional Formatting Controlled via Slicers; Catching Slicer Selections in a Formula; Chapter 3: Conquering Common Calculated Column Conundrums; An Excel Pro's Primer on Calculated Columns; Subtotaling Calc Columns and the EARLIER() Function; Referencing the Previous Row and Similar Calcs; Referencing Rows "Within Range" of the Current Row; Totaling Data Table Values in Lookup Tables; CONTAINSX: Finding Matching Values in Two TablesCONTAINSX Revisited: What Is the Match?Common Calendar Conundrums; Custom Calendar Conundrums; Chapter 4: Modeling and Portable Formulas; Integrating Data of Different "Grains"; A Mystifying and Awesome Solution for the Many-to-Many Problem; Calculating Same-Store Sales in Power Pivot; Same-Store Sales Continued: Using Store Open/Close Dates; Campaign Analysis: Seasonally Adjusted Measures; A/B Campaign Analysis with Start and End Date Slicers; Customers and Website Visitors: Percentage Who Return; Calculating Average Customer Age; Moving Averages, Sums, etcMoving Averages Controlled by a SlicerChapter 5: Power Query; Combining Multiple Worksheets or Workbooks into a Single Power Pivot Table; Using Power Query to Merge CSV Files; Using Power Query to "Unpivot" a Table; Using Power Query to Create a Lookup Table from a Data Table; Creating a Calendar Table: Advanced Usage of Power Query; Chapter 6: Power View; How to Use Power View; Chapter 7: Power Map; Thank you; Index; More from Holy Macro! BooksPowerPivot 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 aElectronic spreadsheetsComputer programsElectronic spreadsheetsComputer programs.005.54Collie Rob1601478Hellyer JocelynLiwam Nellie J.MiAaPQMiAaPQMiAaPQBOOK9910814810603321Power Pivot alchemy3925087UNINA