title
How to use Power Pivot in Excel | Full Tutorial
description
In this step-by-step tutorial, learn how to use Power Pivot in Microsoft Excel. Power Pivot is a data modeling technology that lets you create data models, establish relationships, and create calculations. With Power Pivot you can work with large data sets, build extensive relationships, and create complex (or simple) calculations, all in a high-performance environment, and all within the familiar experience of Excel.
👋 Additional resources:
- Sample files to follow along: https://1drv.ms/u/s!AmxrofZZlZ-whK1SwYVw2ecGaxrcmw?e=fx6IQa
- In-depth Pivot Table tutorial: https://youtu.be/m0wI61ahfLc
- Learn the fundamentals of Excel in just 2 hours: https://kevinstratvert.thinkific.com
⌚ Timestamps
0:00 Introduction
1:23 Enable Power Pivot add-in
3:05 Sample files overview
4:06 Create data model
8:55 Define relationships
12:13 Calculations
16:02 Measures
21:24 Insert PivotTable from data model
24:49 Key Performance Indicators (KPIs)
27:06 Insert PivotChart
28:16 Slicers
29:34 Wrap up
📃 Watch related playlists and videos
- Playlist with all my videos on Excel: https://youtube.com/playlist?list=PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY
🚩 Connect with me on social:
- LinkedIn: https://www.linkedin.com/in/kevinstratvert/
- Discord: https://discord.gg/kHuujY2pZf
- Twitter: https://twitter.com/kevstrat
- Facebook: https://www.facebook.com/Kevin-Stratvert-101912218227818
- TikTok: https://www.tiktok.com/@kevinstratvert
- Instagram: https://www.instagram.com/kevinstratvert/
🔔 Subscribe to my YouTube channel
https://www.youtube.com/user/kevlers?sub_confirmation=1
🎬 Want to watch again? Navigate back to my YouTube channel quickly
http://www.kevinstratvert.com
🛍 Support me with your Amazon purchases: https://amzn.to/3kCP2yz
⚖ As full disclosure, I use affiliate links above. Purchasing through these links gives me a small commission to support videos on this channel -- the price to you is the same.
#stratvert
detail
{'title': 'How to use Power Pivot in Excel | Full Tutorial', 'heatmap': [{'end': 353.661, 'start': 275.288, 'weight': 0.921}, {'end': 521.565, 'start': 474.694, 'weight': 0.907}, {'end': 609.659, 'start': 586.308, 'weight': 0.717}, {'end': 737.522, 'start': 712.617, 'weight': 0.71}, {'end': 1015.561, 'start': 972.284, 'weight': 0.735}, {'end': 1325.574, 'start': 1286.095, 'weight': 0.824}, {'end': 1618.645, 'start': 1594.104, 'weight': 0.822}], 'summary': 'This tutorial on power pivot in excel covers analyzing data, data modeling, defining relationships, calculating revenue, cost, profit, and visualizing data, enabling users to handle massive data quantities and create data models, calculations, measures, and visualizations, with examples of one-to-many relationships and calculations resulting in 5 customers, $2.7 million total profit, and $543,000 average profit per customer.', 'chapters': [{'end': 177.743, 'segs': [{'end': 76.811, 'src': 'embed', 'start': 48.469, 'weight': 0, 'content': [{'end': 52.29, 'text': "Well, just like the name implies PowerPivot's even more powerful.", 'start': 48.469, 'duration': 3.821}, {'end': 55.711, 'text': 'You can analyze data across multiple tables.', 'start': 53.01, 'duration': 2.701}, {'end': 60.032, 'text': "With a standard pivot table, you're locked into just one table of data.", 'start': 56.131, 'duration': 3.901}, {'end': 64.673, 'text': 'Also, with PowerPivot, you can analyze massive quantities of data.', 'start': 60.632, 'duration': 4.041}, {'end': 68.324, 'text': "We're going to start off by creating a data model.", 'start': 65.762, 'duration': 2.562}, {'end': 76.811, 'text': "Then, once we get our data in, we'll create some calculations, we'll create some measures and then we'll visualize our data with pivot tables,", 'start': 69.025, 'duration': 7.786}], 'summary': 'Powerpivot allows analyzing data across multiple tables and handling massive quantities of data.', 'duration': 28.342, 'max_score': 48.469, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w48469.jpg'}, {'end': 121.242, 'src': 'embed', 'start': 90.789, 'weight': 1, 'content': [{'end': 93.21, 'text': 'Power Pivot comes with Microsoft Excel.', 'start': 90.789, 'duration': 2.421}, {'end': 95.33, 'text': 'We just have to make sure that we turn it on.', 'start': 93.33, 'duration': 2}, {'end': 100.152, 'text': "To turn on Power Pivot, let's go to the top left hand corner and click on the file menu.", 'start': 95.811, 'duration': 4.341}, {'end': 104.294, 'text': "Within file, let's go all the way to the bottom to options.", 'start': 100.692, 'duration': 3.602}, {'end': 111.477, 'text': 'Once you click on options, this opens up a prompt and PowerPivot is an add-in for Microsoft Excel.', 'start': 105.334, 'duration': 6.143}, {'end': 115.599, 'text': "Over here on the left-hand side, you'll see an option for add-ins.", 'start': 112.218, 'duration': 3.381}, {'end': 116.58, 'text': "Let's click on that.", 'start': 115.839, 'duration': 0.741}, {'end': 121.242, 'text': "Right here, there's a section called inactive application add-ins.", 'start': 117.48, 'duration': 3.762}], 'summary': 'Power pivot is a microsoft excel add-in, accessed via file menu and add-ins, to enable data analysis.', 'duration': 30.453, 'max_score': 90.789, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w90789.jpg'}], 'start': 2.728, 'title': 'Analyzing data with power pivot in excel', 'summary': 'Introduces power pivot in microsoft excel, demonstrating its capabilities to analyze and gain insights from data across multiple tables, handle massive quantities of data, and create data models, calculations, measures, and visualizations, while also providing a step-by-step guide to activating power pivot as an add-in in excel.', 'chapters': [{'end': 177.743, 'start': 2.728, 'title': 'Analyzing data with power pivot in excel', 'summary': 'Introduces power pivot in microsoft excel, demonstrating its capabilities to analyze and gain insights from data across multiple tables, handle massive quantities of data, and create data models, calculations, measures, and visualizations, while also providing a step-by-step guide to activating power pivot as an add-in in excel.', 'duration': 175.015, 'highlights': ['Power Pivot allows analyzing data across multiple tables and handling massive quantities of data. This feature enables users to analyze data beyond the limitations of standard pivot tables and handle large data sets efficiently.', 'Step-by-step guide to activating Power Pivot as an add-in in Excel. The presenter provides a detailed walkthrough on activating Power Pivot as an add-in in Microsoft Excel, ensuring that viewers can easily access and utilize this powerful tool.', 'Demonstrates creating data models, calculations, measures, and visualizations with Power Pivot. The chapter covers the process of creating data models, calculations, measures, and visualizations using Power Pivot, showcasing its comprehensive capabilities for data analysis and presentation.']}], 'duration': 175.015, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w2728.jpg', 'highlights': ['Power Pivot enables analyzing data across multiple tables and handling massive quantities of data, surpassing standard pivot table limitations.', 'Step-by-step guide to activating Power Pivot as an add-in in Excel, ensuring easy access and utilization of this powerful tool.', 'Demonstrates creating data models, calculations, measures, and visualizations with Power Pivot, showcasing its comprehensive capabilities for data analysis and presentation.']}, {'end': 535.099, 'segs': [{'end': 236.1, 'src': 'embed', 'start': 196.537, 'weight': 1, 'content': [{'end': 201.48, 'text': 'The customer sheet includes all of the customer information for customers at the Kevin Cookie Company.', 'start': 196.537, 'duration': 4.943}, {'end': 203.121, 'text': 'So you see the customer name.', 'start': 201.841, 'duration': 1.28}, {'end': 206.023, 'text': "There's a whole bunch of contact information related to these customers.", 'start': 203.201, 'duration': 2.822}, {'end': 210.146, 'text': "Don't worry, this is all made up information, so don't try calling any of these customers.", 'start': 206.324, 'duration': 3.822}, {'end': 214.57, 'text': 'And then over on the right hand side, there are also some notes related to these customers.', 'start': 210.567, 'duration': 4.003}, {'end': 218.191, 'text': 'The orders sheet contains the bulk of the information.', 'start': 215.39, 'duration': 2.801}, {'end': 221.033, 'text': 'Here we can see the customer ID who placed an order.', 'start': 218.332, 'duration': 2.701}, {'end': 227.156, 'text': 'We also see the order ID, what product they ordered, how many they ordered, and also the date of the order.', 'start': 221.473, 'duration': 5.683}, {'end': 236.1, 'text': "And lastly, there's also a cookie types sheet, and in here you'll find all the different cookie types that we make here at the Kevin Cookie Company,", 'start': 228.076, 'duration': 8.024}], 'summary': 'Transcript provides customer and order information for kevin cookie company.', 'duration': 39.563, 'max_score': 196.537, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w196537.jpg'}, {'end': 300.076, 'src': 'embed', 'start': 275.288, 'weight': 2, 'content': [{'end': 281.029, 'text': "And then we're also going to establish relationships between the data on all of those different sheets.", 'start': 275.288, 'duration': 5.741}, {'end': 282.89, 'text': "And we're going to walk through how to do that.", 'start': 281.249, 'duration': 1.641}, {'end': 288.331, 'text': "But to get started, within Power Pivot, let's go to the top left-hand corner and click on Manage.", 'start': 283.43, 'duration': 4.901}, {'end': 292.906, 'text': 'This now opens up the core PowerPivot experience.', 'start': 289.321, 'duration': 3.585}, {'end': 296.811, 'text': 'And right now we just see this big blank white screen.', 'start': 293.166, 'duration': 3.645}, {'end': 300.076, 'text': 'We need to bring some of our data into PowerPivot.', 'start': 297.252, 'duration': 2.824}], 'summary': 'Establish relationships between data on different sheets within power pivot.', 'duration': 24.788, 'max_score': 275.288, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w275288.jpg'}, {'end': 353.661, 'src': 'heatmap', 'start': 275.288, 'weight': 0.921, 'content': [{'end': 281.029, 'text': "And then we're also going to establish relationships between the data on all of those different sheets.", 'start': 275.288, 'duration': 5.741}, {'end': 282.89, 'text': "And we're going to walk through how to do that.", 'start': 281.249, 'duration': 1.641}, {'end': 288.331, 'text': "But to get started, within Power Pivot, let's go to the top left-hand corner and click on Manage.", 'start': 283.43, 'duration': 4.901}, {'end': 292.906, 'text': 'This now opens up the core PowerPivot experience.', 'start': 289.321, 'duration': 3.585}, {'end': 296.811, 'text': 'And right now we just see this big blank white screen.', 'start': 293.166, 'duration': 3.645}, {'end': 300.076, 'text': 'We need to bring some of our data into PowerPivot.', 'start': 297.252, 'duration': 2.824}, {'end': 305.543, 'text': 'So I showed you the data in an Excel sheet, and you can bring in data from many different places.', 'start': 300.637, 'duration': 4.906}, {'end': 310.37, 'text': 'Right here on the Home tab, we see this section called Get External Data.', 'start': 306.224, 'duration': 4.146}, {'end': 313.213, 'text': 'And you can get data from all sorts of places.', 'start': 310.87, 'duration': 2.343}, {'end': 315.817, 'text': 'Here, for example, I could get data from a database.', 'start': 313.714, 'duration': 2.103}, {'end': 319.622, 'text': 'You could also get it from other services or here from other sources.', 'start': 316.337, 'duration': 3.285}, {'end': 323.867, 'text': "Chances are, if you have data, you'll be able to bring it into Power Pivot.", 'start': 320.222, 'duration': 3.645}, {'end': 327.109, 'text': 'Now I have my data in an Excel file.', 'start': 324.508, 'duration': 2.601}, {'end': 331.631, 'text': "So right here at the very bottom of the list, there's the option to select an Excel file.", 'start': 327.469, 'duration': 4.162}, {'end': 336.733, 'text': "You could also have it in text files or many other types, but let's click on this and then click on next.", 'start': 332.071, 'duration': 4.662}, {'end': 342.994, 'text': "On the next screen, here it shows a friendly name for the connection, and it's currently set to Excel.", 'start': 337.95, 'duration': 5.044}, {'end': 344.254, 'text': 'That sounds fine to me.', 'start': 343.234, 'duration': 1.02}, {'end': 347.797, 'text': 'Right down below, I can select an Excel file path.', 'start': 344.915, 'duration': 2.882}, {'end': 353.661, 'text': "So this is where our spreadsheet is located, and if you're following along, go to wherever you saved that file.", 'start': 347.977, 'duration': 5.684}], 'summary': 'Demonstrating data import into powerpivot from excel, highlighting the flexibility to import from various sources.', 'duration': 78.373, 'max_score': 275.288, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w275288.jpg'}, {'end': 478.797, 'src': 'embed', 'start': 452.457, 'weight': 0, 'content': [{'end': 456.9, 'text': "Here now you'll see that it successfully imported all of the orders into PowerPivot.", 'start': 452.457, 'duration': 4.443}, {'end': 461.384, 'text': 'And down in the bottom left-hand corner, you can see that there are two different sheets.', 'start': 457.481, 'duration': 3.903}, {'end': 466.349, 'text': 'We have all of the customer information and we also have all of the order information.', 'start': 461.845, 'duration': 4.504}, {'end': 471.352, 'text': 'Next, I want to show you how we can get the cookie type information into PowerPivot.', 'start': 467.029, 'duration': 4.323}, {'end': 474.694, 'text': 'But instead of going through and selecting other sources,', 'start': 471.852, 'duration': 2.842}, {'end': 478.797, 'text': 'I want to show you how you can bring it in if it were a ready part of your Excel spreadsheet.', 'start': 474.694, 'duration': 4.103}], 'summary': 'Successfully imported all orders into powerpivot, with two sheets for customer and order info. explaining how to import cookie type info directly from excel.', 'duration': 26.34, 'max_score': 452.457, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w452457.jpg'}, {'end': 544.263, 'src': 'heatmap', 'start': 474.694, 'weight': 5, 'content': [{'end': 478.797, 'text': 'I want to show you how you can bring it in if it were a ready part of your Excel spreadsheet.', 'start': 474.694, 'duration': 4.103}, {'end': 485.842, 'text': 'I have my cookie type spreadsheet open and here I can see the table with all of the cookie type information.', 'start': 479.497, 'duration': 6.345}, {'end': 491.605, 'text': "I'm just going to copy this table and I'll bring it over to the spreadsheet where I'm building out this PowerPivot.", 'start': 486.342, 'duration': 5.263}, {'end': 494.567, 'text': 'So right here I have this table of information.', 'start': 492.306, 'duration': 2.261}, {'end': 496.449, 'text': "I'll blow it up so we can see it a little bit better.", 'start': 494.628, 'duration': 1.821}, {'end': 501.091, 'text': "Now, let's say you already have a table or some data in your spreadsheet.", 'start': 497.229, 'duration': 3.862}, {'end': 504.193, 'text': 'You can very easily add that to your data model.', 'start': 501.592, 'duration': 2.601}, {'end': 512.136, 'text': "Here I'll highlight the entire table and then up within the power pivot ribbon, I see this option to add to data table.", 'start': 504.873, 'duration': 7.263}, {'end': 513.198, 'text': "Let's click on this.", 'start': 512.537, 'duration': 0.661}, {'end': 519.261, 'text': 'Here now you can see that it added all of the cookie type information to my data model.', 'start': 514.417, 'duration': 4.844}, {'end': 521.565, 'text': 'Right down here I have an additional tab.', 'start': 519.543, 'duration': 2.022}, {'end': 529.093, 'text': "Now it brought it in with the name table1 and that's not really that descriptive, so I'll double click on this and let's just call it cookie type.", 'start': 522.025, 'duration': 7.068}, {'end': 535.099, 'text': "Once you're done typing that in, we now have successfully brought all of our data into this data model.", 'start': 529.674, 'duration': 5.425}, {'end': 544.263, 'text': 'One of the benefits of using Power Pivot is not only can you look across multiple tables, but you can also define relationships between your tables.', 'start': 535.86, 'duration': 8.403}], 'summary': 'Demonstrates how to bring data into powerpivot, including renaming tables and establishing relationships.', 'duration': 69.569, 'max_score': 474.694, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w474694.jpg'}], 'start': 177.883, 'title': 'Power pivot data modeling', 'summary': 'Discusses building a data model in power pivot using excel, importing data from multiple sources including 700 rows of orders, establishing relationships between different data sheets, and adding tables to the data model, enabling a comprehensive data analysis.', 'chapters': [{'end': 236.1, 'start': 177.883, 'title': 'Working with sample data', 'summary': 'Introduces three sample files: customers, orders, and cookie types, containing information about customer details, orders, and cookie types at the kevin cookie company.', 'duration': 58.217, 'highlights': ['The orders sheet contains the customer ID, order ID, product details, quantity, and order date, providing essential information for analysis.', 'The customer sheet includes customer information and contact details for customers at the Kevin Cookie Company.', 'The cookie types sheet lists all the different types of cookies made at the Kevin Cookie Company, offering insights into product variety and inventory.']}, {'end': 535.099, 'start': 236.1, 'title': 'Power pivot data modeling', 'summary': 'Discusses building a data model in power pivot using excel, importing data from multiple sources including 700 rows of orders, establishing relationships between different data sheets, and adding tables to the data model, enabling a comprehensive data analysis.', 'duration': 298.999, 'highlights': ['Imported 700 rows of order data into Power Pivot Successfully imported 700 rows of order data into Power Pivot, demonstrating the capability to handle large datasets.', 'Established relationships between different data sheets Discussed establishing relationships between different data sheets, showcasing the ability to connect and analyze data from various sources.', 'Added cookie type information to the data model Demonstrated the process of adding cookie type information to the data model, showcasing the flexibility of including additional data from within the Excel spreadsheet.']}], 'duration': 357.216, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w177883.jpg', 'highlights': ['Imported 700 rows of order data into Power Pivot Successfully imported 700 rows of order data into Power Pivot, demonstrating the capability to handle large datasets.', 'The orders sheet contains the customer ID, order ID, product details, quantity, and order date, providing essential information for analysis.', 'Established relationships between different data sheets Discussed establishing relationships between different data sheets, showcasing the ability to connect and analyze data from various sources.', 'The customer sheet includes customer information and contact details for customers at the Kevin Cookie Company.', 'The cookie types sheet lists all the different types of cookies made at the Kevin Cookie Company, offering insights into product variety and inventory.', 'Added cookie type information to the data model Demonstrated the process of adding cookie type information to the data model, showcasing the flexibility of including additional data from within the Excel spreadsheet.']}, {'end': 723.064, 'segs': [{'end': 576.766, 'src': 'embed', 'start': 535.86, 'weight': 0, 'content': [{'end': 544.263, 'text': 'One of the benefits of using Power Pivot is not only can you look across multiple tables, but you can also define relationships between your tables.', 'start': 535.86, 'duration': 8.403}, {'end': 548.444, 'text': 'So, just as an example here, if I click into the customer sheet,', 'start': 544.903, 'duration': 3.541}, {'end': 553.306, 'text': "you'll see that I have all of these different customers at the Kevin Cookie Company and they all have a customer ID.", 'start': 548.444, 'duration': 4.862}, {'end': 559.773, 'text': "Now, if I click over into orders, you'll see that these orders are all associated with these customers.", 'start': 554.306, 'duration': 5.467}, {'end': 564.598, 'text': "But Excel on its own doesn't know that there are relationships between this data.", 'start': 560.333, 'duration': 4.265}, {'end': 568.443, 'text': 'So I want to establish or define what these relationships are.', 'start': 565.039, 'duration': 3.404}, {'end': 576.766, 'text': 'Similarly, here we look at the product name and here if I click into cookie type, I see the product name or the cookie type here as well.', 'start': 569.324, 'duration': 7.442}], 'summary': 'Power pivot enables defining relationships between tables for better data analysis.', 'duration': 40.906, 'max_score': 535.86, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w535860.jpg'}, {'end': 616.404, 'src': 'heatmap', 'start': 586.308, 'weight': 0.717, 'content': [{'end': 588.188, 'text': 'I just need to define them right now.', 'start': 586.308, 'duration': 1.88}, {'end': 596.31, 'text': "To define these relationships, let's go up to the home tab up on top and over on the right hand side, there's an option for diagram view.", 'start': 588.908, 'duration': 7.402}, {'end': 597.17, 'text': "Let's click on this.", 'start': 596.51, 'duration': 0.66}, {'end': 602.413, 'text': 'Here I can see all of my data and right now it all appears in these rectangles.', 'start': 597.93, 'duration': 4.483}, {'end': 607.857, 'text': 'Here I could pull it down just to expand the view so I could see all of the different fields within that table.', 'start': 602.734, 'duration': 5.123}, {'end': 609.659, 'text': "I'll do the same here and also here.", 'start': 608.078, 'duration': 1.581}, {'end': 616.404, 'text': 'Now once again I want to define relationships between this data or all these different tables.', 'start': 610.379, 'duration': 6.025}], 'summary': 'Defining relationships between tables using diagram view.', 'duration': 30.096, 'max_score': 586.308, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w586308.jpg'}, {'end': 684.498, 'src': 'embed', 'start': 638.242, 'weight': 2, 'content': [{'end': 643.586, 'text': 'Here I can hover over and this once again shows me the different fields that I related to one another.', 'start': 638.242, 'duration': 5.344}, {'end': 647.189, 'text': 'So there you see customer ID and also customer ID over in orders.', 'start': 643.666, 'duration': 3.523}, {'end': 654.453, 'text': "With this line, one other thing that you can see is over here there's a one and over here there's an asterisk.", 'start': 648.03, 'duration': 6.423}, {'end': 658.715, 'text': "Basically what this means is it's a one to many relationship.", 'start': 655.093, 'duration': 3.622}, {'end': 661.396, 'text': "So let's take an individual customer.", 'start': 659.255, 'duration': 2.141}, {'end': 664.078, 'text': 'One customer can have many orders.', 'start': 661.917, 'duration': 2.161}, {'end': 666.519, 'text': "So that's why you have a one here and an asterisk.", 'start': 664.498, 'duration': 2.021}, {'end': 671.464, 'text': 'Now, I also want to connect the cookie type to the orders table.', 'start': 667.299, 'duration': 4.165}, {'end': 676.489, 'text': 'And as we looked at earlier, we saw that the cookie type is the same as the product.', 'start': 672.064, 'duration': 4.425}, {'end': 679.733, 'text': "It has a different name here, but it's the same information.", 'start': 676.609, 'duration': 3.124}, {'end': 684.498, 'text': "So as part of every order, let's say someone orders a chocolate chip cookie.", 'start': 680.674, 'duration': 3.824}], 'summary': 'Explains one-to-many relationship between customer and orders, also connecting cookie type to orders table.', 'duration': 46.256, 'max_score': 638.242, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w638242.jpg'}], 'start': 535.86, 'title': 'Defining data relationships', 'summary': 'Focuses on using power pivot to define relationships in excel and establishing one-to-many relationships in a database, enabling effective data management. it includes examples of connecting customer, orders, product, and revenue data, and highlighting one-to-many relationships.', 'chapters': [{'end': 616.404, 'start': 535.86, 'title': 'Power pivot: defining relationships in excel', 'summary': 'Highlights the benefits of using power pivot to define relationships between tables in excel, enabling the user to establish connections between different sets of data and manage them effectively, demonstrated through an example of establishing relationships between customer, orders, product, and revenue data.', 'duration': 80.544, 'highlights': ['The chapter emphasizes the benefit of using Power Pivot to define relationships between tables in Excel, enabling users to establish connections between different sets of data and manage them effectively.', 'The transcript provides an example of establishing relationships between customer, orders, product, and revenue data, showcasing the practical application of defining relationships in Excel.', 'The speaker demonstrates the process of defining relationships in Excel by navigating to the home tab and selecting the diagram view to visualize and establish connections between different data sets.', 'The speaker explains the importance of defining relationships between data in Excel, emphasizing the need to establish connections between customer, orders, product, and revenue data for effective data management.']}, {'end': 723.064, 'start': 616.964, 'title': 'Establishing data relationships in database', 'summary': 'Explains how to establish one-to-many relationships between different tables, using examples such as connecting customer id to orders and linking cookie types to orders, creating a clear understanding of the data connections and highlighting the one-to-many relationships.', 'duration': 106.1, 'highlights': ['By connecting customer ID to orders and cookie type to the orders table, the chapter demonstrates the establishment of one-to-many relationships, such as one customer having many orders and one cookie type being part of many different orders.', "The visual representation of the relationships between tables, with the use of lines and symbols, helps in understanding the one-to-many relationships, as indicated by the 'one' and asterisk symbols, providing a clear visual representation of the connections.", 'The ability to delete accidental connections by clicking and pressing the delete key is highlighted, offering a practical solution for managing data relationships within the database model.']}], 'duration': 187.204, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w535860.jpg', 'highlights': ['The chapter emphasizes the benefit of using Power Pivot to define relationships between tables in Excel, enabling users to establish connections between different sets of data and manage them effectively.', 'The transcript provides an example of establishing relationships between customer, orders, product, and revenue data, showcasing the practical application of defining relationships in Excel.', 'By connecting customer ID to orders and cookie type to the orders table, the chapter demonstrates the establishment of one-to-many relationships, such as one customer having many orders and one cookie type being part of many different orders.', "The visual representation of the relationships between tables, with the use of lines and symbols, helps in understanding the one-to-many relationships, as indicated by the 'one' and asterisk symbols, providing a clear visual representation of the connections."]}, {'end': 971.584, 'segs': [{'end': 790.225, 'src': 'embed', 'start': 764.365, 'weight': 0, 'content': [{'end': 770.209, 'text': 'And because we define these relationships, I can use those relationships to pull that data in here.', 'start': 764.365, 'duration': 5.844}, {'end': 776.313, 'text': "So why don't we run through an example to see how it works? I'm going to expand some of these columns just so it's a little bit easier to see.", 'start': 770.729, 'duration': 5.584}, {'end': 784.84, 'text': "So first to calculate the profit for each order, well, I take the revenue and then I'll subtract the cost and that'll give me the profit.", 'start': 777.233, 'duration': 7.607}, {'end': 787.763, 'text': "So first let's add a column here called revenue.", 'start': 785.481, 'duration': 2.282}, {'end': 790.225, 'text': "I've now added a revenue column.", 'start': 788.584, 'duration': 1.641}], 'summary': 'Using defined relationships to calculate profit from revenue and cost data.', 'duration': 25.86, 'max_score': 764.365, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w764365.jpg'}, {'end': 961.157, 'src': 'embed', 'start': 932.074, 'weight': 2, 'content': [{'end': 934.736, 'text': "And here you'll see that there's a new column called revenue.", 'start': 932.074, 'duration': 2.662}, {'end': 936.096, 'text': 'So I could select that column.', 'start': 934.776, 'duration': 1.32}, {'end': 937.997, 'text': "Then I'll hit minus.", 'start': 936.957, 'duration': 1.04}, {'end': 940.138, 'text': 'And next I want to type in cost.', 'start': 938.438, 'duration': 1.7}, {'end': 944.541, 'text': 'And here you see that it also finds the sheet called orders and cost.', 'start': 940.779, 'duration': 3.762}, {'end': 946.962, 'text': "So I'll select that item and then hit enter.", 'start': 944.701, 'duration': 2.261}, {'end': 950.073, 'text': 'So check that out.', 'start': 949.113, 'duration': 0.96}, {'end': 954.355, 'text': 'I now have the profit for every single one of these orders at the Kevin Cookie Company.', 'start': 950.173, 'duration': 4.182}, {'end': 961.157, 'text': "And I don't know if you believe me, but in all these tutorial videos, I keep telling you that Kevin Cookie Company is a pretty good company.", 'start': 954.695, 'duration': 6.462}], 'summary': "Using the revenue and cost data, the profit for every order at kevin cookie company is calculated, showcasing the company's success.", 'duration': 29.083, 'max_score': 932.074, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w932074.jpg'}], 'start': 723.584, 'title': 'Calculating revenue, cost, and profit', 'summary': "Demonstrates adding revenue, cost, and profit calculations to the data view by using related values from another table, enabling the calculation of profit for each order. it also showcases how to connect information from different tables to calculate revenue, cost, and profit for every order at the kevin cookie company, highlighting the company's profitability.", 'chapters': [{'end': 843.011, 'start': 723.584, 'title': 'Adding calculations to data view', 'summary': 'Demonstrates adding revenue, cost, and profit calculations to the data view by using related values from another table, enabling the calculation of profit for each order.', 'duration': 119.427, 'highlights': ['The chapter demonstrates adding revenue, cost, and profit calculations to the data view using related values from another table, enabling the calculation of profit for each order.', 'By defining relationships between different tables, the speaker is able to access revenue and cost information for each cookie type, allowing for the calculation of profit for each order.', 'The process involves adding columns for revenue, cost, and profit, and utilizing the related function to pull in the necessary data for the calculations.']}, {'end': 971.584, 'start': 843.652, 'title': 'Calculating revenue, cost, and profit', 'summary': "Demonstrates how to connect information from different tables to calculate revenue, cost, and profit for every order at the kevin cookie company, showcasing the company's profitability.", 'duration': 127.932, 'highlights': ['The chapter demonstrates how to connect information from different tables to calculate revenue, cost, and profit for every order at the Kevin Cookie Company. The speaker explains the process of using related fields to pull in data from other tables and then calculating revenue, cost, and profit for every order.', "The tutorial showcases the company's profitability by highlighting the calculated profit for every single order. The speaker emphasizes the profitability of the Kevin Cookie Company by showcasing the profit calculated for every single order, indicating the company's good performance.", 'The process involves adding different calculations and columns across various rows in the table. The tutorial involves adding different calculations and columns across various rows in the table to perform the revenue, cost, and profit calculations for every order.']}], 'duration': 248, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w723584.jpg', 'highlights': ['The chapter demonstrates adding revenue, cost, and profit calculations to the data view using related values from another table, enabling the calculation of profit for each order.', 'By defining relationships between different tables, the speaker is able to access revenue and cost information for each cookie type, allowing for the calculation of profit for each order.', 'The chapter demonstrates how to connect information from different tables to calculate revenue, cost, and profit for every order at the Kevin Cookie Company.']}, {'end': 1298.14, 'segs': [{'end': 1028.689, 'src': 'heatmap', 'start': 972.284, 'weight': 0, 'content': [{'end': 975.407, 'text': 'But what if we want to find out well, how many customers do we have?', 'start': 972.284, 'duration': 3.123}, {'end': 980.391, 'text': 'Or what is the total profit? Or what is the average profit per customer?', 'start': 975.907, 'duration': 4.484}, {'end': 983.193, 'text': 'We can use measures to calculate that.', 'start': 981.071, 'duration': 2.122}, {'end': 987.757, 'text': "And for now, let's go up to the top corner and let's close out Power Pivot for now.", 'start': 983.874, 'duration': 3.883}, {'end': 990.078, 'text': "And that'll bring us back to our main spreadsheet.", 'start': 988.137, 'duration': 1.941}, {'end': 994.403, 'text': "Right up here under power pivot, there's the option for measures.", 'start': 990.919, 'duration': 3.484}, {'end': 997.606, 'text': "Let's click on this right here and let's add a new measure.", 'start': 995.104, 'duration': 2.502}, {'end': 1004.513, 'text': "I'll show you how you can add a measure here and then we'll jump back into power pivot in a moment and we'll also add some measures there.", 'start': 998.207, 'duration': 6.306}, {'end': 1005.354, 'text': "Let's click on this.", 'start': 1004.714, 'duration': 0.64}, {'end': 1010.138, 'text': 'This opens up a prompt where I can start defining my measures.', 'start': 1006.215, 'duration': 3.923}, {'end': 1013.8, 'text': 'And here I could pick the table that I want to create the measure on.', 'start': 1010.738, 'duration': 3.062}, {'end': 1015.561, 'text': "I'm going to put this on the customer table.", 'start': 1013.94, 'duration': 1.621}, {'end': 1016.682, 'text': 'I think that works fine.', 'start': 1015.781, 'duration': 0.901}, {'end': 1021.685, 'text': "Right down here, I can enter in a measure name and measure one's not that descriptive.", 'start': 1017.442, 'duration': 4.243}, {'end': 1022.745, 'text': "So let's replace that.", 'start': 1021.725, 'duration': 1.02}, {'end': 1025.887, 'text': "And I'm going to type in total number of customers.", 'start': 1023.146, 'duration': 2.741}, {'end': 1028.689, 'text': 'I want to know how many customers we have at the Kevin cookie company.', 'start': 1025.967, 'duration': 2.722}], 'summary': 'Using measures to calculate total number of customers at the kevin cookie company.', 'duration': 33.585, 'max_score': 972.284, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w972284.jpg'}, {'end': 1225.611, 'src': 'embed', 'start': 1198.757, 'weight': 1, 'content': [{'end': 1205.7, 'text': "Once I type in total profit, I could hit enter and I've now renamed this measure and it's not really formatted that well.", 'start': 1198.757, 'duration': 6.943}, {'end': 1213.403, 'text': "so I can click on this item and if I go up above here, I'll format it with a dollar sign or a currency sign and I'll choose USD for now.", 'start': 1205.7, 'duration': 7.703}, {'end': 1217.925, 'text': "Here now you'll see that we had about 2.7 million total profit.", 'start': 1214.103, 'duration': 3.822}, {'end': 1218.665, 'text': 'Not a bad year.', 'start': 1218.105, 'duration': 0.56}, {'end': 1225.611, 'text': 'Next I want to add one more measure and for this one I want to use the two previous measures that I created.', 'start': 1219.847, 'duration': 5.764}], 'summary': 'Renamed total profit, formatted in usd, 2.7 million profit, adding another measure.', 'duration': 26.854, 'max_score': 1198.757, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w1198757.jpg'}, {'end': 1306.143, 'src': 'embed', 'start': 1275.714, 'weight': 2, 'content': [{'end': 1282.442, 'text': "I'll go up here and apply the currency formatting and here you can see that on average we make about $543,000 just per customer at the Kevin Cookie Company.", 'start': 1275.714, 'duration': 6.728}, {'end': 1290.917, 'text': "Now that we have some measures and we also have some calculations, let's close PowerPivot for now.", 'start': 1286.095, 'duration': 4.822}, {'end': 1298.14, 'text': "Next, I want to visualize the data in different ways and it'll be really useful to use a pivot table to do that.", 'start': 1291.557, 'duration': 6.583}, {'end': 1306.143, 'text': "To insert a pivot table, let's go up to insert in the top left-hand corner and over on the left-hand side, there's the option to insert a pivot table.", 'start': 1298.86, 'duration': 7.283}], 'summary': 'The kevin cookie company makes an average of $543,000 per customer, and will use a pivot table for data visualization.', 'duration': 30.429, 'max_score': 1275.714, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w1275714.jpg'}], 'start': 972.284, 'title': 'Calculating and visualizing data', 'summary': 'Covers using measures to calculate total profit, average profit per customer, and other customer-related data using power pivot. it also demonstrates creating measures to calculate the total number of customers, total profit, and average profit per customer, resulting in 5 customers, $2.7 million total profit, and $543,000 average profit per customer, and discusses visualizing the data using a pivot table.', 'chapters': [{'end': 1013.8, 'start': 972.284, 'title': 'Using measures to calculate data', 'summary': 'Introduces the concept of using measures to calculate total profit, average profit per customer, and other customer-related data using power pivot.', 'duration': 41.516, 'highlights': ['The chapter discusses using measures to calculate total profit, average profit per customer, and other customer-related data. This introduces the concept of using measures to calculate data, such as total profit and average profit per customer.', 'The process involves adding a new measure in Power Pivot and defining it based on the desired table. The process of adding a new measure in Power Pivot and defining it based on the desired table is explained.']}, {'end': 1298.14, 'start': 1013.94, 'title': 'Creating measures and visualizing data', 'summary': 'Demonstrates how to create measures to calculate the total number of customers, total profit, and average profit per customer in power pivot, resulting in 5 customers, $2.7 million total profit, and $543,000 average profit per customer, and then discusses visualizing the data using a pivot table.', 'duration': 284.2, 'highlights': ['Creating Measures for Total Number of Customers It demonstrates creating a measure to calculate the total number of customers, resulting in 5 customers at the Kevin Cookie Company.', 'Creating Measures for Total Profit It explains creating a measure to calculate the total profit across all orders, resulting in about $2.7 million total profit.', 'Creating Measures for Average Profit per Customer It illustrates creating a measure to calculate the average profit per customer, resulting in about $543,000 average profit per customer at the Kevin Cookie Company.', 'Visualizing Data Using a Pivot Table It mentions the usefulness of visualizing the data in different ways using a pivot table.']}], 'duration': 325.856, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w972284.jpg', 'highlights': ['The process involves adding a new measure in Power Pivot and defining it based on the desired table.', 'Creating Measures for Total Profit It explains creating a measure to calculate the total profit across all orders, resulting in about $2.7 million total profit.', 'Creating Measures for Average Profit per Customer It illustrates creating a measure to calculate the average profit per customer, resulting in about $543,000 average profit per customer at the Kevin Cookie Company.', 'Creating Measures for Total Number of Customers It demonstrates creating a measure to calculate the total number of customers, resulting in 5 customers at the Kevin Cookie Company.', 'Visualizing Data Using a Pivot Table It mentions the usefulness of visualizing the data in different ways using a pivot table.']}, {'end': 1824.046, 'segs': [{'end': 1438.65, 'src': 'embed', 'start': 1407.677, 'weight': 0, 'content': [{'end': 1411.619, 'text': "I can click on this little drop down and I'm going to click on value field settings.", 'start': 1407.677, 'duration': 3.942}, {'end': 1415.52, 'text': 'Over here I can choose how I want to summarize the valued field.', 'start': 1412.219, 'duration': 3.301}, {'end': 1419.282, 'text': "Right now it's sum, but let me change that to count and click on OK.", 'start': 1416.08, 'duration': 3.202}, {'end': 1424.644, 'text': 'So just like that now I can see how many orders each customer has placed.', 'start': 1419.902, 'duration': 4.742}, {'end': 1428.805, 'text': 'So here I can see that Acme Bytes has 206 orders.', 'start': 1425.124, 'duration': 3.681}, {'end': 1429.886, 'text': "That's quite a few orders.", 'start': 1428.845, 'duration': 1.041}, {'end': 1438.65, 'text': "but the neat thing here is remember that the customer's data was separate from the order's data, but because I defined those relationships here,", 'start': 1430.546, 'duration': 8.104}], 'summary': 'Changed value field setting to count, showing acme bytes has 206 orders', 'duration': 30.973, 'max_score': 1407.677, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w1407677.jpg'}, {'end': 1480.09, 'src': 'embed', 'start': 1449.366, 'weight': 1, 'content': [{'end': 1454.33, 'text': "Now one thing that's really powerful, just a moment ago we created all of these different measures.", 'start': 1449.366, 'duration': 4.964}, {'end': 1457.732, 'text': 'We can use those measures in our pivot table.', 'start': 1454.83, 'duration': 2.902}, {'end': 1463.356, 'text': "So let's say I want to see how much profit I made for each one of these different customers.", 'start': 1458.273, 'duration': 5.083}, {'end': 1470.161, 'text': 'So over here on the right hand side, I can expand customers and right down here I see my measure called total profit.', 'start': 1463.997, 'duration': 6.164}, {'end': 1473.624, 'text': "Right here, I'll click on that and let's pull that in.", 'start': 1471.002, 'duration': 2.622}, {'end': 1480.09, 'text': 'Once I pull that in, here too I can very quickly see how much profit I made for each one of these customers.', 'start': 1474.385, 'duration': 5.705}], 'summary': 'Measures created in pivot table to analyze profit by customer.', 'duration': 30.724, 'max_score': 1449.366, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w1449366.jpg'}, {'end': 1516.264, 'src': 'embed', 'start': 1491.553, 'weight': 2, 'content': [{'end': 1496.917, 'text': "Now, Acme Bytes has quite a bit of profit and some of them aren't quite as high as I would like them to be.", 'start': 1491.553, 'duration': 5.364}, {'end': 1501.86, 'text': 'So maybe I want to add some KPIs around how much profit each customer is pulling in.', 'start': 1497.277, 'duration': 4.583}, {'end': 1505.763, 'text': "Right up on top on the ribbon, once again, let's click on Power Pivot.", 'start': 1502.541, 'duration': 3.222}, {'end': 1508.545, 'text': "And over here, there's an option for KPIs.", 'start': 1506.184, 'duration': 2.361}, {'end': 1509.946, 'text': "Let's create a new KPI.", 'start': 1508.725, 'duration': 1.221}, {'end': 1516.264, 'text': 'This opens up a prompt where I can define a KPI or a key performance indicator.', 'start': 1511.161, 'duration': 5.103}], 'summary': 'Acme bytes aims to improve profits by adding kpis for customer profitability.', 'duration': 24.711, 'max_score': 1491.553, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w1491553.jpg'}, {'end': 1618.645, 'src': 'heatmap', 'start': 1594.104, 'weight': 0.822, 'content': [{'end': 1599.849, 'text': "Well, let's go back over here on the right hand side and you'll see a new icon added with this traffic light symbol.", 'start': 1594.104, 'duration': 5.745}, {'end': 1604.693, 'text': "If I click on that here, I'll simply remove them for now and then I'll re-add them.", 'start': 1600.509, 'duration': 4.184}, {'end': 1610.478, 'text': 'So here now I can see the color associated with all of these different customers.', 'start': 1605.173, 'duration': 5.305}, {'end': 1612.299, 'text': 'I could also click on the goal.', 'start': 1611.118, 'duration': 1.181}, {'end': 1618.645, 'text': 'So here I could see the goal is $600,000 for each one of these and I can see how each customer is performing compared to the goal.', 'start': 1612.339, 'duration': 6.306}], 'summary': 'New icon added for traffic light symbol, showing color associated with customers and $600,000 goal for each customer.', 'duration': 24.541, 'max_score': 1594.104, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w1594104.jpg'}, {'end': 1794.451, 'src': 'embed', 'start': 1769.617, 'weight': 4, 'content': [{'end': 1776.341, 'text': 'So this is a very fast way to analyze and look at your data and also to gather insights from your data.', 'start': 1769.617, 'duration': 6.724}, {'end': 1781.464, 'text': "Hopefully by now you're starting to see the tremendous power of Power Pivot.", 'start': 1776.921, 'duration': 4.543}, {'end': 1784.525, 'text': 'You can bring in multiple tables of data,', 'start': 1781.884, 'duration': 2.641}, {'end': 1791.289, 'text': 'you can relate that data and then you can visualize the data in tables and also charts to get insight from your data.', 'start': 1784.525, 'duration': 6.764}, {'end': 1794.451, 'text': "Now, data on its own isn't really that valuable.", 'start': 1791.869, 'duration': 2.582}], 'summary': 'Power pivot offers fast data analysis, insights, and visualization from multiple tables.', 'duration': 24.834, 'max_score': 1769.617, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w1769617.jpg'}], 'start': 1298.86, 'title': 'Powerpivot for data analysis', 'summary': 'Covers creating pivot tables and pivot charts, analyzing customer data, and visualizing 206 orders placed by acme bytes, emphasizing profitable customers and setting profit targets.', 'chapters': [{'end': 1429.886, 'start': 1298.86, 'title': 'Pivot table in powerpivot', 'summary': 'Explains how to create a pivot table using powerpivot, leveraging a data model to connect multiple tables and summarizing the number of orders placed by each customer, with acme bytes having the highest count of 206 orders.', 'duration': 131.026, 'highlights': ['The pivot table is created using PowerPivot and a data model, allowing connection of multiple tables of data.', 'The pivot table summarizes the number of orders placed by each customer, with Acme Bytes having 206 orders.', 'The process involves selecting and dragging fields to rows and values, and changing the value field settings to count the number of orders.']}, {'end': 1631.036, 'start': 1430.546, 'title': 'Customer data analysis with power pivot', 'summary': 'Discusses using power pivot to analyze customer data, create measures and kpis, and visually represent customer performance, with emphasis on identifying the most profitable customer and setting profit targets.', 'duration': 200.49, 'highlights': ['By defining relationships in Power Pivot, customer data and order data can be related, enabling quick data analysis and insights.', 'Measures created in Power Pivot can be used in pivot tables to analyze profitability for each customer, with Acme Bytes being the most profitable customer.', 'Creating KPIs in Power Pivot allows setting profit targets for each customer and visually representing their performance using traffic light symbols.', "KPIs visually indicate if a customer's profit is underperforming or exceeding the set target, enabling proactive sales strategies to improve profitability."]}, {'end': 1824.046, 'start': 1631.036, 'title': 'Visualizing data with pivot charts', 'summary': 'Demonstrates how to visualize data using pivot charts in powerpivot, including creating a pivot chart, sorting data, and using slicers to analyze and gather insights from the data.', 'duration': 193.01, 'highlights': ['The chapter demonstrates creating a pivot chart to visualize data, such as using a pie chart to analyze the total profit made by each customer. The presenter showcases using a pie chart to analyze the total profit made by each customer, demonstrating the visualization of data through a pivot chart.', 'The chapter explains how to utilize slicers to analyze and gather insights from the data, including dynamically updating the table and chart based on slicer selection. The presenter illustrates using slicers to dynamically update the table and chart based on the selected criteria, enabling efficient analysis and insight extraction from the data.', 'The chapter emphasizes the value of Power Pivot in extracting insights from data, highlighting its ability to bring in multiple tables, relate data, and visualize data in tables and charts for gaining insights. It is emphasized that Power Pivot enables the extraction of insights from data by bringing in multiple tables, relating data, and visualizing data in tables and charts, ultimately facilitating the extraction of valuable insights from the data.']}], 'duration': 525.186, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/rB_IiYbOo7w/pics/rB_IiYbOo7w1298860.jpg', 'highlights': ['The pivot table summarizes the number of orders placed by each customer, with Acme Bytes having 206 orders.', 'Measures created in Power Pivot can be used in pivot tables to analyze profitability for each customer, with Acme Bytes being the most profitable customer.', 'Creating KPIs in Power Pivot allows setting profit targets for each customer and visually representing their performance using traffic light symbols.', 'The chapter demonstrates creating a pivot chart to visualize data, such as using a pie chart to analyze the total profit made by each customer.', 'The chapter emphasizes the value of Power Pivot in extracting insights from data, highlighting its ability to bring in multiple tables, relate data, and visualize data in tables and charts for gaining insights.']}], 'highlights': ['Power Pivot enables analyzing data across multiple tables and handling massive quantities of data, surpassing standard pivot table limitations.', 'Step-by-step guide to activating Power Pivot as an add-in in Excel, ensuring easy access and utilization of this powerful tool.', 'Demonstrates creating data models, calculations, measures, and visualizations with Power Pivot, showcasing its comprehensive capabilities for data analysis and presentation.', 'Imported 700 rows of order data into Power Pivot Successfully imported 700 rows of order data into Power Pivot, demonstrating the capability to handle large datasets.', 'The orders sheet contains the customer ID, order ID, product details, quantity, and order date, providing essential information for analysis.', 'Established relationships between different data sheets Discussed establishing relationships between different data sheets, showcasing the ability to connect and analyze data from various sources.', 'The customer sheet includes customer information and contact details for customers at the Kevin Cookie Company.', 'The cookie types sheet lists all the different types of cookies made at the Kevin Cookie Company, offering insights into product variety and inventory.', 'Added cookie type information to the data model Demonstrated the process of adding cookie type information to the data model, showcasing the flexibility of including additional data from within the Excel spreadsheet.', 'The chapter emphasizes the benefit of using Power Pivot to define relationships between tables in Excel, enabling users to establish connections between different sets of data and manage them effectively.', 'The transcript provides an example of establishing relationships between customer, orders, product, and revenue data, showcasing the practical application of defining relationships in Excel.', 'By connecting customer ID to orders and cookie type to the orders table, the chapter demonstrates the establishment of one-to-many relationships, such as one customer having many orders and one cookie type being part of many different orders.', "The visual representation of the relationships between tables, with the use of lines and symbols, helps in understanding the one-to-many relationships, as indicated by the 'one' and asterisk symbols, providing a clear visual representation of the connections.", 'The chapter demonstrates adding revenue, cost, and profit calculations to the data view using related values from another table, enabling the calculation of profit for each order.', 'By defining relationships between different tables, the speaker is able to access revenue and cost information for each cookie type, allowing for the calculation of profit for each order.', 'The chapter demonstrates how to connect information from different tables to calculate revenue, cost, and profit for every order at the Kevin Cookie Company.', 'The process involves adding a new measure in Power Pivot and defining it based on the desired table.', 'Creating Measures for Total Profit It explains creating a measure to calculate the total profit across all orders, resulting in about $2.7 million total profit.', 'Creating Measures for Average Profit per Customer It illustrates creating a measure to calculate the average profit per customer, resulting in about $543,000 average profit per customer at the Kevin Cookie Company.', 'Creating Measures for Total Number of Customers It demonstrates creating a measure to calculate the total number of customers, resulting in 5 customers at the Kevin Cookie Company.', 'Visualizing Data Using a Pivot Table It mentions the usefulness of visualizing the data in different ways using a pivot table.', 'The pivot table summarizes the number of orders placed by each customer, with Acme Bytes having 206 orders.', 'Measures created in Power Pivot can be used in pivot tables to analyze profitability for each customer, with Acme Bytes being the most profitable customer.', 'Creating KPIs in Power Pivot allows setting profit targets for each customer and visually representing their performance using traffic light symbols.', 'The chapter demonstrates creating a pivot chart to visualize data, such as using a pie chart to analyze the total profit made by each customer.', 'The chapter emphasizes the value of Power Pivot in extracting insights from data, highlighting its ability to bring in multiple tables, relate data, and visualize data in tables and charts for gaining insights.']}