Power Pivots vs. “Normal” Pivots in Excel

A free video tutorial from Maven Analytics
Empowering everyday people with life-changing data skills
Rating: 4.6 out of 5Instructor rating
39 courses
1,232,789 students
Power Pivots vs. “Normal” Pivots in Excel

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 Power Query, Power Pivot & DAX for data analytics & business intelligence, with a best-selling Excel instructor!

07:05:47 of on-demand video • Updated November 2023

Get up and running with Excel's powerful 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 2 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. I've 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 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 grayed 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 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 that 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 Dak's Data Analysis Expressions. And Dak's is a formula language designed specifically to work with the relational databases and data models like the one we're building right now. So there's definitely a bit of a learning curve with Dak's, 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 those common Dak's functions. So that's the perfect segue way. Next video, we're going to talk about what Dak's is all about.