Pivot table data crunching


  1. Pivot Table Fundamentals
  2. Creating a Basic Pivot Table
  3. Customizing a Pivot Table
  4. Grouping, Sorting and Filtering Pivot Data
  5. Performing Calculations Within Pivot Table
  6. Using Pivot Chart and Other Visualizations
  7. Analying Disparate Data Source with Pivot Tables
  8. Shaping Pivot Tables with Others
  9. Working with and Analyzing OLAP DATA
    1. What is OLAP?
      • online analytical processing 
      • RDBs <=> OLAP databases
      • relationships are pre-defined
      • grouping, sorting, aggregations are well pre-defined
    2. Connecting to an OLAP Cube
    3. Understanding the Structure of an OLAP Cube
    4. Understanding Limitaions of OLAP Pivot Tables
    5. Creating Offline Cubes
    6. Breaking Out of the Pivot Table Mold with Cube Functions
    7. Next step
  10. Masing Up Data with PowerPivot
    1. Benefit and DrawBacks to PowerPivot
    2. Mega-Benefits of PowerPivot
      • No Vlookup
      • asymmetric reporting
      • 81 functions to add a calculated column
      • 54 functions to be used to create a new measure
        • countrows(distinct())
        • calculate(exp, filter1, filter2,,,,)
      • 34 time intelligence functinos
    3. Moderate Benefits of PowerPivot
    4. Why is this Free?
      1. pivot table + vlookup + if,sumifs,aggregate + 1048576 rows
    1. Benefits of the Server Version of PowerPivot
    2. Drawbacks to Using PowerPivot
      1. No Grouping( need dax)
      2. No Undo
      3. No VBA
      4. No Drilldown
    3. Installing PowerPivot
    4. Building a PowerPivot Report
    5. Import a Text File
    6. Add Excel Data Copyingt and Pasting
    7. Add Excel Data Linking
    8. Define Relationships
    9. Add Calculated Columns Using Dax
    10. Build a Pivot Table
    11. Slicers in PowerPivot
    12. Some Things are different
    13. Two Kinds of DAX Calculations
    14. DAX Calculations for Calculated Columns
    15. Using RELATED() to Base a Column Calculation on Another Table
    16. Using Dax to create New Measure
    17. "When Filter, then calculate" Does Not Work in Dax Measure
    18. Mix in Those Amazing Time intelligence Functions
    19. Using PowerPivot to Access Named Sets for asymmetric Reporting
    20. Other Notes
    21. Combination Layouts
    22. Report Formatting
    23. Refrehsing PowerPivot Versus Refreshing Pivot Table
    24. Getting your DATA into PowerPivot with SQL Server
    25. Other issues
    26. Next Steps
  1. Enhancing Pivot Table Reports with Macros
  2. Using VBA to create Pivot Tables
  3. Advanced Pivot Table Tips and Techniques
  4. Dr. Jekyll and Mr. GetPivotData