1
00:00:00,260 --> 00:00:05,850
For both of our power pivot reports, so far, we've relied strictly on excels, pivot table magic to

2
00:00:05,850 --> 00:00:07,930
construct our calculations for us.

3
00:00:08,400 --> 00:00:13,680
We haven't touched a single formula so far, which means that we haven't even begun to scratch the surface

4
00:00:13,680 --> 00:00:17,190
of what we can do before we write any formulas.

5
00:00:17,340 --> 00:00:20,430
You need to understand what types of formulas we will be writing.

6
00:00:21,000 --> 00:00:25,200
The language is called tax, which is shorthand for data analysis expression's.

7
00:00:25,530 --> 00:00:30,020
However, there are two distinct types of outputs that can come from our tax formulas.

8
00:00:30,660 --> 00:00:33,600
The first and simplest type is a calculated column.

9
00:00:33,990 --> 00:00:39,930
You can think about a calculated column as being some permutation of items within a specific row to

10
00:00:39,930 --> 00:00:41,260
generate a single output.

11
00:00:42,090 --> 00:00:44,880
Let's look at some Excel formulas to understand what I mean.

12
00:00:45,210 --> 00:00:47,070
This is our employee wages table.

13
00:00:47,310 --> 00:00:52,530
Now imagine if we wanted to calculate the monthly wage paid to each employee on a TRO.

14
00:00:53,430 --> 00:00:59,670
We would take the employee wages and we divide that by the employee count so that that new column would

15
00:00:59,670 --> 00:01:02,610
contain the information describing the average wages.

16
00:01:03,450 --> 00:01:05,670
Most calculated columns are like this.

17
00:01:05,820 --> 00:01:11,610
You have some series of inputs and you want to generate a singular output that is specific to the row

18
00:01:11,610 --> 00:01:12,270
in the table.

19
00:01:12,840 --> 00:01:17,940
Calculated columns are in effect added to the table so that you can think of them as being part of the

20
00:01:17,940 --> 00:01:21,600
original data set in terms of both storage and file impact.

21
00:01:22,600 --> 00:01:27,820
Now, while I've described this as a one for one calculation, in a sense, that doesn't necessarily

22
00:01:27,820 --> 00:01:30,840
mean that it can't include multiple sources of data.

23
00:01:31,240 --> 00:01:36,700
So, for example, I could create a new calculated column that is the sum of another column.

24
00:01:37,420 --> 00:01:39,940
In this case, it's the sum of the entire column.

25
00:01:40,600 --> 00:01:44,080
I can also create one that's a conditional sum using some ifs.

26
00:01:45,040 --> 00:01:50,710
This is pretty common in Excel, as if I wanted to calculate the percentage of the year's total that

27
00:01:50,710 --> 00:01:55,160
each month contributes, while this is technically a calculated column.

28
00:01:55,510 --> 00:01:59,290
It could be better handled the power of it through what are called measures.

29
00:01:59,890 --> 00:02:00,910
So why is that?

30
00:02:01,540 --> 00:02:05,020
If we look at our calculated column, it's not sensical.

31
00:02:05,020 --> 00:02:11,770
Once we start aggregating rows together, imagine dragging this annual total column into your pivot

32
00:02:11,770 --> 00:02:12,160
table.

33
00:02:12,550 --> 00:02:18,310
You'll get the actual annual total times some number because all of them will get added together.

34
00:02:18,820 --> 00:02:21,360
The value is incongruent with the row that it's in.

35
00:02:21,820 --> 00:02:23,320
That is the data in the cell.

36
00:02:23,570 --> 00:02:27,850
Is that a different type of granularity than the data in this particular row?

37
00:02:28,090 --> 00:02:32,500
And so the calculation only makes sense under a specific context and view.

38
00:02:32,680 --> 00:02:39,520
Outside of aggregations, all of the values around it can be added, averaged, counted or operated

39
00:02:39,520 --> 00:02:42,920
on in other ways to get meaningful pieces of information.

40
00:02:42,970 --> 00:02:44,830
So it is very clearly out of sync.

41
00:02:45,280 --> 00:02:47,650
So this brings us to the concept of measures.

42
00:02:48,750 --> 00:02:53,880
Think of measures as standing separate from your data, they are named measures because they are, in

43
00:02:53,880 --> 00:03:00,300
effect measurements of your data, whether that is a total and average account or a more convoluted

44
00:03:00,300 --> 00:03:03,090
calculation that can merge one or more values together.

45
00:03:04,180 --> 00:03:10,060
So, for example, for the sub, we could have specified a smaller table to the side that included just

46
00:03:10,060 --> 00:03:15,820
the entire sum, or we could have had a pair of summary tables that include contingent subs.

47
00:03:16,240 --> 00:03:21,490
But for all of these, we can think of them as the measured sum for the rows that filter down to that

48
00:03:21,490 --> 00:03:22,240
subsection.

49
00:03:22,930 --> 00:03:26,560
In effect, we can simply refer to this as a sum.

50
00:03:27,130 --> 00:03:32,650
Instead of thinking about this as a specific value, we could think about it and save it as a specific

51
00:03:32,650 --> 00:03:33,310
formula.

52
00:03:33,940 --> 00:03:38,980
Just give me the sum for any values from this column that show up in my filter.

53
00:03:39,640 --> 00:03:44,650
In terms of Excel, you should think about it more like the subtotal function which doesn't total hidden

54
00:03:44,650 --> 00:03:51,370
rows and will only give you a aggregation for the rows that are visible to you to demonstrate.

55
00:03:51,380 --> 00:03:53,230
Let's add a subtotal function here.

56
00:03:53,620 --> 00:03:59,470
The subtotal update based upon whatever filter I have applied to the table, it is separate from the

57
00:03:59,470 --> 00:04:04,780
concept of the table, but still reliant on and connected to the table for its results.

58
00:04:05,080 --> 00:04:08,200
So it will update based upon its own context.

59
00:04:08,440 --> 00:04:10,450
That is, which rows are visible to it.

60
00:04:10,840 --> 00:04:15,310
This concept could be difficult to grasp, as there are many cases where it could be challenging to

61
00:04:15,310 --> 00:04:20,140
determine whether a calculation should be done with calculated columns or with measures.

62
00:04:20,410 --> 00:04:25,840
So here's a quick review, along with my thought process differentiating between a calculated column

63
00:04:25,840 --> 00:04:30,340
and a measure, a calculated column adds data to a table.

64
00:04:30,640 --> 00:04:34,480
The column should be at the same granularity as other entries in the table.

65
00:04:36,010 --> 00:04:42,100
Measures, on the other hand, are formulaic computations that should be thought of as merging one or

66
00:04:42,100 --> 00:04:43,710
more rows of data together.

67
00:04:44,380 --> 00:04:50,380
My rule of thumb is that if a computation requires more than one row of data to calculate at any point

68
00:04:50,380 --> 00:04:52,350
in time, then it it is a measure.

69
00:04:52,870 --> 00:04:55,040
Otherwise it is a calculated call.

70
00:04:56,070 --> 00:04:57,510
Now, though, we know what they are.

71
00:04:57,790 --> 00:05:01,240
Let's build some in our next lesson as I introduce you to DAX.