DA-100 Microsoft Power BI – Level 7: 6. Measure performance by using KPIs, gauges and…Section 8: Level

1. 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 visualizations. And this is specifically mentioned in the Power Bi exam as measure performance by using KPIs gauges and cards. So we’re going to start off with gauges. Gauges are semicircles, 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 this. 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 in America townhouse. It has neighbors on either side, as opposed to a semi detached, which only has a neighbor 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 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 color disappears because again, I need to change the aggregation. So we now have a maximum of £166,000 of 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 happens if I take that out and I put in a slicer.

So let’s add a new visualization, a slicer. And this is going to be based on the region name. So I’ll add region name into that and I will make it a lot bigger because I can barely see it. So, going to 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 of 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 Tina, and we are. 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 a semi detached price. So I’ll put that 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, 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 colors, 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 color, you can change how it’s displayed in terms of units. The callout 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 field, say, I want it to be in so many decimal places as well. But for me, saying it’s in seventy three point seventy three k. Seventy nine point seventy three k 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 measuring progress towards a certain target. And sometimes we might be above that target. Remember that target was 80,000, we were above that target.

2. 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 have to want the gauge. Well, that’s no problem. We can use a card. So a card is a single figure. It needs just to be a figure. It could be a day to, it could be text. So for instance, it could be the region name. So if I drag in region name, you can see here tiny, and we are the first region name and in fact it’s the last region name. I couldn’t select additional items in the slides, 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 overwrite the field. Instead, what you need to have is a multirole card. So a multirole card allows you to insert multiple items. So let’s put in the sales volume, let’s put in the average of the semidetached 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 a difference, 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 semidetached price. So let’s go into this field and click on Rename. So semi detached and terraced and sales volume. We just need the space in the middle. So I’m going to have this as an example of a multicard gauge. So multi raw card. I’m also going to put in a standard card as well and put in the let’s put in the average twelve month percent change. So here we can see for ten, we’re on average it changes 6. 33%, south Yorkshire 6. 6 and so forth.

Now, what we might want to do is have particular color 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 bigger so I can actually read the thing and imagine 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.

3. More conditional formatting

So in a previous set of videos we had a look at conditional formatting why to fold filled charts and that usually was going to a color and then clicking on this dot. You can also do it by writing clicking on the color, which again I don’t think it’s particularly obvious. So we had a look at the color 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 twelve months percent change is and this is where 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 ten and less than you can see that there is currently a problem with this logic. So if it’s less than nine and nine, okay, problem with logic goes away, then it’s too hot.

Now if it is greater than two and less than ten, then now notice I can put in overlapping rules if I so wish, but it will basically do one color. 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 option, so is, is exact, is blank means what it says. So if it is greater than, well, I can’t say it’s less than two. So I will say if it’s greater than minus nine, nine and less than two, then it’s too 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 put? It looks green. I think that is green. It’s just right that I can’t actually see it that easily the color.

So with these cards, because I can’t really distinguish the blue and the green that easily, I don’t put it necessarily on the four color. Instead I put it in on the background. So let’s put the background on and that color is going to be exactly what I’ve just put in. So based on rules, if the average of that is greater than ten, let’s say, and is less than that, then it’s too hot. If it’s greater than two and less than ten, then it’s just right. And if it’s greater than minus nine and nine and less than two, then it’s too cold. You could have a bit where it’s neutral.

So it could be less than zero and that would be neutral. So here we can see it’s a green color, here it’s too cold, here it’s too hot. So that gives a very striking visualization 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 color and I could format the lot in a particular color, 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 and click on it and I can select conditional formatting that way. So again, this is probably one where the four color actually works as well as the back color. 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 two hot sides in this time period. And if we go further down, then we get into the cold, especially when we’re in the negative territory. What we can also do with the conditional formatting is add in bars. So it’s not just one set of conditional formatting.

I can add 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 we’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 the conditional formatting again instead of it being greater than zero equals ten, what if it was eight? So now this red would 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 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 colors 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. And 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, very quick way of highlighting information. So it only needs a little bit of set up. If you’ve used used Excel, then this will be fairly self evident. What to do? And you can do it on 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.

4. 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 source 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 could be years, could be months, 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 terraced price 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 access. 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 and we got to 106,000, which is below target. Similarly, if ours to place, replace the indicator from terraced price to semidetached price and average it there, we can see we have reached our target by four point something percent 133. And if I change it to the flat or apartment price, obviously way below and it is attached price, 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 an 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 colors. 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 color coding. So we have a good color, a neutral color and a bad color, and whether high is good. So high is 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 color good, neutral and bad is. So KPIs, they allow you to have a graph at the back, a number at the front, and the color is a sort of conditional formatting like we’ve got over here, which allows you to compare against something else. So those are KPIs.

5. Practice Activity Number 7 – The Solution

So how did you do with this safety Awareness dashboard? So what I asked you to do was to lord from power bi the PA patrol activity driving safety spreadsheet. And we can have a look at this spreadsheet if you want. In the data you can see we have the state information over a twelve year period. We’ve got from 2007 all the way up to 2018. We have got the millions of miles driven, the number of fatalities and the calculation thereof, and the target.

So for instance, for Alaska in 2007, the target was about there were unfortunately 30 more fatalities than the target. And then I’ve got a final column with the 2018 figures. So let’s see what we can do. First of all, we need a slicer. So this slicer is going to contain the state. And I only want you to be able to select one state at once. And I do want you to select a state. So we go into the formatting and the selection controls and say single select on. And there’s something quite subtle that happens when you do that. And you’ll see better if I increase the text size and do that, the squares change the circles. And there is one state selected which wasn’t the case originally. So now we have got a single state selected and you can change that just by clicking wherever you want. Now we want a card which shows this state selected. So let’s find the card, this one. And again, that can have the word state in. And as you can see, we’ve currently got Connecticut.

I’m going to use this as the title. So it’s up there and I’ll have a nice background. So that’s my state selected. I want a gauge which shows at the very latest in this particular example, 2018 figure, so we can see how we’re doing against the target. So let’s get the gauge. And the actual value is the 2018 figures. So there we are, zero 93. The gauge should go from a value of zero to 2. 5. That is not the default. The default is it goes from zero to double the current value. So in the gauge axis I’m going to put go from zero to 2. 5 and the target is 1. 2. And so we can see a line for the target. Now the color of the gauge should be conditional on the value.

So let’s go down to the data colors. We have the fill here and the three dots over here. Conditional formatting. So if it is note . 8, it’s one color. If it’s 1. 2, it’s another. If it’s 2. 5, it’s another. So we’ve got three different colors there. So we’re going to use a diverging scale. So it goes from zero eight to 1. 02 to 2. 5. And so we can start with a nice blue color to a yellowish color to a red color. So I’ll choose a yellow here on this scale. So click OK. So this Connecticut state zero point 93. It’s fairly good. 1. 17, that’s about on the target. Then we’re getting into significantly higher than target territory. And the District of Columbia doing really well. And I think if we go down to Massachusetts really well. So let’s put a nice header on this background color center it, just take it away from the title, just fractionally. Now we need a map showing the state selected.

So let’s get a map. Now, you’ve got two options. You’ve got a map or filled map. If you use just the map, then it will show a dot, whereas a filled map will show the state filled in. So let’s add the location as the state. And so here we can see Massachusetts. It doesn’t quite look like Massachusetts, but there we go. There we are. It is going over the water. That surprised me. Now again, we want to color it the same color as the gauge. So let’s go into the data colors. Again, we use the same conditional formatting but we don’t want it based on the counter state, we want it based on the 2018 figures. And again diverging from a custom value of zero eight.

So anything below null . 8 will be done in this blue color, 1. 2 and highest value of 2. 5. The 2. 5 was this figure in some states in 2007. So you can see it has really gone well down from that in previous years. So we’re starting to get there. We want a KPI. So I’m going to move this map up here. We want a KPI, which I’m going to put at the bottom and it’s going to contain the indicator as the fatality rate. Also the actual fatalities. So let’s do that. Just finishing off this small bit there. So go to the KPIs. So that’s this one. So we’ll have this going across the bottom because it needs a lot of weight because of the years that it’s going to take up. So fatalities in going across, we have the trend access being the years and the target being in this case the average of the target for fatalities. So that’s this target here. Now just something to point out, 31 fatalities. According to this, the goal was 37. 64, means we were better than target but is being shown in red. And we need to change that because if it’s the lower figure that’s better. So we’re going to the goals and we change this to decreasing is positive and in the color cording law is good.

So now if we click we can see which states are doing better than the target and which are not. Now we also have two cards and I’m going to put them right in the middle here, roughly centered, showing the total number of miles driven. So let’s add a card in over here, put it roughly centered but a little away from the others so we can have a unifying background of this color. And I think a title as well would be nice. Miles driven, millions of miles driven. Enter that and then I’ll just copy and paste that so I don’t have to recreate any of the styling.

And this next one’s going to be number of fatalities. I’ll just move it fractionally down. So it is there on the top of the gauge. So it’s looking now fairly informational, but there’s only so much you can cram into a single page. And therefore I wanted you to create additional pages which we can link into this page. So if we rename this as the dashboard and we have another page, which is just a simple chart and has the number of fatalities over time got it the wrong way around, that’s better. Again, just some formatting. But the color, it’s a default blue color, doesn’t really work well.

It doesn’t give me any information. And so I’m going to change the data color. And what we’ve got to do is it needs to be based first of all on the fatalities figure. And the lowest figure is going to be a light purple, and the highest figure a much darker purple. So we can see what is going on then in terms of the colors. So the colors really tell us something as well. Now I’m going to add to this page a drill through. So the drill through is going to be the state. So there we go. And I’m going to rename this as fatalities and I’m going to add to the top there’s the little back button that happens when we have a drill through. So I will just make it a bit more prominent. And I’m going to also add this state there as well. So what I can do is I can right and click on the map and say drill through to fatalities. And there we have the fatalities for Connecticut. Then if I control, click on the button and it’d probably be useful if you had some instructions on how to use this. Here is Georgia. So I will drill through and there we have Georgia. So again, I’m just going to just do some minor formatting changes. So what I’m going to do now is duplicate this page and this is now going to be miles driven.

So instead of that, we have millions of miles driven. Let’s just make sure the data colors are right. They look rightish. Let’s just make sure that they are not sum of fatalities. So always worth checking. If you do this as a shortcut, there are some minor changes there. And again, we’ve got a back button and I’ll duplicate this page again. And this is the fatality rate. And I’m going to change this. That should have been the rate there. I’m going to change this from a stacked column chart to a line and clustered column chart.

So at the moment it looks the same, but we’re going to put a little line across where it is. One, two, so to do that, I need to add in a new measure, so a new column, and this is going to be, let’s say target 1. 2, and it’s just going to have the figure 1. 2. So now I can drag this target into the line measures. So there is my line, and let’s have that as a black color. And I’m going to also increase this y axis font. It’s a bit smaller. So we now have a working dashboard. And while we’re now 14 minutes into this video, so we can click on a state, we can see the state, see how it’s done, see how it’s done over time. And I can click on the map and have a look at various different aspects of the state’s figures.

And I think what I’ll also do is I will change the data colors so they are representative of the data colors of the other map, of the other dashboard we had, with all of these greens, blues going into greens, going into yellows and going into reds. So I’ll just change this. So these data colors start off again, diverging start off with a blue. When we have a figure of zero, eight goes into a yellow figure. When we have a figure of 1. 2 and 2. 5, we have red.

So it ties a connection to this. And it’s quite useful when we are looking at different states. So if I go to district of Columbia, for instance, we can really see how well the driving has improved in various states. So again, all of this is focused on what is the message you’re trying to give to the end user. Well, I hope you found this dashboard useful, and I hope you found the entirety of part one of this course to be useful in creating your own dashboards on your own topics. In part two, we’re going to have a look at get data, and there’s a lot of depth into what we have just skirted over in this part one.