Join Gini von Courter for an in-depth discussion in this video Creating a key performance indicator (KPI), part of Power BI Features in Depth.
Key performance indicators, or KPIs, are visual indicators of performance. Each KPI is based on specific information that we have calculated within our power pivot. First, you need to have a base value, which we've called a measure. It's a calculation and it has to be a calculation. It can't simply be information that's within a particular column that's not calculated. So, we're going to create it in a calculation area, like we did our online sales, and reseller sales measures in the prior movie.
Then we need something to compare that information against, a target value. In the business world, a goal. So we need to be able to articulate a goal like, online sales should be 33% of total sales. Or, online sales should be greater than or equal to last month's online sales. Or online sales should be twice the catalog sales. Reseller sales should be 20% of all the total sales. So, these are the kinds of goals that are set.
Again, they can be set in relationship to the total, they can be set in relationship to the prior month or year or quarter's value. We can have an absolute value. For example, we could compare performance against an average, or we could compare assumptions. Some key performance indicators aren't technically about performance. They're making sure that assumptions that we have about the business are accurate, and we monitor those assumptions. For example, an assumption that catalog sales are decreasing.
We may not have a goal for that, but we might want to actually track catalog sales to see if they're increasing or decreasing. And then finally, in addition to our measures and our goals, we need to have status thresholds. The status threshold is, how good is good enough? If we say that online sales should be 33% of total sales, and notice here's our online sales and our key performance indicator on the right, do we have to get to 33% for that to turn green, or is it close to 33%? And at the other end of the spectrum, how bad is too bad? So if online sales were only 15%, should the indicator be red, or should the indicator turn red at 20%? So, when you talk about goals, having a goal isn't enough. To create a key performance indicator you need some measure that you can calculate, so you have to be tracking the data in the business. The second thing is, you need a goal and the same focus you set the goal need to be able to tell you what the threshold is. So that you can use the appropriate indicators. Lets go to Power Pivot and create a KPI. We'd already created online sales and store sales, reseller sales and catalog sales I created in this version of the workbook. I want to simply show you how easy it is to create similar measures when you already have one or two created. When I created reseller sales, I actually copied it off of store sales. And I'm going to copy, Ctrl+C, reseller sales. And, Ctrl+V, paste. I can't simply press Enter like I would in Excel. And I'm going to change the name from Copy of Reseller Sales to Catalog Sales. And I'm going to double-click on Reseller and change it to Catalog. Looks good. Now, I need total sales because we're comparing to a percentage of total sales. I could copy this again. Total sales is a little bit different, because total sales does not have a filter. Remember that this is the Filter section of this expression, and that it's optional.
I don't need it, because I want to sum the entire net sales amount column. There's total sales. Now I want to use a calculation for my KPI goal. And you'll remember that our first goal is that online sales will be a third of our total sales. So there are a couple of different ways we can do this. Let's call this an online target, and one way we can do this is to say that our online target is our total sales, divided by three, and I can format that. I could also have done total sales times .33, .333, .3333, same.
What I can't do, is 33%. The percent sign has another use, so that doesn't work. So divide by three is exactly a third, if they said 33%, I'd put .333. Total sales divided by three. Now I need to have a target for my reseller sales, and it's going to be similar. I make reseller target 20%, can divide this by five, that'll work quite nicely.
Because I copied it, I don't have to format it again, because it retained the format from online target. So now I have online sales, store sales, reseller sales, catalog sales. I don't have targets for store sales and catalog sales in this particular scenario. That's not something we're tracking. I could create the targets for them and leave them blank, but that doesn't make a lot of sense. So I'm going to have the information here that I would like to be able to use to create KPI's. The first performance indicator I want to create is for online sales. I can either select it and click create KPI, or I don't have to go all the way up there.
It's right here, right-click > Create KPI. Online sales, and what I want to use then, there's my base field, as my target, is Online Target. Now I want to create my status thresholds, and this is the part where I asked what's good enough. Here's the target, sitting right here. I have all these different icon sets, you're probably used to these from conditional formatting in Excel. If I'm going to use some version of red, yellow, green, then I have to set a threshold for red, for yellow, and for green.
If I'm going to have, for example, wider distribution, in terms of a threshold, then I can choose something like this set, which gives me five choices to make at different break points. There are consulting firms that successfully use these balls in order to show how close we are to having, for example, a project done. I like this set a fair amount. It gives me that nice combination of color, but it still is something that you can tell what the different values are if I'm forced to print this on a black and white printer. And once I've chosen the icon style, I need to get engaged with what the choices are around my status thresholds.
This one is red isn't good, yellow is better, green is best. Here, we have the opposite. Low values are good. Medium values, not so good. High values, really not good. So, if you are comparing to something where, for example, here we're looking at sales. Here, perhaps, we're looking at defective products in a manufacturing setting, and the fewer you have the better off you are. On the right, we have two different versions of these targets. This one is when we're aiming exactly at a particular target value.
For example, we would like to make sure that we spend all of the money in the budget, not too much, not too little. We've been given us budget. We're supposed to expend it also. How close are we coming to having spent the amounts for each of the different departments? If we go over a little bit, maybe it's okay, but we might want to crank that down and say, no, we really can't go over. That wasn't good and this is really also not good. Soon as you start going like a little bit over, you're really in trouble. That's what that would look like. And if we underspend too much, then it's lost opportunity. This is what I meant when I said that you have to have a conversation around what the thresholds are. Because they're about business value.
This set, pretty straightforward. Says if we get to 80% of the target, it turns green. But it might be that for online sales we want to crank that down a little bit. We want to crank it down to, for example, 67%. You can type in these boxes by the way. You don't have to drag them. And this one to 33, because this is relatively new business for us right now, and we're thinking if you get to two thirds, that's feelin' pretty good here. On the other hand, you might say no, we need to set those more tightly.
It's possible that the only way this indicator turns green is if you achieve 100%. Anything short of that is not allowed, is insufficient. All of these status thresholds depend on the nature of your business. I'm going to set this one though at 67% so we can see it, and I'm going to click OK. And there's my KPI. I can see a little indicator for it right here, and if I right-click in the cell, I can go back and edit the KPI settings. I'm now going to create a reseller sales KPI and I'll go through this one a little more quickly. I'm simply going to create a KPI based on my reseller target.
I'm going to change these so the thresholds are the same because they are similar indicators. There might be a compelling reason that they would be different. And you don't want to be changing them all the time. So whatever you've determined, you should set up and have a good rationale for why they were set at this level. If you change them all the time, then the reports become far less meaningful. Click OK. There's our second KPI. Now, let's go to a pivot table. We already have one living here.
We can use it just fine. I'm going to get rid of a lot of information. For example, I don't want online sales, store sales, or reseller sales. We'll pitch those all out the door. I'm going to open up the sales table. And we'll choose the items that we want in order. Here's online sales. Open this up. There's the value of online sales. It was there just a moment ago. Here was our sales goal. That could use some formatting. And there's the status. That looks good. Now, let's do the same thing for our reseller sales. Value, goal, status. Now, how most key performance indicators actually look, is just like that. Notice that as I collapse, these are accurate. I don't particularly want this grand total, so let's slide over to the Design tab.
And let's turn off Grand Totals. And there now is my key performance indicator, expanded to years, to quarters, for online sales, and reseller sales goals
- Understanding data analysis and business intelligence
- Installing Office BI add-ins
- Searching for online data with Power Query
- Shaping data in the Query Editor
- Connecting to data sources
- Modeling data with Power Pivot
- Enhancing PivotTables and PivotCharts with PowerPoint
- Visualizing geospatial data with Power Map
- Creating and formatting Power View reports
- Sharing your data using Power BI for Office 365