Locked lesson.
About this lesson
KPIs are great for showing you most recent period’s performance versus a target value, with a longer term trend as an area chart in the background.
Exercise files
Download this lesson’s related exercise files. You can download the source data files for the course from the resources section of your Lessons page.
KPIs .pbix461.1 KB KPIs - Completed.pbix
461.5 KB
Quick reference
KPIs
Working with KPIs in Power BI.
When to use
KPIs are great for showing your most recent periods performance versus a target value, with a longer term trend as an area chart in the background.
Instructions
Creating a KPI
- Click the KPI Visual
- Add the measure you want to focus on to the Indicator field
- Add a field to the Trend axis (calendar based fields are ideal)
- Optionally, add a field to the Target goals to compare against your indicator
Understanding a KPI
- The Indicator always shows its value based on your Indicator measure in the context of the most recent value in the Trend Axis.
- The Goal always shows its value based on your Target measure in the context of the most recent value in the Trend Axis.
- The color coding is chosen based on the difference between the Indicator and Target values. So long as this difference is positive, the KPI will show in green (by default).
- It is possible to see a KPI show green, even though the Indicator has decreased over the prior month as the spread between the Indicator and Target could still be positive.
Hints & tips
Common modifications for a KPI
- Provide a custom title for the KPI
- Change the number format of the Indicators (Note that setting a custom format affects both the indicator and the target)
- Modify the KPI color coding settings
Login to download
- 00:05 In this video, we're going to look at the KPI visual.
- 00:08 This is a visual that's specifically dedicated to showing key performance
- 00:11 indicators and there trends overtime.
- 00:14 Now, before we get started, I just wanna call out real quickly here.
- 00:18 I have a couple of visuals already here, ready to go.
- 00:20 I've got a table which holds the end of the month sales and budget values.
- 00:25 So this, if we scroll down, shows the comparison between sales and budget for
- 00:29 every month of the entire data set that we actually have.
- 00:32 I also have a little clustered column chart here that has been drilled down into
- 00:37 the year and month so that we can take a look at what dates we have, and
- 00:41 what our sales are by month.
- 00:42 So of course, I can go and cross-filter anything by clicking on it.
- 00:45 I can hold down my Ctrl key to select multiple data points,
- 00:48 as well, to expand the table and
- 00:50 obviously to control and filter the cards in the top right-hand corner.
- 00:54 What I'm gonna do right now is I'm just gonna unfilter everything, and
- 00:59 now I'm gonna create a KPI visual.
- 01:02 So we'll create the KPI by clicking on the little card with the up down arrows.
- 01:05 And then on my indicator, I'm going to put on a value of Sales.
- 01:10 And at this point, not much happens here.
- 01:12 And if you mouse over that little i, it tells me that we need to have values for
- 01:15 both the Field or for the Value and for the Trend axis as well.
- 01:20 So for the Trend axis, I'm gonna go back and it makes logical sense to go and
- 01:24 pick up something that's based on time like end of month.
- 01:28 And I use end of month because it only shows me the end of month values.
- 01:31 It doesn't show me all the values in the entire data set,
- 01:34 cuz that would be an awful lot.
- 01:37 Now you'll notice that this gives me a value,
- 01:41 16,765 and it gives me this little trend axis behind it as well.
- 01:45 If I go and click on May, you'll notice that we come to $317,367,
- 01:49 which happens to match the card for the total selected sales.
- 01:53 But the trend axis goes away.
- 01:55 If I go and select on April, I get a funny little arrow or line behind it.
- 02:01 You'll notice again, it's 317,367 even though
- 02:06 my sales are now showing at 527.92K.
- 02:10 That's a little bit strange.
- 02:10 So what's actually happening here?
- 02:12 The more I expand this, you'll see that it actually starts to show us the trend of
- 02:16 the individual months that we actually have.
- 02:18 And this probably makes sense because the background should really match what this
- 02:23 pattern is of this column chart that's here.
- 02:26 And if I go and clear it right out, you'll notice again that we're
- 02:31 back down to the 16,765 instead of the 5,242.
- 02:35 So where do these numbers actually come from?
- 02:38 Well, they actually come from here.
- 02:40 What I'm gonna do is I'm gonna come over my table and I'm gonna sort it, so
- 02:42 that it's actually showing in descending order.
- 02:45 And you'll notice that we have the $16,765 right here.
- 02:49 This is the value for that specific month that I've used in the trend axis.
- 02:56 So if I go and click on July, you'll notice that 469,133 is the value.
- 03:01 If I click on multiple different columns,
- 03:04 you'll notice that it's always returning the latest,
- 03:08 even though my card is returning the sum of all of these data points.
- 03:12 So this is kind of an interesting little comparison.
- 03:14 It's just giving me that latest data point.
- 03:17 Now, what else can I do with this?
- 03:19 Well, I have one more field that I can use on my KPI.
- 03:23 And that is a target goal area.
- 03:25 So if I go to Budgets, it makes sense that the budget would be my target goal,
- 03:30 so I'm gonna drop that on there.
- 03:32 And you'll notice that now, it turns red.
- 03:34 And if I look really carefully underneath this, I can see that at 16.765,
- 03:39 and underneath, it's got 253.8K is the goal, minus 93%.
- 03:44 So what does that mean?
- 03:46 Well, what it means is this.
- 03:48 When I go back and look at the unfiltered data, 16,765, and
- 03:53 here's the budget goal, 25,300.
- 03:56 So if I were to go back and say give me July, you'll notice that it goes green.
- 04:02 If I were to go to say, January, it goes red.
- 04:05 But why?
- 04:06 Well, here's the deal.
- 04:08 Let's start with January and February.
- 04:10 It's red.
- 04:12 March is green.
- 04:13 April is green.
- 04:14 May, June goes red.
- 04:18 Now you can start seeing a trend here, that every time the last data point is
- 04:21 lower than the previous one, it's going down.
- 04:25 But that's actually not the real thing that's happening here.
- 04:27 What's happening is that any time the sales number is lower than the budget
- 04:31 number, it's going to turn red.
- 04:34 That's what the real key is in this whole thing.
- 04:36 So those things is a little bit of a red herring there that
- 04:38 you gotta watch out for.
- 04:39 But as we expand this, you'll notice that as the budget's sort of roll up and
- 04:44 down here, we can see that things change here.
- 04:46 So the big key thing to remember is if it's green,
- 04:49 that means that it actually is better than budget.
- 04:51 It's irrelevant compared to the change axis.
- 04:54 So you can actually see something where a number goes down like it has here.
- 04:57 Notice that March is less than February as far as the sales go, but
- 05:01 we're still green.
- 05:02 So that's kind of a key thing to actually watch out with for the KPIs.
Lesson notes are only available for subscribers.