Transcripts
1. Lesson 1 Introduction: Hey, welcome to the course. Now, among the computer programs which Xist, Microsoft Excel is one of the most important because of the key role it plays in many sectors. It is most used spreadsheet program in many business activities, classwork, and even postal data organizations. Any businesses, firstname, and institutional enterprises have embraced the use of Excel because of its utility and the ability to serve as a visual basic for different applications. No doubt the importance of MS. Excel can be seen in every sphere of life. Made be graphing, mathematics, statistics, programming, or even big data. A thorough knowledge. Some of the hidden advanced tools in Excel can really set you apart from the crowd to be looked upon as a go-to person when it comes to playing with large volumes of data. And one such hidden tool in Excel that can help you play with a large cumbersome data is the PowerPivot function in Excel, and that is going to be our focus in this lecture series. So what exactly is a Power Pivot? Power Pivot is a free Microsoft Excel tool that increases the capabilities of Excel's pre-existing pivot table tool by allowing users to input data from multiple sources. Powerpivot is a feature of Microsoft Excel that was introduced as an add-in to Excel 20102030, and now is a native feature for Excel 2016365. As Microsoft explains, PowerPivot for Excel enables you to import millions of rows of data from multiple data sources into a single Excel workbook. And then it will allow you to go ahead and create relationship between heterogeneous data. Create calculated columns and measures using formulas, build pivot tables and pivot charts. And then further analyze the data so that you can make timely business decisions without even requiring IT assistance. And why do we need to care about this? Because x l itself has its own limitations. Now, Excel itself is a very big culprit because of its own limitations of just ten lacked 48,576 rows, means you cannot analyze any data beyond this particular limitation. If you have data that runs into rose more than ten lac, 48,576, XL cannot handle this particular data. Now this may seem like a lot, but when you talk about databases, these numbers can really be miniscule. Doing your analysis on two data sources at the same time is virtually impossible in Microsoft Excel. Other le, PowerPivot gives us that functionality because we can extract data from various sources, combine them in a data model and work on it. And large amount of data can actually bring down the speed of your excel. As you keep on adding more and more data to your worksheet, you will observe that Xcel starts behaving in a very sluggish manner. And finally it comes down to a stage where your Excel workbook will literally begin to crawl. So let us know a little bit about the course. So we start this course with a refresher on the pivot tables because that is something that we need to know fundamentally. Then we understand the nuts and bolts of pivot table functionality in exit, we then move on to the concept of Power Pivot and then cover the following topics in detail. We learned how to go ahead and activate the power pivot added in Excel. Then we start constructing data models based upon the data that we have derived through various sources. We then build relationship between the different datasets that are present in our data model. And ten, we start constructing reports like a pivot table or a pivot chart within UP power pivot environment. Finally, we learned how to go ahead and add calculated fields and KPIs in our reports. One of the best path for this particular course is it comes with a lot of resources for your practice. So if you go to the project and Resources section of the course and come towards the right-hand side of the screen, you will observe that I have provided two different resources. The first resource, which is the pivot FileResource, is something that you can use as you learn along the course. And then I have also gone ahead and provided a project raw data for you. What you need to do is once you have completed the lecture, you need to go ahead and open this particular raw data file and tried to replicate the report that you have learned to create in the lecture series. And then you can go ahead and paste a screenshot of your results in the project section. And I shall definitely review the same if you have any questions around the course, do not hesitate to go ahead and use the discussion sections of the course and start a new conversation. And I will ensure that I get back to you as soon as possible. So let's get started and let's explore the power of Power Pivot.
2. Lesson 2 Introduction to Pivot Tables : Hey, welcome back. So in this particular lecture series or in the next five lectures, we are going to get introduced to something that we refer to as the Excel Pivot Tables. Now why do we learn PivotTables are what exactly are pivot tables? So PivotTables are a powerful tool within Excel that can be used to analyze, sought, filter and present data in an understandable way. Now why do we need that? So many times before we go ahead and create a dashboard, we have a lot of cumbersome data in front of us. And we need to first convert that data into a meaningful data and then proceed to go ahead and create a dashboard out of that. So this is where the pivot tables will come handy for us. Now it is called the pivot table because you can rotate its row and column headings around the core data area to give you a different view of the source data. Which means that the data that you have in a pivot table, let's say the data is in the rows. You can move the data from the rows to columns and columns to the rows. And that is the magic of pivot table. As we go through the course, you will understand that. And as a source data changes, you can always update a pivot table, which will give you a real-time view of the running statistics. And before we start and go ahead and construct our first pivot table, let's understand the importance of pivot tables. First of all, PivotTables are user friendly and you can go ahead and create a pivot table in just a matter of few clicks. Pivottables can go ahead and create in some data that we can go ahead and use for dashboard building. Pivot Tables makes the data analysis easier, which means a large, cumbersome data can be converted into a meaningful output. Pivot tables are easy to update and the add dynamic as and when your source data changes, you will observe that your pivot table can be easily updated just in a matter of a few clicks. And pivot tables summarizes your data. Very easily. Pivot Tables can assist in finding certain data patterns. And last but not the least, Pivot Tables can create accurate reports real fast. So let's get started and let's go ahead and create our first pivot table. So over here, what I have is this particular dataset, and I have a lot of records over here. So let me just go ahead and have a look as to how many data records do I have a wheel. So I have close to 97 records. And this data is about the sales per month of various products. And these products fall under different categories. They had been sold under different locations. And there are different units that have been sold. The price of each and every unit is different. The profit per unit is different. And then that is a total revenue, which is the product of units sold and the price. And then finally, we have the total profit, which is the product of unit sold multiplied by profit per unit. Now, I want to go ahead and create a dashboard, all of this particular dataset. But before I can go ahead and make a dashboard out of this, I need to go ahead and convert it into some kind of a meaningful output. Now what we are, what I have done is I have gone ahead and created two different pivot tables. So first, PivotTable gives me a month wise report of the different categories that I sell. And then finally, it gives me the grand total of both my categories. My categories fall into bakery and snacks. And this is a month wise bifurcation of the C. Secondly, if I'm looking at this particular pivot table over you, it gives me a bifurcation of the total seals done by each and every location. Not only on the basis of category, but also on the basis of the products that have been sold, taking into concentration in which particular category the products fall. So let's quickly go ahead and construct these pivot tables real quick. So I am going to use the same data in a blank Excel sheet. And here is my data. Now to go ahead and cast a pivot table real quick. What I'm gonna do is I'm going to click anywhere on this particular dataset. And then I will go to insert and click on the table. Over here. It has already gone ahead and selected my data range. And I will say, go ahead and create a pivot table on a new worksheet and click on, okay. When I go ahead and do that, it has gone ahead and created this particular pivot table layout for me. And over here I have the pivot table fields. Now in my first PivotTable, I need the data month wise for each of the categories. And finally, I need the Grand Total. So to do that, what I'm gonna do is I'm just gonna go ahead and select the seals month and put it in my rows section over yo. Then what I'm gonna do is I'm going to select the categories and put it in the column section. Now what I need in my value section is the total profit. So what I'm gonna do is I'm going to just go ahead and drag this total profit value over you. And you will observe that the moment I did that, I have my pivot table ready. Let's quickly go ahead and construct the second pivot table. Now, the second pivot table over your is having the categories. And the categories I have the subcategories and then it shows me the bifurcation of the seals as per the cities. So let's quickly go ahead and construct that. So I will once again go ahead and click on minority Tojo, click on Insert, have it click on pivot table. And then I will go ahead and click on this particular option that says Existing worksheet. And then I will click over here. I will come down to my sheet over here. And let's say I want to go ahead and construct the pivot table. And I'm gonna go ahead and hit Enter. Click on OK. Now the layout is really all I need to go ahead and do is drag the locations to my column section. Then I need the categories. And under the categories, I also need the name of the product. So I'm gonna go ahead and drag the product. So we, and now finally I will go ahead and run the total profit. So you will observe that immediately it has gone ahead and created my second pivot table as we're reading, it gives me the bifurcation of the categories and subcategories and the total profit earned by each of the cities. Now, when you're working with pivot tables, pivot table gives you a lot of options where you can go ahead and format your data. Now, if at all, I select this particular pivot table over here and go to the Design tab. I have lots of options over you. So I'm not gonna go much into the details of each of them because this is not an exclusive PivotTable course. But I will just show you a few options that you can actually go ahead and play around with. You can come down to this particular option that says Report Layout and change the way your data appears in the pivot table. So I can go ahead and click on the outline format. And you will observe that the categories and the subcategories now appear in separate columns. I can go ahead and use a tabular format, or I can keep on continuing with the compact format. Also, you will observe that towards the right, that is something that we call as a PivotTable styles. Now, Oh yeah, if at all, i go ahead and click on this drop-down. I have a lot of pivot table styles that I can pick up from. Now, why do we need this? Now? Many a times you have a team for your dashboards. And sometimes it's necessary that you go ahead and show your pivot table along with your dashboard. And to match the theme, you probably might need a particular style. And that is where this particular option can come in real handy. So what I can do is I can go ahead and select this particular style over you. And the moment I did that, you will observe that my data is ready. Now there are also other formatting options. Let's go ahead and quickly look at them. Now let's say over here in my value section, instead of the sum, I'm interested in knowing the average. So I can go ahead and click on average and click on OK. And you will observe that it picks up the average value. I can also go ahead and format the numbers in case I want to. So for that, what I can do is I can right-click on my Pivot Table anywhere, go to Number format. And I can come down to this option that says numbers. And let's say I don't want to see the values as decimals. So I can go ahead and change the decimal places to 0. Click on OK. And you will observe that the decimal values have vanished. Also, you will observe that as and when the data changes, the size of my pivot table keeps on changing. So let's see what happens is if at all i go ahead and go to my number format, and then I go ahead and change the decimal places by two. You will see that the size of my pivot table keeps on changing as the data changes. Now to avoid that, what I can do is I can right-click on my pivot table, go to Pivot Table Options. And then I can go ahead and uncheck this option that says Auto Fit column widths on update. And I can go ahead and click on OK and post which if at all, i go ahead and change any values or go ahead and refresh my pivot table. The size of WebPivotTable will not change. Also under the design section, I have a few options like subtotals, grand totals, wherein I can go ahead and hide or unhide. My subtotals are showed them at the bottom or top of the Group. For example, if I go ahead and click on this particular option that's a subtotals at the bottom of the group. You will see that all my subtotals appear at the bottom of my pivot table. If at all, i go ahead and click on top of the group. It appears at the top. Similarly, this particular option called grand total can be used for turning of the grand total for rows and columns, and probably for both of them. For example, over here, if at all I click on off for rows and columns, you will observe that all my grand totals have vanished. If at all I click on, on four rows and columns, they appear. So you can actually go around and play with the pivot table depending upon the requirement of your dashboard. So that was a kind of a quick introduction to the Pivot Table. I hope you enjoyed this quick introduction to pivot tables, and I shall see you in the next lecture.
3. Lesson 3 Formatting Pivot Table: Hey, welcome back. So in our last lecture, we understood how we go ahead and create pivot tables. Now in this lecture, our focus is going to be on going ahead and applying conditional formatting to our pivot tables. We are going to use the concept of data bars, and we will try to apply the data bars to our existing pivot tables over here. So now what I'm gonna do is I just want to go ahead and apply the data bars, do this particular data over you. So what I'm gonna do is I'm going to click on this. I'll go to conditional formatting, would do data bars. And I will select this particular d dt of R over u. Now you will observe that the moment I did that, it has gone ahead and applied a conditional formatting to my dataset over here. But along with that, there is a small widget that has popped up on the right-hand side. Let me just go ahead and click on that widget. And you will observe the directory different options over here. So one says selected cells, and that is by default. The second option over here is also showing sum of total profit values. So I'm gonna go ahead and click on that. And you will observe that it has gone ahead and applied that conditional formatting to my entire dataset. However, one thing that you need to notice over year Is it has also gone ahead and taken my grand total into consideration. And that is the reason when it is going ahead and comparing these values with my grand total, There is a huge variance. And that's why all these data bars are pretty small in size as compared to the grand to-do over you. And I don't want that to happen. So how do I go ahead and fix that? What I can do is I can just go ahead and click over here. And I will select this particular value over here that says all says, showing some of total profit values for sale of month and category. And the moment I did that, you will observe that it has not gone ahead and taken my grand total into consideration. And it has gone ahead and apply the data bars to these particular values away you. Now just in case if I want to go ahead and apply a different set of conditional formatting to this particular dataset. I can go ahead and do that as well. Let me just go ahead and select this particular data. Go to conditional formatting. And then I will select a different kind of a Conditional Formatting movie. And you would also have that the moment I did that it has gone ahead and compare these values amongst itself and have gone ahead and applied a different conditional formatting altogether. So what I have done different Yo-Yo is I have selected the entire dataset first, and then I have gone ahead and applied the Conditional Formatting. Now, let us look at some more scenarios over year. Now, let me just go ahead and first change this pivot table a little bit. So what I'm gonna do is I will select this pivot table, right-click on it and click on Show Field List. And the way you are, what I will do is I will remove the locations and I will also remove the average to two. And now I will just go ahead and add. Total revenue away. And then I will add that total profits. And let me just go ahead and close this. And now let me just go ahead and click on this minus sign away. And you will observe that now it is only showing my categories and not my subcategories. Now let me go ahead and try applying a Conditional Formatting over here. So let me just first select this particular cell. We'll click on Conditional Formatting and I'll go to Data Bars and let me just select this particular form, I believe now once again, it gives me that small widget. So I'm going to click on that. And I will select this option that says all cells shrink some of total revenue value for category. Okay, so not the subcategory, just a category. So I'm gonna do that. And you will also, the moment I did that, it has gone ahead and applied my data was to both my categories over here. If I go ahead and collapse the categories and look at my subcategories, no conditional formatting has been applied to the c. Now if at all, I want to go ahead and apply conditional formatting to my subcategories, then I will have to select them, go to the conditional formatting, go to Data Bars. And let me just select a gradient fill lawyers. So let's select the green one. And once again, the vision has appeared. So I will click on this drop-down over here and I will click on all cells showing some of total revenue. And you will observe that the moment I did that immediately it has gone ahead and applied this conditional formatting to all my subcategories as we're now. Similarly, let me just go ahead and apply the conditional formatting to my total profit as well. So I will just click over here. Click on Conditional Formatting. Go to Data Bars, and let me just select this particular formula. And if I click on it, and I click on All cells showing some of the total profit values for categories. It will apply the conditional formatting to bone the categories over you, the bakery and thus snacks. But it has not gone ahead and applied any formatting to the subcategory Zoe or the name of the product. So you, so let me just apply some conditional formatting lawyer. So I will select this gradient, fill this time. And once again, I will click over here and I will go ahead and apply the conditional formatting. But now, if I come back to this particular column over here, you will observe that my budget is available on the right-hand side, but it is not visible away you now just in case if I want to go ahead and make any modifications to my conditional formatting, then how do I do that? It's very simple. So I will just go ahead and click on any of the cells within my pivot table, go to conditional formatting. And then I will click on Manage Rules. And you will observe that the moment I did that, all the rules that have been applied for conditional formatting within this pivot table have appeared over you. Let's look at them very closely. So the first one, which is the red bar, is applied to the sum of total revenue within the category. Okay, my categories are bakery and snacks over here. The green one is applied to the product, okay? But on the revenue side, similarly, the pink one is applied to the category on the profit side, and then the yellow one is applied to the total profit on the product side. So you need to understand that initially it might be a little confusing. But as and when you get a hold on conditional formatting, you will understand how these roots function. And in case you want to go ahead and make any changes to any of the rules, all you need to do is highlight the rule. And then you click on Edit True. And the moment you do that, you will observe that all those three options that you were looking at initially in the Widget are available or YOU, the selected cells. All cells showing some of total profits and all cells showing some of total profit values for products. So just in case you ever wish to go ahead and change any rules, you can go ahead and do that using the Manage Rules option. So that's how you can go ahead and apply conditional formatting to pivot tables. In the next lecture, we will learn some more features of our pivot table. So I hope you enjoyed this lecture and I shall see you in the next one.
4. Lesson 4 Filters and Slicers: Hey, welcome back. Now in our last lecture, we focused on going ahead and doing conditional formatting to our Pivot Tables. And over here, in this particular lecture, our focus will be on understanding how do we go ahead and filter the data in our pivot table. So what I'm gonna do is add in the same sheet. I will first go ahead and clear all the rules. So it's very simple. I can just click anywhere on my sheet over your go to conditional formatting. And I can click on clear rules. And I will select this option that says clear the rules from entire sheet in case you ever want to go ahead and clear the rules from the selected cells, you can select this post option. But right now our focus on this lecture is on filtering. So I'm going to clear all the rules from the sheet. So now we have these two pivot tables in front of us. Ok? Now, let's say I want to go ahead and filter this data. And what do I mean by filter? So let's say in this particular data, I want to go ahead and look at the data of only a few cities because this particular data is a cumulative data of all the cities that I have in my database. Because if I'm looking at my database, it has picked up the data from all the several cities that I have. So how do I go ahead and filter the data so that I look at the data only from specific cities. Now there are a few ways of doing that. I will right-click on the PivotTable and click on this particular option that says Show Field List. And over here I have this particular section which is called as the filter area. Now, I want to go ahead and filter the data on the basis of location. So what I can do is I can just go ahead and drag the location over you. And you will observe that the filtering criteria has just popped up above my pivot table over you. Now effect all I go ahead and click on this particular data. It gives me the option of selecting any particular city, and it will only show me the data for that particular city. So let's say I want to see that the dove only Bangalore. And I go ahead and click on ok. It is only showing me the data for the city of Bangalore. Now, in case I want to go ahead and select multiple cities, I can just go ahead and put a check mark on this particular option that says select multiple items. And let's say now I want to see the cumulative data of Bangalore and Delhi. I can go ahead and put a check mark on daily as well and click on ok. So now what has happened is it has gone ahead and filtered my data. And it is showing me the data for two cities, Bangalore and Delhi. Now that's one way of going ahead and filtering your data. Now let's look at another way of filtering your data. Now here is something that we're gonna learn, something called as slices. So let me first go ahead and select this particular pivot table and remove the filter from you. And now let me just go ahead and add slices. Or you know, let's understand what a slices. So what I'm gonna do is I'm going to select this pivot table over here, and I will click on Insert. And over here I have this particular option called us license. Now let me just go ahead and click on that. Now when I click on that, it asks me on what particular criteria do I want to set my slices over you. So let me go ahead and select location. In fact, I can select anything or any criteria on the basis of which I would like to do the filtration. But right now I'm going to select location. And let's see what happens when I click on okay. A small window popped up a video. And it shows me all the different cities that I have over you. Now, I can go ahead and click on any of the cities. And you will observe that it only shows me the data for that particular CTO. I can go ahead and click on this particular option that says multi-select. And then I can go ahead and select multiple options also. So I can see the data of Kolkata and Mumbai together. Let me just add Delhi to it, and it will go ahead and show me the data for all the three cities. And in case I want to go ahead and clear my filters, I can go ahead and click on this particular option that says Clear Filter. Once I do that, it will just go ahead and clear all the filters. And you will observe that it is now showing me the data for all the cities cumulatively. Now let's say I want to go ahead and apply this same filter on this particular PivotTable as well. But when I'm go ahead and make any changes, you will see that the changes only happen in this particular pivot table, but no changes are happening or where you. Now, let me just show you a small trick wherein I can go ahead and link multiple pivot tables with one set of slices. So what I'm gonna do is first I will go ahead and clear all the filters. I would just right-click on my slices. Over here. We go to this particular option that says report connections. Now we did showing me that my slices are only connected with PivotTable three. So what is Pivot Table three? Let's first understand that. If I click on this particular pivot table over here, and I click on the Analyze tab. It says the name of my Pivot Tables. Pivot Table four. Let me just give it a particular name. So let me just call it revenue PivotTable or let's just call it revenue dividend. Okay? And over Europe, and I click on this particular pivot table. It says the name of my Pivot Table Is Pivot Table three. So let me just call this Pivot Table As category period. So this is how you can actually go ahead and name your pivot table. Because sometimes while creating dashboards, you might be dealing with multiple pivot tables and having a specific name for your pivot tables can really make your life easy. So over here, I have named this particular PivotTable as category pivot. And this particular pivot table has been named as revenue pivot. And now what I can do is I can just right-click on this license over here. And then over here, I can go ahead and click on this particular option that's is a report connection. And when I click on report connection, you will realize that this particular slicer is currently only linked to it, category pivot, which is this pivot table. And therefore, when I click on it, any changes only happen in this pivot table and naught in this vivid to. But what I can do now is I can go ahead and put a checkmark over here. And now this particular slicer is connected with both the pivot tables. And let's see what happens now. So I'm going to click on OK. And now if I go ahead and click on any of the options over here, you will observe that the changes are happening in both my pivot table. Now just to let you know that there are various styles that you can pick up from so that it matches with the theme of your pivot table or with your dashboard. So what I can do is I can just select the slicer over here, go to the Options tab, and over here I can select a design of my choice. So right now, I can go ahead and select this particular design, or maybe this particular design or this one. So select the design of your choice. But the ultimate objective of any slicer or any filter. Go ahead and filter the data based upon certain criterias. So that you are only looking at the output that you are only interested in looking at. So that's a very quick lecture on data filtering using pivot tables. In our next set of lectures, we'll learn a few more things about pivot tables. So I hope you enjoyed this one and I shall see you there.
5. Lesson 5 GETPIVOTDATA: Hey, welcome back. So in our last lecture, we got introduced to the concepts of the pivot table. And then we also learned how do we go ahead and incorporate slices in a pivot table. So in this particular lecture, our focus is going to be on understanding how do we go ahead and retrieve some specific information out of our pivot table. So what do I mean by that? So let's say I have this entire pivot table in front of me. And out of this n-type to the table, I'm only interested in looking at specific data. So let's say I'm interested in only looking at the total revenue that I have gone to. My c is I'm only interested in looking at the total items that are sold and the total profit that I have learned from my big recedes. Now, to understand the concept better, I have gone ahead and added one more slicer away you. And that is the category Slicer life. I just go ahead and right-click on my slicer Rovio and click on this particular option that seems report connections. You will observe that I have gone ahead and established a connection between this particular slicer. And both might pivot tables over here. Okay, so that is a checkmark on both the options over here. And if I go ahead and click on OK, I can now go ahead and start retrieving the specific information that I'm looking on far from my pivot tables over you. So let's first of all look at the total profit that I have on from the beaker. Now, to get this particular information, it's pretty easy. All I have to do is I can go ahead and keep on equal two and inlet PivotTable. I know that this particular value is available in this particular cell or we, you. So I will just go ahead and click on it. And when I click on it, you will observe that a spawn formula has popped up in that particular cell that says GETPIVOTDATA, That is a bracket and then there is some information out there. So let's understand what does it say. So over here, what it says is pull up the information from my pivot data from the column which has a heading called sum of total profits. Then you will see that dollar f $1.3 sine over here. And what does that mean? Is that my pivot table actually starts from the cell F3. And then there is a comma and it says category. And there is a comment and it says bakery, which means it's asking Excel to pull up the information from the pivot table where the category matches the bakery and column which has a heading of sum of total profits. So let's go ahead and hit enter. And you will observe that the moment I did that, it has gone ahead and pull up that information. And that information is available in this particular cell over here. But now let's see where we can face a problem. Now for some reason, let's say if I go ahead and use this particular slicer, Rio, and I'm only interested in looking at the data for my snacks. So I will just go ahead and click on it. And what has happened now is because I have gone ahead and selected slacks in my filters. The data for bakery has just vanished. And because that has happened, now, this particular cell is giving a reference. Now, how do I fix that? Now there is a small tweak that I can use an Excel. Let's understand how do we go ahead and fix this issue. So first of all, what I will do is I will just remove the filters, a wheel. I will come down to this particular cell. And when I go to my formula bar away, yo, you will observe that the formula is available over you. I just need to go ahead and rectify this formula a little bit. So I will say is equal to if error. And then I didn't open brackets. Now what I'm telling Excel is effect all the value is present. Pull up the value from the reference cell. However, for any reason, if the value is not available over there, then alternatively returned the value as 0. And then I will close the brackets and I will hit enter. Okay, now let's go ahead and change the filters away and let's see what happens. So now if I click on snacks, you will observe that instead of giving me an error, it is returning the value as 0. Now, again, the value 0 is not right because the total profit that I've learned from my bakery is not 0. So what I can do this, I will just make a small modification to my GETPIVOTDATA over there. Once again, I will just go ahead and remove the filters from you. Come down to this particular cell over here. And instead of a 0, I will say double-quotes. I will use the minus sign and give a double good again. And now I will hit Enter. And now if I go ahead and change the filters, you will see that it is not returning any value. So it's much better than looking at a 0 which can sometimes misguide the person who's looking at that particular data. So now let's go ahead and remove all the filters. And let's look at another scenario where you're now the second thing that I need in this particular data where u is the total revenue, and then I need the total items that have been sold. So now I know that my total revenue is available in this particular pivot table, a wheel, and it is available in this particular cell. So let's use the get pivot function and tried to establish a relationship between this particular cell and this sense so that we get the specific value. So I will say equal to, and I will click on this particular cell. And it has gone ahead and filled up the formula by default. And just going to press enter, and I have the value over there. Now let's see what can happen if I go ahead and start using my slices. Like if I go ahead and click on any of the CDs over you, you will observe that my grand total keeps on changing and accordingly, the value in my total revenue also keeps on changing. Now, I don't want that to happen. What i want is, no matter what kind of filters are used over your, I want this value to remain bonds to it. So how can I achieve that? The best fixed for that is going ahead and creating a separate PivotTable. Let's go ahead and go back to our raw data. Were you I'm going to click on any of the cells over here. Click on Insert, go to Pivot Table. And I will go ahead and create this pivot table on a new worksheet. I can click on OK. And now I will go ahead and create a new pivot table over here. So first of all, I will select the CFS month. Then I want the total units sold. So I'm going to select this particular option over here. It says units sold. And then I will select the total revenue because these are the two values that I'm interested in. And now I have gone ahead and created a separate PivotTable. And now I will go back to my sheet over here. And this time I will delete this particular value. And now I will give a reference. Now before I go ahead and give any kind of a reference, the best practice is to go ahead and name our pivot table. So I'm going to select this particular pivot table, go to the Analyze tab. And I will say this is a reference pivot table, so I will just name it as reference period. Ok. And then I will come back to my sheet OEO. I will say equal to and go back to this particular sheet and give this particular value. So now it has gone ahead and picked up the total revenue. I will also go ahead and give the value for the total items sold. So I will say equal to go back to my sheet, select this particular value and hit enter. And now let's see if a tall I go ahead and change the filter, what happens? So I am changing the filters and you will observe that my grand total is changing. However, this particular value over a year, the total revenue and total items sold remains constant. So that's how we go ahead and make use of the get pivot function to pull up any specific data from a pivot table. And we can only go ahead and look at the data that we're interested in looking at. That was the shot somebody about GETPIVOTDATA. I hope you enjoyed this lecture. I shall see you in the next one.
6. Lesson 6 Pivot Charts: Hey, welcome back. So the last thing that we will learn in this particular lecture series on pivot table is how do we go ahead and add charts, PivotTables. And before we go ahead and do that, let's do a small recap of what we learned in the previous lectures. So I'm going to select this particular data we, you and me do some conditional formatting to it. So I will add some data bars to it. I will select this particular data. And then I will go ahead and add another set of data bars to it. Let me just go ahead and resize this a little bit. So my conditional formatting is done. Now let me just go ahead and add some license to it. So I will select this pivot table, go to the Insert tab, click on slicers and let me just add the location slicer and let me just add the categories over here. And we click on OK. Let me just resize this a little bit so that it fits when. So I will just select this, I will resize this and I will set distinguishable you. And I will just resize this as width. Let me just go ahead and do some kind of a formatting with this. So I will select this, and I would select this particular format. And we, yo, I will select this and applied this pediculus Tyler. Okay, so I have my data ready and now let's quickly go ahead and add some charge to this. So let's say I want a bar graph that shows the CSS of my B agree as well as my snacks or the period of the last 12 months. So what I can do is I can just select this particular pivot table. Would do insert a wheel. I see this pretty good option called Pivot Chart. I would click on it. And when I click on pivot chart, we would judge suggests me a particular chart type based upon the type of data that I have. So I will go with whatever it suggests. I will just select the clustered column and I will click on, okay, so with that might chapters ready, I can go ahead and do some kind of a manual formatting to it. I can remove this particular grid lines or wheel. And then probably eigenstates remove this particular axis. And if at all I want, I can go ahead and change the condos. But rather than doing all this, I prefer going ahead and selecting a ready-made design so I can just always go ahead and select my job. Go to the Design tab and select the design of my choice. So let me just select this one. And the way you are, let's say I don't want to go ahead and have the data labels. So what I can do is I can just select the data labels and I can delete it. And now because I have gone ahead and created a vivid shot, I also have all those away you. But when you look at the chart, they seem to look a little uglier and I don't want to have them on my Jada. We you. So what I can do is I can select the chow. I can go to the Analyze tab. And the way I see this particular option that sees field buttons. So I will click on high HDL and that will hide all the buttons that I have on my chart. Because whatever I can achieve to these buttons, I can always go ahead and do that to my slices as well. Now let's say I only want to see the data for my bakery. So if I click on B3, you will observe that it's only showing me the data on a degree away. Or let's say I'm only interested in looking at the data from V3 for Bangalore city. And if I click on that, you will observe that now it is only showing me the data for Bangalore city and quanta category bakery. So integrate this as a kind of a dynamic chat because as and when you make any changes to your pivot table, that job will change accordingly. Now let me just go ahead and add one more chart. We'll, so what I will do is I will just resize this chart a little bit and over you or let me just go ahead and select this particular data. Go to the insert tab, go to Pivot Tables, and click on pivot chart. And let me just go ahead and draw a line chart this time. And it will automatically suggest to me a charge. So let me just select this particular chart, the wheel. And I will go ahead and play somewhere where you, and let me just go ahead and select a ready-made format for this chart as well. So I'm going to select this gel. Click on Design tab, and let me just select this particular format, a wheel. And once again, what I can do is I can remove these buttons that are there. So I will just select this particular chart. Click on Analyze, go to feed buttons, and then I will click on high dot. Now for both the charts, I will also remove these legends and whatever formatting and doing away your, it is completely up to you. You can go ahead and format the Tad Asbury Ostian and as per your requirement, let's say I don't want the liquids away as I'm going to delete them. And I will delete the labels year as well. Keep the neighbors of V0. And let's say I want to add a few features to my chart. So let's say I want to go ahead and add the Bitly to my chart. So I will say chart title and let's say this is the revenue chart. So I'm going to label this as revenue jump, and we'll let me just go ahead and label this particular charge. So let me just give it a chat item and let me just call this B3 versus snacks job. Just in case I want to go ahead and change the design or select another color pattern. I can always select the chart, go to the design tab and we, oh, I have this particular option that sees change colors. I can select the color pattern of my choice over here. Similarly, I can go ahead and make the changes to this, but he gonna chat as well. I can come down to this change colors option and the color pallet off my toys over here. So let's select this one. Now let me just go ahead and check my child. So eo, so if I click on any of the filters, a wheel, you will see that my chalk will change accordingly. So in a way, it is the only dynamic gifts. I only wanted to look at the data for my snacks. I can do that. I can also go ahead and play with the chart elements as an when I want to let say I want the data labels over here so I can click on this chart elements, the Candida labels, Selected Data Labels, and then I can just change the colors. So in a way I can just go ahead and play around with my chart over you. And it is the only dynamic because it's the only dependent upon your pivot table over here. So you can actually go around and play with your charts and give them a kind of a format that you need as per the team and design of your dashboards. So the objective of this particular lecture was to go ahead and give you a small hands-on experience in terms of how you can go ahead and create a quick pivot chart using your pivot tables. So I hope you enjoyed this lecture and I shall see you in the next one.
7. Lesson 7 Why PowerPivot?: Hey, welcome back. So I'm assuming that by now you must be very comfortable with the concept of pivot tables. And pivot tables have been into existence since 1993. But now we're going to move a little bit further and we're going to get introduced to the concept of Power Pivot. And power pivot is one of the most powerful tools in Microsoft Excel. But before we go ahead and start learning PowerPivot, I want you to understand what is the need of going ahead and learning PowerPivot in first place? Now the first answer to that is that pivot table has its own limitations. Let's understand those first. Now, Excel itself is actually a very big culprit since its own limitations, because it has only ten lac, 48,576 rows, which means you cannot analyze any data which has more than that many rows. And this may seem like a lot, but when you are working with large databases, this number can just be a miniscule number. Secondly, doing your analysis on two data sources at the same time is virtually impossible. Because when it comes to pivot table, we're only working on one source of data and creating pivot tables out of that. However, if at all your data comes from more than one source, then working with pivot tables is not possible. And if at all you go ahead and keep on adding large amount of data to your pivot table, you will observe that gradually your Excel workbook will just begin to work very slow. In a way I can say that it would just begin to crawl. So now let's focus on the need for going ahead and learning power pivot. Now PowerPivot is an Excel add-in that allows users to put in large volumes of data from multiple sources and creating relationships. And the data generated can then be analyzed and worked with efficiently. Let's look at the example that we have on our screen over here. Now what I have over your Is three different sets of data. Ok. First is the customer info, then I have the order info, and then I have the third dataset, which is my product info. Over here, I have the list of all the customers that I have on in my database, in my ordering for I have the list of all the transactions that have taken place with these customers. And each of these customers have purchased one of the product that is available in my product list. Now, each product over here has a certain price. Now using the concept of pivot table, if at all, I tried to go ahead and create a customer buys a revenue report wherein I want to know how much revenue each of the customer has generated for me. Now, that is practically going to be impossible using pivot tables. Because each of this data is available in three different datasets. And these datasets are not related to each other. And this is where power pivot with come handy. In power pivot, we would go ahead and create something called as the data models. And then we would work with the data models in order to go ahead and create our pivot tables. And what exactly is a data model? A model is nothing but a list, a list of data. But through a data model, we can set up multiple list. And that is something that we will learn in the next set of lectures that will follow. Secondly, PowerPivot is an underlying reporting engine that enables Power BI and modern Excel to delivery those modern funky reports that can help you succeed in a business. In a way, what you can do is you can go ahead and combine multiple datasets, create a data model, and build up data reports out of that. That will give you a lot of business intelligence. Powerpivot is a crucial tool for users, especially those in the business world. And it enables users to input data, create relationships, make changes quickly, and provide company management with clear and up to the moment analysis. So if at all I have to go ahead and summarize. Powerpivot is an Excel add-in that enables you to prepare your data, merger data from multiple sources and work with big data. But when it's ready, this data will be analyzed by a PowerPivot in Excel. So let's go ahead and let's try out Power Pivot. And let's see how power period works inside this course, o year. Let's try it out.
8. Lesson 8 Powerpivot Addin : Hey, welcome back. Now before you can start using the Microsoft Excel add-in PowerPivot, you've gotta activated inside of your version of Microsoft Excel. And to do that, it's very simple. So if you look at my screen over here, I have already gone ahead and activated the PowerPivot added in my Excel. And if at all in your version of Excel you cannot see it in these particular tabs over Europe. Then probably you need to go ahead and just activated. And how do you do that? It's very simple. Just right-click on any of the tabs that you have on your screen, and then click on Customize the Ribbon option. Then you will come down to this particular tab over here, which says add-ins. And over here, if you come to the bottom of the screen, there is an option that says manage add-ins. So what I'm gonna do is I'm going to click on this drop-down over here. And I will click on COM Add-Ins over here. And then I will click on Go. And when I do that, you will observe that a COM Add-ins window would pop up on my screen. Now, all I need to do over here is go ahead and put a checkmark on this particular option that says Microsoft Power Pivot for Excel. In your version of Excel, it might be deactivated. So it does not have a checkmark on it just to ensure that you put a checkmark on it and then click on, okay. And when you do that, you will observe that the PowerPivot button will appear on your ribbon. And with that, we have gone ahead and activated the PowerPivot add-in in Microsoft Excel. Let's go ahead, right-click on any of the tabs. Bow to the Customize Ribbon option. Go to the Add-ins, come down to the manage adding section. Click on COM Add-ins, click on Go and make sure you go ahead and put a checkmark on Microsoft Power Pivot for Excel, and then click on OK, and we're good to go. And in the next lecture we will learn how do we go ahead and use the PowerPivot to build our data models and then build relationship amongst the same.
9. Lesson 9 Data Models : Hey, welcome back. Now before we go ahead and start taking advantage of the PowerPivot tool in Excel, we need to go ahead and build something that we refer to as the data models. And what is a data model? As I mentioned earlier, data model is nothing but a list, a list of data. That's all it is. So let us go ahead and understand how do we go ahead and create a data model that contains these three lists that we have. A warrior, the customer info, the order NFO, and the product info. And once we have gone ahead and created these data models, we will go ahead and build a relationship between them. So let's get started. So before we go ahead and start constructing a data model, the first thing that we have to do is convert our data into tables. So first I will go to my customer info tab over here, and I will click on any of the cells within the data that I have. And then I will go to the Home tab. And then I will come down to this particular tab that says Format as Table. And I will click on that. And then I will select a particular design of my choice over here. Okay? And then I will go ahead and click on OK. And now once I have gone ahead and created this table, I will go to the design tab, and then I will go ahead and name my table. So I am going to call this table as cost info. And now I will go to my order info tab. Click on any of the cells within the data that I have over here. And once again, on my home tab, I will go to this particular button that says Format as Table. And I will go ahead and select a design. And then I will go ahead and click on OK. Just make sure that when you go ahead and create a table, put a checkmark on this particular option that says my table has headers. Because in this particular case my data actually has headers. Okay, so I will go ahead and click on OK. And this particular table, once again, I will go to the design tab and name it as ordering food. Okay? And now what I will do is I will go to my product in pushy to, we'll click on any of the cells, go to my home tab, click on Format as Table. And I will convert this data into a table. And then I will click on OK. And this particular table, I will go ahead and name it as product info. Ok. And now what I will do is I will start constructing my data models. So I will go to the Customer Info tab over here. Click on any of the cells within my table. I'm gonna go ahead and click on the Power Pivot tab. We'll come to this particular button that says Add to Data Model. And what I'm gonna do is I'm going to click on this particular button over here. And let's see what happens. And you will observe that the moment I did that, it opened up a completely separate window from exit. And what you see on your screen right now is my PowerPivot for Excel two. And if you look at this tool very closely, you will realize that it has its own ribbon with its own tools. And I have got a duplicate list of my entire Customer Info table, which is available back there in my Excel data. Another thing that you need to observe very closely over a year. Is whatever the name that I have given to my table, it has gone ahead and picked up that name by default over you. So oh, yeah, it has gone ahead and picked up the name as customer info. Now, with that, I have gone ahead and created a data model for my customer info Data list. Now similarly, I need to go ahead and create a data model for the remaining two lists that we have. So let's quickly go ahead and create them. I will go ahead and close this particular window. And you will observe that the moment I did that, I come back to my Excel environment, I will go to my order info sheet over you. Click on any of the cells within the table. And then I will go ahead and click on Add to Data Model. And you will observe that immediately a second list has been populated in my PowerPivot environment. So just next to the Customer Info, now I also have my order info. Let's quickly go ahead and create our third list as well. So let's close this. Let's come down to the product info sheet over here. Let's click on any of the cells within the sheet. And then let's click on Add to Data Model. And you will observe that immediately even my third list has been added to the PowerPivot tool. So in a way now, I have three different sets of data, three different lists, but all a part of my one data model. So in a way, all these three lists will comprise of my one single data model. And if at all, I need to go ahead and see that. All I need to do over here is in the same environment. If I come towards the right-hand side of my screen, I see this particular button that says Diagram View. If I click on that, you will observe that all my three lists, which are a part of my single data model over you, are available over u. Ok, and now before I go ahead and start creating any sort of reports using this particular data model, I need to go ahead and build a relationship within my data model. So let's understand how do we go ahead and build a relationship. So what I will do is I will go ahead and minimize the screen over you. And I'm back to my customer and push you to where you. Now if you observe this particular column over u, it has something called as the customer ID. Okay? And then similarly, if at all i go ahead and click on the order info. Were you next to every order? That is a customer ID. So if at all, I want to go ahead and build a relationship between the customer info sheet and the order info sheet. The common link that I have over here is the customer ID. For example, in my customer info section, I have this ID called CALEA II 1234. And if at all i go to my ordering for sheet and I run a filter on my customer ID. And let's say I type CALEA II 1-2-3 for over a year. You will observe that these many orders were placed by this particular customer. Who has the Customer ID? C l 1234. And the name of the customer is clear. Good. So in a way, what I'm trying to explain to you over year is the customer info sheet is linked to the order info sheet using a common link, that is the customer ID. And if at all, i go ahead and look at the orders that I have over you. You will observe that just next to every order there is also a product ID. And then if I move to my product info sheet, you will observe that the same product IDs are available in the product ID section, the product info sheet. So in a way, the order info sheet is linked to the product info sheet using a common link, and that is the product ID. For example, if at all i go ahead and look at this particular product over here, which has the product ID 1-0-0 00 for 5-4. And I go to my product info section and I look at this particular product, it falls under the category of furniture, the subcategory of bookcases, and the name of the product is Bush Somerset collection bookcase and there is a price associated with that. So to summarize it, the customer info sheet and the order info sheet has a common factor, that is the customer ID. And the order info sheet. And the product info sheet has a common factor, that is the product ID. And I'm going to make use of these common factors to go ahead and build a relationship in my data models. So let's move to our data models and now let's go ahead and build a relationship amongst them. So I will come down to my data model sheet over you, and now I will start building a relationship within my data model. So I'm gonna go ahead and link this customer ID to the Customer ID that I see over here. Okay? And the moment I did that, you will see that the link has been established. So in a way, a relationship has been established between this particular list and this particular list based upon the common factor, that is the customer ID. And when we spoke about the order info and the product in full list, the common factor amongst both of them was the product ID. So what I'm gonna do is I'm going to click on the Product ID over here. And then I'm going to drag this particular arrow and link this to the product ID in the product in posts section. So with that, what I have done is I have gone ahead and created not only my data model, but I've also gone ahead and established a relationship between the lists that I have within my data model. So that's how you go ahead and construct a data model which comprises of multiple lists. And then based upon the common factors within these particular list, you go ahead and create a relationship between them. So I hope you enjoyed this lecture and I shall see you in the next one.
10. Lesson 10 Pivot Table with Powerpivot : Hey, welcome back. So in our last lecture, we went ahead and created a data model. And then we went ahead and created relationship between the lists that we have within our data model. And now what we can do is we can go ahead and create a pivot table based off a newly-created data model. So let's get started. So on this screen over here where I have my data model and I can see the relationships. I have this particular tab called Does it pivot table. So I don't even need to go back to my Excel sheet. I can start constructing my pivot table year itself. So let's go ahead and click on the Pivot Table Tab over you. And when I click on the drop-down over here, I can see this particular option that says Pivot Table. And over here I'm going to select this particular option that says new worksheet, and I will click on OK. And now you will observe that a pivot table layout has been created. But if you look at the pivot table fields over your, you will realize that there are three different tables that are listed out here. So if I click on my customer info, I can see all the fields that are associated with my customer input table. Similarly, if I click on Order info, I can see all the fields over there. And then if I go ahead and drill down on the product info table, I can see that all the fields are also available over there. So now what I can do is I can go ahead and construct a pivot table out of this. So let's say I want to know what is the order quantity placed by each of the customer, and what is the revenue that has been generated through sales for each of the customer? So what I can do is I can go down to my first table over here, which is the Customer Info table. And what I will do is I will select the Customer Name and place it in the row section over you. And now what I need is the total number of orders placed by each of the customer. So for that, what I will do is I will scroll down and come down to my order Info section. And then I will go ahead and take a count of the order ID. Okay. So the moment I did that, you can see that just next to each of the customer, I can see the total number of orders that have been pleased by each of the customer. And now I'm interested in knowing how much revenue each of the customer has generated for me. So for that, what I will do is I will select this particular field called sales, and I will drag it to my value section over u, OK. And the moment I did that, my pivot table is ready. I can see that I have the name of the customer. Just next to each of the customer. I have the total number of orders that have been placed by each one of the customer. And then I also have the total revenue that I've managed to generate to sales for each of this particular customer. So what I have done is I have gone ahead and made use of my data model and linked all the three lists together. And now based upon the relationship that I have built, I can go ahead and construct a pivot table and any sort of report that I want to generate out of this particular data. And now if I go back to my PowerPivot environment and I go ahead and click on the drop-down over here. I can see that I can also go ahead and create a pivot chart, or horizontal and vertical charts out of this particular data. So it is as simple as that. So you must have realized that PowerPivot is a very simple tool, but at the same time, a very powerful tool that we can use to grab datasets or list whether they are from Excel or from any other source of data. We can not only go ahead and create relationships between them, but we can also then go ahead and create pivot tables out of them. So try this out, get your data model going, build a relationship between them. Launched the pivot table tool and try to create your first pivot table out of that. So I hope you enjoyed this lecture and I shall see you in the next one.
11. Lesson 11 Calculated Fields and KPI Skillshare: Hey, welcome back. So in our previous lectures, we understood how do we go ahead and create data models? How do we create lists and datasets within our data models? How do we build relationship between them? And then finally, how do we go ahead and make use of these data models to go ahead and create our pivot tables and pivot charts. Now our focus in this particular lecture will be on understanding how do we go ahead and create calculations within a data model environment. And then using those calculations, how do we go ahead and incorporate KPIs in our reports? So let's understand that with an example that we have over you. So before we proceed, what I'm gonna do over here is I'm gonna go ahead and select this pivot table. And I'm just going to convert this sum of sales into average of sales. So I will go ahead and select the sum of Sales field over here. And then I will click on this option that says Value Field Settings. And what I'm gonna do is I'm gonna go ahead and convert this into an average. We'll click on average, and I will click on OK, OK. And I will also go ahead and arrange the decimal places over here. So what I'm gonna do is I'm going to select this particular column over here, right click on it, go to Format Cells. Click on number. And I will set the decimal places to two decimal places. Okay? And now what I will do is I will go back to my data model environment. And to do that, all I have to do is I have to go ahead and click on this button called manager. You're. And when I click on it, it takes me back to my data model environment or my PowerPivot environment. Now, I'm on the order in full list over you. And I went to go ahead and create some calculated fields over you. So creating calculated field is a very easy job when it comes to Power Pivot. So let's say I want to go ahead and create an average of the sales figures, the rest CEO. So all I need to do is just click on any of the cells within the Sales column. Then click on the Home tab. And then I will come down to this button over your call autosome. Okay, it's available in the calculation sections over here. And I will click on the drop-down over here. And I can see that there are a lot of options that are available for me away or these are already made options. So if at all, i go ahead and click on the average, let's see what happens. And when I click on average, you will observe that immediately at the bottom of the sales list, I have the average figures which have been automatically generated by exit for me. Similarly, I can go ahead and use other auto functions like count, distinct, count, max, min, etc. For example, I want to know what is the average quantity ordered in this particular list over you. So what I can do is I can click on any of the cells within the Quantity column over here. Once again, go to my autosome. Click on average over u. Ok. And if I just go ahead and enlarge this a little bit, I can see that my average quantity is 4.0 one. Similarly, if at all I want to know what is the distinct count of the different shipping costs that are involved in each of the orders. What I can do is I can select any of the cells over you, go to autosome. Then I click on distinct count over here. If I just go ahead and enlarge this, you can see that the distinct count of shipping cost is three. So either it's $5.3 dollars or $8. Okay. Nothing other than that. And that's why the distinct count that I get in this particular report is three. So that is how I can go ahead and create calculated fields in my PowerPivot environment. Now one of the advantages of going ahead and creating calculated fields is that I can go ahead and create KPIs. So what exactly are KP is? So let's understand that to an example over you. So my average sales that I have got through this particular report is $241.25. So let's go ahead and create a KPI based on our average sales. So what I'm gonna do is I'm going to right click on this cell over here, and I'm going to click on create KPI. Okay? And now what I want to do over you is since my average sales is $241, okay? If at all my sales figures are close to 90% of my average, I want them to be shown with the green shade. Anything that is between 80 to 90% off my average GPA should be shown with a yellow sheet. Okay, so I'm going to bring it down to 80%. And anything below 80% of the average should be reflected with the red sheet. Okay, so what I'm gonna do is I'm gonna go ahead and set these thresholds over you. And now what I'm going to do is I will go to this particular button that says absolute value. And I'm gonna go ahead and input this value over there. So the value is 241.25. Ok? And now I will go ahead and click on OK. The moment I did that, you will observe that a small KPI icon has appeared over u. I can always go ahead and edit my KPIs by right-clicking on them and clicking on edit KPIs settings. And if at all I want to make any changes to my KPI, I can once again go ahead and make any changes over here. Right now. I am not interested in making any changes over here, so I'm gonna go ahead and click on OK. And now what I want is D's, KPI's should reflect accordingly in my Pivot Table or any of the reports that I generate. So what I'm gonna do is I will once again minimizes the screen and I will come down to my pivot table over you. And now if I go ahead and click on this order Info table over here and scroll down, you will observe that some new fields have appeared over you. And along with that, I also see another field called average of sales, which has a small icon of the KPIs next to it. What I'm gonna do is I'm going to click on this arrow next to it. And then I'm gonna go ahead and put a check mark on this particular box that says Status. And the moment I did that, you will observe that just next to my average of sales figures, the KPI icons have appeared. And the KPIs respond to the value of the average of sales for each of the customers over there. So if it is above 90% of the average, it is shown as green. Between 80 to 90% of the average, it is shown in yellow. And anything which is below than 80% of the average is reflected as red. Now how can these KPIs help us? So we can be happy about the customers for whom our average C is, our showing in green. For customers who are showing in yellow, we need to go ahead and put some sales efforts so that they can be pushed into the green zone. Whereas for the customers for whom the average is in the red zone, we need to get in touch with these customers and, and find out ways and means on how we can go ahead and engage these customers so that they can purchase products from us of higher values. So basically, KPI will give us an indication in terms of the kind of action that we need to take based upon the signals that it is showing up in the report. So go ahead, try this out. Jump back into your data model. Tried to create some calculated field, and then tried to incorporate IT KPIs using these calculated fields. And then tried to incorporate these KPIs into your pivot tables. What threshold you want to go ahead and set in your reports. I leave that completely to you. You can set the report as 708090 or 809095 based upon your requirement and the kind of benchmark that you want to set for the different KPIs that you have within your organization. So go ahead, try it out. And with that, we have come to the end of the lecture series on Power Pivot. And by now, I'm sure you must have realized that PowerPivot is a very easy to use tool, but at the same time, it is very powerful. You can go ahead and extract data from various sources, put them into a Dataset, Create a data model out of it, builds a relationship between these datasets and then finally convert them into meaningful reports, calculations, and KPIs. So I hope you enjoyed this entire lecture series and I'm really happy on your course completion. Take very good care of yourself. Happy learning and stabilised.
The Guruskool
