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 • 22 courses • 501,366 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 here 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. It'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. The normal pivot version just lists fields within this single table or source that were 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 Analise tab of a power pivot, you're going to see those calculated field and calculated items grade out. Now, at 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 to caveat it here. Another one of those annoying little naming things. If you're using Excel twenty ten or twenty sixteen, you'll see these referred to as measures in 2013. You might see them called calculated fields, which is super confusing. But just note, 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 A1 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 DACS Data Analysis Expression's and DACS 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 DACS. 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 we've got a whole section at the end of the course diving into this common DACS functions. So that's a perfect segue. Next video, we're going to talk about what DACS is all about.