Calculated columns vs. calculated measures - Part 1

Hello everyone, and welcome back to the course on power BI. In this tutorial, we're going to go through some steps to give more life to our our map. We're going to change the size of these bubbles. And we're going to add some coloring into this as well. We're also going to explain some concepts between what is the difference between calculated columns versus calculated measures. So let's get into it. Right now we're at the very bottom of our hierarchy. So we're at the city level at the moment. Let's scroll to the top because our map is just too cluttered at the moment okay great. So for the very first step, let's change the size of our bubbles to represent the number of sales that we're making in each of these countries. So we'll go to the data pane and we'll open the order breakdown table. And we'll grab in the sales the number of sales. We'll click and drag this into the bubble size. And you can see that the size of these bubbles has changed relative to the amount of sales that we are making in each of these countries. So for example, UK, the UK, uh, and UK, the France and Germany, these are three of our most largest bubbles. So that means these are where we're making the most sales. And of course if we drill down into the region level, we can see, uh, for those countries exactly which regions are performing very well. So in the UK it's England and France, it's the Ile de France, and in Germany it's North Rhine-Westphalia. Um, I hope I pronounced that right. And we can also, of course, drill down to the city level and within the city level of, uh, let's see what's in the UK. So London, London seems to have some of the most sales and in France. Oh, that's, that's difficult to determine. So there's no one city that pops out. Oh, maybe there is. That's, uh, Paris and in Germany. Oh, you have a, you have a couple in Germany. So. Berlin. Oh, that's, uh, this is the only one in Germany. And then we can also see Vienna is, um, is quite a large chunk, um, of sales, so that's pretty good. Let's scroll back to the top level. And what we want to do next is that we want to color each of our bubble charts so we can see that the number of sales. But we also want to see, uh, for example, what is the profit margin in each of these countries or regions. And the way to do that is by making a calculated measure. Reason why we can't do that immediately is our data set is at a product level. So we have the profit for each product, but we don't have the profit margin for, um, you know, country or a region. So we'll have to perform some additional calculations to be able to do that. So to explain that concept, Kirill will go through some slides that he has prepared and walk you through it. All right. See you soon. All right. Calculated columns versus measures. We've already discussed calculated columns. So this is going to help us understand how calculated measures are different. So let's imagine that we've got our data set. And on the left we've got all these rows. So every blue block here or rectangle represents a row. Now what happens when we aggregate our data is certain rows are combined together. So for example in our case they might be in the same region. So some of them might be in UK, some of them might be in Germany, some of them might be in France. And they'll be aggregated together. So they're aggregated. And for example, in the case of sales, which we just put onto our map, they are summed up. So all of the rows relating to a certain region are added up together, and then rows relating to another region are added up together and so on. And then they're displayed on the map. So as you can see here, the red ones would be the UK, green ones, Germany, orange ones France. And that's just a An hypothetical example. Of course, the number of rows doesn't have to be three per geography. It can be different, it can be varying, and so on. But this is the main concept that we have data at the row level. Then we have data that is aggregated to the level of granularity of our visualization. And finally the data is displayed on our visualization. Now what are calculated columns which we have discussed previously in this course? Well, let's imagine that we have some data in these red rows. So let's say ten 2050, 100, 400, 150. And the numbers on the left are the profit for the sales of each item. So $10 profit and uh, $100 sales. So the number on the right is sales and then $20 profit for $100 in sales. So basically uh, the item uh was sold at $400, but the profit is only $20 because, uh, there was a certain expense associated with purchasing the item in the first place, or with the shipment of the item, or with the marketing of the item and things like that. So there's the profit and there's the sales. Now the aggregation in this case, what it does is it adds up the profit and adds up the sales. So we've got some of profit which is 8010 plus 20 plus 50 and sum of sales, which is 650, 100 plus 400 plus 150 gives us 650. And what we visualized as the size of the bubble on our map was the sum of sales, which is pretty straightforward. Up until this point. Everything is quite straightforward and very intuitive. But next, what we want to do is let's say we want to create a calculated column. So something we've talked about before and this is a very simple procedure. Basically we calculate something a column based on our existing columns. And we add it to our data. And here is a calculated column which represents profit divided by sales for each individual row, which equates to the profit margin. So in the first case, for the first sold item, the profit margin is 10% or 0.1. For the second one is 0.05. For the third one is 0.33. And that's also fair enough. It's a pretty straightforward procedure. Just divide one column by the other and add this synthetic column into our data set. Wonderful. Um, but now what? What will happen with this additional column when this whole data set is aggregated? Well, now we're going to have a new aggregation, which is the sum of the calculated column or hypothetically, it's not going to be there, but we can if we want to, we can add that to our visualization. We can add up this column just as we did with profit or as we did with sales. But the thing with, with um, uh, the profit margin is that it doesn't make sense to add up the profit margin for each individual item, right? So if you had more items, you could come out. you could easily get a profit margin of over 100%, and that makes no sense at all. And therefore adding up the profit margin isn't going to be useful to us. So in that case, what is the profit margin for a whole region? The parameter that we're looking for, the parameter that we want to add to our visualization. Well let's get rid of these calculated columns. And the profit margin for the whole region is it's quite intuitive that the whole profit margin for the whole region is actually the division of these two numbers. So 80 divided by 650. So the profit made in the whole region divided by the sales made in the whole region. So 80 divided by 650 gives us 12% or 0.12. And that is the number we're after. So how do we get to that number. Well this is our profit margin. And in power by this calculation is actually called a calculated measure. So we're taking um two columns and we're dividing them one by the other, but after the aggregation. So this is the main difference between calculated columns and calculated measures. Calculated columns are calculated before the aggregation. So at the row level, whereas calculated measures are calculated after the aggregation has occurred, and also as a consequence of that, calculate columns. Once you've calculated them, they're stored inside your data set. They're stored inside your just alongside of the rows that you got originally. Whereas calculated measures, they are calculated every time you recreate your visualization or you recalculate your visualization so they're not actually stored in your data set, they are a dynamic thing that gets calculated on the fly when it is required. So that's the main difference. And hopefully this explanation made it a bit more a bit easier to understand. It's a bit clearer now. And now let's go back to the visualization and perform a calculated measure and add it to our chart.