+ Reply to Thread
Results 1 to 4 of 4

Regular Excel Pivot Tables vs PowerPivot

  1. #1
    Registered User
    Join Date
    07-25-2008
    Location
    USA
    Posts
    8

    Regular Excel Pivot Tables vs PowerPivot

    Can anyone tell me the major advantages / disadvantages of using PowerPivot over a basic Excel Pivot Table?

    I have read about it condensing file sizes (not critical to my application). Also, the measures seem to be easier in PowerPivot than the Calculated Fields in regular Pivot tables. Are there any other major pros / cons?

    my project:

    My end goal is to create a simple dashboard in Excel (for a non excel user) that visually shows a list of students and what % of their courses they have completed. The raw data (100-500 lines) is hard to read.

    I created a pivot table and am using some slicers. That looks ok and works ok. I have to created a Calculated field for Percent of Courses Completed. If i do this in power pivot, I feel like i have more flexibility w/ Measures should I need to build on this going forward. Measures give you prompts for syntax and Calculated Fields don't.

    When I have completed this, I will need to train an average Excel user how to copy, paste new data and refresh the dashboard. I have done this w/ regular pivot tables many times and it works well. Is it as simple to copy paste data into the data model as it is into a spreadsheet? I would have to confirm the user has power pivot right?

    Any thoughts or suggestions are appreciated.

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Regular Excel Pivot Tables vs PowerPivot

    The biggest benefits:

    1. The ability to join data from multiple sources. Regular pivot tables work from a single source data table. With Power Pivot, you can use data from multiple tables.

    2. Measures. As you pointed out, DAX measures give infinitely greater possibility than regular pivot calculated fields.

    2a. Time Intelligence. Time based measures (such as YTD, MTD, rolling 12m, etc) are super simple using DAX.

    3. Scalability. Power Pivot models can quickly and easily be scaled up to Power BI, or SSAS Tabular.

    In terms of updating data: yes, it can be as simple as updating data, then refreshing. I'd always try to eliminate the "copy paste" step, where possible - Power Query can directly access most data sources, so there's rarely a need for a manual paste step.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    07-25-2008
    Location
    USA
    Posts
    8

    Re: Regular Excel Pivot Tables vs PowerPivot

    Olly,

    thanks for the confirmation. I suspected what you say is true, but I am still grinding through several tutorials to get the details. The more I am learning the more excited i am about the possibilities for these tools.

    thanks for your detailed response.

    Follow up question. Can you tell me when Power BI would be in play? most of my applications are on less than 20,000 rows of data. Is Power BI just excel pivot tables on steroids? Is this mostly relevant w/ larger data sets, or rather is more useful w/ more complicated data sets (multiple tables)?

  4. #4
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Regular Excel Pivot Tables vs PowerPivot

    I always prefer to use Power BI, when possible. There are some (increasingly rare) cases where I revert to using Excel, mostly when extensive manual data input is required. Power BI is much more than just "Excel pivot tables on steroids" - hugely increased functionality, security and scalability. Data size is irrelevant - some of my most used and appreciated Power BI models are based on just several hundred rows (and some based on several hundred million rows...!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Merge Two PowerPivot Tables in Excel
    By varunc777999 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-11-2016, 02:13 PM
  2. using regular excel formulas in powerpivot manage data
    By stephme55 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-09-2016, 02:47 PM
  3. [SOLVED] is power pivot faster than regular pivot tables
    By stephme55 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-27-2016, 01:21 PM
  4. [SOLVED] distinctcount in regular pivot tables
    By stephme55 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-27-2016, 03:23 AM
  5. Replies: 4
    Last Post: 07-23-2015, 03:35 PM
  6. Dynamic Filters for PowerPivot Sourced Pivot Tables
    By nnazarian in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-13-2015, 02:06 PM
  7. Replies: 2
    Last Post: 06-02-2014, 09:34 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1