PL-300 – Section 4: Part 1 Level 3: Creating different visualizations: Matrices and bar charts

29. Matrix

Hello and welcome to level three. And in level three we’ll be looking at different types of visualisations and we’ll be concentrating on bar charts and area charts towards the end, line charts but we’ll be starting off with a matrix.

So, what is a Matrix? Well, if you have got plenty of experience with Excel, then you will know them as Pivot tables. So, if we have a look at our existing table matrix, you’ll see that it has a range of values. In this case, region name, sales volume and three different types of average price. And they just go from top to bottom, or, individualization, from left to right.

Now, matrix is different in that it may have the same or similar type of values but they’ll be located in different places. Some will go from down the left-hand side, some will go across, and some will go in the middle.

So, now let’s create a new visualisation, a new matrix and we’ll see what the way is. So, I’ve deselected my visualisation because I don’t want to change my existing viz, I want to create a new one. So, here we can see the new visualising and you can see what we have got in the Fields pane is different. It used to be, we had Values and that was it. Now, we’ve got Rows, Columns and Values. So, suppose, I wanted to know, per year what is the total number of sales for each region? So, there are three elements to this: per year, total number of sales, each region.

Now, the thing which is summarised, the thing which is aggregated, the thing which is totalled, that goes into the Values. So, out of those three, which would go into the values? For each year, I want the total number of sales per region. And that would be, total number of sales. So, I’ll go and drag SalesVolume into Values. So, there we can see our total sales volume 3.795 million, exactly what we have here in our first visualisation.

Now, obviously, bit too small, let’s go and change that. Go into the Grid, scroll down to near the bottom and increase the text size. Right, so that’s our Values.

So, what is Rows and what is Columns? So, what is left? For each year, or the total number of sales volume, per region. So, one’s going to be region name and one’s going to be the year of the date. So, which goes in rows and which goes in columns? And the answer is it’s really down to you. My advice is to have that which is longer going down. Suppose, there was 100 years, then I would have that going down as opposed to going across. So if we’ve got 100 years and only six regions, then I would have the regions going across and the years going down. Alternatively, if I had only six years and six regions, I would probably have the years going across because the names, the size I would need for each region is longer than the size I would need for each year. 2010 is a lot shorter than greater Manchester.

So, let’s just try it and see what happens. So, I’m going to put region name into columns and instantly, you can see that this 3.795 million has been divided exactly as we’ve got in the first visualisation. But then when I drag in date into rows, you can now see that it is split up into both regions and years. So, if I just resize this fractionally then you’ll see that we have got totals for each region and totals for each year.

Now, you’ll also see that we’ve got this scroll-bar because we can’t actually fit it all in into one particular visualisation and that might not be a bad thing. It might be fine. It might be what you want. It could be you want stuff in that and you do want to scroll on and just have the ability to look at each individual year rather than display it all on one particular screen.

Now, if you do find you actually do want it full-page, well, then obviously you could use the focus mode and have it a bit bigger, but that’s only a temporary solution. Alternatively, you can add a new page. And I’m just going to copy this visualisation, Ctrl + C, into this new page, Ctrl + V, and then I can have it occupy the entirety of the page like that.

Now, let’s see what would happen if I get rid of date. I’ll click the X next to it. Then get rid of region name and I’ll drag region name into rows and date into columns. Would that work better? Now, for me, that doesn’t. I much prefer the other way where we’ve got a limited width and a fairly long height. But it’s entirely down: A) partly down to personal preference; and B) what are you going to do with the data? And C) what data do you have to begin with? So, I’ll just reverse that back. Let’s get rid of the date, drag region name down to columns, drag date across.

Now, as we will do for all of these new visualisations, let’s have a quick look at the Format pane. And, you’ll see that there’s really not that much difference between a Table and Matrix. You’ve now got Subtotals as well as the Grand total. So, you can switch the Subtotals on or off. And same for the Column subtotals. And there you’ve got your Grand total right at the bottom. But, the vast majority of all of these formattings remains unchanged.

So, now we’ve spent so much time on the table visualisation that you really don’t need to spend much more time on the matrix visualisation. You already know how to do all of the formatting to add a title, to change the style. So, all of those things we’ve covered in previous videos, we don’t need to recover them. So, we spent a lot of time on tables but now all we need to do is spend a very short time on matrices. However, there is one more thing that we need to look at and we’ll look at it in the next video. And that is, what are all of these icons? They weren’t there in our table. What are they doing here?

30. Drill Down Data, See Data and Records, and Export Data

So, what are these buttons that have suddenly appeared in this new visualisation? Well, it’s not unique to matrices, but they don’t appear in tables. And the reason why they’ve appeared is, when you have a look at the row section, we haven’t just dragged in the date field, though that is literally exactly what we have done, with that, we’ve also dragged in year, quarter, month, day. Now, these are different parts of the date and they form a hierarchy.

Now, we’ll have a look at hierarchies later, but there are many days in a month, many months in a quarter, many quarters in a year, and many years characterise the entire date field. Now, if we didn’t want all of that, suppose we only wanted the year field, then what we can do is, we can click on X next to the quarter, month and day. Now, you’ll notice nothing has happened. The visualisation looks identical, apart from the fact that those buttons have disappeared. So, let’s see what those buttons are and how they could be useful.

Now, the first button I’m going to have a look at … Oh, before I do, we should point out, that as we’ve got this visualisation selected, we can see the description of all of these in Visual tools, Data/Drill.

Now, firstly, I’ll start on the right-hand side and have a look at this. This is the expand next level. Notice, the left-hand side, we are seeing the year, 1995, 1996, and so forth. If I click on Expand next level, we now go down the hierarchy showing not just the year, but the year and the quarter. So, now we have 1995 quarter one, quarter two, quarter three and so forth. Click it again. And we can click down on this button as well, the Expand next level, we go down to months, and click again, we’d go down to days, though, in this particular case, they’re all on the first of a month because they represent months. Now, to go back up, we can click on the up button or drill up, and you can see we’re going up the hierarchy.

So, if that’s what Expand next level is, what does Show next level? Well, instead of showing year and quarter, when I click on these two down arrows, it just shows quarter, so it moves down a level. So, this figure for quarter two, Greater Manchester, isn’t quarter two for any particular year, 1995, 1996. It’s for all of the years. And you can see this a bit better if I click on see records, or Data point table, and then select a particular intersection, a quarter and a region name. So, this gets us into something similar to the focus mode, but here it’s going into the data and is filtering the data for everything that builds up to that particular figure. So, 234,978. So, all of those, when added together, make up 234,978.

Now, if we wanted to test this, say we weren’t sure about the mathematics, you can click on the dot-dot-dot (..) and export data. So, this will export it as a .csv file. So, I will call it ExportofData. go into Microsoft Excel, open it up. So, going to my resources. There’s … You won’t see it at the moment, because it’s showing all Excel files. I’ll change that to all files, and now we’ll see the ExportofData. It’s delimited by commas. You can see that in the demonstration at the bottom. So, check commas and here you can see the information, and I’m just going to change all of the columns, and now if I highlight all of the sales volume, you can see that the Grand total two, three, four, nine, seven, eight. It works.

So, let’s now go back to Power BI. So, this quarter two is all of the quarters. And again, if I show the next level, it will go down to all of the months. So, this could be useful, if you want to see if there’s any seasonality with regard to the sales. So, you’ll see here. July seems to be the hottest month for sales, 360,000, compared to January, 237,000. So, you can see there is some seasonality.

Now, notice, see Records is still enabled, so if I was to click on any of these, I would see all of the records for that particular intersection, so I’ll get rid of that. I’ll decheck it.

So, now I can go all the way back up to the top. And that leaves one button that we haven’t had a look at – Drill down. Now, notice something interesting happens when I click on Drill down. Nothing. It’s selected, but nothing is happening. So, what this does, it allows me to narrow my drill down on one particular year. So, I can navigate down the hierarchy, but not for the totality of the data, but just for one specific element. So, if I click on 1997 now, it’ll take me down into the quarters of 1997. Equally, if I go down to quarter three, it will show me July, August, September. And if I had to go down further, it would show me the individual days and so on. So, I’ll deselect that, and go back up, and go back up.

Just finishing off for this section, see Data or Visual table that allows you to see the data which is not actually that useful for a matrix, bit more useful when we get into charts though, because you can see it’s identical. So, if you had a chart and you clicked on see data, you would see the underlying numbers. So, it wouldn’t go down to the record level, but it would tell you what the numbers, each individual line or bar, represented.

Drill through, we will have a look at later. If I click on Drill through and click on something, it says no available actions. We haven’t set up the Drill through.

Finally, in the March, 2020 update, we’ve got these little plus icons next to the start of a hierarchy, and going all the way down except to the very final element. So, we’d have a plus next to the Year, Quarter, Month in this example. And this allows us to drill down one particular year while still being able to see all of the rest of the data. So, with this Drill down here, I can have a look exclusively at 1997. However, if I wanted to see all of the years plus 1997 broken down, I can now do this by clicking on the pluses. This was something that you could do for ages in Excel pivot tables, but it’s finally been introduced to Power BI and is, I think, a good addition.

So, in this video, we’ve had a look at what a hierarchy is and how to navigate down and up it, and we can navigate down a particular selection or we can expand the lot. Now, just to show you how this is different to a table. If I was to add in the date into the table, it would just come fully expanded and there’s no options for the data drilling. So, matrices, they allow you to have more than one axis and allows you to drill down on a particular element within a hierarchy.

31. Stacked Bar Charts and Switch Theme for Reports

Now, it was good to be able to drill down through the quarters, and months, and seasonality. But the problem is, I’m still waiting through an awful lot of figures. Let’s have a look at this in a more graphical visualisation.

So, first of all, I’m going to duplicate this page. So, I’m going to right and click on Page 2. In fact, I’m going to rename it. First, I’m going to call that Matrix. And now I’m going to duplicate the page. I’m clicking on it, Duplicate page, and then I’m going to rename this as Bar chart.

Now, I’m going to select the visualisation. None of it is selected, there’s these dotted lines around it but that’s just around the page, rather the in the visualisation. So, I’m going to click on the visualisation so we can see these little markers. And I’m going to change the visualisation and see what we get.

So, the first is a Stacked bar chart. So, what this shows, are Greater Manchester, Merseyside, all of the regions, the total sales volume going across and each bar is divided up into each individual year. Okay, that hasn’t happened. What’s going up? Well, let’s have a look at the axis. The axis is date and region name. Previously, we had date on one axis, columns on another. Whereas now, they’ve all been grouped together on one particular axis, but we can’t see the date. We can only see the region name. Well, it is a bit hard, but having all of these on the same axis allows us to drill up and down. So, if I drill right to the top, you’d see the date year and then the quarters, going down and then the month, and then the day and then the region name. So, it could be useful. Basically set it once, use many times for different types of data, but that’s not what I personally want.

I want to see all of the region names and let’s say different colours for the date. So, what I’m going to do, is I’m going to drag the date into the Legend field. And now you can see we’ve got this little legend at the top which has a different colour for each year. Now, for me, this is a bit too much. I would swap these two around. I will have date in the axis field and region name there. But again, for me, not quite right if I’ve got so many different values. I, generally, don’t have them running from top to bottom. I have them running from left to right, like this. So, here we can see individual regions. We can see their sales and we can see the totality of the sales for each individual year.

Now, it could be that I would want to drill down on this. So, I’m just going to remove date, a date back in and now we’ve got the drill down functionality so I can drill down instead of individual years, I can drill down into quarters. So, here you can see the seasonality again, months. And if there was day data, that would be there as well. Alternatively, I could pick a particular year. Let’s talk about 2006, the height of the market, and I can have a look at 2006 in terms of individual quarters, months and days.

Now, it could be that you don’t want them on top of each other, you want them next to each other. Well, instead of using a Stacked column chart, you can use a Clustered column chart. But this is where it gets a bit messy because I’ve got too much data. So, we’ll have a look at a Clustered column and Bar chart in a later video. So, the key is you mustn’t overwhelm with data, this provides a story. How many sales did we have per year, and how was it broken down into each of the particular regions?

Now, maybe you don’t like the colours, they are the default colours. Well, let’s see if we can change them. We’ll go into Format and we’ll go into Data colours. And so you can see, you can select individual your colours for each of the regions. So, it could be, I want to go onto this sort of bluish type colours. So, I will have them of, perhaps, darker colours as we go up. It may be that you want the darker colours right at the bottom. So, now it looks a lot more coherent; the previous one just looked like lots of different colours altogether.

But you can actually change individual colours. If a click on Revert to default, you can change individual colours but you can also change the default. And that is done by switching themes. If you’re going to home theme, switch theme or later versions, go to the View menu. We can see that there are a few themes we can have a look at. So, for instance, City park, Classroom, Colorblind safe, or High contrast. So, Microsoft really tailors for people with certain disabilities. Not sufficient number of theme, well, you can go to the Theme gallery and that’ll take you to the Internet. And you can choose from a wide variety of different themes.

Now, there is something else called Import theme. And that is quite complicated, it uses a file called a .json and uses hexadecimal codes for colours, and basically it’s for much more advanced users. So, right now, I would not use that. I would, instead, get a theme that is close to what you want and then adjust the individual colours using the more graphical version than using hexadecimal colours that you’d have to if you were importing a theme.

So, you can get your chart looking exactly as you would wish with a certain amount of style.