PL-300 – Section 9: Part 1 Level 8: Other Visualization Items

79. Define Quick Measures

Hello, and welcome to level eight, the final level of this part one, which is all about visualisations.

Now, in this level, what we’re going to do is look at all the other things that we need for the DA-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 are actually new 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, D-A-X, formulas. And we 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 card, 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 access 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? I mean, we will eventually have these yearly graphics but what if we wanted it every month? So in January we’ve got 8,600 and February 8,900. Well, what if you could show 8,600 plus 8,900 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 a select calculation that we need to do first. And you can see that there are an awful lot of potential calculations. Now, 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 shown. So, it’s filled this 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 over these little eye symbols, and it tells you what information it’s looking for.

So, let’s click okay. And you’ll see in the right hand side on the 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 auto filled in a fair bit. It’s filled in, well, you wanted 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, the date, and number of periods? Well, I’m going to say one year. Click okay, 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, well, this is a hierarchy, we can just drill up, and we’re able to add legends. We’re going to see 2008 being a particularly bad year. And we will 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 for 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 there are calculated measures that 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 into part three of this course, you will be able to know what calculate is, and sum, and data, and what each of these do. And then you 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 dropdown 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 4.5, but 4,5. Then at the time of the coding, quick measures won’t actually work properly. So, you may need to modify the formula for it to work, maybe changing the commas for semi-colons. It does seem to be a bit of a hole in all of this quick measures. It’s a hole that, hopefully, they will fill in at some point, but these are quick measures. They are there to kickstart the creation of new measures.

80. Export Report Data

Now, in the previous video we created, this little Visualisation with a year on year percentage change. But what are the figures behind this?

Well, to see some of the figures, you 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 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, dot, 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 its 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 over 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 to the file called By Year, but its 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 visualisation.

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 Tooltip. So, it doesn’t actually alter the visualisation apart from the fact you can see it in Tooltip. You can see it when you show us the 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 are missing a year, you will 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 visualisation has been made up.

81. Create Reference Lines by Using Analytics Pane, Including the Forecast Feature

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 normal sales to go up by five to 10% each year. We can add lines onto our visualisations by clicking on the visualisation and then going to this 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 10 because the computer will interpret that as being 1000%. So instead I need to say 0.1, so you can change the options as well. For example, I want to in black, transparency 50% yeah, that’s okay. I could have it solid or dotted, but as here, I prefer 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 the 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. Let’s add a second line, so first of all, let’s rename this. So like 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% lines of the value is 0.05. 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 that as 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 0.48, and there’s our line and we’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 the 2008, you can see full 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, click an X next to it and I’m going to, 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 stack charts, a stacked area, bar, column, or a hundred percent stacked bar or cone chart or a waterfall chart, then you can only use a constant line. So the thing about a stock chart is its 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 there’ll be side by side.

Now, with a stacked visualisation, 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 all the time, so, hey you can see, we start off relatively good all the 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 minus 48%, but then when we get in deeper, it seems to go all the way down to minus a hundred up because these final two months don’t have actually any sales figures, so needless to say, the amount of sales is a hundred percent down on the previous year. But recalculates, according to the data that is showing, we have a max line, so just like the main, he was our max and again, when we drew down, the figure changes. So, here it’s just under 70%, whereas was here, it’s around 25%, there was suddenly 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 the top 90%. So, in other words, any values, which are both 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, they’re overlines 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 semi-detached compared to detached houses and overtime. 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 colour 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 a nova 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, vehicle values. So, at 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 subtotal of the X-axis against the subtotal of the Y-axis to come with some sorts of ratio, different slope, providence 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, well, 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, I see these last two points, then I’ll 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, so analytical lines that can be used in most visualisations that come to be used in combination charts, like for instance, the line and cluster child or the line and stacked column charts it can’t be used in ribbon charts, can’t be used in funnel charts and can’t be used in other things you might expect like pie charts, tables, gauges, donut 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 visualisations, you might have trends, min, max, average, median percentiles, and forecast with some special ones, the scatter graphs. And all of these, can be accessed using the third icon instead of fields, instead of formats, you’ve got Analytics.