1
00:00:00,840 --> 00:00:06,030
As part of structuring this course, this practice exercise, I thought one last video running through

2
00:00:06,030 --> 00:00:08,840
some slightly more advanced measures might be in order.

3
00:00:10,260 --> 00:00:15,960
The first thing we'll do in this lesson is we will improve our key performance indicators by making

4
00:00:15,960 --> 00:00:20,220
it compare our cash on hand against the expenses from the previous month.

5
00:00:20,940 --> 00:00:27,000
This will standardize our expense level at a specific rate rather than letting it expand and contract

6
00:00:27,000 --> 00:00:28,980
with our other measures and contexts.

7
00:00:29,640 --> 00:00:35,550
This will give us a more consistent view of how our cash on hand position is relative to expenses.

8
00:00:36,060 --> 00:00:41,370
The second thing we'll do is we will create a more advanced profit measure that will mix and allocate

9
00:00:41,370 --> 00:00:44,310
our labor costs across each of our departments.

10
00:00:44,730 --> 00:00:49,860
If you remember from the custom hierarchy lesson, we couldn't drill down past the general level for

11
00:00:49,860 --> 00:00:54,270
any of our profit computations since our wages and expenses did not line up.

12
00:00:55,290 --> 00:01:00,390
So let's start with our key performance indicators to do this, I'm going to create a new measure and

13
00:01:00,390 --> 00:01:02,640
we're going to call it the prior month expenses.

14
00:01:03,340 --> 00:01:06,990
I'm going to follow a similar pattern to what I use for the cash on hand.

15
00:01:07,120 --> 00:01:11,190
So similar that I'm going to copy its formula and use it as the base.

16
00:01:11,910 --> 00:01:14,340
I'll replace profit with total expenses.

17
00:01:14,670 --> 00:01:21,030
And in the filter area, I want to adjust our filter so that the list of rows from our calendar table

18
00:01:21,270 --> 00:01:23,460
is any date from the prior month.

19
00:01:24,480 --> 00:01:27,870
This may seem to be a bit more challenging to envision than it seems.

20
00:01:28,410 --> 00:01:34,230
I'm going to keep the all calendar peace, but now I want to restructure the following filter component.

21
00:01:35,300 --> 00:01:40,340
I still want the dates that are before our current maximum date, but instead of strictly less than

22
00:01:40,340 --> 00:01:44,410
our maximum date, I want dates that are at least a month before that date.

23
00:01:45,020 --> 00:01:50,030
Your first thought might be to adjust that maximum calendar date by using the date add function and

24
00:01:50,030 --> 00:01:51,130
subtracting a month.

25
00:01:51,620 --> 00:01:57,710
However, the date and function works with arrays of data, so that leaves our calendar date array for

26
00:01:57,710 --> 00:02:02,000
us to work with and so we can't subtract a month from our maximum date.

27
00:02:02,330 --> 00:02:07,700
What I can do is add a month to the calendar dates themselves, so I'll use the date, add function,

28
00:02:07,700 --> 00:02:14,850
add it to the front, put a comma after the calendar date, use one for one month and then I'll use

29
00:02:14,850 --> 00:02:16,760
a month as the iterator.

30
00:02:17,570 --> 00:02:23,750
So this'll tell my measure to filter out any records that are aren't at least one month old.

31
00:02:24,040 --> 00:02:25,940
If that's still not quite what I need.

32
00:02:26,660 --> 00:02:31,310
I need records that are only one month old, not records that are older than one month.

33
00:02:31,790 --> 00:02:33,800
So I need an end condition here.

34
00:02:35,180 --> 00:02:41,450
I'll use the end function and pardon me while I make a bit of formatting changes here to help control

35
00:02:41,450 --> 00:02:41,960
the flow.

36
00:02:42,080 --> 00:02:43,820
And let's think about this next step.

37
00:02:44,360 --> 00:02:51,170
I can use the same concept as I did with the data add here, so I'll copy the line and paste it.

38
00:02:51,710 --> 00:02:57,470
If I want to include only those dates in the prior month, then adding two months to those dates beyond

39
00:02:57,470 --> 00:03:01,430
that would generate a date greater than my current maximum date.

40
00:03:01,880 --> 00:03:03,290
So that's the approach I'll take.

41
00:03:03,680 --> 00:03:08,840
I'll replace the one with the two and the less than or equal to sign with a greater than sine.

42
00:03:09,380 --> 00:03:12,790
I'll do a check of the princes click on my check formula.

43
00:03:13,780 --> 00:03:20,710
And I'll press, OK, so let's go see this in action, my cash on hand goal is my total expenses field,

44
00:03:21,220 --> 00:03:25,270
so let's filter to a month view and then add my prior month expenses.

45
00:03:25,840 --> 00:03:31,180
When I do this, I can see that my prior month expenses line up with the total expenses from the prior

46
00:03:31,180 --> 00:03:31,510
month.

47
00:03:31,900 --> 00:03:34,300
They're all like by month and so it looks great.

48
00:03:34,850 --> 00:03:36,700
So let's go adjust our KPI.

49
00:03:37,390 --> 00:03:41,560
Simply changing total expenses to prior month expenses is a solid change.

50
00:03:41,860 --> 00:03:45,700
However, I'll also change the targets to 15 percent and 30 percent.

51
00:03:46,240 --> 00:03:51,610
When I close out of this, we can look at our cash on hand status and see an indicator that is more

52
00:03:51,610 --> 00:03:52,730
useful than before.

53
00:03:53,620 --> 00:03:55,380
That was nice and quick and easy.

54
00:03:55,720 --> 00:03:58,090
Our next task is slightly less so.

55
00:03:59,880 --> 00:04:05,670
For this next step, it's not strictly necessary you could stick to aggregating your data up so that

56
00:04:05,670 --> 00:04:11,070
you're not making any data adjustments and simply hide the department drilldown since it provides no

57
00:04:11,070 --> 00:04:11,890
current value.

58
00:04:12,570 --> 00:04:17,430
But what if we really wanted to have some type of allocation across the different department groups

59
00:04:17,430 --> 00:04:20,550
to see profit computations on a per department basis?

60
00:04:21,180 --> 00:04:25,470
We know that butcher and bakery are losing money, but where are our profit centers?

61
00:04:26,440 --> 00:04:29,300
Step one is to conceptualize what we want to do.

62
00:04:29,680 --> 00:04:34,220
I'm going to assume that each unit of quantity takes an equivalent amount of labor to manage.

63
00:04:34,570 --> 00:04:37,690
So I'm going to use the quantity details to allocate labor.

64
00:04:38,210 --> 00:04:43,420
That means that if one department makes up 10 percent of the quantity of goods, then I want that department

65
00:04:43,540 --> 00:04:45,860
to get 10 percent of the labor expenses.

66
00:04:46,480 --> 00:04:48,660
So how does this translate into our measures?

67
00:04:49,390 --> 00:04:53,080
So what we want for our measure then is something like this.

68
00:04:53,740 --> 00:05:00,400
We want the quantity of the specific department divided by the quantity of all of those departments,

69
00:05:00,970 --> 00:05:04,270
multiplied by the wages from all of those departments.

70
00:05:04,930 --> 00:05:09,700
That sounds pretty complex, but we've already made measures similar to each piece of this puzzle.

71
00:05:10,330 --> 00:05:15,850
While we could combine all of this into one measure, I'm going to break it up into multiples, one

72
00:05:15,850 --> 00:05:21,220
for the quantity of the specific department, one for the quantity of the sections and then one for

73
00:05:21,220 --> 00:05:27,010
the wages of the section are final adjustment will be to the wages total measure, which will use all

74
00:05:27,010 --> 00:05:29,090
three of those previous measures together.

75
00:05:30,370 --> 00:05:33,310
So first up is the quantity for the specific department.

76
00:05:33,670 --> 00:05:36,940
That's just going to be the sum of quantity for whatever aren't given.

77
00:05:36,940 --> 00:05:44,110
Context is we've already defined this with a quantity total next as our quantity by section, I'm going

78
00:05:44,110 --> 00:05:47,270
to create a new measure for this in quantity total section.

79
00:05:48,100 --> 00:05:50,260
Think about how we did our cumulative total.

80
00:05:50,620 --> 00:05:56,500
We used calculate and then we had a filter that expanded what rose we included, which will bring us

81
00:05:56,500 --> 00:05:57,880
to a new all function.

82
00:05:58,000 --> 00:06:01,710
The all except function, the all except function will take a table.

83
00:06:01,720 --> 00:06:06,560
In this case, I'll use the department's table, since that's where I want to modify our filter it.

84
00:06:06,650 --> 00:06:12,850
It'll give us all of the filters removed except for those filters that we want to maintain the except

85
00:06:12,850 --> 00:06:13,360
clause.

86
00:06:14,050 --> 00:06:19,810
What this will let us do is selectively define which context filters will remain in place, in which

87
00:06:19,810 --> 00:06:21,550
context filters to ignore.

88
00:06:22,510 --> 00:06:24,190
That's a terribly confusing statement.

89
00:06:24,200 --> 00:06:25,840
So let's visualize for a moment.

90
00:06:26,260 --> 00:06:31,720
Our department's table has exactly three columns in a traditional setting with no special filtering

91
00:06:31,720 --> 00:06:32,110
rules.

92
00:06:32,350 --> 00:06:37,390
Like for our quantity, all three fields will be filtered based upon the context.

93
00:06:38,080 --> 00:06:43,750
If we use the all function, then none of the three columns will be filtered and will always get all

94
00:06:43,750 --> 00:06:45,760
of the department values at all times.

95
00:06:46,990 --> 00:06:53,110
With the all except function, we can go somewhere between these two, we can specify which filters

96
00:06:53,110 --> 00:06:56,050
we want to use versus which filters we don't want to use.

97
00:06:56,950 --> 00:07:03,730
So using all except the department table and then the high section and the middle section will give

98
00:07:03,730 --> 00:07:08,350
us all of the departments, except if the high or middle sections are filtered.

99
00:07:09,070 --> 00:07:15,160
So this measure will use, calculate and take that quantity total what the filter context applied that

100
00:07:15,160 --> 00:07:18,070
keeps filters on our high and our middle Middlefield.

101
00:07:19,090 --> 00:07:24,280
I'll press, OK, and we can add our quantity measures to our pivot table to see if it's working at

102
00:07:24,280 --> 00:07:28,880
the high level, we can see that our quantity and quantity of total sections match up.

103
00:07:29,140 --> 00:07:31,350
That's a good sign since we would hope they would.

104
00:07:32,170 --> 00:07:35,380
Once we drill down to the detail department level, we see a difference.

105
00:07:35,770 --> 00:07:42,520
Our quantity total section has the total quantity for each of the sections and our quantity by department

106
00:07:42,520 --> 00:07:45,340
is different and we want the ratio of those two fields.

107
00:07:45,800 --> 00:07:52,330
Our last step is to recreate that quantity total section logic with our wages so that we have a proper

108
00:07:52,330 --> 00:07:54,340
wage amount for each context entry.

109
00:07:55,040 --> 00:07:58,810
I'll copy that quantity total section formula and create a new measure.

110
00:07:59,170 --> 00:08:04,780
Once I paste it in, I'm going to change the quantity total measure piece to some of the wages.

111
00:08:05,230 --> 00:08:08,260
Why don't I just use wages total since we have that already?

112
00:08:08,470 --> 00:08:13,810
Well, as I mentioned, we're going to be changing our wages, total measure to take this value multiplied

113
00:08:13,810 --> 00:08:14,680
by the ratio.

114
00:08:15,160 --> 00:08:18,070
And so I don't want to introduce any circular logic.

115
00:08:18,370 --> 00:08:23,980
This will now be the place where our wages amount is initially calculated from our underlying tables.

116
00:08:24,430 --> 00:08:27,460
Once I've done this, we can go on to our wages total measure.

117
00:08:27,910 --> 00:08:31,390
We could have put all of this logic into just this one measure.

118
00:08:31,630 --> 00:08:37,360
However, splitting it out lets us check the quantity values and evaluate the individual steps along

119
00:08:37,360 --> 00:08:37,810
the way.

120
00:08:38,290 --> 00:08:42,760
Once they're defined, we can link them all together in a very nice, concise formula.

121
00:08:42,760 --> 00:08:48,370
Here, I'll use Divide to calculate the fractional percentage of quantity that we want to allocate our

122
00:08:48,370 --> 00:08:53,470
wages by, and then I'll multiply that with our wages total section measure and that's it.

123
00:08:54,220 --> 00:09:00,400
Since all of our profit and cash on hand measures calculate using this wages total, our entire power

124
00:09:00,400 --> 00:09:02,320
pivot infrastructure will stay in sync.

125
00:09:03,370 --> 00:09:05,710
So let's go see what happens with our cash on hand.

126
00:09:05,710 --> 00:09:07,050
Key performance indicators.

127
00:09:07,970 --> 00:09:13,610
The first thing to notice is that the general section no longer has a huge negative cash on hand status

128
00:09:13,760 --> 00:09:16,190
since there are no wages allocated to it anymore.

129
00:09:16,670 --> 00:09:23,120
Instead, each of the underlying departments now has a lower cash on hand value since the wages are

130
00:09:23,120 --> 00:09:26,840
taken out with dry goods even appearing to go negative.

131
00:09:27,720 --> 00:09:33,450
Once I drop the quantity, Collins, the general section even disappears from our pivot table altogether,

132
00:09:33,630 --> 00:09:36,160
since it no longer has data associated with it.

133
00:09:36,600 --> 00:09:42,100
We've completely merged that wage data from the general entry across the other departments.

134
00:09:42,630 --> 00:09:46,580
Naturally, there are always different ways to perform an allocation like this.

135
00:09:46,950 --> 00:09:52,830
My selection of quantity is the determination factor was strictly a personal decision based upon a thought

136
00:09:52,830 --> 00:09:58,800
process you could find and argue for different approaches based upon purchase order amounts or something

137
00:09:58,800 --> 00:10:03,760
like allocating based upon square footage or simply selecting percentages to apply.

138
00:10:04,230 --> 00:10:10,140
With that, we'll close out this lesson on advanced measures, and I'll bring you up to speed on the

139
00:10:10,140 --> 00:10:12,120
exercise in the next video.
