No video :(

Pivot Table vs a Power Pivot based Pivot Table

Подписаться 6 тыс.

Просмотров 26 тыс.

50% 1

This video is going to show you three big differences between an Excel-based Pivot Table and a Power Pivot-based Pivot table. The three differences we will emphasize are different calculations on Totals, Pivot Table Sets, and inserting a formula column into the Pivot Table. I find these the most influential differences between the two pivot tables but will discuss others along the way. You can also find resources to many more differences if you click the links below.
This tutorial was made using Excel 365, but all methods work with Excel 2010 and above.
Files if you want to follow along (large as it includes the Excel Table version):bit.ly/339kCAm
Link to a blog post that are relevant:
Pivot Table vs Power Pivot or what is Power Pivot (Excel Unplugged) - bit.ly/3tacHxu
Does a PowerPivot Pivot Table beat a regular Pivot Table (Excel Unplugged) - bit.ly/3q9JuAT
PowerPivot Inside Out (Part 1/3) (Excel Unplugged) - bit.ly/3Gfzv2n
PowerPivot Inside Out (Part 2/3) (Excel Unplugged) - bit.ly/3raZVMK
Chapters:
0:00 Excel Olympics Animation
0:08 Introduction
1:03 some info about Power Pivot
2:08 Introduction to the Data
2:36 How much space does it Use
3:51 Three differences emphasized in an Excel Pivot Table
7:17 Creating a Power Pivot Based Pivot Table
9:02 Connecting two tables in Power Pivot
9:36 Creating a Measure
10:46 Measure that calculates differently on Totals
13:09 Pivot Table Set based on Column Items
14:59 Inserting a Column with custom formulas in a Pivot Table
15:42 Convert to Formulas command in Pivot Tables
16:32 Using the Add this Data to the Data Model command
17:32 More sources on differences between the two Pivot Tables
18:14 A final comparison of file sizes
19:25 Conclusion
19:51 Outro
Gear Used:
US Links
Camera: Canon EOS R6 - amzn.to/3e9zxML
Microphone: AKG C3000 (Analog Mic) - amzn.to/3tYd37w
Audio converter: Zoom UAC-2 - amzn.to/3dgM8wS
Laptop: Dell XPS 15 - amzn.to/3w8CyoK
Lights: Elgato Key Light - amzn.to/3rurte7
EU Links
Camera: Canon EOS R6 - amzn.to/3tubwGy
Microphone: AKG C3000 (Analog Mic) - amzn.to/32pi2m0
Audio converter: Zoom UAC-2 - amzn.to/3amDRXP
Laptop: Dell XPS 15 - amzn.to/3effMmT
Lights: Elgato Key Light - amzn.to/3vcs6v9
Green Screen (visible in bloopers): amzn.to/3ajUm6Y
Recorded With: Camtasia - www.techsmith....
Subscribe for more Excel tutorials on the Excel Olympics RU-vid Channel: bit.ly/EOYTsub
Subscribe for the Excel Olympics Newsletter: bit.ly/3w3rkjK
Disclaimer: Some links included in this description might be affiliate links. If you purchase a product or service with the links that I provide, I may receive a small commission with NO additional charge to you! I appreciate your support!
#Excel #ExcelOlympics #gasperkamensek

Опубликовано:

17 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 45

@davidlinton4127 2 года назад

Thanks Gasper, I always perk up when I see one of your new videos. I have been aware of power pivot but never really dug in, now I will. 😎

@ExcelOlympics 2 года назад

That is just about the best thing I heard today. Thanks David (and I'll see you on the next one 😀). Oh, and you definitely should check out Power Pivot.

@alexrosen8762 2 года назад

Extremely useful tutorial. Thanks!

@ExcelOlympics 2 года назад

Thank you for those king words Alex.

@jazzista1967 2 года назад

Gasper.. that was a great refresher on PowerPivot. I use PowerPivot a lot in my office and like you said in the video one of the great features of PP is the asymmetric reporting capabilities thanks to the SSAS tabular running behind the scenes inside excel. I will go and read your article. Happy new year! Thanks for posting

@ExcelOlympics 2 года назад

A great 2022 to you too jazzista! See you on the next one 😀

@jazzista1967 2 года назад

Hi Gasper. Good morning. Look forward to next video. Also, i forgot to tell you that i liked the way you used the HASOVEVALUE function to obtain the average at the grand total level of the pivot table . Very clever.

@ExcelOlympics 2 года назад

Thanks Jazzista. It's a technique I use all the time 😀.

@davorercegovac3674 2 года назад

Master black belt excel👍 Thank you Gašper🙂

@ExcelOlympics 2 года назад

Thank you for those kind words Davor. Means a lot!

@DK_85 27 дней назад

Great. Thanks a lot! Are there new things to come, Gasper?

@wayneedmondson1065 2 года назад

Awesome Gasper! Was not aware of managing sets. Power Pivot really opens up new possibilities. Thanks for demonstrating. Looking forward to more! Thumbs up!! PS - Thanks for providing the sample data. Always fun and instructive to follow along :))

@ExcelOlympics 2 года назад

Thanks Wayne. Sample data should be a rule from now onwards (unless AutoSave overwrites it again 😀).

@wayneedmondson1065 2 года назад

@@ExcelOlympics Awesome! Very helpful and much appreciated :))

@user-dn5gd1rn9f 11 месяцев назад

Thank you- this was great!

@tracywilliams61 Год назад

Wow! thank you, what a very informative demo. I stopped using excel a long time ago because of how restrictive it was and opted for a tool called Monarch (which I love) but now I’m heading back to excel using all the powder features and thinking Monarch and I could be breaking up real soon. 😅

@ExcelOlympics Год назад

Glad to bring you back to the Excel camp Tracy 😀 Hope the break-up goes smoothly 🤣

@Lena-ks5ni 2 года назад

Thank you, with your videos,I always learn something new and interesting...keep on rocking, Gasper!

@ExcelOlympics 2 года назад

Thank you for those kind words Lena. It warms my heart to hear them.

@AlizSmith Год назад

Great tutorial!

@ExcelOlympics Год назад

Thank you for those kind words Aliz.

@dougmphilly 2 года назад

17:00 best description of the data table

@ExcelOlympics 2 года назад

Thank you for those kind words Doug.

@ExcelWithChris Год назад

Only came across this today. Attended one of your workshop in Amsterdam in 2019. I see the link to the exercise files does not exist anymore. Where can I find the LARGE file. Want to use when training. Thanks. Best wishes from South AFrica.

@martyc5674 2 года назад

Nice video Gasper 😊

@ExcelOlympics 2 года назад

Thanks Martin.

@abdullah_alhathloul_ Год назад

Nice , thanks

@ExcelWithChris Год назад

Sorry, my mistake. I see if I click on the link it starts downloading automatically.

@edimathomas-cr4km Год назад

The OLAP tool "Convert to formulas" worked differently for me. It converted the power pivot to a range. Pls help. Been trying to multiply column Y by column Z in my power pivot table and have the result column be part of my power pivot, and I've been unable to do so. Super Frustrating.

@TheLaopi Год назад

Great job from France, Can you share the data ? I'm doncic fan 👍👍👍

@mmbcampus 2 года назад

Need one suggestion. In the raw data/source datasheet, I have one column for Birthday information in Date number format. Furthermore, Some data are missing and some cells are mentioned with non-number values (i.e "N/A", "Not update" etc.). Now the problem is when I add this Table into power pivot, Dates are stored as text. That's why I could not add any function. Can you please how to solve this?

@ExcelOlympics 2 года назад

My suggestion would be to use Power Query to get the data into Power Pivot and during the transformation you can deal with n/a values and other problems and definitely defining the column as Date data type. Than everything will work.

@JJ_TheGreat Год назад

0:43 And I can name 2 of those 3 differences: 1) You can create a PivotTable from multiple worksheets with a PowerPivot. With a traditional PivotTable, you can only use data from the current worksheet. 2) With a PowerPivot, you get the option to calculate a Distinct Count. With a traditional PivotTable, you do not have that option.

@AlizSmith Год назад

Wondering, what is the best way to combine 2 different datasets that has slightly different fields? I am trying to make a file that has actual sales for the closed months, but future months need to added from a forecast table to make my outlook.

@ExcelOlympics Год назад

You don't need to combine the two. There is a case to be made that you do not want to have a single column of values and one of the dates, as this does not allow you to differentiate between AC and FC. If you connect both tables to all needed dimension tables, it will work like a charm :)

@Maldive177 2 года назад

Hi there, this is amazing. I do have a question. i have already loaded 9000 records into the power pivot and built a dashboard based on it, it is a monthly report. but now problem the is, each month i have new sales data. in that case, how to just load or add the data to the table that 's already in the power pivot??? cause i tried many ways, it would just add the data to a different tab, like a new table, but that is not how i want it. please help. thanks in advanced.

@ExcelOlympics 2 года назад

If the table was created by Power Query you can simply append the new data. If not, you need to use a Power Query to create the original table.

@Maldive177 2 года назад

@@ExcelOlympics oh no... really, what i did was adding the data in excel to data model directly, did not use the power query. and , i deleted the orginal data in excel. so bascalliy now, the power pivot has the data, and I build the report based on the data on the power pivot. oh my god, so, does that mean there is no way i can append the new data to existing data model without using power query??

@Maldive177 2 года назад

so i have to use the power query to load the data to data model and rebuild everything again???

@ExcelOlympics 2 года назад

Well the "I deleted the original data" could be a problem. But you do not need to build the whole thing again. You bring in the table again, this time with Power Query. You then connect the table to the model and edit all the measures so that they point to the new table. And in theory everything should work, even the Pivot Tables... This would allow you to then delete the original table.

@dougmphilly 2 года назад

piqued my interests. i have huge report that uses power query into a pivot table but its size is 85mg and takes 15 minutes to refresh the data from the server. i'm going to translate all to power pivot.