Power Pivots vs. “Normal” Pivots in Excel

Maven Analytics
A free video tutorial from Maven Analytics
Award-Winning Analytics & Business Intelligence Training
4.6 instructor rating • 20 courses • 443,089 students

Lecture description

In this lecture I'll explore the similarities and differences between regular Excel PivotTables and "Power" PivotTables, which connect to entire data models.

Learn more from the full course

Microsoft Excel: Business Intelligence w/ Power Query & DAX

Learn advanced Excel for data analysis & business intelligence (Power Query, Power Pivot & DAX language. Excel 2013+)

07:01:46 of on-demand video • Updated January 2021

  • Get up & running with Excel's game changing data modeling & business intelligence tools
  • Learn how to use Power Query, Power Pivot & DAX to absolutely revolutionize your workflow in Excel
  • Master unique tips, tools and case studies that you won't find in ANY other course, guaranteed
  • Explore fun, interactive, and highly effective lessons from a best-selling Excel instructor
  • Get LIFETIME access to project files, quizzes and homework exercises, and 1-on-1 expert support
  • Build pro-quality business intelligence solutions to blend and analyze data from multiple sources
English [Auto] Let's take two minutes and talk about the differences between a normal pivot table and a power pivot table you're on the left side of the screen. I've got a screenshot from a normal pivot here we're looking at the sum of a quantity metric broken down by day. We're looking at our field list on the right side of the screen. And here's a power pivot version of the same view. Got a total quantity metric that's broken down by day. Got the same filter set above the table and we're looking at our field list on the right. Now the obvious difference that you've probably noticed is that in the power pivot version boom we've got access to way more tables and normal pivot version just Liszt's fields within this single table or source that we're pointing to power pivot. Allows us to access any of the fields in any of the tables in our data model and then analyze them based on any relationships that we've defined. So that's the most obvious of the differences. But there's one other important difference as well. No more calculated fields. So if you click on the fields items and sets button in the analyze tab of a power pivot you're going to see those calculated field and calculated items greyed out. Now first this might make you sad because you wanted to add some calculations maybe to supplement your analysis. But here's the thing. One of the key features of power pivot is this ability to create much more robust calculated fields that we're going to call measures. Now I've got a caveat here. Another one of those annoying little naming things. If you're using Excel 2010 or 2016 you'll see these referred to as measures. In 2013 you might see them called calculated fields which is super confusing but just know they're the same thing. So because these measures and because this power pivot table interacts directly with the data model normal cell formulas won't do the trick. So we can't use those A-one grid style cell formulas because we can't point to specific cells in our data model tables. So instead we're going to learn how to use a new but familiar formula language called daks data analysis expressions and daks is a formula language designed specifically to work with relational databases and data models like the one we're building right now. So there's definitely a bit of a learning curve with daks. But the good news is that a lot of the functions will feel pretty familiar because they're based on their cell formula counterparts. So you've got a whole section at the end of the Course diving into this common daks functions. So that's a perfect segue. Next video we're going to talk about what daks is all about.