DA-100 Microsoft Power BI – Level 2: Formatting our first visualization Part 2

5. 8g. Align visuals

Now in the last video we copied a visualization and put it on top of a second one. In this video we’re going to see what we can do with multiple visualizations. First of all, I’m just going to take one visualization and you’ll see that when I click on a particular visualization. First of all, we have all of these markers around which denote that it has been selected. But secondly, we got this new Visual Tools menu which only appears when I select a visualization. And I want to look at the align. First of all, I can align a visualization to the left, the center or the right of the screen, the top, the middle or the bottom. So if I wanted it to be in the center middle, that’s very quick to do.

But of course you can do all of that with these little helper lines as well. There we go. But we can also use a line with more than one visualization. So if I select not just one but two visualizations now, how am I going to do that? Well, in other applications I can go to left hand corner and drag all the way to the right hand corner to select multiple objects. Here I can’t instead I need to click on the first visualization and then go to the second visualization, hold down the control key and click. So now you can see both of these have been selected. So I can now align center, left or right. And what this does is if these visualizations are of differing width like this, then aligning left, center and right will make these visualizations aligned.

They will still be aligned if they are of the same width, but it’s much more visual if they are of different widths, similarly if they have different heights. So I’ll just make this much taller, flip off, click one, hold on CTRL, click another. Then I can align top, middle and bottom to again have various alignments. Now, if I just select the one again, you’ll notice that there is another option available.

Distribute horizontally, distribute vertically. However, this just doesn’t solve the question of what happens if you’ve got two visualizations that are literally right on top of each other. So now I’m getting these two visualizations to be exactly the same width and height, but you can see they overlap. Well, trouble is one of them has to be in front of the other. But you can select which one by clicking on a visualization and either bring forwards or send backwards. So imagine that you have got a series of playing cards and one playing card. In this case, this one is behind but slightly above another one. So you can’t see the bottom of the card.

So that is what sending backwards does. It has it going all the way back to the end of the deck. However, bringing it forward puts it further and further and further in front of the deck. So if there were 52 playing cards and it was right at the back, then bring it forward would make it 51st on the front and then fifty th and then 49th. So any objects it’s now in front of would be obscured by it. But bring to front brings it right to the front of the deck to number one.

So now you can see it is number one in the deck right at the front. Similarly, send it backwards and send it to the back does the same it send it back by one position or send it back all the way. So hopefully you won’t have overlapping visualizations that often. But if you do, it’s useful to know how you can actually deal with it. So you can have it aligned exactly, or you can have one on top of another when needed. But generally that’s not a good idea. So now I’m going to get rid of this second of visualization by just press highlighting it and pressing delete. And that removes the visualization.

6. 8f. Configuring summarization, both default and in a specific visualization

I want to add an extra column to our visualization. Currently we have the sales volume per region name. I want to add the average price. Now, I can’t just click or check or select average price right now, because if I do, it will create a new visualization. This is because I haven’t selected which visualization I would want average price to be added into. So I need to select the current visualization. And you can see in the fields pane, we have checked region name and sales volume. Those are the two that we’re currently using. So to that, I’m going to add the average price. I’m just going to check it and you can see we need a bit more formatting in terms of the columns. I can’t see the column, so I’m just going to move it to the right. And there we have the average price. Job done.

The average price is for Greater Manchester is £25 million. We can move on to the next video. Just wait a minute. The average price of a property in Greater Manchester is £25 million. That’s about $38 million. I don’t think so. Okay, let’s just have a look at the data. We go to the data icon on the left hand side and we can see that the average price varies between about 40,140 7000. So I’m guessing 25 million isn’t about right. So what’s happened? Well, what’s happened is that even though this is called average price, the computer has identified this as something that you can total a measure and it has totaled it, it hasn’t averaged it. So what I need to do is change this from the sum of average price to the average, for example, of average price. And to do this, I need to go into the visualizations pane. Now, you’d think I could stick in the fields pane? There’s a huge number of options with this dot, dot to the right, but not one of them is change this measure, too.

So what I need to do is therefore go to the visualizations pane and I’ll show you what’s happened. So, previously we’ve been having a look at the format section here, clicking on the format icon. But the first icon is the fields. So it’s a fields icon within the visualization pane as opposed to the fields pane. Okay, so here we have the Average price. And if I click on the drop down, you can see that it is here that we can change the visualization, the aggregation, how it totals up. So at the moment checked is sum. I’m going to click on average, and you can see that our average price goes down from the Beverly Hills $38 million Hans, to something a bit more realistic, around £100,000, $130,000. Notice that the total has also changed. The total used to say something like 154,000,000. Now change this to an average. Then the total is now 97,000. Again, more realistic.

So let’s just have a look at those aggregations, that’s the technical name for totaling. But it might not be a sum total, it could be an average. It’s just making a summary. So we have some that’s a total of all of the numbers average. So that’s the mean average. We have another average called the median. So if you list up all the values in order, so suppose we had 112310. Well, one plus one is two 4717, there’s five items. So 17 divided by five is 3. 4, there’s your mean average, but the median average is the one in the middle if you arrange it in order.

So one one 2310, the median average would be two. So if I click on that you can see it gives us a different sort of average minimum, that is the smallest amount and maximum is the biggest amount. So we can see what is the peak for houses in the West Midlands, for instance, 163,000. Count is the number of values, count distinct is the number of unique values. Usually use countdistinct more for text I would suggest, rather than numbers and then standard deviation and variance. That is only for people who actually understand what standard deviation and variance are. So standard deviation is a measure of how spread out the numbers and the variance is roughly what the average spread is. So if you’ve never used it, you will never need to use it.

Don’t worry, I do use standard deviation for some things I do, but very rarely. So what I want here is, let’s say the mean average of the average price. So there we are, 97,000 overall. Now the problem comes is when I want to use this average price again, it’s fine fixing it just the once, but suppose I’m going to create a new visualization or maybe somebody else is going to create a new visualization, just going to check that and get a total. If I put it into a table of 154,000,000 and that’s clearly not right, they would want something closer to the average average price. Well, you remember near the start of this course I said that there were three stages of creating these visualizations in power bi. First of all you laud the data, then you transform it and model it so that it becomes the way that you want it and then you create your visualizations.

We’ve largely been looking at this third aspect, but we have to go a little into the second, making the data exactly as you would wish. Now Microsoft has changed where you need to go. In previous versions you need to click on a field and go to the modeling tab. In more recent versions you need to click on the field and you go to the new tab called column two. You can see we have under the properties, the data, the default summarization of sum. Now we can change this. So it says average min max count or count distinct. So right at the moment when I add it to a new visualization, it’s going to take the sum of it. So 154,000. But what if by default, instead of taking the sum, we’ll take the average?

So now somebody else comes along in this page, in this model, clicks on this, he doesn’t get an answer of 154,000,000, he gets an answer of around 97,000, closer to what you’re expecting. So in this video, we’ve had a look at the problems with the aggregation with the summarization. We’ve seen how you can change it in a particular visualization by going to the visualization panes, the fields section and changing the aggregation for that one particular visualization. And this might be good if you want to have multiple of the same field. So this is the average of the average price. But suppose I wanted the minimum price and the maximum price. Well, the problem is I can’t just check this average price a series of more times because you can only check it once. If I check it again, it disappears. So what I need to do instead is drag it to the value section within the visualizations field bit. Here we are. So that gives another average price and I can do it again. And so this second one could be the minimum price and this third one could be the maximum price.

It would be nice if they actually said minimum and maximum price as opposed to minimum average price. Well, what I’m going to do while I’m here is I’m just going to rename them. I’m going to click on Rename and put a space in average price and I’m going to say for this one minimum price and this one maximum price. So there are business reasons why you might want to override the default summarization or aggregation.

However, it is always good to ensure that the initial aggregation is actually realistic. Some of the average price is not realistic, an average of an average price is. And you can change the default summarization by clicking on the field in the field panes and going to modeling in the older versions or column tools in the later versions of Power Bi and default summarize.

7. 8i. Changing number and date formatting

Now you may have noticed that these numbers are formatted differently. The sales volume doesn’t have a thousand separator whereas the average price does and has two decimal places. Well we can change the default formatting of any particular numeric field. So if I want to change the sales volume I click on sales volume in the fields pane and I go to modeling or column tools and we can see here formatting. Now there are several aspects to this formatting. First of all the date data type. So this says whether it is going to be a whole number or a decimal number and if so, how many decimal places. Or it could be is it going to be a date or time. So if I click on date you can see date, date and time, date or time. Is it going to be text or is it going to be a yes or no, true or false? And then we have various formatting. So no real formatting is available for text but for numbers. If I click on the sales volume again we can see it can be formatted as a whole number, as a decimal number. So with decimal places, but the default number of decimal places is zero.

So the only difference is actually nothing because you can always add decimal places here it’s more of a declaration of intent rather than adding functionality. So it can be a whole number, it could be a percentage, it could be scientific. So that is saying nine followed by five zeros. It could be general. So what’s best for this? Well, I think what’s best for this is it’s a whole number. But this still hasn’t got the comma in the decimal locator, the thousands separator. So I’ll click on the comma which adds in the thousands and I could say how many decimal places I wanted as well. Now the average price is in fact a number in currency. First of all we have auto number of decimal places. Well actually I don’t need to know that there’s eight pence with this price.

So I’m going to take that down to zero. And what I’m also going to do is I’m going to add a currency. Now there’s a dollar symbol here that doesn’t indicate that everything is going to be formatted as dollars. If I click on it, you’ll see that we’ve got common currency symbols. So in my case this is going to be the British pound. But further down we have currencies from all over the world both in terms of using symbols and in terms of using the freeletter currency code as well for GBP for instance, for great British pounds. So what I’m going to do is I’m going to format this as pounds. English United Kingdom and in case you’re wondering, yes, there’s a difference with some of the locales. For instance, the Euro sign in some locales can go after the number and sometimes before it. So this is why it’s very specific.

So I’m going to say this is English United Kingdom. Now, you can see that any changes to the formatting that I do are affecting the formatting that is live, that are used in visualizations which have already taken place. So what I’m going to do is I’m going to change this so that it is pounds and zero decimal places. But what if I wanted one of these columns to be to two decimal places but not the others? You may notice that if I go to Visualization Fields Values that there is no change the format. However it is available if I click on a particular visualization, I don’t need to click on a particular column. Go to visualizations format and then scroll down to field formatting. So you can see we’ve got five columns at the moment and I can change the average price to be in a different number of decimal places. So I want this to be in two decimal places so the scrollers don’t work interestingly. But I can just click inside it and type the number two. And so you can see there is two there.

It takes more places. There is no actual point possibility of changing it. So it doesn’t show the currency symbol or the comma separator. But what you can do is change the units. So I could say I want this to be in thousands 97,000, you could say I want it to be millions or billions or trillions. So you need to change the currency and whether using the commas in the default for that particular field but within a particular visualization, you can change the number of decimal places and you can change whether it is being displayed in thousands, millions, billions or trillions. Now, a quick look at dates. Dates again you can change the format.

Needless to say, all of these to one decimal place doesn’t actually affect dates. But here we can change the format so that it is a shorthand version or a longhand version. So it gives the technical definition afterwards, but basically concentrate on this first one. So Wednesday the 14 March 2001 or Wednesday March the 14th 2001. You’ll also notice at the bottom you can just have March 2015 or you can have March the 14th or you can have year 2001 or year or one all possible abilities for formatting dates.

So you can see that if you’re formatting numbers or dates you generally format the default which will be used in all of these utilizations. However, in a particular visualization in a particular column you can change the number of decimal places and you can change whether it is going to be shown in billions or trillions by going to the Formatting and Field Formatting section. In more modern versions of Power Bi you can also type the format directly into the format box, just like you do a custom format in Excel. For more details about that in the next video.

8. Practice Activity Number 2 – The Solution

So how did you do with this practice activity? In this section we’ve been looking at formatting visualizations as well as aligning multiple ones and configuring this summarization. And that’s the very first step that we’re looking at. Previously we in this visualization and if you can’t see the specific fields in the visualization, then click on the vis to start. In this visualization we have file extension and size. But it doesn’t tell me what sort of summary it’s going to be. Is it going to be a count, is it going to be a sum, is it going to be min and so forth. So we need to check the default summarization for the size field and we do that by clicking on the size field over here and then going into the modeling or column tools. And you can see the default summarization is sum.

Next, we are adding the date created field into the visualization as well. So currently we’ve got file extension and size. So you could have it at the beginning, I’m going to have it in the middle. So now you can see on this visualization that we’ve got file extension, year, quarter, month, day and size. So my next question was has it created a detailed hierarchy? And you can see it would because we got this little drop down next to date created. And in there we’ve got the date hierarchy. Now, if it did, we need it to click on the date created in the values well and we’re going to select date created as opposed to date hierarchy. So now we just have a single date created. So now we’ve got the file extension, say MP3 and we’ve got the date created. But we’ve got all of these individual times, the 6 May 2010 at ten or four and 43 seconds, 44 seconds, 45 seconds.

So what we’re going to do now quite often that’s probably why you want to have hierarchy. So you just have year month, for example. But we’re going to change the summarization for date created. Now, if I click on date created you can see that the default summarization is don’t summarize, but we want to change it just for this one visualization. I did say don’t change the default. So this will change it for all future visualizations, not what you want.

And you’ll also notice in this default summarization, it gives me just two options, count and count distinct, whereas I want the earliest date created. So I’ll click on this drop down and click on earliest. And so now we’re back to our free rows with a total row and we’ve got the earliest date created. Next we need to change the default formatting of the date created field. And to do that we click on the date created or column tools and go into modeling formatting format.

So because it’s a date time field there’s only one section available to us and we’re going to change it so it shows the month and year and you can see that this has affected this current visualization as well. Next. Well this visualization is a bit too small so let’s change the font size. So with the visualization clicked I go to the format section here and we go to not style but grid and we can see here the text size we can increase. So while you think it might be part of style and in fact there are some nice styles in there to actually change the textile it’s actually part of the grid text size, it’s actually part of the grid so they are much more visible. Now change the color of the header background so let’s close this grid section and scroll down and we got things like title, background lock, aspect board and so forth but actually the headers are up here near the top separated from all of the similar type of things that you might expect. So column headers at the top and then we have then the values and then the totals at the bottom.

So we need to change the color of the background for the header so we’ll change that to a reddish color. There we go, maybe a bit too strong, I’ll just make it a bit less better, weaker. Now next in this visualization we need to change the size so it shows instead of the individual bytes, millions of bytes and to do that we go down to field formatting and here you can see what we are looking at so if we were looking at earlier state or size we would have different options. So size you can see that we’ve got this display units so we can have them in thousands, millions, billions or trillions so I’m going to choose millions and now you can see 289,001,570 6 million, much better.

I’m more easily able to read that than work out how many million that is. Now next we have to copy this visualization so I’ll select it, copy and deselect it and paste and I’ll move it away. And also this second visualization so it just has year of date created and size. So we get rid of file extension, we get rid of size. Well actually if we don’t get rid of size but we get rid of date created and inside date created we go into the date hierarchy and pull out the year. So now it’s not just the minimum of the date created, it is every single year that we have got. As you can see we don’t have an earliest date created of 2007 but we have 2000 900 million bytes on that here.

So that’s the difference between earliest date created and just date created. And now we’ve got to move this visualization, the first one so that the center of it is halfway down screen. So we just drag on it and you can see we have all of these little lines coming up. And so the center of it being halfway down the screen is there. Now you might argue, well, actually I need to tighten up the visualization and then it’s a different place halfway down the screen. And now I need to move the second visualization. So the top is the same, so click on it, drag it, move it down and I find I can’t go all the way down. So again, I’m going to decrease the size.

And now it works. So we have now the year header being right at the same level as the file extension. You can see this little dotted line that appears when you get it just right. So in this video, we’ve had a look at everything that we’ve looked at in this section. We’ve looked at how to style and format visualizations. We’ve looked at how to align two different visualizations together and we’ve looked about changing the number and date formatting and the summarization, both the default and the one used for this particular visualization. Now, you’ve probably had enough of tables, but they are probably the most important element. So you can actually read them and see what’s what, but they’re not as visual as some of these other visualizations. So in the next section we’ll have a look at some of the chart visualizations.