PL-300 – Section 3: Part 1 Level 2: Formatting our first visualization Part 3

23. Format Painter

Now, what if you have multiple visualisations set up, and you decide that you want to change a style of them?

So, let’s create a second visualisation. I’m just going to click on this first one, press Ctrl + C for copy, click elsewhere, and Ctrl + V for paste. So, now we have two visualisations, both with a title with a blue background, and both with a red border. So, let’s change this on one of the visualisations.

So, I’ll click on the visualisation, and I go down to Title, in the Format section, and in Background colour, go to change that to a light red. And then the Border, going to go down and change that to black. So, now we have a different style, and we want to change all of your other visualisations. Well, this is when the Format Painter could come in handy.

Now, the Format Painter is something that is underutilised, but is very useful in Excel. So, if I have a cell, say, C3, and another cell, D5, and C3 has a yellow background and red foreground, and I want D5 to have the same. So, if you go into D5, and I could change it. Or, I click on C3, I click on the Format Painter, and I click on D5. And now you can see that D5 is the same format as C3, even though the contents aren’t changed.

And it’s exactly the same in Power BI. So, I click on the source visualisation, the one which has the formatting I want. I click on Format Painter, and then I click on where I want to apply to. And now you can see that both of these visualisations have got a red title and a black border. Now, notice it doesn’t actually change the contents. So, let’s change this content so that it doesn’t have region name.

So, I’m going to change this, again, so that the title, instead of having a red background, has a purple background. So, again, I click on the visualisation it’s going from, click on Format Painter, and click on the visualisation it’s going to. So, you can see the formatting has changed, but not the actual contents. So, I’m going to press Undo to get back to where I was.

So, Format Painter, it is very useful if you have got multiple visualisations, and you change the formatting in one, and you want that to go across to other visualisations.

24. Configuring Summarization, Both Default and in a Specific Visualization

I want to add an extra column to our visualisation. 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 AveragePrice right now, because if I do, it will create a new visualisation. This is because I haven’t selected which visualisation I would want AveragePrice to be added into. So, I need to select the current visualisation and you can see in the Fields pane, we have checked RegionName and SalesVolume. Those are the two that we’re currently using. So, to that I’m going to add the AveragePrice. 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 AveragePrice, job done. The AveragePrice for Greater Manchester is 25 million pounds, we can move onto the next video.

But wait a minute, the AveragePrice of a property in Greater Manchester is 25 million pounds, 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 AveragePrice varies between about 40,000 and 147,000. 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 AveragePrice, the computer has identified this as something that you can total and measure and it has totalled it. It hasn’t averaged it. So, what I need to do is change this from the sum of AveragePrice to the average, for example, of AveragePrice. And to do this I need to go into the Visualisations pane. Now, you’d think I could stick in the Fields pane, there’s a huge number of options with this dot-dot-dot (…) to the right. But not one of them is Change this measure to…

So, what I need to do is, therefore, go to the Visualisations pane. And I’ll show you what’s happened. So, previously, we’ve been having to look at the Format section here. I’m clicking on the Format icon. But the first icon is the Fields. So it’s a Fields icon within the Visualisation pane as opposed to the Fields pane. So, here we have the AveragePrice, and if I click on the drop down, you can see that it is here that we can change the visualisation. The aggregation of how it totals up.

So, at the moment, checked is Sum. I’m going to click on Average and you can see that our AveragePrice goes down from the Beverly Hills $38 million mansions to something a bit more realistic. Around 100,000 pounds, 130,000 US dollars.

Notice that the Total has also changed. The Total used to say something like 154,000,000. Now, I’ve changed this to an Average, then the Total is now 97,000,000. Again, more realistic.

So let’s just have a look at those aggregations, that’s the technical name for totalling. But it might not be a sum total, it could be an average. It’s just making a summary.

So, we have Sum, 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 a one, one, two, three, ten. Well, one plus one is two, four, seven, seventeen. That’s five items, so seventeen 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, two, three, ten, 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 at 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 Count (Distinct) 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 AveragePrice. So, there we are, 97,000 overall. Now, the problem comes is when I want to use this AveragePrice again. It’s fine fixing it just the once, but suppose I’m going to create a new visualisation or, maybe, somebody else is going to create a new visualisation, 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 AveragePrice.

Well, you remember near the start of this course I said that there were three stages of creating these visualisations in Power BI. First of all, you load the data. Then you transform it and model it so that it becomes the way that you want it, and then you create your visualisations. We’ve largely been looking at this third aspect, but we have to go a little bit into the second, making the data exactly as you would wish to get the answer to this.

So, what I would need to do is go into the Modelling tab here. And if I click on one of these fields, let’s take AveragePrice, you can see we have under the Properties, the Default Summarization of Sum. Now, we can change this, so it’s Average, Min, Max, Count, or Count Distinct. So, right at the moment, when I add it to a new visualisation, 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 were 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 visualisation by going to the Visualisation panes, the Fields section, and changing the aggregation for that one particular visualisation. And this might be good if you want to have multiple of the same field. So, this is the average of the AveragePrice, but, suppose, I wanted the minimum price and the maximum price. Well, the problem is, I can’t just check this AveragePrice a series of more times, because you can only check it once. If you check it again, it disappears.

So, what I need to do instead is drag it to the Value section within the Visualisations field bit. Here we are. So, that gives another AveragePrice, and I can do it again. So, this second one could be the minimum price, and this third one could be the maximum price. It would be nice if it actually said minimum and maximum price as opposed to minimum of AveragePrice. 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 AveragePrice, and I’m going to say for this one MinimumPrice, and this one MaximumPrice.

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. Sum of the AveragePrice is not realistic. An average of an AveragePrice is. And you can change the default summarization by clicking on the Field in the Fields pane and going to Modelling, and default summarization.

25. Changing Number and Date Formatting

Now, you may have noticed that these numbers are format differently, the SalesVolume doesn’t have a thousand separator, whereas the AveragePrice does and has two decimal places. Well, we can change the default formatting of any particular numeric field.

So, if I want to change a SalesVolume, I click on SalesVolume in the Fields pane, and I go to Modelling or Column tools and we can see here Formatting. Now, there are several aspects of this formatting. First of all, the Data type. So, this says whether it is going to be a Whole number or Decimal number and if so, how many decimal places. Is it going to be a Date or Time? You can see Date, Time, and Date/Time. Is it going to be Text, or is it going to be a yes or no, True/False?

And then we have various formatting. So, no real formattings available for text. But for numbers, if I click on the SalesVolume 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 Whole number. But this still hasn’t got the comma in the decimal locator, the thousand 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 AveragePrice 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 pounds but further down, we have currencies from all over the world, both in terms of using symbols and in terms of using the three-letter currency code as well so 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 locals, for instance, Euro sign in some locals 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 visualisations which have already taken place. So, what I’m going to do is I’m going to change this so that it is pound 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 Visualisation field Values, that there is no change the format. However, it is available if I click on a particular visualisation. I don’t need to click on a particular column. Go to Visualization’s 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 AveragePrice 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 2, and so you can see there is 2, in decimal 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 currencies and whether you’re using the commas in the default for that particular field, but within a particular visualisation, 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 14th of March, 2001. Or Wednesday, March 14th, 2001. You’ll also notice at the bottom you can just have March 2015, or you can have March 14th, or you can have year 2001 or year ’01. 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 the visualisations. However, in a particular visualisation, 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. We’ll have more details about that in the next video.

26. Custom Number and Date Formatting

In this video, we’re going to have a brief look at the custom formatting that you can put in in newer versions of Power BI Desktop. So, if you are limited to this list, then you may have an older version.

Now, it’s the same sort of things that you can do in Excel. So for example, a zero indicates that a figure has to be there. So, if I put in lots of zeros, then you can see that even if the zeros are not necessary, then they will be there.

Secondly, we can put in commas as thousand separators. These will be shown as your thousand separator. So, if you use a full stop, a dot as a thousand separator, then that will be shown instead.

Then, we can also put in a dot and then, however, many decimal places you might want to use.

Now, you can also use a hash (or pound) sign. So, this is probably a bit more common. This indicates, “Okay, give me these digits if they are there, if not, don’t put lots of zeros.” So, if I was to add a few more at the end, then you will see, and you can see, it’s a bit tricky to do. It won’t force a zero to be there if it doesn’t need to. Similarly, you can put these hashes (or pound) signs after the dot as well.

You can also put things in strings. So, suppose, I wanted to put a speech mark and then the word pound, preceded by a space. That’s also possible. Now, just like in Excel, you can also put in up to semicolons and this shows the first bit is for all positive numbers, the second is for all negative numbers and the third bit is for zero. So, we’re getting really advanced here.

So, you might want a different format for positive numbers, compared to negative numbers, compared to zeros. But if you don’t put any of those in, then it will apply to all numbers. So, let’s say, I wanted this to be formatted with a comma. Then, I can just put in hash (or pound), comma, and then a couple more, and then zero because I generally want the units figure to appear, even if it is zero and then maybe dot and a couple more decimal places.

Now, I could also do custom formatting for dates as well. And if I click on the Format, you can see the various custom formats that are here but you can also add your own. So, for instance, you can have ds. While a single d is for the date, so 9. Two ds will mean that there will be a leading zero at the beginning, so 09. And then ms would be for the months. So, you can have again one m, which would be the number 1; two ms, which would be the number 01; or four ms, that would be March. Three ms would be the shortened version, Mar. And it’s the same for ys. You can have two ys, and you can have four ys. You can also have h, one or two for hours. N or m for minutes and s for seconds. You can also have AM, PM. You can have also additional things, such as colons to separate, for instance, the hours from the minutes. So, if this gets a bit confusing, well, firstly, don’t be too confused.

But secondly, why not have a look in Excel? So, have a particular number format. So let’s set this up as a percentage sign with some decimal places. If we go into the Number of Format cells and go into Custom, here you can see how it is formatted. And you can see some other suggestions as well. So, you can use all of these in Power BI.

One final thing, you can also do all of this in the Model tab. So, if I was to go to the Model tab, I click on a particular field, so let’s go down, it’s a bit trickier, this thing, than the actual scroll bar, go down to SalesVolume. We’ve got this Properties pane. If you can’t see it, it might be collapsed. And if I scroll down, we can see the Data type as we’ve had before and the Format but the bottom format is different, it is Custom.

And this then allows you to put in the custom format as we have just done. So, none of these options in the Model tab actually give you anything else. It’s just a different way of being able to set it up. So, similarly, if I go to Date and scroll down, I can change the Date format to Custom, and then in here, I can type in my date format. So, I could type in d mmmm yy, for example. If I tab out of it, then you’ll see an example of what this will look like.

So, this is how you can implement your custom formatting. Now, for more on this, you’ll see attached to this video as a Resource, a link to this web page, which goes through all of these various symbols. So, you can read more about it if you wish to. However, most of the time, you’re probably fine just sticking to the default formats that you can see here.