Power BI: Calculated Columns vs Calculated Measures

Hello and welcome back to the course on Power BI, in today's tutorial we're going to add some character to our map. We're going to change the sizes of these circles and we're going to add some color and in addition to all that we're going to also learn a new feature in Power BI which is calculated measures and how that is different to calculate columns. All right so let's get started. First of all we're going to add sizes to our circles so we could differentiate them by sizes. And as the size, we're going to select the sales for the associated geography. So let's go ahead and drag sales or let's open first of all the field here and drag sales into size right away. The size of the circles have changed. Now what we want for the color of the circles is the profit margin in the associate region. However we don't have the profit margins here in our data set. And that is fair enough because our data set is at the Order ID or actually item level. In this case in the case of this table it's at the item level and since the store to this table there the whole thing is in the item level. Now how would we be able to calculate the profit margin at the level of the region if our dataset is at item level. And this is where the calculated measures will come in handy and help us out. So let's learn about calculated measures. I prepared some slides. Let's go through them. All right: calculated columns vs measures We've 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 rules so every blue block here or a 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 might be in Germany some of them might be in France and they'll be aggregate together. So they're aggregate it. And for example in the case of sales which we just put on our map there summed up so all of the rules relating to us and region are added up together and then rows really into another region around it together and so on. And then there are displayed on the map. So as you can see here the red ones would be the U.K. green ones Germany orange ones France. And that's just an hypothetical example. Of course the number of rows doesn't have to be three per geography it can be different 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 visualisation. 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 10 20 50 100 400 150 And the numbers on the left are the profit for the sales of each item. So $10 profit and $100 sales so that the number on the right is sales and then $20 profit $400 in sales so basically the item was sold at $400 but the profit is only $20 because there was a certain expense associated with purchasing the item in the first place. This shipment of items with them marketing of item and things like that so there's a profit and there's a sales, now the aggregation in this case what it does is it adds up the profit and adds up sales so we've got some of profit which is 80 sum plus 20 plus 50. And sum of sales which is 650 100 plus 400 plus 150 keeps 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 columns 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 a profit divided by sales for each individual row which equates to the profit margins in the first case for the first sold item. The profit margin is 10 percent or 0.1 or of the second one in 0.05. The third one is that two point thirty three. And that's also fair enough it's a pretty straightforward procedure just the right one to call. by other and add this synthetic column in a dataset. Wonderful. But now what will happen with this additional column when this whole dataset is aggregated. Well now we're going to have a new aggregation which is the sum of the calculated call or hypothetically it's going to be there but we can if we want to we can add that to our visualization and we can add up this column just as we did with profit or as we did with sales. But the thing with 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 percent and that makes no sense at all and therefore adding up the profit margin isn't going to be useful to it. 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, But let's get rid of these calculated columns and the profit margin for the whole region is it's 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 that sales made in the whole region. So 80 divided by 650 gives us top a sense or 0.12 to and that is the number we were off. So how do we get to that. Well this is our profit margin. And in Power BI This calculation is actually called a calculated measure. So we're taking two columns and we dividing them one by the other but off to the aggregation. So this is the main difference between calculate culms and calculator measures calculate columns are calculated before the aggregation so at the roll level whereas calculated measures are calculated off to 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 visualization and perform a calculated measure and add it to our chart So here we are. And now let's go ahead and create a calculated measure so basically just right click anyway here and select a new measure and here you've got measure over there appears so type in profit margin equals first performed aggregation on the first column that we're interested in which is profit. So make sure that data set pops up so close aggregation divided by the aggregation. of the second column we're interested in which is sales. Again make sure that dataset pops up and close aggregation and click enter. There we go now we have a profit margin here. And as you can see the Icon is a bit different to what we had when we were creating calculated columns. All right so now we're going to take profit margin and we're going to drag it onto color saturation and now we're going to adjust the colors going into data colors and here minims going to be read Maxim is going to be that select I don't know. maybe a blue on blue blue that's a bit too neon. I think something like that so there we go. We've got red and blue. And the beauty of this calculation is that it is dynamic and therefore as we drill into visualization as we learned previously this calculation will update to the correct level of granularity. So watch this we're going to move, let's go back here, so we're going to move we're at country level right now. We're going to move to state level. So as I expand the next level you'll see that it's being recalculated and now our profit margin is calculated the state level can even go further down. Go to the level of the city. Yes it's a very cluttered. But as you zoom in there'll be more space and you can see here the profit margins for the cities and you can also see the sales which is represented by the size of the bubble. So probably let's go back up to the state level that should be sufficient for us. And that's pretty much it. That's how calculated measures work in Power BI. And that's how they're different to calculate column. So the main things to remember are the calculated columns are calculated at the row level and they are actually stored in the data set calculated measures are calculated after the aggregation has been performed. And as a consequence they are not stored in the data set but rather calculated on the fly whenever the visualization is updated. Hope today's tutorial and I can't wait to see the next one. Until then happy analyzing.