1 00:00:00,300 --> 00:00:06,060 We've officially reached measures now the really deep part of power, pivot and power by that will astound, 2 00:00:06,060 --> 00:00:09,930 amaze, confound and positively anger you on occasion. 3 00:00:10,500 --> 00:00:11,130 But why? 4 00:00:11,610 --> 00:00:14,160 So far, our measures have been incredibly simple. 5 00:00:14,520 --> 00:00:19,430 The formula we've used is simply equal to some of the field and it just seems to work. 6 00:00:20,490 --> 00:00:21,980 But think about that for a moment. 7 00:00:22,500 --> 00:00:24,720 We have a bunch of conditions in our pivot table. 8 00:00:24,930 --> 00:00:28,940 How can our power pivot do these sums if I haven't given it conditions? 9 00:00:29,580 --> 00:00:34,440 What if I want more complicated conditions such as a cumulative running total of our profit? 10 00:00:34,830 --> 00:00:38,520 Ultimately, all of this is going to boil down to context. 11 00:00:38,940 --> 00:00:44,400 Every tax formula calculates based upon the data context in which it is encapsulated. 12 00:00:45,150 --> 00:00:48,450 This pivot table starts out with thirty six context sets. 13 00:00:49,080 --> 00:00:52,100 Once we expand it like this, it's up to over 90. 14 00:00:52,770 --> 00:00:56,370 I keep saying this word context, but what what is it I'm talking about? 15 00:00:57,000 --> 00:01:02,760 Well, the context refers to the data universe as it relates to a particular value cell in our pivot 16 00:01:02,760 --> 00:01:06,390 table or in power by a particular point in a chart. 17 00:01:07,050 --> 00:01:12,180 That means that if we look at this particular cell, the context is all of the data that's available 18 00:01:12,360 --> 00:01:18,090 that has a relationship to Kroger in the stores table and relationship to the quarter three thirty one 19 00:01:18,090 --> 00:01:20,370 twenty nineteen in our calendar table. 20 00:01:22,520 --> 00:01:28,030 So think about it is starting with all of the data and then it gets filtered down by relationships, 21 00:01:28,580 --> 00:01:31,580 note that any unrelated tables would not be filtered. 22 00:01:31,700 --> 00:01:32,830 This can come in handy. 23 00:01:33,560 --> 00:01:38,990 There are ways to modify context within a DACS formula to create alternative context implications for 24 00:01:38,990 --> 00:01:40,090 a given calculation. 25 00:01:40,730 --> 00:01:45,050 But before we figure out how to do that, let's identify an example where we might need it. 26 00:01:46,970 --> 00:01:52,100 If we think about how our relationship definitions work, they limit tables to those entries where the 27 00:01:52,100 --> 00:01:53,540 target values match up. 28 00:01:54,020 --> 00:01:59,060 In this case, we want our measure to capture all of the values that are less than are equal to a given 29 00:01:59,060 --> 00:02:00,680 date, not just equal to it. 30 00:02:01,550 --> 00:02:06,620 Since our relationships aren't up to the task, we need to create a more complicated measure that takes 31 00:02:06,620 --> 00:02:10,640 into account some of our relationships, but not all of our relationships. 32 00:02:10,880 --> 00:02:13,910 And then we need to define a customer relationship on top of it. 33 00:02:14,900 --> 00:02:18,950 This sounds pretty difficult, but if you think about how we might do this with a service function, 34 00:02:19,250 --> 00:02:22,220 it's only a mile change with power pivot. 35 00:02:22,410 --> 00:02:27,740 It's a little bit more complex in that there are two steps to it and unfiltered step and then a filter 36 00:02:27,740 --> 00:02:30,020 step which will be tied to our measure. 37 00:02:31,430 --> 00:02:37,160 All of this is going to boil down to understanding and managing the measures context, so let's really 38 00:02:37,160 --> 00:02:38,840 dive into what context is. 39 00:02:39,080 --> 00:02:40,040 Context isn't. 40 00:02:41,000 --> 00:02:48,350 The hardest part of context is that it's very contextual, obviously the context for a measure is computation 41 00:02:48,590 --> 00:02:51,900 will be based upon where the calculation occurs within an output. 42 00:02:52,610 --> 00:02:55,820 So let's look at our pivot tables for a few examples. 43 00:02:56,850 --> 00:03:02,160 Each of the cells in our pivot table can be imagined to have a full series of tables behind them, each 44 00:03:02,160 --> 00:03:04,290 with a different context related to them. 45 00:03:04,830 --> 00:03:09,990 The cell where three thirty one twenty, nineteen and Kröger intersect has access to all of these table 46 00:03:09,990 --> 00:03:14,280 data sets, all of them filtered to three thirty one twenty, nineteen and Kröger. 47 00:03:15,380 --> 00:03:21,140 Moving to Tom Thumb and we would have Tom Thumb is the filter, so when we request the sum of the purchase 48 00:03:21,140 --> 00:03:27,710 orders or the sales field within the context of that cell, it's calculating the entire sum from that 49 00:03:27,710 --> 00:03:28,160 table. 50 00:03:28,570 --> 00:03:34,100 There's no if computation involved at this stage anymore, which seems odd since we think about it as 51 00:03:34,100 --> 00:03:39,140 being a sum if computation some if the is Kroger and the quarters. 52 00:03:39,140 --> 00:03:41,150 Three thirty one twenty nineteen. 53 00:03:42,830 --> 00:03:46,020 So in this calculation occurs, it's simple, it's fast, it's efficient. 54 00:03:46,370 --> 00:03:51,810 So this raises a question, are there ways to manipulate the context within the computation directly? 55 00:03:52,790 --> 00:03:59,310 Yes, we can modify our measures so that we can manipulate the context in quite a few different ways. 56 00:03:59,570 --> 00:04:02,780 So let's discuss some of the general concepts and functions involved. 57 00:04:03,450 --> 00:04:08,060 The most important function to know is the calculate function, which is where we'll start from. 58 00:04:10,360 --> 00:04:16,300 The calculator function takes some type of computational function or command, such as are some function, 59 00:04:16,480 --> 00:04:21,430 or it can take another measure and it applies one or more filter modifications to it. 60 00:04:22,150 --> 00:04:26,770 In this case, I have simply written a filter that filters down to the store in the sales table equals 61 00:04:26,770 --> 00:04:27,460 the store value. 62 00:04:27,880 --> 00:04:32,320 Obviously this will calculate the exact same results as before, which I can see when I add it to the 63 00:04:32,320 --> 00:04:32,970 pivot table. 64 00:04:33,820 --> 00:04:36,360 But what if we wanted to create a weekend sales total? 65 00:04:36,820 --> 00:04:41,370 Obviously we could add the weekend identifier to our table and modify the context that way. 66 00:04:42,160 --> 00:04:47,440 But in this case, I'm going to go into the measure and modify it so that it has a filter specifically 67 00:04:47,440 --> 00:04:48,310 to weekend. 68 00:04:49,450 --> 00:04:53,610 This filter function adds filters, but we can remove them as well. 69 00:04:54,740 --> 00:04:59,900 There are quite a few different filter removal commands, many of them start with all I'm going to use 70 00:04:59,900 --> 00:05:01,670 the general all function here. 71 00:05:01,940 --> 00:05:07,220 When you use the URL function with a specified field or table, it will remove any filters that are 72 00:05:07,220 --> 00:05:09,610 applied specifically to that table. 73 00:05:10,100 --> 00:05:15,710 That's very odd language to say filters applied to that specific table. 74 00:05:16,400 --> 00:05:19,700 Well, this is where our dimensional tables become very important. 75 00:05:20,240 --> 00:05:24,350 Envision our little relationship here between calendar stores and sales. 76 00:05:24,950 --> 00:05:29,560 When we have store selected as Kroger and our quarter selected is three thirty one twenty nineteen. 77 00:05:30,080 --> 00:05:32,690 We're not directly filtering our sales table. 78 00:05:32,990 --> 00:05:36,980 We're applying our filters to the stores table into our calendar table. 79 00:05:37,250 --> 00:05:42,980 And then we're using these filtered tables and the relationships between these tables with the sales 80 00:05:42,980 --> 00:05:45,020 table in order to get the proper data. 81 00:05:46,620 --> 00:05:53,070 So let's say I wanted to remove my date filter limitation and get just the total for stores across all 82 00:05:53,070 --> 00:05:53,500 dates. 83 00:05:54,180 --> 00:06:00,210 So in other words, I want to ignore the date filter while using the calculate function and the sum 84 00:06:00,210 --> 00:06:00,870 of sales. 85 00:06:01,140 --> 00:06:04,780 I can add a filter that says use all from the calendar table. 86 00:06:05,580 --> 00:06:11,880 Now, with this measure, the context of the cells within the pivot table will always filter only by 87 00:06:11,880 --> 00:06:12,380 store. 88 00:06:13,260 --> 00:06:18,060 I can remove quarter from the pivot table and we can confirm the totals real quick. 89 00:06:18,720 --> 00:06:24,360 As you can see, this all dates version calculates the total ignoring the date field just as we've liked. 90 00:06:25,110 --> 00:06:29,370 Pretty nifty, but what if we wanted to refill targets for a cumulative approach? 91 00:06:30,870 --> 00:06:33,900 This is where we'll really take advantage of some context. 92 00:06:33,900 --> 00:06:34,440 Magic. 93 00:06:35,310 --> 00:06:40,430 If we go look at this next measure, we're going to calculate our sum and apply a compound filter. 94 00:06:40,980 --> 00:06:46,090 The first step in the filter says to give us all of our calendar table, stripping away the calendar 95 00:06:46,090 --> 00:06:46,800 restrictions. 96 00:06:47,070 --> 00:06:52,770 And then it adds a new filter to our calendar table that says to pull the calendar date when it is less 97 00:06:52,770 --> 00:06:54,870 than the maximum calendar date. 98 00:06:55,530 --> 00:06:58,580 That what that there's no way that doesn't make sense. 99 00:06:58,620 --> 00:07:00,750 That's just going to pull all of the calendar dates. 100 00:07:00,780 --> 00:07:01,230 Right. 101 00:07:02,470 --> 00:07:04,360 Let's go at it to the table and see. 102 00:07:06,000 --> 00:07:09,780 When we add it, it looks like it's a running cumulative total, but why? 103 00:07:10,670 --> 00:07:16,430 Context, as always, if we look at our measure and we dig into this filter function a bit. 104 00:07:17,860 --> 00:07:25,270 We could figure this out, what would we get if we calculated our Max calendar date while in this three 105 00:07:25,270 --> 00:07:29,320 thirty one twenty 19 cell independent of all other factors? 106 00:07:30,550 --> 00:07:36,310 Well, because we have relationships, the largest state within this context would be three thirty one 107 00:07:36,310 --> 00:07:37,280 twenty nineteen. 108 00:07:37,750 --> 00:07:42,100 That is the max calendar date isn't the maximum of the table in total. 109 00:07:42,760 --> 00:07:44,950 It's the maximum of the calendar date. 110 00:07:45,130 --> 00:07:51,760 With respect to the context, outside of the calculated function, if we moved out to sell our new max 111 00:07:51,760 --> 00:07:53,620 date would be six thirty, twenty, nineteen. 112 00:07:53,890 --> 00:07:59,860 Since we're now in a different context, giving us a new cumulative total, we'll dig into this in a 113 00:07:59,860 --> 00:08:01,720 little bit more detail as we continue. 114 00:08:02,200 --> 00:08:07,690 But with that little bit of context magic, we've created a new, more flexible measure that accumulates 115 00:08:07,690 --> 00:08:08,790 our sales together. 116 00:08:09,580 --> 00:08:14,560 Join me in our next lesson as we start changing our measures together and we'll continue discussing 117 00:08:14,650 --> 00:08:18,160 and expanding on the beauty of tax context.