DA-100 Microsoft Power BI – Level 3: 8a. Creating different visualizations: Matrices and bar charts Part 2

6. Clustered and 100% Stacked bar charts

Now, in the previous videos, we’ve had a look at stacked bar and column charts, and the essence of these is that the individual elements get put on top of each other. So this item, which is 17,000, gets put on top of another one, which is 33,000, and together they get to the 50,000 line. But suppose you wanted each to be on an individual space. Well, you can do that with the clustered column charts. So if I just select this particular item and convert it to a clustered column chart, obviously you can also insert one as well, if you so wish. Here you can see the results and you can see there’s an awful lot of information. So I don’t recommend using clustered column charts where there is this much information. If, however, I was to drill down a bit, so that instead of looking at all of the data, we would just be going down to the next level. Here we have the quarters and even the months. That is a fairly reasonable amount of data that can be shown. And here we can see, for instance, the seasonality of Greater Manchester and see whether there is a similar seasonality of Tiny Weir.

Whereas previously, if I just click on do a few times, it was difficult to see the seasonality of Tiny and Weir because the places were the individual bars were in a different place each time, as if I redo that, you can now see they are individual elements. So we can see there is still the same seasonality for Tiny and Weir, just not as much over the summer as there is in Greater Manchester. Greater Manchester goes from 200 to 235 to 255, whereas Tyler Weir, it goes up from 76 to 97,000, but then sort of stabilizes 102,000. So while you have a limited number of items, then this can be good. It requires an even more limited number of items.

If it was a bar chart the other way, but I suppose it depends on how much space you’ve got. If you had it in an individual page, and as one of many visualizations, then it could be that you have more height than width, in which case having it the other way around could work quite nicely. Now, sometimes you really don’t care about how big the individual items are. It’s what is the percentage of the totality? So here we can see everything is rising, maybe. But is Greater Manchester Rising at a greater rate than West Yorkshire or the West Midlands? Well, we can have a look at that using 100% stacked bar charts. What this does is this stretches the totality so that it reaches the very top. So here in 2003, we don’t have that much to stretch, but going back to 1995, we need to nearly double the height to get it all the way to the top. So if I change this to 100% stacked column chart now you can see all of them being at compared in the same ratio. So here we’re looking less at the actual sales volume, but looking more at the percentage.

So looking at this we can see that it’s roughly the same throughout, but there are variances. For instance, West Yorks have gone down to 20%, whereas previously it’s been in the 21%, even getting to 22%. So in 2011 it is the most affected perhaps of the entire six regions that we’ve got by the falling house prices and falling house sales particularly. So this is probably the least used of the bar chart. It’s there when you want to do comparisons between similar items, but you’re caring less about the absolute values which you can see in the stacked column charts and you just want to see the ratio.

 So here I can’t see the individual sales, I can’t see that sales went up between 1995 and 2003, whereas here I can see this quite easily. So we have got now three different types of bar charts and column charts. We have got the stacked variety where they’re on top of each other, we’ve got the clustered variety. So if I go down to a lower level, we can see how that can be used more effectively maybe and we have got the 100% stacked column charts as well. So here we can see if there’s any major variation variances in seasonality. Looking at the formatting, you will see that there are no new categories of formatting to learn in all of these three. So once you’ve mastered one, you’ll be able to find where all of the formatting is in all of the others.

7. Line and area charts, including 8b. Configure duplicate pages

In this video, we’re going to have a look at all of the line and area charts. So I’m just going to duplicate this. So we name this page line or area and go back up to the top. So line and area charts follow very similarly to what we’ve just seen in the bar charts. So, first of all, our standard line charts. So this is each of the individual areas not stacked on top of each other. So you can see Tania, we’re at 24,000. What’s next? South Yorkshire at 27,000, isn’t being stacked on top of each other. The idea of the line chart is that it enables you very simply to see where each of these values go, including if they interact with each other, if they overlap. Now, this is probably when it’s better not to have similar colors. So if I switch it to the default, which it thought it was, but then go into data colors and reset to default. So here we can see, for instance, this line goes into weaving.

And similarly here, this line goes below the line. So it allows you to have these individual items on top of each other if you so wish. You could always, of course, just have one overall item. You don’t actually have to have this subdivision. So here we just have the total number of sales. But if you are having many different lines, then you can see the interweaving that comes into play. Now, the next visualization is an area chart, and it’s moderately successful. And you can see, again, the interweaving that happens, and it does allow for you to be able to see the lines as well. Now, the only problem with this is that each color is not necessarily associated with a particular region. For example, right here, this darker color is associated with the second lineup, South Yorkshire. But now this darker color of the area, that is to say is associated with the second lineup, which is Merseyside. And similarly, this lightest gray is associated with the line second from the top, West Yorkshire, but now it is associated with the West Midlands.

But again, it’s a second from the top. So it’s the older. So it’s moderately successful. It’s better than the XL version in that you can actually see the lines even if they are behind of a line. Now, you can also have stacked lines as well, stacked areas. So here we can see the 20. Well, if we go to left and side, the 33 on top of the 17, again, is right next to the 50 point of the axis. So that’s useful if you want to see the grand total as well as the individual buildups in terms of the formatting. There’s just one new section to have a look at, and that is the shapes section. So we’ve got the stroke width, how thick you want the lines, you’ve got the line style. So whether you want solid or dashed or dotted. But really it’s this stepped one that’s perhaps the most interesting. It allows you to radically change what it looks like. And this is the true for lying charts as well as for area charts.

It looks a bit up to me, but it could be useful if you’ve got one of these perhaps being one particular type and others being another. So, for instance, maybe you have these line charts and you wanted to really show Merseyside as a different type of line. So I’m going to customize this series and I’m going to say that Merseyside is going to be stepped and everything else is going to be normal. And this really draws attention to Merseyside. Not often used, I think, but it’s nice to know that it is there. So if you want something a bit more striking, you can have this stepped function and you can customize a series so that just one particular item gets this stepped. Or perhaps a thicker stroke width, as you wish. All the others are basically the same as what we’ve previously seen. So we’ve seen formatting on the X axis and the Y axis. We’ve seen formatting individual data colors and data labels. It’s really this shapes which is unique to the line and the area charts.

8. Combo charts (Line and column charts)

Now suppose you had more than one measure that you were looking at. So I’m going to start a new page and I’m going to drag or click on the line and stacked column chart on this visualization and what I want to do. So my question is, per year, what is the difference in the twelve month change of flats, also known as apartments, compared to seven detached buildings? So what I’m going to do is I’m going to drag the date onto the shared axis. So this is the axis at the bottom and we have got a column series. So just like the previous series, the legend that we got here, I could drag on region name if I saw west, but I’m just going to leave that blank for the moment. And then it says, well, what are your values? And it gives you two separate values, column values and line values. So what do we want to be represented by? Column. And what do you want to be represented for? A line.

So I want the flat, that’s department twelve month percent change. Here I represented as a column and I’m going to change this. So this is the average. Now, looking at some of these figures, we go all the way up to 27% if possible, that house prices went up by 27%, but it’d be nice to actually see if it did. So I’m going to right and click on it and go to see records, which in more recent versions is called Shaw data point as a table. And you can see, yes, it looks like on average house prices did go up by 27%. So this is realistic. And you’ll also notice a few other things that we’ve talked about in previous levels, in previous videos as well. You’ve also got include and exclude.

So if for some reason you didn’t want to see 2003, 2004, then you can exclude it and it gets removed. But I’m just going to undo that. Now I want to compare and contrast that with the semi detached twelve months percentage change. So I’ll drag that into the line values. So here we can see how these two correlate and you can see we got all the way up to 30% almost in 2004. Just make sure that is the average. There we go. That’s better. That wasn’t making much sense. Notice what happened, it was the sum and it was therefore plotting it because it was such a different figure, it was plotting it on a second axis. So make sure when you drag things in it is giving you something that’s the summation that you actually want. So here 2010, we actually have some positive figures, plus 3% for some of these attached houses. But our flats or apartments are still in negative territory. They’re going down by minus zero point 75% a year on average.

Now we can make this more complicated by putting in the region name. So if I put region name into the column series. Then you can see them being built up on top because this is a stacked column chart, or on the side, because this is a clustered column chart. But notice it only does this for the columns. The lines remain just as the single line. So this is a column series, column values and line values. Personally, I wouldn’t recommend doing this too much. It’s starting to get a bit cluttered, but it is informative. It’s telling you at its peak, for instance, Merseyside Had, oh, my goodness, about 35% change in the flats compared to the overall national change of SEM detached of 27%. So what you can do, of course, is have the same comparator for column values and line values.

So if I drag in again the average of the flat twelve months percentage, here we can see the overall average, and here we can see the individual items, the individual regions, so we can see who is above and who is below. In this case, West Midlands not joining in with the house price inflation at this stage, but then it gets a bit cushioned later on, maybe. As always with these things, make sure that you don’t overdo it. Make sure that you are communicating a message to the end user, preferably not too complicated a message. So this is how you use line and stacked column charts and line and clustered column charts. When you’ve got two separate measures that you are going to compare and contrast, or if you are dividing one measure up into various series.

There’s only one new formatting that we need to have a look at here, and that is in the shape section that we had to look at in the previous video. But we’ve got a shade area section. So if I just click on that, you’ll see that the line gets converted to an area. So even though this is line and clustered column chart, it can also change to an area and column chart as well. If you wish to edit the color, then we’re going to the data colors. You’ll see there’s a separate section for your line, or in this case, an area compared to all of the other region names of the columns that you’ve got.

9. Practice Activity Number 3 – The Solution

So how did you get on with this practice activity? First of all, I asked you to create a matrix so looks very similar to a table but these darkest sections on the right and the bottom. Now I asked you to add year of date created in the Roswell. So we expand date created into the date hierarchy to get year. Then the file extension goes in the columns well so you can see here it is going across and if I focus on this we can see this much more clearly and size in the values and asks you to use the alternating rows style so that’s in format style and the style so alternating rows. Next I ask you to change the formatting of the size so that’s what it displays in millions. So this is something that we did in the previous practice activity but it’s always worth repeating. So if we go into field settings we can change it so that it is in millions. You could see the amount of room, amount of weight that we needed really comes down when we do that. Next, I asked you to add the quarter in to the rows and then you should have a look at all of the various drill options. So we’ve got here drill to the next level in the hierarchy so that will give us quarters only as opposed to years or we can expand one level.

So that gives us you can see 2007, quarter to quarter three, quarter four, 2008, quarter three, quarter four. Here you can’t see it’s 2009 because the actual style that we’ve used gives a white background when you drill down. Additionally what you could do is just expand one particular year. So here I’ve expanded 2008 to quarter three and quarter four. You can’t really see it. So I’m just going to change the Raw headers so that we can actually see that we’re drilling down. So visualizations not only have to contain the right information but you’ve also got to see that they contain the right information. So I didn’t ask you to do this but I’m just going to increase the size of the grid as well just so I can see it a bit better. Right, next, add a second visualization which is a stacked column chart. Now it’s important that you have not selected the current chart before clicking on this otherwise it will change and I want to create a second visualization.

So if you are seeing any fields in these wells then you know you’ve got to click onto the blank desktop. So let’s add another visualization. Here it is, a stacked column chart with file extension in the axis well and size in the value well. And again just for my personal satisfaction I’m going to increase the size of the text here just so it’s more readable for me. Add a third visualization which is going to be an area chart with year and quarter of date created in the axis and size in the values. And then click on expand all down one level in the hierarchy to show both year and quarter. Now, you can’t actually see the quarters here. However, you do know that they’re actually there, because if I go back up to a year, you can see how stark it is. 2010 has one value, 2011 has another.

But when I go down to 2011 quarters, you can see we’ve got multiple values for 2010. For instance, now you’ll notice that there are lots of gaps. For instance, there’s a gap from 2010 quarter two to 2011 quarter three. So here you can see the gap which is joined here together quite smoothly. If you didn’t want it to be joined together smoothly, if you wanted to see the individual quarters without any line in between, then you could go to the x axis and change it from type continuous to type categorical. And then you can see each of these being given a separate heading here. So you might want to have this arranged so it occupies as much width as possible. So this is about designing the dashboard, making sure everything fits. So you might not want as much height for some other things as well. You could just scroll through them. Now let’s see what happens if I was to click, say, on the year 2010. You can see that the visualizations chain.

So in this third visualization here, it filters just down to the 2010 figures, whereas here in the second visualization, it just highlights the bits that are 2010. So it doesn’t actually change the bar, it just changes what is highlighted, whereas this bottom one is filtered for just those particular items. So I’ve asked you to change this so that this second one isn’t highlighted, it is filtered. And the way to do that is to go to visual tool and format. And here we have the edit interactions. So I’ll click on that. So this is when you might want to change the heights of things of all these visuals, so you’ve got enough space to see which of these symbols belong to which. So when I click on this, I want it to filter as opposed to highlight. The other option is to do nothing. So I’m going to click filter now I’m going to deselect edit interactions. And now when I click on 2010, this graph becomes filtered as opposed to highlight. And it’s similarly if you were to click on any of these more refined sets of data in the matrix. If I was to click on m four B, for instance, it would give me just the m four B.

Whereas if I click on 2010, it will give me all three of these file extensions. And then if I want to go out, I just click on any of the white space within this visualization. So I hope you’ve enjoyed this practice activity and I hope you’re starting to see how all these visualizations can fit together into a dashboard. We can have numbers, we can have graphs. We are really getting the building blocks for your dashboard or series of pages of information. But what we’re going to do next is have even more control to these visualizations. So maybe we don’t want all of the information. Maybe we just want to show the top ten items. Or maybe we want to allow the user to say, actually, I want to have a look for this. So we’ll be looking at how to add more control to your visualizations in the next section.