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.