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.
