PL-300 – Section 8: Part 1 Level 7: Measure Performance by Using KPIs, Gauges and Cards

73. Gauges

In these few videos, we’re going to go back to our HPI admin data and just create a little dashboard. And the purpose of this dashboard is to introduce another set of visualisations. So, we’re going to start off with gauges.

Gauges are semi-circles which show how much you have done towards a certain target. So, over here, this is a gauge, so if I click on that and I will focus on it. So, what I’m going to do is I’m going to measure the average terraced price. So, this is a house, it could be an America town house. It has neighbours on either side, as opposed to semi-detached which only has a neighbour on one side. So, I’m going to add this terraced price into the value and you can see this is not the price for a particular house so I’m going to change it to the average. So, we have an average of 80,000 pounds or dollars.

Now, you’ll notice that the gauge is half-full. We start at zero and go all the way down to twice the current value, and that is intentional. So, what I could do is say, “Well, I want the minimum “to be a certain field and the maximum to be a certain field.” So, maybe I want the maximum to be the detached price, so I’ll drag detached price into there, you can see all the colour disappears because, again, I need to change the aggregation. So, we now have a maximum of 166,000 pounds or dollars and that is the detached price average.

More likely, however, you may want to actually just fix the minimum and the maximum price because let’s have a look at what’s happens if I take this out and I put in a slicer. So, let’s add a new visualisation, a slicer, and this is going to be based on the region names, I’ll add region name into there. And I will make it a lot bigger because I can barely see it. So, go into items and expand that, there we go. So, we have this gauge and if I click on Greater Manchester you can see that the average is 77,780, for Merseyside, 73. But wherever I click, this is always going to be halfway across the circle, that doesn’t really inform me. So, instead, I’m going to go into the formatting, into the gauge axis and I’m going to manually put in my minimum, my maximum and my target. So, let’s say I have a minimum value of 50,000, a maximum value of 150,000 and I want to see how I’m doing to my target of getting the average house price to 80,000. And you can see the average house price here for these terraced houses is being represented by a line. So, you can see that I’m over my target in Tyne and Wear and West Midlands, I’m at my target in West Yorkshire and I’m below my target in Merseyside and South Yorkshire.

Now, it could be that you want a variable target, maybe you want the target to be the semi-detached price. For some reason, you want to make the terraced price the same as the semi-detached price. So, I’ll put this into the target value and nothing seems to happen, again, it’s the aggregation that’s the fault. So, here we can see, on a scale of 50 to 150, that in Merseyside the average price is 73,000 and our target, the one we’re trying to get to, is 104. And by having fixed minimum and maximums, then you can see the relative performance of each of these regions.

Now, looking at the formatting, so we’ve had to look at the gauge axis, data colours, the fill and target, you can do conditional formatting with those, as well. The data labels are the 50,000 and the 150,000. The target is this line here, and with these things you can change the colour, you can change how it’s displayed in terms of units. The call-out value is this one, so you can either switch it off or you can say, “Well, actually I want this to be displayed in exact.” So, number of units, none. I don’t want it to be in thousands, or millions or billions or trillions. And I could also, potentially for some types of fields, say I want it to be in so many decimal places, as well. But for me, saying it’s in 73.73K, 79.73K is fine.

So, these are gauges, they have an actual value, they have a target value, and they also have a minimum and a maximum. So, gauges, it’s for measuring progress towards a certain target, and sometimes you might be above that target, remember that target was 80,000. We were above that target.

74. Cards and Multi-Row Cards

Now, sometimes you might just want a single figure to be shown. For example, you can see that this big figure here is quite prominent. Maybe, that’s all you need and you don’t actually want the gauge. Well, that’s no problem. We can use a card.

So, a card is a single figure. It needn’t just be a figure, it could be a date, or it could be text. So, for instance, it could be the region name. So, if I drag in region name, you can see here, Tyne and Wear, the first region name, and in fact it’s the last region name. I can select additional items in the slicer, but you can see it’s just providing the first region name. And I’m getting those additional slicer items by holding down control while I’m doing this.

But quite often it’s used with numbers. So, if I remove, you can see the other options are last, count distinct, and count. And I put in this was the terraced price, and change it to an average, there we can have the actual figure without needing the gauge.

Now, it may be that you want to put in multiple items and that’s fine as well, but you can’t put in multiple items in a card, it will override the field. Instead, what you need to have is a multi-row card. So, a multi-row card allows you to insert multiple items, so let’s put in the sales volume, let’s put in the average of the semi-detached price, and let’s put in the average of the terraced price for instance. So, we just need to do things with these data labels, make them bigger for instance. We can change the outline of the card. You can make it a frame. You can change the size of the category labels, because quite frankly, I can’t read them. That’s a fair bit better. But I still can’t read them now, it still says average of semi-detached price. So, let’s go into this field and click on rename. So, semi-detached, and terraced, and sales volume, it just needs a space in the middle. So, I’m going to have this as an example of a multi-card gauge. Sorry, a multi-row card. I’m also going to put in a standard card as well, and put in the… Let’s put in the average 12-month percent change. So here we can see for Tyne and Wear, on average it changes 6.33%. South Yorkshire, 6.6, and so forth.

Now, what we might want to do is have particular colour schemes when it’s hot. So, maybe, we’ll have another slicer which is going to be the year. So, let’s have a slicer, put it up here, it’s going to show the year. So, if I put it at the hottest part, for instance, we’ve got here 14%, that’s really hot. So, let’s add some conditional formatting to make it hot. And we’re going to do that in the next video, along with having a look at our humble table, and we’re going to, again, put in our years, and we’re going to put in the rate of change.

Let’s get it much-much bigger, so I can actually read the thing and imagine that you can read the thing as well. And so what we’re going to do is we’re going to add in conditional formatting into both the card and into this, which, again, should be an average. That’s better.

So, we can see when it gets hot, and when it gets a fair bit cooler. And we’ll do that in the next video.

75. More Conditional Formatting

So, in a previous set of videos we had a look at conditional formatting, why it involved filled charts and that usually was going to a colour and then clicking on this dot-dot-dot (…). You can also do it by right-hand clicking on the colour, which, again, I don’t think is particularly obvious. So, we had a look at the colour scale. But let’s have a look at a different item, formatting by rules. And we’ll be looking at field value much later on, by the way.

So, looking at rules, if the average of the Terraced 12 months’ percent change is, and this is why we can put stuff on. So, I want to say if it is greater than or equal to a particular number, as opposed to a percent. So, if it’s greater than five, maybe, actually, if it’s greater than 10, and less than, you can see that there is currently a problem with this logic, so if it’s less than 999. Okay, problem in logic goes away, then it’s too hot.

Now, if it is greater than two, and less than 10, then, now notice I can put in overlapping rules if I so wish, but it will basically do one colour. So, that is just right. And, if the value is greater than or is greater than or equal to, and you can see the fairly, the other options. So is exact, is blank means what it says, so if this is greater than, well, I can’t say this is less than two.

So, I will say if it’s greater than minus 999 and less than two, then it’s good cold. So, we’ve got the Goldilocks Syndrome. So here we can see it’s way too hot. If we go just for a few years, it’s greater, now we can see it’s getting cold and now it’s way too cold. Get a bit later, and we can see right now, what did I, it looks green, I think that is green. It’s just right, though I can’t actually see it that easily, the colour. So, with these colours, because yeah, I can’t really distinguish the blue and the green that easily. And don’t put it necessarily on the fore colour. Instead, I put it in on the background. So, let’s put the background on and that colour is going to be exactly what I just put in. So, based on rules, if the average of that is greater than 10, let’s say, and it’s less than that, then it’s too hot. If it’s greater than two, and less than 10, then it’s just right, and if it’s greater than minus 999, and less than two, then it’s too cold.

You could have a bit where it’s neutral. So, it could be it’s less than zero, and that would be neutral. So here, we can see it’s the green colour. Here it’s too cold, here it’s too hot. So that gives a very striking visualisation of your target, of your figures. Is it too hot, is it too cold? I can see that without even looking at the number.

Now, we can also do the same thing with this table. So, maybe, the table I want, well, I want one of these things to be a particular colour, and I could format the lot in any particular colour, but I want one particular field. So, what I do is I go to that field and I click on the drop down or I right-hand click on it, and I can select conditional formatting that way. So, again, this is probably one where the fore colour actually works as well as the back colour. So, if I put in exactly the same rules, and I’ll just pause the video and do it, so exactly the same rules as before. You can see we’re getting a bit on the too hot side in this time period. And if we go further down, then we get into the coldest, especially when we’re in the negative territory.

What we could also do with the conditional formatting, is add in bars. So, it’s not just one set of conditional formatting, I can have data bars. So, these data bars can show whether things are in the positive or in the negative. And you notice I didn’t have to set anything up to get there. So, we can see it’s really heating up in 2004. And just like any table, I can click on something to restrict the information to that particular time period.

Additionally, I could put on icons. So, if I go back to here and go to conditional formatting icons, you can see the sort of icon styles that are available. So, we can see when it’s in the top 33%, the middle 33%, and the bottom 33% of the values.

Now, what happens if you’ve got two overlapping values? For instance, we’ve got some figures which are 9%, what happens if instead of having the first conditional formatting and to modify the conditional formatting you just go back into conditional formatting again. Instead of it being greater than or equal to 10, what if it was eight? So now this red will take priority, maybe? Because of the numbers which are nine is greater than? So does it stop here?

And you can see, no it doesn’t. It continues through. So, if I wanted this one to have priority, then I need to move it further down so it gets towards the end. And now you can see nine is too hot. So nine is both too hot and just right. So, it’s whichever one comes later that has the last figure. So, you can picture it that it goes green and then it goes red. Only one of those colours will happen and it will be the last one.

Now, you could have a gap in the middle, like that. So, now nine doesn’t have any conditional formatting whatsoever so it becomes black. Or, more accurately, it remains black. If you want to remove any of the conditional formatting, then you can do that with the remove conditional formatting here. Or if you’re talking about conditional formatting on the background, for instance, then you can say revert to default.

So, conditional formatting, it’s a great way, a very quick way of highlighting information. So, it’s only needs a little bit of set up if you’ve used Excel, then this will be fairly self-evident of what to do. And you can do it in either a color-scale, so that says, when you’re at the bottom or the middle or the top of a particular set of values, or you can do it by rules, and this is when you can specify the exact number that you want it to be hot, cold, or just right.

76. KPIs

Now, the final performance measure in this particular section is the KPI, key performance indicator.

Now, a key performance indicator, it allows you, just like gauges and just like conditional formatting, to compare the progress of one particular measure against something else. Generally, another measure.

Now, what I’m going to do is I’m just going to shrink this down just a little. It’s a bit on the big side for what we want to do. And then it can go vertically. So, a key performance indicator measures something over time. So, you’ve got measurement and you’ve got some sort of time display and then you’ve got some sort of indicator, some sort of target. So, I’m going to insert the key performance indicator here and here you can see Indicator trend so that’s your time generally. It could be years, it could be months, it could be anything else you’re measuring against in terms of a continuous set of values and there’s your target. So, I am going to put in the number of sales. I mean, the average price for the, let’s have a look at the TerracedPrice. So, there’s my indicator. I need a timeline, so I’m going to put in my year into the trend and here you can see it is an axis. Again, we need to change the indicator there to be an average.

Now, what target shall I set? Well, we have an average price here. So, I’m going to put in the average price and then make it the average which it is as the target. So, here you can see we have a target of 127,000 pounds and we got to 106,000 which is below target.

Similarly, if I was to replace the indicator from TerracedPrice to SemidetachedPrice, and average it, there we can see we’ve reached our target by four point something percent. Or 133. And if I change it to the Flat or ApartmentPrice, obviously we’re way below. And the DetachedPrice, we’re going to be way above.

So, let’s have a look at what formatting tools we’ve got. So, we can see it’s basically just a area chart without any of the labels. So, we have an indicator here. So again, just like previously, we can say we want it to be exact or we want it to be measured in thousands or in millions. We have a trend axis. So the on/off shows the background. So, you can still have a KPI with the background off because you will have the KPI being shown in the various colours.

Then, we have a goal, so the actual number of the goal and the distance or the percentage from your actual to the target and then colour coding. So, we have a good colour, a neutral colour and a bad colour and whether high is good. So, high’s not necessarily going to be good. Suppose, you were reporting on staff absences, well, then low would be good. So, here you can change what colour good, neutral and bad is.

So, KPIs. They allow you to have a graph at the back, a number at the front and the colour is a sort of conditional formatting like we’ve got over here which allows you to compare against something else. So, those are KPIs.