DA-100 Microsoft Power BI – Level 5: 8a. Other visualizations

1. Ribbon charts

Hello and welcome to level five. In this level, we’re going to have a look at some of the other visualizations that we have not previously looked at. So in level two, we had to look at the table visualization. Level three, the matrix and the bar and the line charts. Level four, we’ve had a look at adding more control, more user interaction into your visualizations. So now we’re going to continue to expand our repertory of visualizations. So, first of all, we’re going to create a line visualization and it’s one that we’ve created in the past. We’re going to put the date and we’re going to put the sales volume as the value and we’re going to separate this in terms of the different region names. And you saw that we had a problem last time. The problem is that what is fifth and what is second keeps changing. So here, for instance, we’ve got West Yorkshire being second. This is in 2006. But when we get to 2010, West Yorkshire is now third. And it’s fairly easy to see with perhaps these six, but not so easy when you’ve got more than these six to say.

What is happening? How the interactions? What the jockeying for position? Who is first, who is second? Well, we can solve that dilemma by using a ribbon chart. Now, I just want to look at the stacked area chart, just to have another look here. Now, we can’t even see which is 1st, 2nd or third, but we can’t see the grand total. So both of these have got advantages and disadvantages. The ribbon chart takes some of the more interesting aspects from each. So let’s have a look. So here you can see that we’ve got West Yorkshire starting off third, and then it comes up to second and then goes down to third again. Similarly, you can see very clearly that South Yorkshire starts at fifth and then becomes fourth in 2001 before slipping back down. So the ribbon chart here allows you to see the volume, the total.

So it’s equivalent to the stacked that we had previously, but it doesn’t display them in a fixed order like these stacked charts do with West Yorkshire always at the top, greater Manchester always at the bottom. Instead, it puts at the top the one which is the biggest, and at the bottom, the one which is the smallest. And you can see, if we hover over these things, all of these values, that we have the rank as well. So here we can see the 2005 and 2006 rank is four. Here we’ve gone from five to four up one place.

And we can also see this sales volume change, which is also quite interesting to have all at the tips of your fingers. I didn’t need to do any additional programming to get that from the line chart. All I needed to do was change it to a ribbon chart. Now, let’s just have a look at the formatting for the ribbon. And you’ll see that there is a special category here called ribbons. First of all, we’ve got the spacing. Are there any gaps between the various categories? So now you can see being exploded a bit. Now this percentage is, or this number is a percentage of the total height. So the entirety of the Y axis do the series colors need to match throughout?

So Red here is entirely South Yorkshire. Without that we would have a lot of grays in between. So these are the actual ribbons where it can floor in between. I can’t really see the use of that. So I always have this on transparency. Well, fairly obvious. How transparent do you want the inbetween bits, the ribbon to be? So that gives you a bit of shading and then a border. Do you want a border to be at the top and bottom of each of the strands, each of the ribbons, each of the lines? Now, you may also notice there’s a lack of a Y axis here. Part of this reason is because you can space it out and therefore up here is not really meant to be the total of everything. It’s not stacked in the sense of being able to give you a total answer. So this is when your data labels might come in use. Now, you see, they can’t actually be shown at the moment there’s too much data on the screen. But if I just reduce the text size of the labels here, we can see forty two k, fifty one k and so forth.

And we have the usual options here for the data labels. If you want to change the data colors, that’s also available under the data colors. So ribbons useful when you want to know the ranking of individual categories together with their size. But it can’t unless you’ve got them not exploded. You can’t take for a fact that the totality of this equal is related, directly related to the totality of the in this case the sales volume. If they’re not exploded, yes, you can. But if they are exploded, then it’s more of a picturesque view, rather than being something that’s, strictly speaking, 100% accurate. So it serves a different purpose to the line charts at the stacked area charts. But it does make for an interesting topic of conversation.

2. Waterfall charts

Now, we’re looking at a lot of house sales, but have we actually totaled them up? So if I have a new page and I’ll just click on sales volume, just add that to a new visualization, you can see that in total, we have got 3. 8 million homes sold. So what how many homes did we sell by the end of 2004? Okay, I can put on a filter for that. So I’ll drag date down into the visual level photo, or maybe I’ll add a slicer. So I want is on or before. And I can type that in the 1231. 2004 apply filter. So you can see we’ve now got 1. 976 million. But what about the end of 2005 and so forth? Can we have a running tally? So what I’m going to do is instead of applying this as a filter, I’m going to put it into the axis. So here we can see an axis of all of the sales volume per individual year, but it’s not cumulative.

I want it to all add up. So I’m going to change it to a waterfall chart. Look what happens. So now each year’s, sales volumes gets added to the previous year. So you can see eventually this is how the 3. 8 million has been calculated. And you can start you can see it each individual point. 2004, we’re around the 2 million mark sold. Now, what if we put a region name to this? Well, we don’t add it to the category or the y axis. We add it to something called the breakdown. So if I add in the region name into the breakdown, you can see that in 1995, we have West Midlands, Greater Manchester, West Yorkshire, and each of those gets added into all of the years. So here we have a few negatives. So it goes up, but we have a few negative sales as well. It goes down. And when we get to 2004, 2005, we’ve got some really negative sales volume compared to the previous year. And then 2008, it drops off completely. Now, I should point out that this axis starts at 100K. So when we have a figure all the way down here, it’s not actually down to zero.

 If I wanted to change that, I would go into the formatting, the x axis, and say the y axis, and I would change the minimum from auto to zero, which doesn’t make it look quite as bad, but let’s just change it back for now. Now, you’ll notice there are some yellow colors as well as red and green. Green means these are your big advances. Red means these are your biggest decliners. Yellow, well, what happens is I can say, okay, I may have 10, 20, 30 products here. I don’t want to see all 1020 30. I want to see a maximum of five is the default. So if I go into formatting of this visualization and go to breakdown, you’ll see that it says how many breakdowns do I want? So if I want two breakdowns, then it’s only going to show me the more significant two, and then all the others are going to be wrapped up into other. So it still allows me to see what’s the biggest bottom and the biggest top, for instance.

But everything else is included in this other section.

You’ll also notice that as soon as I put in a breakdown, it’s no longer cumulative. So the end figure is not the totality of 1995 to 2016, it’s just the year 2016. So you have a choice. You can either have it all cumulative, or you can take each year as the final figure. And in other words, we look at 2003 down here at the bottom and see what has caused that to change the most. So what’s caused it to change in 2003? We can see that Merseyside has gone up 2000 units and West Midlands has gone down by 2000 units. So if I take another example, let’s have a look at a line chart of the average house price change. So we have a twelve month percentage change and I’m going to show that by year. Now we’ve got a problem in that there isn’t really a percentage change of 2060. So I’m going to change that from sum to average. There. I’ll always make sure the answer looks realistic.

And we’ve seen previously how we are able to change the modeling so that the default summarization is average. For instance. So you can see that we start off at around 4%, go all the way up to plus 28%, and then all the way down to minus nine. Now if ours to put the waterfall, you can see roughly how that is all cumulative. It doesn’t work exactly in terms of the actual maths because a twelve month percentage change is an exponential figure. So this doesn’t really pay real weight to all of the drops that it should do. But it’s an interesting graph, but much better is if I add in the breakdown. So if I add in the region name and then change the region breakdown to a maximum of two. So we can see that in the between 2001 and 2002, the biggest rises south Yorkshire and Thailand. Weir but then between 2004 and 2005, while other things were going up, or at the very least recovering, we have got huge declines in time and wear and Merseyside. So it just allows a different view of your data.

It allows you to see how it’s been going over time in this particular case, but what the biggest contributors are, what really have been driving this. And equally, if you don’t want to break down that, then you can see it at a more very cumulative level. So waterfall child they’re great. If you’ve got positive and negative figures, for instance, you might be calculating a company’s profit and seeing what the most profitable items were. And equally, those which made the most loss.

You can be auditing major changes. So I sell, for instance, that particular regions contributed heavily to changes. It could be looking at the change in totals. So, for instance, suppose this was a headcount number of employees. Then you can see how many new people, minus levers, have arrived. Or you could put it into how much money you make and spend each month. So, waterfall charts, they’re fairly specialized, but I hope you can see they can be quite useful when you’re wanting to have an either look at his accumulative account or if you want to drill down into the significant figures.

3. Scatter, bubble and dot charts

Now in this video, I’m going to want to answer the question how does the sales volume vary according to price inflation? So if houses are rising quickly, do we have more sales volume? So people are trying to buy the houses before the prices get too high. And if it’s going down, do we have a reduction in the sales volume? Are people frightened and not wanting to buy? So here, instead of what we’ve done previously, we are comparing two numbers together. We’re comparing sales volume and house price inflation. And for that we use a Scatter graph. So if I add a new page, add a Scatter graph which looks like these little circles, we can see that we’ve got lots of axes. So let’s just think about the question again. I want sales volume against house price inflation.

So sales volume, I will put that onto the x axis and house price inflation. I’m going to take the twelve 1% change and put that on the y axis. Doesn’t really look much of a Scatter graph. It’s only got the one item. Well, what’s happened is that it’s given the sum of the sales volume against the average of the twelve monthly percentage change overall. Okay, I don’t want it overall, I want it, let’s say per year. So I’m going to drag the year into the details. And now it’s getting more like a Scatter graph. We’re really starting to get some additional dots. Okay, I’m going to add into that the region name. So we’ve got 22 years, we’ve got six regions, so we should have 132 dots by the time we finished. So let’s add in the region name. Doesn’t look different and that’s because we’re back to these drill levels. So you could expand the next level and you will see all 132. But really, if you want this to be the default view rather than it being drilled down and drilled up, then you need some sort of unique Identifier. Here something that combines date and region name.

And we’ll have a look at how this could be done in the modeling section later on. But for now, just know we can only choose one series unless we are going to be drilling down and expanding to the next level. So what I’m going to do instead is focus on the region name. Now the region is all in the one color. When I expand it to the next level, it’s all in the one color. So can we have it in lots of different colors? And we can if I drag region name to legend.

So now they’re all differently colored, so you can see the difference in sales volume for the various regions. Okay, but what if I wanted to concentrate on one year at a time? Well, I can drag year down from details to the Play axis. That’s a new axis. We haven’t seen that before. What does that do? So let’s get the year down to the play. And we’ve got at the bottom, it’s your play button. If I click on it, you’ll see that we have all of these dots for each year. And so we can see how they change over time. So we can see now that things are going down negative ten, that the sales volume has really collapsed.

Now we’ve started to have a little bit of confidence that sales volume goes up. So this gives us a bit of flexibility in what we’re going to do. So do we want all of the dates to be shown at once or do we want it to be shown more as a presentation? Now we’ve got another property here, another thing we can fill in, which is the size. And what I’m going to do is I’m going to fill that with sales volume again and you can see the bigger the sales volume, the bigger the size. So let’s play this again. So the further right we go, the more these circles get bigger. But as soon as we get to the left hand side, they really start reducing in size. So the idea about this is generally to have three independent variables, numeric. So we’ve got the sales volume, we’ve got the 12% change.

So let’s have a different, a third value for the size. So let’s have the average price as your size. So now we can see that the cumulative effect of all the house price inflation. But when we get to around 2007, this is when the house prices were at their maximum and then they slightly declined, but they’re still fairly big, even though there is some negative inflation. And even though sales values are down, we still have much higher house prices in 1996 or much lower house prices in 1996, compared to the peak of the negative house price inflation around 2009. So, as you can see, the circle is much bigger. So this allows us to compare three different values. Now, it’s all being grouped together on something called the Scatter. Visualization the Scatter chart, but when we add this a third value, it becomes technically a bubble chart. There is a third variant of this called the dot plot.

And this doesn’t actually use on the x axis a number, it uses anything else, a category. So I could put the area code on the x axis. For instance, you can see we can’t use the play axis to do that, so get rid of the play axis. And now for each of these, we have got all of these individual plots. I think this is probably the most underused version of the Scatter plot, largely because you can fairly well replace it with a column chart. But if you don’t want the actual column, you just want the dot, then it can be used. But most of the time it is used with two numeric values for the scatter and three numeric values for the bubble. So I’ll just undo a few of these changes to get back to where we were. There we go. So let’s have a look at some of the formatting that we can do with scatter plots. First of all, we can change the shape, so it needed to be actual round circles. It could be squares, it could be diamonds, or it could be triangles. And if you wanted one particular item to be something different, then you can have that as well. So useful if you wanted to highlight, say, Greater Manchester and make it a different shape.

You can also change the shape size as well, relative to whatever else variables you’ve got. You can add in the category labels if you so wish. So quite useful when everything’s moving. So you can visually see which is Greater Manchester and which is tin and wear. For instance, you can add borders, so adding slightly darker shades of the shape around it. And that’s it. And that’s a shame, really, because one thing I haven’t been able to spot is how you can actually change the speed of this playback, because there isn’t actually a play axis category. And that, for me, is a bit of a shame.

However, scatter plots or scatter graphs or bubble plots, these are useful for being able to add multiple items, dots, squares, when comparing two numeric values and a third numeric item if you want to add this size. And just to have to confirm, there is currently no way to actually change the play speed, but hopefully that will be something that Power Bi will implement at some point. They do change Power Bi every single month, new features come out, so hopefully we’re getting better and better product each month.

4. Pie charts and donut charts

In this video, we’re going to have a look at pie charts and donut charts, and they’re not that difficult. If I add in a pie chart, let’s say I wanted to see what the total sales volume was. Okay, so 100% of all sales were made. That doesn’t tell me anything. So now let’s divide it into the region name. I’m going to add region name into the details section. And here we can see we have got these six different regions and we can see what percentage of the overall sales were in Greater Manchester, west Midlands, West Yorkshire and so forth.

Now, there’s not too much more in this until I add something else. Suppose I wanted to subdivide each particular pie, each particular portion, say, the Great Manchester. I wanted to know what the cells were in quarter one, quarter two, quarter three and quarter four. But I want the colors to remain as each individual region name. So what I’m going to do is drag region name into the legend section. Nothing visible has actually changed. And now I’m going to go into the date hierarchy and drag quarter into details. And now you can see that each of these pi segments have been divided up into four. So you can see quarter one, quarter two, quarter three, quarter four for each individual region name. So if you’re going to do this, make sure you put the legend on.

Now, the difference between a pie chart and a donut chart is simply whether there is a hole in the middle or not. So you can change the data colors individually. So maybe I wanted Greater Manchester to be in a purple. Absolutely fine. You can change the labels so that, for instance, instead of saying the category, so 1234, you could have the data value or the percentage of the total or some combination thereof. Or of course, you could just switch them off if it’s fairly obvious what each segment is for and that’s about it. And it’s a bit disappointing in terms of the functionality. For instance, what if you had 20 items? So I’ll change this from region name to date year. It gets very crowded very quickly.

Now, in some other places, some other programs, you could say, I want the top ten to be shown separately and everything else to be grouped together. That is not yet possible in Power Bi. Hopefully there will be expanding this so that the standard Power Bi pi and donut could have some greater flexibility into it. But for now, if you have got some data which adds up to 100% and you want a visualization of how each component makes up the total, then please have a look at the pie chart or the donut chart.

5. Treemaps

Now, an alternate way of displaying values which are 100% of a total value is by using the tree map. So if I duplicate this page and call it Tree map, we can just change this visualization to a tree map over here and you can see that we have the entire have visualization taken up by each of the region names and we can also drill down to show all of the quarters equally.

We could drag the date from the grouping down to the details to get the same effect. Except there is a difference. When we have a look at expanding to the next level, all of these different boxes have got different colors, whereas if it’s put into the details section, then they all have the same color. So Greater Manchester, the four different quarters are all grouped together quite nicely, so they all add up to the same thing. Even if the details are removed, it’s just breaking this down into more detail. And again, this sort of thing, like the other charts we’ve been showing, might be really good for adding drill through.

So imagine clicking on any one of these and getting to a visualization for that particular area so you can drill through to something a different page that you have set up. So Tree maps, fairly straightforward, we have the grouping, only the top grouping is shown at first and then you can go further down. Then you have details if you want to subdivide while still keeping the same color. And then we have the sales, the volume, the values at the end. Now, there is nothing new in the form formatting. All the formatting that we’ve seen previously are included in this particular visualization. So very little new to learn. So just an alternate way of visualizing the 100%. Instead of being in a circle, you could have it in a square and that is the tree map.

6. Funnel charts

Now, the final of these other visualizations that I want to show before we go on to mapping and measuring performance using KPI gauges and cards is a funnel chart. Now, a funnel chart shows percentages of the total. So rather like what we’ve had here from top to bottom. So if I do duplicate this and change this visualization into a funnel chart, you can see that we have Greater Manchester having the greatest sales volume, and then it getting progressively.

What smaller is the idea, if it is a true funnel. So if, for instance, instead of having the region name, we had the date and specifically the year, then you could imagine this showing figures perhaps going progressively out and then getting very much smaller. Funnel charts are good if you’ve got sequential data. Perhaps you’re going through various stages and you want to see how many hours are being spent by labor, how many hours are being spent by plumbing, how many hours are being spent by decorating. If you’re building a house, it’s really good if the volume is going down each time.

So if you went from 2007 to 2012, that could create quite a nice funnel, because it shows the largest value at the top and then going further down, it can also reveal bottlenecks in processes. So imagine this was showing the number of hours worked in any particular trade, and we have more hours worked in the middle trade than in all the others.

That could show that that middle trade could be a bottleneck. So anything which is a linear sequence is a good fit for the funnels charts. It could show, for instance, how much volume of a project you’ve got as it goes through various stages, how much wasted there is at each time. There’s only one new section in the formatting, and that is conversion, the conversion rate label. And what that shows is the percentage difference from the very first item. Or if I switch that off, you’ll see this 100% line disappears. So these are funnel charts.

7. Adding Marketplace visualizations (Import a Custom Visual)

Right, I’m going to create a storyline, I’m going to do a presentation and I’m going to say, first of all, this is the overall picture from 90 95 to 2016. Then I’m going to look at 2000 to 2009 and I’m going to just show the top two regions so we can fork us on them. Then I’m going to talk about 2006 and then 2009. The problem with all of this is that in a presentation you could sort of say, okay, I’m going to change this to 2000, I’m going to change this to 2009. But then changing this so that it’s the top two and then clicking on it. It’s not really a very presentable way of doing things. What you can do instead is set up your display exactly how you want and then create a bookmark.

BOOKMARKS allow you to save which page you’re on, any photos, slices, including what the values are, sorts, drills, locations, whether things are being shown of the visibility and the focus or spotlight of any particular visible object. So, let’s start off at the beginning. I want this to be my first bookmark. So I’m going to go to View and bookmark Panes. So I want to add my current state of what I’m seeing as my first bookmark. So I’ll click Add and I’m going to rename that overall. Next, I’m going to change this so that it is the year 2000 to the year 2009. Make it the 31 December. And I’m going to change this so that it is filtering on the top two.

So I’ll go into the visual photos and say that the region. Name the top two values according to the sales volume. Apply filter. So this is going to be my next. So I’m going to let say 2000 to 2009 with top two. And then I’m going to look at 2004. So this is a highlighter. So I’m going to click this and say rename 2004. And then I’m going to compare and contrast that with 2009 or 2000. Then 2006 has got West Yorkshire is one of the top two. 2008 has got West Midlands. So I want something with West Midlands. I’ll use 2008. So I will add this as a view. So now to get in between these views, all I have to do is click on a bookmark and it instantly takes me to where I was at the time of creating the bookmark.

Now, maybe my BOOKMARKS, I don’t want it to affect absolutely everything. So I can click on the dot dot and say, what are you going to affect? Are you going to affect data properties like photos and slices and sort states? Are you going to affect display properties such as the spotlighting and any visibility of objects and which page is visible? And also, is it all visuals or is it just selected visuals? Now, let’s say I also wanted to not show the slicer or the instructions. Once I get past overall. So I get into BOOKMARKS. So now I’m going to select the selection pane.

And here I’m going to say, okay, these instructions, I want to hide this visual. So just click on each one if you’re not sure where things are. So here’s the shape, here’s the filter. I’ll keep the filter. So I’m going to therefore say that this is going to be my new 2000 to 2009. So I’m going to update this bookmark. So now here’s my overall with all of this shown. And then here’s my bookmark. And you see the objects that have asked to not be shown. They’ve got a little icon next. When that is shown, then those particular shapes are not going to be displayed. So filter by dates is displayed, but here they’re not going to be displayed. And finally, these pre BOOKMARKS, these are Yearly Views. So I’m going to select them all. I’m holding down control and clicking each one.

Click the dot dot and I’m going to group them together. So this group is going to be Yearly Views. And with this I can have a lot of BOOKMARKS that are hidden away by just clicking on this arrow. So if I need them, they’re there, if I don’t, I can just hide them away. So hold this shows the power of BOOKMARKS, especially when used in conjunction with the selection pane. So you can go to a particular layout, particular filter, particular objects being shown, and then very swiftly move to another one and make your presentation a lot easier to do.

8. Practice Activity Number 5 – The Solution

Now, even though these are the last of the visualizations, apart from maps and measuring performance, there are additional visualizations that you can download if you click on this. Import a custom visual you can import from the marketplace. So this will show you lots of additional visualizations that you you can use. Now, most of them perhaps a bit more esoteric. Some of these are building on the original Power BIS. So for instance, if I look for the word do or not, which can be spelled two different ways with the Ugh or without, you can see that some people have spent time looking at problems with the current Power Bi and are trying to find a way to expand on its functionality.

So in each case you can have a look at the various graphics, look at the description, see if this is something you want installing. And if you do want it installing, then all you have to do is click on the Add. Bear in mind, a lot of these are additional paid for options. So click here to buy the advanced drill down Donut chart. And you can see also the reviews, whether it is actually good, other people have found it’s useful, so it can be worth just having a look down, seeing if there’s anything here that’s particularly of interest too, and just downloading and having a play. So it’s best for those that don’t require additional purchase.

But sometimes the premium things may have exactly what you want. So just have a look down, see if there’s something you want and press the Add button. So here we’ve just added a visualization and now we can just add it like any other visualization and we can just use it as we see fit. So I’ll add sales volume to the X, add inflation to the Y, and here we can see a Scatter graph going all the way down to the data level it looks like. And then you can explore the various different options that it gives you and see how you can customize it to make best use. If you find that you’ve downloaded one that you don’t like, then you can always remove it. So click on the Delete a custom visualization. I’m going to delete the histogram of points. Yes, delete. And now it’s no longer included in my Power Bi. So if you want to import a visualization from the marketplace, just click on the dot in the visualization panes, find it and click Add.