1 00:00:00,300 --> 00:00:06,990 KPIs or key performance indicators, are visual cues for you to use with your power pivot tables, I 2 00:00:06,990 --> 00:00:12,300 think about them sort of like conditional formatting cells that are specific to pivot tables using power 3 00:00:12,300 --> 00:00:12,660 pivot. 4 00:00:12,990 --> 00:00:18,180 Now, KPIs are really simple to set up and use, but the difficult trick will be in creating visually 5 00:00:18,180 --> 00:00:24,480 useful and informative KPIs that have the measures set up ahead of time to construct and support them. 6 00:00:25,050 --> 00:00:31,080 Let's start from the end and look at a configured KPI to get an understanding as to what they are in 7 00:00:31,080 --> 00:00:31,970 this pivot table. 8 00:00:32,190 --> 00:00:36,890 The dots that you see are for the KPI that have defined for my cash on hand measure. 9 00:00:37,410 --> 00:00:40,890 Obviously green is good, is OK and red is bad. 10 00:00:41,440 --> 00:00:47,430 Each of these circles is tied not only to the cash on hand measure, but also to another measure, my 11 00:00:47,430 --> 00:00:48,770 total expenses measure. 12 00:00:49,290 --> 00:00:50,520 So what's going on here? 13 00:00:51,570 --> 00:00:57,270 Last set up the KPI so that it shows a red dot, if the cash on hand is less than 10 percent of the 14 00:00:57,270 --> 00:01:04,260 total expenses for the period yellow, if it's under 40 percent and green otherwise, as each of these 15 00:01:04,260 --> 00:01:10,530 are key to the relationship between the values as a pivot table expands and contracts, these values 16 00:01:10,530 --> 00:01:12,870 and the associated dots will change. 17 00:01:13,470 --> 00:01:16,850 This indicator is fairly simple and not particularly useful. 18 00:01:17,160 --> 00:01:23,430 A better one might look at whether cash on hand exceeds a certain percentage of the last few days worth 19 00:01:23,430 --> 00:01:25,430 of expenses or even in the last few months. 20 00:01:25,920 --> 00:01:28,790 So seeing this result, how can we set these up? 21 00:01:29,010 --> 00:01:32,710 Let's go look at a clean version of this file and build this from scratch. 22 00:01:33,360 --> 00:01:34,350 First things first. 23 00:01:34,350 --> 00:01:36,390 Let's look at the measures that I have in place. 24 00:01:36,840 --> 00:01:42,180 I have a new measure called Total Expenses, which is simply the purchase orders plus the wages. 25 00:01:42,360 --> 00:01:43,650 There's nothing special about it. 26 00:01:43,650 --> 00:01:49,200 Otherwise, your key performance indicators will be managed strictly through the power ribbon with an 27 00:01:49,200 --> 00:01:51,840 Excel under this KPIs dropdown. 28 00:01:52,470 --> 00:01:54,450 Like our measures, we have two options. 29 00:01:54,480 --> 00:01:58,290 One, for creating a new KPI and one for managing our KPIs. 30 00:01:58,800 --> 00:02:02,730 Let's jump straight into the management interface since we'll create a new one from there. 31 00:02:03,540 --> 00:02:10,800 This one looks very much like our manage measures window with options for a new edit and delete clicking 32 00:02:10,800 --> 00:02:11,160 on new. 33 00:02:11,160 --> 00:02:13,950 We'll bring up the interface for defining our KPI. 34 00:02:14,670 --> 00:02:19,890 The interface might look daunting at first as there are a lot of options, but it basically boils down 35 00:02:19,890 --> 00:02:20,940 to three things. 36 00:02:21,450 --> 00:02:23,970 One, what measure do you want to look at? 37 00:02:24,510 --> 00:02:28,110 To what value do you want to compare that measure against? 38 00:02:28,440 --> 00:02:32,340 And three, what type of visual are you interested in seeing? 39 00:02:32,940 --> 00:02:35,730 So these options follow the points from top to bottom. 40 00:02:36,180 --> 00:02:39,900 This first section of the top is the base field for the KPI. 41 00:02:40,380 --> 00:02:42,660 This is the number that will be used for the starting point. 42 00:02:43,050 --> 00:02:44,400 I'll select cash on hand. 43 00:02:44,400 --> 00:02:50,130 And now let's imagine that this number is one hundred as we set up the rest step to pick what we want 44 00:02:50,130 --> 00:02:51,510 to compare this against. 45 00:02:51,750 --> 00:02:54,450 And we can either use a fixed number or another measure. 46 00:02:54,930 --> 00:02:57,330 Let's start with an absolute value of one hundred. 47 00:02:58,110 --> 00:03:03,570 This automatically sets our initial thresholds at forty and eighty, which we can then drag left right 48 00:03:03,570 --> 00:03:04,770 to customize our range. 49 00:03:04,770 --> 00:03:08,460 Or we could directly edit the value via text like sell. 50 00:03:09,060 --> 00:03:14,940 These absolute values are particularly good for percentages where you could set the thresholds to something 51 00:03:14,940 --> 00:03:19,830 like 70 percent with ranges to the left or right as appropriate for the measure at hand. 52 00:03:20,490 --> 00:03:25,020 Immediately below this color bar are a series of alternative color bars. 53 00:03:25,200 --> 00:03:28,800 We can see that the top left one is selected, which is red, yellow, green. 54 00:03:29,160 --> 00:03:34,050 Each of these offers a different permutation of how to relate the observed base field's value to the 55 00:03:34,050 --> 00:03:34,950 target value. 56 00:03:35,400 --> 00:03:41,160 Whether getting close to the target value is good or bad, whether you want a central range or a left 57 00:03:41,160 --> 00:03:42,000 or right range. 58 00:03:42,240 --> 00:03:47,430 Now, clicking on each one of these will update our color bar to that specified version below. 59 00:03:47,430 --> 00:03:53,700 These color bars are another set of options with different icon styles to further customize the appearance. 60 00:03:54,090 --> 00:03:59,310 The first four all work effectively, identically, with different variations on the style. 61 00:03:59,670 --> 00:04:03,390 The first are simple circles where the only differences in the color. 62 00:04:03,930 --> 00:04:10,710 The second set offers an exclamation mark, an X and a checkmark, giving a little bit of variation 63 00:04:10,710 --> 00:04:13,110 on each of the circles besides their color. 64 00:04:13,650 --> 00:04:20,340 The third one simply adds a dark background to our circles, and then the fourth option changes each 65 00:04:20,340 --> 00:04:23,460 of the circles to a unique shape based upon the color. 66 00:04:23,910 --> 00:04:28,680 The final section to the far right is kind of a pie chart section. 67 00:04:29,280 --> 00:04:33,930 This can make some sense for a measure that you're tracking growth wise to completion. 68 00:04:34,140 --> 00:04:37,890 However, it only shows in quarter increments it doesn't show Parshall's. 69 00:04:38,700 --> 00:04:44,280 So let's say with our value of one hundred and we're at nineteen, well, it's going to show an empty 70 00:04:44,280 --> 00:04:44,760 pie. 71 00:04:45,090 --> 00:04:48,690 As soon as it hits twenty one, it'll be at twenty one point twenty five percent. 72 00:04:49,530 --> 00:04:52,440 Then once it hits the forty one, it'll show fifty percent. 73 00:04:52,590 --> 00:04:57,330 Because of this, I'm not particularly fond of these in relation to the other formats. 74 00:04:58,020 --> 00:05:01,680 Now with absolute values, the bars are essentially percentage based. 75 00:05:01,860 --> 00:05:05,550 When we have hundred selected we have forty and eighty is our starting points. 76 00:05:06,090 --> 00:05:08,100 But let's say we change it to ten thousand. 77 00:05:08,190 --> 00:05:10,890 Now our bar looks for four thousand and eight thousand. 78 00:05:11,100 --> 00:05:12,420 This changes only slightly. 79 00:05:12,420 --> 00:05:17,640 Once we move from absolute value to another measure, I'm going to go select, measure and choose the 80 00:05:17,640 --> 00:05:19,380 expenses total measure here. 81 00:05:19,920 --> 00:05:24,630 Once I do this, the value selectors on the bar change from numbers to percentages. 82 00:05:24,900 --> 00:05:30,330 At this point, the value comparison will be based upon the percentage of plot against the measure you've 83 00:05:30,330 --> 00:05:32,040 defined here so far. 84 00:05:32,040 --> 00:05:35,540 Cash on hand is one hundred and the expenses is one hundred. 85 00:05:35,820 --> 00:05:38,790 Then cash on hand is one hundred percent of expenses. 86 00:05:39,420 --> 00:05:45,780 If the cash on hand is fifty, then it would only be fifty percent of the expenses and it would fall 87 00:05:45,780 --> 00:05:50,370 in the yellow range for this specified selection with this type of target. 88 00:05:50,650 --> 00:05:53,380 You generally want enough cash on hand, but not too much. 89 00:05:53,740 --> 00:05:56,500 And really, this isn't a great measure for this. 90 00:05:57,220 --> 00:06:03,490 So I'm going to go ahead and say that under 10 percent is read, under 40 percent is yellow, and then 91 00:06:03,490 --> 00:06:04,480 the rest is green. 92 00:06:05,710 --> 00:06:11,920 Once I press, OK, this shows up in our CPI's list, and it's named after the target measure, that 93 00:06:11,920 --> 00:06:13,120 is the cash on hand. 94 00:06:14,310 --> 00:06:20,900 And now I can close this and we'll see it in our pivot table, once cash on hand is defined as a KPI, 95 00:06:21,090 --> 00:06:27,210 it will show up with a traffic light icon inside our Pivot Table Fields area with a dropdown containing 96 00:06:27,210 --> 00:06:32,280 three different fields the value field, the gold field and the status field. 97 00:06:33,170 --> 00:06:38,630 The value field is our original measure field as it previously existed, so it's already in our pivot 98 00:06:38,630 --> 00:06:42,620 table, the gold field will show the gold target for that context. 99 00:06:42,920 --> 00:06:46,690 So it's in this case, the expenses total. 100 00:06:47,150 --> 00:06:50,130 And if we add it, we could see the target expenses value. 101 00:06:50,420 --> 00:06:53,870 Finally, the status is the indicator symbol that we're interested in. 102 00:06:54,680 --> 00:07:01,310 Once we add it to our pivot table, it will display the visual indicator are red, yellow and green 103 00:07:01,310 --> 00:07:01,940 circles. 104 00:07:03,230 --> 00:07:05,480 And that's basically it for CPI's. 105 00:07:05,870 --> 00:07:09,320 Once you know how to create them, it's very simple to create more. 106 00:07:09,590 --> 00:07:15,230 But mastering them, that is creating the right combinations of measures to compare with the visual 107 00:07:15,230 --> 00:07:17,360 flow is the real challenge. 108 00:07:19,590 --> 00:07:24,940 You now have the tools in your arsenal to build some powerful and informational reports from your data. 109 00:07:25,380 --> 00:07:27,960 It'll take some practice and some learning. 110 00:07:28,200 --> 00:07:33,660 But once you figure out the right approaches to using these measures and KPIs, you'll really be able 111 00:07:33,660 --> 00:07:36,960 to garner incredible amounts of information from your data.