PL-300 – Section 7: Part 1 Level 6: Mapping Part 2

68. Filled Maps, Conditional Formatting, and Color Blindness

A couple of videos ago, we made this maps US. But, whilst these bubbles are good and we had to look at heat maps, wouldn’t it be good to actually have the states be filled in a single colour? And this you can do. And that is called a filled map.

So, very easy, I’m just going to click on this map, and I’m going to change the visualisation from map to filled map. So, now we can see, everywhere is filled.

Now, you may notice that Florida isn’t filled. That’s because there is actually a deliberate typo in the Florida state that we will use later and find out how to correct it. Obviously, the easiest way to correct it would be go back to the data, but sometimes, that’s not a problem.

Now, you might be a bit disappointed with this filled map. Yes, it filled it in but everything’s all the same colour. No problem. What we’re going to do is we are going to colour it based on the population. So, I’m going to drag a population down to the colour, which is not there. Got location, legend, could it be legend? Well, that certainly colours it, let’s face it, but it fills in a different colour for each population, and that’s hardly useful. I can’t really see that California, for instance, is a bigger colour than Pennsylvania or Rhode Island or something like that.

So, no, that’s not what we need. But we need some way of adding in the colours, so it must be in formatting. Right, so we’ve got these colours here, so it’s no problem. So, we’ve go in here and oh, we have a default colour and then we have show all. So, we could change these colours individually but, hang on. That means we have to do the analysis. That’s not going to actually be useful. How can we change the colours so that it reacts to something like population?

And the answer is literally not staring us in the face. Have a look at the right hand side of these data colours. I’m just going to move it up, and here you can see three dots appear when I go over the default colour. So, it is very hidden, very out of the way. Heaven’s sakes, filled maps. This should be your primary colour focus. You should be able to say, you are by default going to do it based on some on other field, but apparently that’s not the case. We’re hiding it away with three dots which are only visible when you hover over default colour. So, click on that, and we get conditional formatting and this new box appears. So, we can format based on a colour scale or rules or field value. Let’s have a look at colour scale. This is the more usual one. So, I want to do a colour scale based on the population, let’s say. And it’s going to sum the population.

There are various options: average, min, max, count distinct, count and median and standard deviation and variance. If there is none, so for instance, Florida. Florida is deliberately empty. What should you do? Well, you could say don’t format or give it a specific colour. So the lowest value is going to be name of colour, so we’re going to say red. We could also say is going to be this particular number. That can be useful when you want to, for instance say, any state that’s got less than 10 million is going to be the same colour. Same for the maximum highest colour.

So, now you can see that there is this differential. We can see that Hawaii is in red, Alaska is in red. Basically, everything’s in red apart from Texas, California and New York is greenish. Okay what’s going on here? Why have we not got more colours? It’s because we have such a huge separation with the data. California have 38 million, Texas 26 million, New York and Florida, which isn’t there, 19 million, and then everything else, 12 million and less. So really, having it by a population is not actually going to be of much use. What we really want is to have it by a population ranking, let’s say. So, if I get rid of that, not in tool tips, in the FX, which is the default colour, and change it from sum of population to sum of population ranking.

Now, we get a much more useful colour scheme. So, California is in red. That’s because it’s ranked one, which now is the minimum rather than the maximum. So, let’s change these colours around so I’ll have a green going to red. So, California is green, and then you can see the most populous states in green, and the least populous states in red. Maybe, not that much of a differential. I mean, all of these states look roughly the same. We’ve got Minnesota and Wisconsin and Missouri all in a sort of light green or lime green or something.

So, what we can do is have a diverging colour set. So, essentially, instead of two colours, it gives us three. So, we can have as the middle value, another colour. And you can see the difference in the colour scheme here. This goes from this sort of green to a green/red to a red. This one has much more variety. So, now when I look at it, for me, the colour scheme isn’t quite right, but it certainly is more informative.

One potential problem all of this is, of course, colour blindness. 8% of males and a much smaller percentage of females have particular colour blindness. For example, red/green colour blindness which really, if it’s red/green colour blindness, that will make this very difficult to interpret, because this colour green, a fairly dark green, would be roughly the same has a fairly dark red. It’s the amount of darkness. So, make sure when you’re designing the maps, for instance, that you have got some idea of colour blindness in mind, and maybe have look at simulating colour blindness.

There are plenty of websites where you can simulate colour blindness, for example colororacle.org. This is a filter which allows you to change what you’re seeing onto the screen for a particular variety or version of colour blindness. So, one possibility could be, instead of making it red/green, make it red/blue. So, if I change this to a blue, this will be a lot more visible to people with red/green colour blindness. Examples of other colours you could use. Here we have a website called colorbrewertwo.com, and you can see, you can have various colour schemes, either multi hue or single hue, and you’ll notice, one of those that it doesn’t have, it doesn’t recommend, is red/green, so if I want to show only those which are colorblind safe, you can see that all of them that are shown are here colorblind safe. And here you can see red/green colour blindness affects about 8% of men and 0.4% of women. So, it’s an important thing, one in 12 men would not be able to see well with a red/green chart. So, have a look at various ranges that you can use and see which one that these might actually look good for the data that you are trying to present, as well as not showing problems for people with a particular colour blindness. So, aside from this conditional formatting, which is really hidden away with these dot-dot-dot (…), then there’s nothing additional in the formatting for filled maps that wasn’t there in maps. However, you won’t be able to use the filled maps with success everywhere.

Now, if we go back to our original maps US, everything was in green. So, we can change this so that it uses conditional formatting again. So, if I just put in the population ranking, you can see that is how you really use colour in a map. But, if I go into maps Afghanistan, for instance, and tried to change that to a filled map, computer goes I’ve no idea what you’re talking about. It doesn’t know that these are regions as well as little points on the map.

Similarly, if I was to go into here, our admins regions, yes, West Midlands is a region, Greater Manchester is a region, but the computer, I’m afraid, doesn’t know where those regions are. It only knows specific locations.

So, filled maps can be quite useful, but unfortunately Power BI is fairly limited in what areas of the world it covers. However, fingers crossed, it covers your area. Give it a go.

69. Creating Hierarchies

Now, in this video, I want to talk to you about hierarchies. So, we’ve had an example of hierarchies in the past when we were looking at dates, for instance, we have a date hierarchy and this hierarchy goes from the widest to the narrowest, so it starts off at year and then go quarter, month, and day. And that is a computer generated hierarchy, also known as an implicit hierarchy.

Now, we have used hierarchies already. For instance, when we had a look at the maps Afghanistan, we have got a hierarchy here of country and then name, and we can go up, and drill down the hierarchy, as we wish.

Now, you’ll notice that these hierarchies aren’t actually hierarchies over here, they don’t have the same indentation as what this date hierarchy does. Why would you wish to create a hierarchy? Well, it’s largely as a guide to the end user, somebody else is going to use your model, for instance all of these fields, and you want to say actually what we have in our data, we have country, then we have state, maybe we’re looking at a series of products so when go from the widest to narrowest, so we have categories and then we have subcategories, and then we have the product, and then maybe we have the product for a particular colour. So narrowing down each time. So, it’s just a way to indicate what a particular hierarchy should be. For instance, if I said, “Well, the hierarchy is state and then it goes into country.” Okay, that’s the wrong way round, it goes from country to state. So, you can state the order of the hierarchy. So, let’s do this for this Afghanistan model. So, I’ll get rid of country and name two. And in the Afghanistan source, I’m going to create a hierarchy. So, to create a country hierarchy, then I can click on it and go to the dot-dot-dot (…), or I can right and click on it, both will work, and click on create hierarchy. So, this creates a hierarchy with just one field.

Now, when I want to add a second or third or fourth field then I click on the dot-dot-dot (…) next to it. And I can go to add to hierarchy and then select which particular hierarchy you want.

Now, suppose you’ve got these in the wrong order. It used to be you could just drag them up or down or could click the dot-dot-dot (…) and there’d be a move up and move down, that’s no longer the case. Instead, what you have to do is go into who the model tab on the left-hand side, select the hierarchy. So, here I’ve got the country hierarchy, and in the properties pane, so expand it if you can’t see it, scroll down and you can see I’ve got these two different levels, country and name two. So, I can drag these up and down and then apply the changes when I’m done. So, now you can see that name two comes before country, and move it down and it’s changed back.

Now, I can also select another field or another column to add another level, so if I wanted number of people to be a level, and click apply level changes, then there it is. And if I want to remove it, I can click on the X and that gets rid of it from the hierarchy.

Now, it’s important to realise that doesn’t remove it from your overall number of columns that you’ve got here, it just removes it from the hierarchy. So, if I add number of people again, and if I go back into my report view, another way of doing that is by clicking on the dot-dot-dot (…) next to a field you no longer want to be part of a hierarchy and then Delete from Model.

Again, that doesn’t remove the original field, it just removes the one which is in the hierarchy. It’s a good idea to name this as a particular hierarchy, so this could be like the location hierarchy.

Now, this can lead to a problem. You have got a country hierarchy here and a country hierarchy here. What do you do? Do you really want to have the end user have two different countries to choose from? Most of the time, you’ll probably say no, that will just confuse them, and so what you should do is hide all of those measures or all of those dimensions, descriptors, that are now part of the hierarchy that you don’t want to be part of separate items. So, now I can drag the hierarchy into the location, and it works just as we’ve done before. So, previously, we had to drag country and then the name in, now I just need to drag the hierarchy. So, I can now drill in, expand all levels, drill out as before.

Now, there is one particular little thing you do have to bear in mind. Let’s say I go to this US country, and you’ll notice that we’ve got country and states, not currently using country but if I were to drag that in then it would function as a hierarchy. So, we’ve got country and state, but notice that in Afghanistan, we’ve got these little globe symbols and we haven’t for the country and the state.

Now, you may remember that we got these little globe symbols by clicking on a particular item, and then going to Modelling in the older versions or Column Tools in the later versions of Power BI, Data category, and saying what sort of category it is whether it is city, consonant, country, county, or that sort. And you can see that at the moment I could change the data categorization. What I’m going to do now is I’m going to create a hierarchy. So, here’s my hierarchy with country and state, it’s called country hierarchy, obviously it’s not, it’s, again, a location, so I’m going to rename that as location hierarchy again. Again, probably bad idea to have two hierarchies exactly the same name so I’ll call that US Location Hierarchy. So, what I’m going to do, I’m going to hide country, I’m going to hide state, and now I want to have these little globes, I want to do the categorization, so I’ll click on country and I’ll go up to Modelling or Column Tools in Data category, and I’ll click here and it’s not working. You cannot categorise items which are part of a hierarchy or in the hierarchy. So, what I’ve got to do is we’ve got to unhide all, so that gets me the country and the state, and from the country and the state there I can categorise country, and then you’ll notice that country is now categorised in both locations, and state I can categorise, and it’s only then that you can hide it.

An alternative to unhide all is View Hidden and you can see the hidden ones there, now you can afterwards deselect View Hidden. So, I’ll hide this and hide this.

So, hierarchies don’t just have to be used for dates, they can be used for anything which goes from a big wide categorization like country which contains, in the case of the United States, 50 states, down to the state, down to even closer, like for instance a city or maybe a county and it can go further down postal code and so forth but it can also be used for objects and anything else that you can have various categorizations.