1 00:00:00,360 --> 00:00:06,090 I introduced measures briefly in our columns versus measures less than a few videos ago, and we spent 2 00:00:06,090 --> 00:00:12,930 the last few lessons working strictly with calculated columns, you can do a lot with calculated columns 3 00:00:12,930 --> 00:00:13,500 and dacs. 4 00:00:13,500 --> 00:00:17,930 However, eventually everything will lead up to the definition of a measure. 5 00:00:18,660 --> 00:00:22,110 We've already defined a few measures through the use of our pivot tables. 6 00:00:22,800 --> 00:00:28,200 Any time you drag a column into the value section of a pivot table, your pivot table will define a 7 00:00:28,200 --> 00:00:31,770 temporary measure based upon whatever function you've selected. 8 00:00:32,280 --> 00:00:38,460 So when we drag the employee wages to our value section and it calculated the sum, it created a measure 9 00:00:38,460 --> 00:00:40,410 called sum of employee wages. 10 00:00:41,940 --> 00:00:46,530 This is a temporary measure, to be sure, but it is a measure nonetheless, and it's based upon the 11 00:00:46,530 --> 00:00:47,400 employee wages. 12 00:00:47,400 --> 00:00:54,120 Calculated column measures represent aggregations or measurements of information contained within one 13 00:00:54,120 --> 00:00:58,410 or more columns, and they can be written in DACs just like any other formula. 14 00:00:58,950 --> 00:01:04,890 In fact, it's fairly common to create explicit measures for calculations such as are some of employee 15 00:01:04,890 --> 00:01:10,770 wages and then hide the original employee wages column to prevent users from creating inappropriate 16 00:01:10,770 --> 00:01:16,080 measures when they drag them to the value section where they pick, say, the counter average functions 17 00:01:16,260 --> 00:01:18,430 where the data isn't structured appropriately. 18 00:01:19,380 --> 00:01:24,690 With that in mind, let's update this pivot table to use proper measures rather than these temporary 19 00:01:24,690 --> 00:01:25,140 ones. 20 00:01:25,320 --> 00:01:29,490 I find measures are easier to create on the Excel side than the parapet side. 21 00:01:29,730 --> 00:01:35,310 By going to the parapet, reben go to the measures, dropdown and choosing the manage measures. 22 00:01:36,120 --> 00:01:37,530 I won't click on it just yet. 23 00:01:37,530 --> 00:01:42,240 And instead I'm going to show you how to create measures and the power pivot area really quickly and 24 00:01:42,240 --> 00:01:44,130 then we'll abandon that since it's terrible. 25 00:01:44,730 --> 00:01:50,310 So go ahead and open the data model with the manage button here and go to the purchase orders table. 26 00:01:50,760 --> 00:01:54,360 We're going to click on one of the cells in our calculation area at the bottom. 27 00:01:54,660 --> 00:02:00,960 And I'm going to type the formula, equal some purchase order when I do this and new measures added. 28 00:02:00,960 --> 00:02:03,480 And we can look at the formula bar to see the format. 29 00:02:03,930 --> 00:02:07,410 It says measure one colon equals our formula. 30 00:02:08,520 --> 00:02:10,610 Now, this is awful for a number of reasons. 31 00:02:10,920 --> 00:02:15,720 First of all, the only way to find this measure is to look at the purchase orders table and then look 32 00:02:15,720 --> 00:02:17,190 at our calculation section. 33 00:02:17,670 --> 00:02:21,170 If we go to another table, we wouldn't be able to see the measure anymore. 34 00:02:21,510 --> 00:02:27,630 Now we can rename it by modifying the formula, but that's kind of a pain two and it's prone to errors 35 00:02:27,630 --> 00:02:28,980 in case utility equals. 36 00:02:28,980 --> 00:02:31,230 And then you'll have to fix that as well. 37 00:02:32,110 --> 00:02:37,630 So I'll rename it and then we'll leave the measure here and pop out to ourselves, to the Excel managed 38 00:02:37,630 --> 00:02:43,210 measures view with all of the measures in our data model from top to bottom to allow for easy viewing, 39 00:02:43,360 --> 00:02:50,380 editing and deletion, we can easily select our existing measure and choose edit within the edit window. 40 00:02:50,570 --> 00:02:56,320 I can modify the table that the measure belongs to the measures, name the description, check our formula 41 00:02:56,440 --> 00:02:59,560 and define the category for some easy formatting options. 42 00:03:00,100 --> 00:03:05,560 To me, this window is far superior for managing measures in every way, overwriting them within the 43 00:03:05,560 --> 00:03:09,760 data model, which is the reverse of how the relationships management options are. 44 00:03:10,150 --> 00:03:15,610 We can change the measure name to purchase order total, change the category to currency, set the symbol 45 00:03:15,610 --> 00:03:20,200 and decimal places to the level that we want and I'll drop the decimal to zero and then press. 46 00:03:20,200 --> 00:03:27,010 OK, closing out of our management window, we can modify our pivot table under the purchase orders 47 00:03:27,010 --> 00:03:31,050 table is a new field with effects in front of it called Purchase Order Total. 48 00:03:31,690 --> 00:03:37,300 This indicates that it's a measure so it cannot be used as a filter arrow or a column item. 49 00:03:38,110 --> 00:03:43,120 I'm going to drag this next to our sum of purchase order and we can see from the table that they match. 50 00:03:43,570 --> 00:03:48,640 One major difference, though, is that the format has already been applied and there's no some of that's 51 00:03:48,640 --> 00:03:50,330 been added to the front of our field. 52 00:03:51,310 --> 00:03:56,140 Another difference can be seen if we click on the arrow next to it in our pivot table fields area and 53 00:03:56,140 --> 00:04:00,910 choose the value field settings here under the summarised values tab. 54 00:04:01,180 --> 00:04:03,550 All of the options are great out and disabled. 55 00:04:03,970 --> 00:04:08,440 This measure has been specified and so there's no aggregation formula selection to be made. 56 00:04:09,310 --> 00:04:14,710 As with the other fields, of course you can change the additional calculations on top to get the percentage 57 00:04:14,710 --> 00:04:18,190 of the row or the percentage of the column or a number of other features. 58 00:04:18,590 --> 00:04:23,650 For now, let's go and create our other two measures for sales and wages really quickly and then I'll 59 00:04:23,650 --> 00:04:25,150 discuss the profits measure. 60 00:04:25,360 --> 00:04:30,280 Using the measures dropdown, I can click on New Measure to jump right into creating a new measure. 61 00:04:30,280 --> 00:04:34,660 This time I'll select the sales table and call the measure sales total. 62 00:04:34,870 --> 00:04:39,970 Then in the formula section I'll type equal some sales, but I won't close the parentheses and I'll 63 00:04:39,970 --> 00:04:41,260 add an extra Eagleson. 64 00:04:43,050 --> 00:04:48,480 If I click on the check formula button to get a quick notification of the error, it's not particularly 65 00:04:48,480 --> 00:04:50,980 helpful, but it does provide some quick feedback. 66 00:04:51,300 --> 00:04:57,300 I'll go ahead and close the prince's, check the error again, remove the equal sign and then get a 67 00:04:57,300 --> 00:04:58,670 nice, clean notification. 68 00:04:59,370 --> 00:05:04,880 I'll set it as a currency with zero decimals and press, OK, and then pop back into my management area. 69 00:05:05,400 --> 00:05:09,420 I can see both of my measures and I'm going to go ahead and add the third and final one with the new 70 00:05:09,420 --> 00:05:12,900 button here, which takes us back to that same interface. 71 00:05:13,170 --> 00:05:16,800 I'll knock this one out quick so we can move on to our fourth and final measure. 72 00:05:17,820 --> 00:05:22,650 The last measure we want to calculate is the profit, but it's not based on any calculated columns. 73 00:05:23,010 --> 00:05:27,570 One of the really powerful features about measures is that they can calculate off of each other. 74 00:05:27,900 --> 00:05:32,200 So the profit is simply the sales minus the purchase order total, minus the wage total. 75 00:05:32,970 --> 00:05:37,140 Now I'm adding this to our labor table, but the table isn't really important for the measures. 76 00:05:37,440 --> 00:05:42,210 A common approach is to create a blank table, specifically to store your measure, since so many of 77 00:05:42,210 --> 00:05:44,670 them are independent of the data table supporting them. 78 00:05:46,190 --> 00:05:51,590 So let's go into our data model and create that empty table to do this, I'm going to copy a random 79 00:05:51,590 --> 00:05:52,670 cell and then paste it. 80 00:05:52,970 --> 00:05:55,400 I'm going to name this table, underscore measures. 81 00:05:55,430 --> 00:06:00,710 After doing this, I'll right click on this pointless column and choose hide from client tools. 82 00:06:01,680 --> 00:06:07,590 This is a common practice in Paraguay, and I like to use it in Excel too, and now I can close out 83 00:06:08,130 --> 00:06:08,940 back and Excel. 84 00:06:08,950 --> 00:06:13,590 I'm going to go into the managed majors section and edit each of the measures and assign them to that 85 00:06:13,590 --> 00:06:14,880 underscore measures table. 86 00:06:15,330 --> 00:06:16,910 Once I've done this, we're good to go. 87 00:06:17,250 --> 00:06:23,310 Our measures are easily accessible from this topmost section of our pivot tables field window and now 88 00:06:23,310 --> 00:06:26,340 I can reassign them within the pivot table itself. 89 00:06:27,540 --> 00:06:31,560 Once I've done this, I've rebuilt our report entirely using measures. 90 00:06:33,200 --> 00:06:38,660 This covers the last major feature of power pivot that will cover in this course from here on, we'll 91 00:06:38,660 --> 00:06:44,480 look at DACs and some patterns that we can use with DACS to create some more powerful and useful measures 92 00:06:44,690 --> 00:06:47,990 than simple sum or count calculations.