DA-100 Microsoft Power BI – Part 1 Level 8: Other Visualization Items for the DA-100 exam

1. Define quick measures

Hello and welcome to level eight, the final level of this part one, which is all about visualizations. Now, in this level, what we’re going to do is look at all the other things that we need for the DA Dash 100 exam that don’t quite fit into the rest of the course easily, or some of the topics may be quite difficult, or some of the features actually knew and have been introduced into power bi. After this course was first created, and with Microsoft introducing new features every month, then there are bound to be some interesting things coming up. So to start off with, I want to talk to you about quick measures.

Now, part three of this course is about how to write DAX DAX formulas and we’ll be talking about calculated columns and measures in that particular part. Well, what these quick measures allow us to do is go beyond what we’ve been able to do and it sorts of bridges between writing your own DAX code and getting the computer to do a little bit for you. So let’s see what’s available. First of all, I’ve loaded the HPI admins source and I’m going to add a column chart and I’m going to add the date hierarchy in the axis and the sales volume in the values and what I’m able to do, because it’s a hierarchy I’m able to drill in and I’m just going to drill into the month.

So we can see for any particular year like 1995 that we have these fluctuations. But what would it look like if we had the total up to the end of that year? From the beginning of that year to the end of that year? We would eventually have these yearly graphics, but what if we wanted it every month? So in January we got 8600 and February 8900.

Well, what if we could show 8600 plus 8900 together? So that would be 17,500 or so. This is the sort of thing that you can do with quick measures. To introduce a new quick measure, click on the little arrow next to any measure and go to new quick measure. Now it’s this select calculation that we need to do first. And you can see that there are an awful lot of potential calculations. Now, do be careful, some of these can get complicated. Like the average per category. You have to specify what the field is as well as the category. And if you don’t have anything there, you can just drag it in.

So it could be date, it could be year of date, for instance. So whatever calculation you do, it will give you an idea of what is needed. Now, I want a year to date total. This is part of the time intelligence, so I’m going to click on that and you can see it’s, filled it in based on what I currently have. I currently have the sum of sales volume and I currently have the year and month. Sean so it’s filled these in so you can see that’s all I have to do, I have to say what sort of calculation it is and the computer can make an intelligent guess as to what these parameters are going to be. And you can highlight all of these little eye symbols and it tells you what information it’s looking for. So let’s click OK. And you’ll see in the right hand side under fields a new field created called sales volume, year to date.

And it’s now got two values. I’m going to get rid of the original sales volume and you can see that for February, we now have a total of 17,600 and it resets each year, which is exactly what we asked it to do. Let’s create a new quick measure. And this quick measure is going to be a year on year change. So we’re going to be comparing, say, March 1996 with March 1995. So again, you can see the computer has also filled in a fair bit. It’s filled in well, you want the sales volume year to date? No, I don’t. I actually just want the sales volume itself. What is your time period, your date, field date, number of periods when I’m going to say one year. Click OK. And you can see that the computer has now changed so that it’s giving a percentage of what it is compared to the previous year. And if we don’t want to see it per month, or this is a hierarchy, we can just drill up and we’ll be able to add legends. We’ll go to see 2008 being a particularly bad year and we’ll have so much information about this that it just expands the amount of things that you can report on.

So you’ve got rolling averages. For instance, I could say I want the average over the last three months, or we could do a month, a month change or running total. Or we can add subtract, multiply and division, things that are fairly easy in DAX. When we get to DAX formulas in part three, but not so easy right now. And then finally, if you click on any of these measures and you can see they were calculated measures, they have a calculator right next to them. You can see the code that’s behind it. Now, don’t be frightened. You don’t need to be able to recreate this code except when you get it into part three of this course, you will be able to know what calculate is and sum and data what each of these do. And then you’ll be able to use these as the basis of your formulas and expand it as you wish. So quick measures are a way of expanding what you are able to report on without having to use any formulas.

And to get into it, you just click on the drop down for any measure and say new quick measure. Just a few words of warning about quick measures. First of all, it’s not available with all models. So for instance, if you are using something that’s called direct query, then certain measures can’t be used because they aren’t supported, they slow the computer down. Additionally, and perhaps more significantly, if you are in a locale that uses a comma as a decimal separator, for instance, four and a half is not four dot five, but four comma five then at the time of recording, quick measures won’t actually work properly so you may need to modify the formula for it to work. Maybe changing the commas for semicolons. It does seem to be a bit of a hole in all of this quick, quick measures, a whole that hopefully they will fill in at some point. But these are quick measures. They are there to kickstart the creation of new measures.

2. Export report data

Now, in the previous video we created this little visualization with a yearonyear percentage change. But what are the figures behind this? Well, to see some of the figures, you can click on the dot, dot, dot and go to show as table. So this allows you to see the figures together with the graph. Now notice that the table that we’ve got at the bottom is at the same level of group granularity as the graph. The graph is in years. The table is in years as well. Now, what if you wanted to go deeper? Well, you could always drill down into the hierarchy and here we have the year and the month. Now it’s overall being able to see it here, but we can’t really manipulate it. Well, we need to export it into Excel or something else to really be able to see the figures. And we can do that again by clicking on the dot. The more options label up here and going to export data.

So I’ll click on the export data and here we can see it’s exporting as sales volume year on year. So that’s the values name by year. Click save and there it’s exported. So now let’s import it into Excel. So if you get Excel and you go file open and go to the folder, make sure that you’re either looking at all files or you’re looking at text files. So you can see this CSV CSV. Comma separated values. Now we need to import it in as delimited and using a comma as a delimiter. So here is our data. Now notice what we have got. First of all, we have got the file called by year, but he’s exported it by month. So it allows us to get deeper into the data. Secondly, it’s not giving us all of the data.

All of the data would include, for instance, the six different regions. We haven’t got any of that. Instead, we have basically got what you can see as the necessary fields for creating this visualization. Now, do you want more fields? For example, wouldn’t it be nice to actually have the sales volume? Well, the best way to do that is to add sales volume as a tool tip so it doesn’t actually alter the visualization apart from the fact you can see it in tooltip, you can see it when you show us table, but equally importantly, you can see it when you export the data.

So let’s export the data. Notice the file name is a bit different. It now says and sales volume. Let’s go back into Excel, let’s open it again. Delimited comma separated values. And now we’ve got the sales volume. And notice that we now start in 1995. Previously we had 1996. And the reason for that is it was a year on year calculation and there is no calculation from the first year. You can’t go back a year. So if you were missing a year, you wouldn’t know it from this particular export. However, now we’ve exported the sales volume. You can see the difference. And you can also say, okay, what is one sales volume divided by the other minus one? Let’s make sure that this calculation is, in fact, correct. And as you can see, yes, it is. So, export data. It allows you to export it from power bi to a comma separated value, but it only exports the things that you need to be able to see how a particular visualization has been made up.

3. Create reference lines by using Analytics pane

Now in the previous videos, we created this sales volume year on year. And let’s say we’ve got a target for the number of houses, number of apartments or flats. To increase the number of sales to go up by five to 10% each year, we can add lines onto our visualizations by clicking on the visualization and then going to this, the third icon, the analytics icon. So we’ve got one for fields, one for formats. So now we have an analytics one. So here we have a constant line. I’m going to expand this and add one. So I’m going to add one at 5%, or maybe I’ll add one at 10%. And I don’t just put the value being ten because the computer will interpret that as being 1000%.

So instead I need to say 0. 1. So we can change the options as well. For example, I want it in black, transparency of 50%. Yeah, that’s okay. I could have it solid or dotted, but actually I preferred the dash. I could have it in front of the bars or behind the bars. And then I can add a data label. So let’s add a data label as well. It’s right there, blue on blue, so you can’t see it. So I’ll change it to black there, we can see it now and it’s on the left hand side and it’s above the line, right, let’s add a second line. So first of all, let’s rename this. So I double click inside of it and say the 10% line. So there we go. So let’s add another one. So this is going to be the 5% line. So the value is 0. 5. Again, change it to black. And I’ll have my data label as well, a black data label. But this is going to be below the line rather than above. So we can see there’s the range and that line remains the same relative position, 10% or 5%, regardless of whether I change the dates or change what level of the hierarchy I’m looking at. Right, let’s add another one which is going to be right at the minimum we’ve got. So what’s this? 48. 82%.

Okay, so let’s add this. So this is the minimum. So this is going to be minus 00:48. And there is our line and I’ll just make it just fractionally lower. Now the problem is when I get into the deeper levels of the hierarchy, this is no longer going to be the minimum. When I go across 2008, you can see for about half a year the sales on sales negative growth was much more than that. So I can’t just say give me a value and I can use it because that value and the data might change, leading to that value no longer being correct. So let’s remove that one particular line. I’ll click an X next to it and instead of adding a constant line, I’m going to add a minimum line. And the minimum line is not there. And the reason it’s not there is because I’m using a stacked chart.

So if you’re using any of the stacked charts, a stacked area bar, column or 100% stacked bar or column chart or a waterfall chart, then you can only use a constant line. So the thing about a stacked chart is it’s lots of bars on top of each other. Let’s change that to a cluster chart where if for instance, we were having all of the areas, they wouldn’t be on top of each other, as you can see here, but they’ll be side by side. Now, with an stacked visualization, I’ve only got the one option, the constant line. However, with a cluster chart I have many more. I have a trend line. So we can see how we are doing over time. So here you can see we start off relatively good over time and then it gets seemingly getting worse. So this is a line of best fit in other words, we have a minimum line. So now we can see our -48%, but then when we get in deeper, it seems to go all the way down to -100 up because these final two months. Don’t have actually any sales figures. So needless to say, the amount of sales is 100% down on the previous year, but it recalculates according to the data that is showing we have a max line. So just like the min here is our max and again when we drill down, the figure changes.

So here it’s just under 70%, whereas here it’s around 25%. There were certainly some really hot months. We have an average line. So the average is still positive. We have median line. So the first average is a mean, the second is the one in the middle and we have a percentile. So we can see those values which are at the top 90%, so not the top 100% which would be the maximum, but then all of those which are in the top 90%. So in other words, any values which are above here are in the top 10% and similarly we could have the bottom 10%. So any values below this line are in the bottom 10%. So you can see it’s really this small bit here and a fair bit here in the last two months.

Now, there are other lines available. If you have a Scatter chart, then you can have not just a single constant line, but you can have a constant on the x axis and a constant on the y axis. So let’s just take a quick example of that. So here we have a Scatter chart and we’re going to be looking at the number of sales or maybe the price for semidetached compared to detached houses and over time. So here we can say I want an x axis line, so I want a line at 300,000. For instance, there’s my line. Now, this can also be useful to force a different color for zero. So, for instance, suppose I wanted a dotted line at the zero axis. This is another way of doing this, zero value. Similarly, I can do this for the y axis as well. So there we have another value line and there’s also something called symmetry shading. So what this is doing is it’s putting a diagonal line.

So each of these points are points of equal value. So this point here is 200,000 on the y axis and 200,000 on the x axis. So you can see all of those things below that line and all of the things above that line. And then there’s also a ratio line. So suppose you didn’t want it to have a line at equal value. So here we have these being at equal value. Instead, this line plots the totality, the sum of the or the subtotal of the x axis against the subtotal of the y axis to come up with some sort of ratio, a different slope rather than one for one and additionally one more. If this were, for example, a simple line graph, then we would have another measure that we can use for line, and that is a forecast. So we’ve got it so far up to 2016. What could it be in the future? And so this is a fairly wide variety. But if I go down to the months, you can see this is what the computer is expecting. This is a central forecast and these are the outer bounds of the forecast.

And I can say, well, actually, these last two points, they’re not significant. Ignore these last two points and click apply and this is a more realistic forecast of where the future might be. So these are the analysis or analytical lines. They can be used in most visualizations, they can’t be used in combination charts, like for instance, the line and cluster charts, or the line and stacked column chart. It can’t be used in ribbon charts, it can’t be used in funnel charts and can’t be used in other things you might expect like pie charts, tables, gauges, doughnut charts and matrixes. So if you are using these stack charts, you will have access to just one, the constant line. If you’re using other types of visualizations, you might have trends, min, max, average, median, percentiles, and forecast with some special ones for scatter graphs. And all of these can be accessed using the third icon. Instead of fields, instead of formats, you’ve got analytics.

4. Design and configure for accessibility

In this video we’re going to be looking at how to design and configure for accessibility. And there’s a lot of accessibility features already built into Power Bi desktop. For example, maybe you can’t use the mouse while you can use the keyboard. And if you want to know a list of common keyboard controls and just type in a question mark.

And here you can see various sections that you can use the keyboard for. Now you’ve also got accessibility when you’re looking at Windows accessibility. For instance, maybe you’ve got a high contrast color view set in Windows. If you can’t see visualizations clearly, then if you press Alt and Shift and f eleven you get into a table view. Now this table view is similar to the table view. It shows a table. But this is better formatted for screen readers. So that’s some of the ways in which it is already accessible power Bi. But there are some ways you need to add. For example, how can this be best seen by somebody who’s blind? Obviously they can’t see it. They can’t see all of the contours. They don’t know what it’s all about. Well, if we go into the format section in general, then just like with the Internet, there is an Alt text section.

And as you can see, this is a description which will be read by a screen reader when they select a visual so they’ll have some information about it. So this is all about the sales volume change. Now, as you saw with the keyboard shortcuts that we get with a question mark, you can move forward in sections and move backwards. But what’s the order of these sections? Suppose I reduce the weight of this visualization and put a filter on and in that filter I’ll put region name and I want to be able to tab between the two or allow somebody else to do that. Well, if I go to view selection, there is a tab order and you can see the various orders. And now when I click on the first one and click on tab, I now go between these two selections, these two different visualizations. So what we’ll suppose there was a third visualization.

So again I’ll put on another filter which is going to be for area. So now I’ll go between them. But am I going between them in the right order? I’m only going between these two at the moment. So if I go back to selection and tab order, then it can regenerate the tab order, but it’s now going the wrong way. So I’m going to move this one all the way to the top. So we start off with this one and then I want this slicer next and then that slicer. So this allows for a good user experience even if you’re not using the mouse. So there are other things that you can do. For instance, suppose you wanted a theme that was colorblind safe or high contrast. You can do that by going into view themes. Maybe you have a line graph where you are plotting two different lines on top of each other. So let’s just create a line graph and we’re going to plot over time. We’re going to do the average semi detatched price and the average terraced price. So make sure that these are averages.

So yes, these are similar colors. They probably will be visible to people with certain color blindness. But maybe we can do something in addition to that. Maybe we can add markers on these lines. So if we go to shapes and show marker so here are markers but then we can customize them so that there are different markers for the different lines. So maybe we’ll have this being a shape and a fairly big shape whereas these are small circles. For instance, a lot of other things are just potentially good design for reports. For instance, maybe have a background to distinguish various sections, have contrasting colors, don’t clutter the place up if you don’t need to. Maybe have consistency between the different pages. Maybe use tool tips only when necessary. In other words, if you’ve got important information, don’t hide it away on tool tips because some people might not be able to use the mouse easily to access those.

On the other hand, if you are putting too much information on the page, maybe hide some away in the tooltips if they’re not quite necessary. So most of this advice is to do with general design of reports and knowing that people have got particular colorblindness. Find out what your needs are of your users. So two things that could definitely help is if you go into general and add some alt text and if you go into view, select Section and Tab order and make sure that all of your visualizations are in the logical order so that when you press tab, then you will be able to go from one to another naturally. And then you might also want to have a look at the various themes and see whether one of them is right for you.

5. Identify outliers

In this video, I’m just going to have a quick word about how to identify outliers. Now, we’ve already had a look at how we can make use of the analytics lines and putting constant lines and see what values are above and below. Now, it doesn’t look like we are significantly above here, about 20%. It doesn’t seem like 2006, there is much of an outlier. But the problem with this is that we are at the year granularity. All of our access data is in years. If I drill down so that it is months, have a look now at 2006, but then have a look at 20 10, 20 09, 20 10. Now, it didn’t really look at 2009, 2010, there could be such huge values. But if you drill down to something closer to the source data, it does look like there’s something big. Now, another thing that you can do is to use the scatter chart. So this really gets down to a base level of granularity when used, right. So let’s say we have the sales volume in the x axis and then we have the average price in the y axis. Now, sequentially we only have one point because it is averaging everything together. So what we need is the details. Let’s see if we can drag dates in. So go down the hierarchy and the further down the hierarchy you go, the more data you’ve got. So we can see, for instance, these two end months with zero sales.

Definitely looks like they are outliers. Now, we can also add in a legend, maybe we’ll add in the legend of the region name and again, this increases the number of dots we got. So we can see potentially we’ve got something like this greater Manchester in 2004, in July. That is a bit of an outlier in that we have got a huge number of sales, but not necessarily a huge price. It’s just somewhere separated from everything else. And then we’ve got these in West Midlands where we’ve got very low sales but high level of prices. So there’s no particular magic formula to identify outliers. It’s the case of looking at your data at various granularities and going, what is this data telling us?

Because at some granularities, for instance, at the year, you might not be actually seeing anything that’s an outlier. Whereas if we go down to the Christmas period of that year, we can see real outliers. And if you have got two measurements that can be plotted against each other, then do so in a scatter graph with as much detail as you can and then see if any of these figures, these individual plots, are just away from each other and they can represent outliers, they could represent typographical errors. You didn’t have 1000 sales, it shows you 10,000, it shows you 100 sales. And in that way you can investigate the data eight to better and see if there are any obvious errors.