Creating a Power Pivot report

- [Instructor] We're going to use H+ sports transactions data and product information to build a simple but powerful dynamic report using Power Pivot. In this file, I have physically brought in two data sets, one for transactions and the other for products. If we were using Power Query, I could point to these externally, but for the sake of ease and your learning, I've simply brought them into this one file on two separate worksheets. Let's take a look. I want to get a report of total cost for all products broken down by product descriptions and product categories. But in this transactions dataset, the only product level information that I have is right here in column C, the SKU. I have no product description, no product category, no item cost, I only have SKU as well as the number of items that I have sold. However, in the product info dataset, I have the SKU, but then I also have the product description, the product category, and then the item cost. Now, why am I making this point? Because if I'm going to analyze my transaction costs at the product level, I can't do that over here in my transactions table. The details aren't even here, which is why I need to connect these data sets to each other through relationships. I'm going to format each one of these data sets as a table. I'm going to highlight the entirety of my data, go to insert, and format as a table. Let's go up to the table design and name this table "Sales Transactions Table." Now, I'm not going to do the second table just yet, and you'll understand why in just a moment. I'm going to go over to my product info page, and I'm going to go up here to Power Pivot added to the data model. So look, I didn't format this data as a dynamic table, and Excel now is prompting me to do it. That's why tabular data is so important to get accustomed to. I'm going to click okay. I'm going to go back to Excel. And I'm going to name this "Products Listing Table." Now I'm going to go back over to the transactions, up to Power Pivot, and add it to the data model two. Let's make sure that we have this table worksheet named as well. Now I'm in my Power Pivot ribbon. I'm going to go over here to diagram view and see the relationships between these data tables. They don't exist yet. Notice where the overlap is. I have SKU, product, category, and cost. Over here, I have order number, employee ID number, again, SKU, region customer date, and number of items. There's only one overlapping category here and it's SKU. I'm going to grab it from the transactions data set, connect it to the products listing, and let go of my mouse button. Now, when I do, you can see that I have this one to many relationship going upstream from the product listing to the sales transactions. Why does this matter? Because the product info table is a register. It has all of the root source information listed one at a time, whereas the transactions are likely to have lots of instances where SKUs are sold many times. So we have just built our first data model and connection. No V lookups, no X lookups required. But we're not done yet. What we want to be able to do is to be able to see the costs by product and product category. In my Power Pivot ribbon, You can see right here in the middle, I have an icon that says "Pivot Table." When I hit this dropdown arrow, you can see that I have many different options as a way that I can slice and present this information. For ease, let's go up here to a classic pivot table. I'm going to add it to a new worksheet, click okay. Now this is where things are going to get really interesting. Rather than just have access to one data set or the other, the way that it would be with a classic pivot table, we are going to have access to both and they're going to be communicating with each other. So I'm going to do a right click and I'm going to go over here to show field list. So as you can see, I now have access to both of these data sets. I'm going to click on these arrows to expand. And let's have some fun. I'm going to begin by going into my sales transactions table down to the bottom and grabbing number of items. I'll drag that into my values field. You can see that it totals to 10,218 in change. Let's quickly run a quality check and go over to the transactions data and make sure that those reconcile. They do. Now, I'm going to put customer into my rows. Again, this adds up to 10,218, but there's really nothing special about this. You could do this in a regular pivot table in Excel. So let's push this into Power Pivot territory. Something we wouldn't be able to do in a classic pivot table or an Excel without lookups. Up here at the top of my field, you'll see my other data table, products listing. This is where all my product info is located, product, product category, and item cost. So what I'm going to do is I'm going to grab product, and I'm going to put this below my customers. This now shows me all of the different products and the sum of the units by individual customer. If I wanted to represent this a little bit differently, I could put this above customer. And this now shows me how much of my 30 day H+ supplement pack I'm selling to each one of my customers, how many battle ropes I'm selling to each one of my customers, and so on. Okay, now you understand why this matters. If I go back into my transaction data set, I have units, I have SKU IDs, but I have no information as it relates to what these SKU IDs translate into from a product perspective. In my product info register, I have SKU, product description, product category, but I don't know how many units I sold. Power Pivot lets me work with both. Now, let's rearrange this a little bit. I'm going to add in product category at the top of my rows. Now I have my four product categories, apparel, fitness, mineral water, and supplements. Within each of these categories, I have my four products, mineral water blueberry 12 cases, peach 12 cases, and plain 12 cases. Within each of these products, I have my customers. Let's filter this in a different way. I'm going to go down to my rows field, and instead of having customer in my rows, I'm going to drag it over here to my columns. Isn't this amazing? Now I can filter all of this information, I still have the 10,218, but now I get to see my four different categories of products, all of the product descriptions within those categories, and then across the horizontal axis, I can see across one, two, three, four, five different customers as well as my grand totals. And finally, I'm going to add date to my filter. Date doesn't exist in my product listing, I'm going to need to go down here to my sales transactions. I'll grab date here, put it into my filter. Now seemingly, nothing has changed because I'm including all transactions of all dates, but I'd like to only see May, 2024. If I click on this dropdown, expand this, you'll see that here is all of my information for my dates. It lists out each one individually. Well, the problem is checking each one of these is going to be a real hassle. Is there a better way for me to do this? If I go into my data model and click manage, I'm going to select my design ribbon, and then I'm going to go over here to date table, and I'm going to select new date table. This is remarkable. This is called auto time intelligence. Amazingly, Power Pivot adds in a new table that has all sorts of useful date information. It creates a basic internal calendar, and now I can slice the parameter without extra modeling. So this is pretty incredible, right? Now, watch what I can do. This here is going to be a root source table. I'm going to have each date listed one time. If we want to see what goes in here, I'm going to select the table, I'm going to go over to the home ribbon to be able to take a look. I'm going to click on data view. Here you can see each date listed one time. This is the year, the month number, the month name, the month sequence of a three character month, four character year, the day of the week number, the day of the week name, so much information that I can work with here. Let's go back over to diagram view and let's build a relationship. Okay, well, if I look over my products listing, I have no dates listed here. Remember, this is a root source data set. This is also a root source data set, so I'm going to need to connect my calendar to my sales transactions. I'm going to grab date from here and connect it to date on my calendar. And when I do, now I have a one to many relationship connected on the date element. This now means that I should be able to access all of this information from either my products listing or my sales transactions. This is a direct connection, this is an indirect connection. Now I'm going to go back over to my workbook. Here's my Power Pivot table. And now you'll note that in my Pivot Table field, I now have a new data set represented by calendar. Instead of leaving this as it is, I'm going to select month and bring it into my filter. I'm going to remove the date, and now I should be able to sort this information by my month date hierarchy. I'm going to select May 2024, click OK, and there we go. In this Power Pivot table, I can now see all of my transactions by product category, by product description, by customer for the month of May, my grand total is 1,198. Let's just make sure from our transactions that that number is correct. It is, 1,199 rounded, 1,199 rounded. If we are smart about the way that we structure our data and we connect them through relationships, we can conduct all sorts of relevant analysis. That's the essence of how Power Pivot works.