KPIs or key performance indicators, are visual cues for you to use with your power pivot tables, I
think about them sort of like conditional formatting cells that are specific to pivot tables using power
pivot.
Now, KPIs are really simple to set up and use, but the difficult trick will be in creating visually
useful and informative KPIs that have the measures set up ahead of time to construct and support them.
Let's start from the end and look at a configured KPI to get an understanding as to what they are in
this pivot table.
The dots that you see are for the KPI that have defined for my cash on hand measure.
Obviously green is good, is OK and red is bad.
Each of these circles is tied not only to the cash on hand measure, but also to another measure, my
total expenses measure.
So what's going on here?
Last set up the KPI so that it shows a red dot, if the cash on hand is less than 10 percent of the
total expenses for the period yellow, if it's under 40 percent and green otherwise, as each of these
are key to the relationship between the values as a pivot table expands and contracts, these values
and the associated dots will change.
This indicator is fairly simple and not particularly useful.
A better one might look at whether cash on hand exceeds a certain percentage of the last few days worth
of expenses or even in the last few months.
So seeing this result, how can we set these up?
Let's go look at a clean version of this file and build this from scratch.
First things first.
Let's look at the measures that I have in place.
I have a new measure called Total Expenses, which is simply the purchase orders plus the wages.
There's nothing special about it.
Otherwise, your key performance indicators will be managed strictly through the power ribbon with an
Excel under this KPIs dropdown.
Like our measures, we have two options.
One, for creating a new KPI and one for managing our KPIs.
Let's jump straight into the management interface since we'll create a new one from there.
This one looks very much like our manage measures window with options for a new edit and delete clicking
on new.
We'll bring up the interface for defining our KPI.
The interface might look daunting at first as there are a lot of options, but it basically boils down
to three things.
One, what measure do you want to look at?
To what value do you want to compare that measure against?
And three, what type of visual are you interested in seeing?
So these options follow the points from top to bottom.
This first section of the top is the base field for the KPI.
This is the number that will be used for the starting point.
I'll select cash on hand.
And now let's imagine that this number is one hundred as we set up the rest step to pick what we want
to compare this against.
And we can either use a fixed number or another measure.
Let's start with an absolute value of one hundred.
This automatically sets our initial thresholds at forty and eighty, which we can then drag left right
to customize our range.
Or we could directly edit the value via text like sell.
These absolute values are particularly good for percentages where you could set the thresholds to something
like 70 percent with ranges to the left or right as appropriate for the measure at hand.
Immediately below this color bar are a series of alternative color bars.
We can see that the top left one is selected, which is red, yellow, green.
Each of these offers a different permutation of how to relate the observed base field's value to the
target value.
Whether getting close to the target value is good or bad, whether you want a central range or a left
or right range.
Now, clicking on each one of these will update our color bar to that specified version below.
These color bars are another set of options with different icon styles to further customize the appearance.
The first four all work effectively, identically, with different variations on the style.
The first are simple circles where the only differences in the color.
The second set offers an exclamation mark, an X and a checkmark, giving a little bit of variation
on each of the circles besides their color.
The third one simply adds a dark background to our circles, and then the fourth option changes each
of the circles to a unique shape based upon the color.
The final section to the far right is kind of a pie chart section.
This can make some sense for a measure that you're tracking growth wise to completion.
However, it only shows in quarter increments it doesn't show Parshall's.
So let's say with our value of one hundred and we're at nineteen, well, it's going to show an empty
pie.
As soon as it hits twenty one, it'll be at twenty one point twenty five percent.
Then once it hits the forty one, it'll show fifty percent.
Because of this, I'm not particularly fond of these in relation to the other formats.
Now with absolute values, the bars are essentially percentage based.
When we have hundred selected we have forty and eighty is our starting points.
But let's say we change it to ten thousand.
Now our bar looks for four thousand and eight thousand.
This changes only slightly.
Once we move from absolute value to another measure, I'm going to go select, measure and choose the
expenses total measure here.
Once I do this, the value selectors on the bar change from numbers to percentages.
At this point, the value comparison will be based upon the percentage of plot against the measure you've
defined here so far.
Cash on hand is one hundred and the expenses is one hundred.
Then cash on hand is one hundred percent of expenses.
If the cash on hand is fifty, then it would only be fifty percent of the expenses and it would fall
in the yellow range for this specified selection with this type of target.
You generally want enough cash on hand, but not too much.
And really, this isn't a great measure for this.
So I'm going to go ahead and say that under 10 percent is read, under 40 percent is yellow, and then
the rest is green.
Once I press, OK, this shows up in our CPI's list, and it's named after the target measure, that
is the cash on hand.
And now I can close this and we'll see it in our pivot table, once cash on hand is defined as a KPI,
it will show up with a traffic light icon inside our Pivot Table Fields area with a dropdown containing
three different fields the value field, the gold field and the status field.
The value field is our original measure field as it previously existed, so it's already in our pivot
table, the gold field will show the gold target for that context.
So it's in this case, the expenses total.
And if we add it, we could see the target expenses value.
Finally, the status is the indicator symbol that we're interested in.
Once we add it to our pivot table, it will display the visual indicator are red, yellow and green
circles.
And that's basically it for CPI's.
Once you know how to create them, it's very simple to create more.
But mastering them, that is creating the right combinations of measures to compare with the visual
flow is the real challenge.
You now have the tools in your arsenal to build some powerful and informational reports from your data.
It'll take some practice and some learning.
But once you figure out the right approaches to using these measures and KPIs, you'll really be able
to garner incredible amounts of information from your data.