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.
