1 00:00:00,180 --> 00:00:06,800 Let's take a look at how we can add calculations and multiple pivot table reports to our spreadsheets. 2 00:00:06,810 --> 00:00:13,510 We also want to answer this question: Which customer has the highest percentage of total sales? 3 00:00:13,690 --> 00:00:18,360 I already reverted the changes I made to the data in the previous lecture. 4 00:00:18,360 --> 00:00:25,580 So our data is back to what it was originally. To be able to get the answer to our second question here. 5 00:00:25,680 --> 00:00:32,070 What I want to do is to create a separate report that is going to be right here. 6 00:00:32,070 --> 00:00:38,220 The easiest way to get this done is to copy your existing pivot table and paste it again. 7 00:00:38,220 --> 00:00:43,250 This keeps the design adjustments that you've already applied to your pivot table. 8 00:00:43,330 --> 00:00:50,050 Now to copy this, all you have to do is copy the entire pivot table by just selecting it. Or, click 9 00:00:50,050 --> 00:00:59,140 in one cell, go to analyze. Go to select, entire pivot table, then press Control+C. Go to the cell you 10 00:00:59,140 --> 00:01:06,670 want to have your second pivot table and Control+v. Now you can make adjustments to this. 11 00:01:06,740 --> 00:01:13,550 In the second report, we just want the customer information and we want to see the percentage of total 12 00:01:13,550 --> 00:01:14,000 sales. 13 00:01:14,030 --> 00:01:16,940 This involves a calculation. 14 00:01:16,940 --> 00:01:18,610 First let's deal with the customer. 15 00:01:18,620 --> 00:01:20,400 We don't want region or article. 16 00:01:20,410 --> 00:01:24,050 So let's kick them out by just dragging and dropping them. 17 00:01:24,110 --> 00:01:29,570 I want the customer names. Let's place a check mark beside it. That automatically adds the customer 18 00:01:29,570 --> 00:01:34,670 here and we have our sales value by customer. 19 00:01:34,670 --> 00:01:37,160 But that's not the analysis we want. 20 00:01:37,160 --> 00:01:43,430 We want the percentage of total sales. So we want to get this as a percentage of that grand total right here. 21 00:01:43,430 --> 00:01:47,630 This is the other advantage of pivot tables. 22 00:01:47,820 --> 00:01:52,650 You have the ability to do calculations without writing any formula. 23 00:01:52,650 --> 00:01:59,970 All you have to do is right mouse click, go to show values as, and select the option you need. 24 00:01:59,970 --> 00:02:04,470 In this case we want to get each number as a percentage of grand total. 25 00:02:04,470 --> 00:02:10,720 I'm just gonna go with that. And I get my values. To get this in order, 26 00:02:10,720 --> 00:02:17,770 I'm going to sort this. So right mouse click, sort, sort largest to smallest. The customer that accounts for 27 00:02:17,770 --> 00:02:22,910 the most sales in percentage wise is Liebher. 28 00:02:22,960 --> 00:02:26,710 Now you may be asking: What if you want to see both values? 29 00:02:26,720 --> 00:02:34,550 What if you want to see the sales in USD but also the sales in percentage in the same report. 30 00:02:34,570 --> 00:02:41,950 The trick is to add that category two times to your reports. So you are going to drag it again in there 31 00:02:42,670 --> 00:02:46,840 and drop it and you get to add your second view in here. 32 00:02:46,870 --> 00:02:52,870 You could also show this as something else. Or, you just keep the values like we have here. 33 00:02:52,870 --> 00:02:58,960 Now if you want to change the order of this, we can change it in our field list here. Or, we can just do 34 00:02:58,960 --> 00:03:05,980 it with drag and drop. Just highlight the column and then drag it to the place you want and then drop it. 35 00:03:05,980 --> 00:03:14,830 Now this one is just sales USD, space, enter and you can adjust the formatting as you like. 36 00:03:18,130 --> 00:03:24,310 Now for this report it would also be good to add the percentage for each article but instead of getting 37 00:03:24,310 --> 00:03:27,000 the percentage of the grand total. 38 00:03:27,190 --> 00:03:35,350 Let's get the percentage for each region. This way we can see the sales proportion for each article in the 39 00:03:35,350 --> 00:03:36,480 region. 40 00:03:36,570 --> 00:03:45,260 To do that we have to add our sales USD gain to the report. Right mouse click. Which one do we need to 41 00:03:45,260 --> 00:03:50,300 select here? Show values as and instead of grand total 42 00:03:50,360 --> 00:03:55,680 I'm gonna go with parent total. It wants to know what is the base field. 43 00:03:55,730 --> 00:03:57,360 So in this case it is region. 44 00:03:57,380 --> 00:04:01,370 If it's something else, you can select it from this drop down and then click on ok. 45 00:04:01,380 --> 00:04:09,470 Now I have the sales percentage of each article in that region. 46 00:04:09,470 --> 00:04:17,510 Our diamond smartphone case accounts for 38.4 percent of sales in Europe and the 47 00:04:17,540 --> 00:04:23,840 black simple T-shirt accounts for 32.6 percent of the sales in America. 48 00:04:23,840 --> 00:04:29,440 Before we end this lecture, let's check what happens if new data is added to our raw data table. 49 00:04:29,480 --> 00:04:35,990 Do we have to refresh each pivot table separately or are they automatically going to refresh if one 50 00:04:35,990 --> 00:04:38,060 of them is refreshed? 51 00:04:38,060 --> 00:04:43,260 Let's go to our raw data table and add some dummy data to this. 52 00:04:43,280 --> 00:04:51,620 I'm going to add a new region for Asia and then just copy and paste these. Just change the customer 53 00:04:51,620 --> 00:04:53,620 name to Aida Asia 54 00:04:53,720 --> 00:04:55,920 and press enter. 55 00:04:56,150 --> 00:05:00,200 Okay so let's go back to our dynamic pivot table report. 56 00:05:00,200 --> 00:05:02,390 We only have Europe and America here. 57 00:05:02,390 --> 00:05:05,090 We don't have the new customer in here. 58 00:05:05,090 --> 00:05:10,510 What I'm going to do is for this report here, to right mouse click and refresh it. 59 00:05:11,630 --> 00:05:17,180 I got my new customer added to my second pivot table that I didn't refresh. 60 00:05:17,180 --> 00:05:23,720 So not only did it show up here but also in the other pivot tables. Which means that the moment you refresh 61 00:05:23,870 --> 00:05:29,780 any of your pivot tables, all the other ones will automatically get refreshed as well. Because they're 62 00:05:29,870 --> 00:05:37,070 ultimately using the same pivot cache. Ok so that's how you can add calculations and multiple pivot 63 00:05:37,070 --> 00:05:38,630 tables to your spreadsheet.