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

1. Matrix

Hello and welcome to level three. And in level three we will be looking at different types of visualizations 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 any experience with Excel then you’ll know them as pivot tables. So if you 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 the left hand side, some will go across and some will go in the middle. So let’s create a new visualization, a new matrix and we’ll see what there is. So I’ve deselected my visualization because I don’t want to change my existing viz, I want to create a new one. So here we can see the new visualization 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 summarized, the thing which is aggregated, the thing which is totaled, 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 sales volume into values. So there you can see us total sales volume 3. 795 million, exactly what we have up here in our first visualization.

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 how values. So what is rows and what is columns? So what is left for each year? I want the total number 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. So suppose there was 100 years, then I would have that going down as opposed to going across. So if we 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 got in the first visualization. But then when I drag in a date in two rows, you can now see that it is split up into both regions and years. So if I just resize this just fractionally, then you will see that we have got totals for each region and totals for each year. Now you’ll also see that we got this scroll bar because we can’t actually fit it all in into one particular visualization. And that might not be a bad thing. It might be fine, it might be what you want, it could be you want something that you do want to scroll on and just have the ability to look at each individual year rather than the displayed 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 visualization control C into this new page control V, and then I can have it occupy the entire body’s 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, 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 preferred 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. So let’s get rid of the date, drag region name down to columns, drag data across. Now, as we do for all of these new visualizations, let’s have a quick look at the format pane and you’ll see that there’s really not that much difference between table and matrix. You’ve now got subtotals as well as the grand total. So you can switch the subtotals on or off and save for the column subtotals and then 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 visualization that you really don’t need to spend much more time on the matrix visualization. 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 Matrix C’s. 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?

2. Drill down data, see data and records, and export data

So what are these buttons that have suddenly appeared in this new visualization? 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 raw 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 were many days in a month, many months in a quarter, many quarters in a year and many years characterized 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 visualization 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, before I do, we should point out that as we got this visualization selected, we can see the description of all of these in Visual Tools data Stroke, 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 1990, 519 96 and so forth. If I click on Expand Next Level, we now go down the hierarchy showing not just the quarter, 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 would 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 1990, 519 96. It’s for all of the years. And you can see this a bit better if I click on C 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 mod, 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 239,000 234,978. Now, if you wanted to test this, say we won’t show about the mathematics. You can click on the dotted dot and export data. So this will export it as a CSV file. So I will call it export of data. Go into Microsoft Excel. Open it up. So, going to my resources, 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 export of data. It’s delivered 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 234978. It works. So let’s now go back to Power Bi. So this quarter too 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 C 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 d check it. So now I can go all the way back up to the top. Now that leaves one button that we haven’t looked 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 will 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 were to go down further, it would show me the individual days and so on. So I’ll d select that and go back up and go back up. Just finishing off this section C 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 C 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 drove 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 and 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 particular element within a hierarchy.

3. Stacked bar charts and 8j. switch theme for reports

Now, it was good to be able to drill down through the quarters and months and see seasonality. But the problem is I’m still waiting for an awful lot of figures. Let’s have a look at this in a more graphical visualization. So first of all, I’m going to duplicate this page. So I’m going to right and click on page two. In fact, I’m going to rename it first. I’m going to call that matrix. Now I’m going to duplicate the page, right and clicking on it duplicate page. And then I’m going to rename this as bar chart. Now I’m going to select the visualization. None of it is selected. There’s these dotted lines around it, but that’s just around the page rather than the visualization. So I’m going to click on the visualization so we can see these little mockers and I’m going to change the visualization and see what we get. So the first is a stacked bar chart. So what this shows are going to 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 on? 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 odd, but having all of these on the same axis allows us to drill up and down. So if I drilled right to the top, you would 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 at once, used 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 colors 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 color for each year. Now, for me, this is a bit too much. I would swap these two around. I will have date in the access 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, add 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 is it broken down into each of the particular regions? Now, maybe you don’t like the colors. They are the default colors. Well, let’s see if we can change them. We’ll go into format and we’ll go into data colors. And so you can see, you can select individual colors for each of the regions. So it could be I want to go onto this sort of bluish type colors, so I will have them of perhaps darker colors as we go up. It may be that you want the darker colors right at the bottom. So now it looks a lot more coherent, a lot more like deplete. This one just looks like lots of different colors altogether. But you can actually change individual colors. If I click on Revert to default, you can change individual colors, but you can also change the defaults and that is done by switching themes. If you go into home theme, switch theme or in 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 themes.

Or you can go to the theme gallery and that will 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 actually that is quite complicated. It uses a file called A JSON and it uses Hexadecimal chords for colors 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 colors using the more graphical version, using Hexadecimal colors that you would have to use if you are importing. So you can get your charts looking exactly as you would wish with a certain amount of style.

4. Bar Chart formatting

Just a quick look at all of the formatting that you can do. So you can change the xaxis, so you can have either continuous or a categorical type. So you can see the difference. What this means is categories. Each year becomes a unique category as opposed to something on a scale. So here it’s treating it something on a scale from 90 95 through to 2015. Here it’s treating it like a category just like red, green, blue or car vehicle lorry, each of those are categories. If it was continuous, then you get to set the start and end values. And if you have got logarithmic data, so data that goes 110, 101,000, which really multiplies, rather than just intends, rather than just adds like how it does in want, then you can choose logarithmic. But if you don’t have that data, just keep to linear.

So we’ve got that for the x axis. We’ve got similar things for the y axis. You’ll see that linear is the only one that the computer thinks makes any sense. But you can change the start and the end values as well. And you can have the position of where the axis is. So here it is on the right hand side. Here it is on the left. Now, scrolling down, you can change other things like the display unit, probably quite useful. You can see it’s automatically displayed in thousands. You may say, no, I want it to display as the full figure or I want it to display in millions. So you can have an access title. So here we have sales volume on and off and you can say show the title only or show the unit only. Here you can see the word thousands or show both. You can edit the axis title if you’ve got one. You can edit the size, add grid lines or remove grid lines. The grid lines are these little lines going across and how thick the grid lines are on a scale of one to four, and whether they are solid dotted or dashed, looking at some of the other things.

So we’ve got a legend. So whether we see the legend that’s this up here and where it is positioned, so you can have it in quite a few places, whether you’ve got the title for the legend, in this case region name, or whether that’s not needed. And of course you can adjust the title. We’ve had a look at adjusting the data colors previously. You can have data labels. Now, data labels allow you to see instantly what that particular value is. So it gets written in this case inside the data label, right in the middle. But you could have it right at the top or center middle or center bottom. So it depends where you want it. Now you may notice some of the labels aren’t showing. So the reason for this is because it thinks, okay, if I show too much, that’s going to perhaps annoy the end user so you could change the label density so that it only shows certain labels, or change it to 100% so it shows all. So if you’re struggling and going, why aren’t all of them being shown? Have a look at the label density. There’s also a show background, so if you want the label to have its own background like this, you can also do that as well. Just going down. We’ve also got the plot area, so here we can add an image to the background, if you so wish, and change the transparency of the background. So if you had, I don’t know, a sunset or your corporate logo that could be in the background, washed as much as you wish. Now, in addition to all of those which I have just gone through, there are also a lot of those particular categories that we’ve had a look at previously and I won’t be pointing them out each time. So there will be a border, there will be two tips you can add, there will be titles, there will be general web, you can actually fine tune it here you can see it’s not quite all the way to the left. So this is a look at all of the formatting that you can do for stacked column charts and stacked bar charts.

5. 8c. Configure interactions between visual (Edit interactions)

Now, in this video, I want to have a look at the interactivity between two different visualizations. So what I’m going to do is I’m going to copy this visualization and go back to our original visualization, the table. I’m just going to get rid of this second one, which we’ve now included in the matrix, and paste it resize. And you could also do lots of other things as you wished as well. Now, we have got here one table visualization and 1 bar chart visualization. Now, if I click on Greater Manchester in the table, notice what happens in the bar visualization. And it’s actually a bit difficult to see what does indeed happen, but you can see something definitely has if I deselect. Now, the reason it’s a bit difficult is because all of these colors are sort of grayed out anyway.

So if I click on the formatting into data colors and just reset them, revert to default, you’ll see that we’ve got vibrant colors to begin with. And then when I select Greater Manchester, all of the other colors get washed out apart from Greater Manchester. Going to Merseyside and you can see they get highlighted. Now we can edit what happens between visualizations. If I click on one of the visualizations, the one that I’m going to be selecting, the various components, and I go to Visual Tools, format Edit Interactions. So you see what it says, change how visualizations interact when data points are selected. So I’ll click on this Edit Interactions and what happens is it gives me an extra three icons and you’ll see they expand as soon as I get onto them. But really these three icons I want to talk about, we’ve got highlight. Highlight in this case is the default. So if I click on a particular region name, we’re looking at that region. But the other one, other main one I want to talk about is filter.

If I click on this, nothing appears to happen. But now I’m going to click on South Yorkshire and the bar changes the bar chart so that it is solely about South Yorkshire. Click on Merseyside and it’s solely about Merseyside. Now, the third interaction is none. So that means I can click on any of these and it doesn’t actually affect this lower visualization. So you can select what happens when a particular visualization gets clicked on. What actually happens now, this is very useful when you have additional visualizations on a particular page, more than one. So I’m going to insert a new table visualization. So just click on table. There it is. And I’m going to add in the date, just the year, and I’m going to have a total of the sales volume. So let’s just format it so that it’s a lot bigger. So what I can do now is click on South Yorkshire and that 3,795,000 total gets reduced to 431. Click on Merseyside and that gets reduced as well. So what happens if I’ve got South Yorkshire and I click on 2000. So you’ll see that only one selection happens at once. So I can’t click on Tyne and Weir and 2002. As soon as I click on something else, the other one gets deselected. Well, I can if I use the control key, but more about that later.

Now, another thing to be aware of mind is that if you’ve got visualizations which are very close together, then you might not see the little icons. For instance, now I can’t see the highlight photo icons to remedy this. Let’s just move this table up. And now you can see that we’re hiding behind this over visualization. So if you’re doing this edit interactions, then you might want to just have a bit of space, vertical space between all your different visualizations just to make sure you can see everything okay. So after I still got these edit interactions, I can say, okay, with this, I want you to do a filter. So here we have a filter. So now if I select 2003, it focuses solely on 2003. But you can also see that we can also interact with other tables. So here we are filtering this second table or this first table through the second table. So when I click on a particular year, it just shows what’s in that year. So how you can use this combination of highlighting formatting is really only defined by your ambition.

For this particular page, what do you want it to do when you click on a particular number? Do you want it to filter or do you want it to highlight? So I’ll change this one back to a highlight and so you can see it highlights year 2000. So this could be quite a useful demonstration tool. So you could say, well, let’s just focus on 2006. Here was the results. Now let’s add 2007. I’m going to hold down control and select 2007 and 2009. And so you can select more than one thing from a particular visualization. Now, if you do want to have, say, South Yorkshire in 2006, seven and nine, then you can do that using the control key like this. So here I’ve got South Yorkshire in 2006, seven and nine, all highlighted. Now I’ve added West Midlands to mix and I’m going to deselect West Midlands, all of that through using the control key. So this makes it quite a powerful tool to be able to look at individual items or more than one item at once and just focus down on this.

Now, you may have noticed in other applications, I could say click on the first one and then hold down shift and click on the last to get a range. That is not a possibility here. You’ve got to use the control to select multiple items. So this edit interactions, once you’ve finished deselect it, the interactions will still happen, but you’ll no longer have those little icons that enable you to change what they do from highlight to photo or to none. So interactions are very useful on one particular page. They allow you to focus on a particular topic, and you can narrow it down by using additional tables to focus the end user’s attention. And just in case you’re thinking, can we do it the other way? Yes. If I click on a particular area of the graph, we can change the tables so that it’s focusing on Tanning, where for the year 2004, for example, and then holding down control, I can add additional years or additional other areas as well. So all of that can be tailored with the edit interactions.