First Calculated Columns and Measures in Power BI

expertiseexcelerate

First Calculated Columns and Measures in Power BI  Unlock the power of calculated columns and measures in Power BI with this comprehensive playlist. Designed for professionals, data enthusiasts, and Power BI users, this course takes you through the essential steps of creating calculated columns and measures, enabling you to derive deeper insights from your data. Whether you’re new to Power BI or looking to enhance your skills, this course will guide you in mastering these powerful tools to boost your data analysis and reporting capabilities.  What You’ll Learn: • How to create calculated columns and measures in Power BI • Key differences between calculated columns and measures • Best practices for leveraging DAX formulas to enrich your data models • Tips for improving report performance and accuracy with calculated fields  Ideal For: • Data Analysts and Business Intelligence Professionals • Power BI users aiming to enhance their reporting and data modeling skills • Beginners looking to learn the foundations of calculated columns and measures  Subscribe to stay updated with the latest software tutorials and become proficient in Power BI’s advanced features for more impactful data analysis.  Power BI Calculated Columns Tutorial Measures vs Calculated Columns in Power BI Creating First Measure in Power BI Power BI Data Modeling Techniques Power BI Calculation Groups Explained DAX Basics for Calculated Columns Power BI Calculated Fields Guide Advanced Measures in Power BI Understanding DAX for Beginners Power BI Data Analysis with Measures  #PowerBI #Datavisualization #Businessintelligence #Datamodeling #PowerBIcolumns #PowerBIdashboard #PowerBItutorial #Dataanalysis #PowerBImeasures #DAXformulas #PowerBI2024 #PowerBIadvanced #MicrosoftPowerBI #PowerBIforbeginners #PowerBItipsandtricks #LearnPowerBI #PowerBItips #PowerBIreporting #PowerBIexamples #PowerBItraining #CalculatedColumnvsMeasure #PowerBITutorialforBeginners #PowerBIDashboard #PowerBIDAX #Measures #DAX

Transcript

00:00Hey friends, welcome back.

00:07Now after we set up our data model, now it's time to dive deeper into calculations in Power

00:12BI.

00:13And there are various options how we can calculate something, there are calculate columns, there

00:17are measures, so a lot to explore.

00:19So let's get into it.

00:21So our first calculation could be that we want to calculate our revenue or our sales.

00:27Why is that?

00:28Because if you take a closer look at the sales table currently, and of course we could also

00:33take a look at the data view, select the sales there, we can see that we have a quantity

00:37column in here and we have a price column in here.

00:40And by the way, I can see there are a lot of ones, but if you simply took a look here,

00:45you see that there are also, for instance, three items purchased or four items purchased.

00:49So we can see there are various entries in here.

00:52So that means in order to calculate the revenue here, we need to multiply the quantity times

00:57the price.

00:58So for each row in the data set, we want to multiply how much has been well sold and what

01:03was the price for this item in order to derive the revenue or the total sales, depending

01:08on how you want to call it.

01:10To do this, we can actually create a calculation inside the Power Query Editor.

01:16So that means we can go to home and in here, I'd like to go to transform data.

01:20So click this option here and then the Power Query Editor opens again and then we can see

01:25all the columns we have.

01:28So our first calculation will be on the sales table.

01:30So we can select sales in here and then we can create here a new column.

01:36And this can be done by simply going to add column here.

01:39So at the top in the ribbon bar, click on it and then there's an option to create a

01:43custom column.

01:44That's what I would like to do now.

01:47So I can click on custom column, here we go.

01:51And now I can see here, I have here my custom column, which currently specifies here no

01:56syntax errors have been detected.

01:58So that is fine.

01:59Of course, I don't have a formula in here right now.

02:03So I need to create something.

02:05So first, let's actually name this maybe, as I said, total sales.

02:08Okay, let's call it total sales.

02:10And because it's a column, let me just name it column for now.

02:14So that's our total sales column.

02:16And in order to now write our formula here, unfortunately, I can't zoom in here, but hopefully

02:21you can see it.

02:22Otherwise, please follow along that you know what we need to do here.

02:27So I can now either, I have actually three options in here.

02:29But what I could do is I could simply open the bracket.

02:33So the normal bracket like that.

02:35And then I have here and 10 cents, which now tells me all the columns I have, which I can

02:40refer to.

02:41For instance, I could use price, I can use quantity and that's what I want, right?

02:44To select it, simply click on price.

02:46And now I've selected the price column.

02:49But the other option would be to simply, if I remove this for now, simply to go in here

02:54available columns.

02:55You can scroll down.

02:56And if I select price from here, I can either click on insert, then it's also in here.

03:01Or the third option would be that I simply double click, double click on price, then

03:06it's also inserted in here.

03:09And I can then multiply the price, simply use the star symbol here, and I want to multiply

03:14by the quantity, right?

03:15So I can double click on quantity.

03:17And then I also have my quantity inside.

03:19And that's actually my, actually my formula, because I need to multiply the price times

03:25the quantity for each row in my data set in order to calculate the sales.

03:31You can also see there are no syntax errors.

03:33If I would, for instance, remove one of the brackets here.

03:36So in this case, if I remove this one here, now you see token, in this case, the closing

03:41bracket here is expected.

03:43So it gives you an indication and also the red quickly line here that something is wrong

03:49here or missing.

03:50In this case, we can just edit it by simply adding this closing bracket here.

03:54And now you can see that everything is fine.

03:57So maybe Power BI in the future decides that if everything's fine, that they don't show

04:02this red box, but maybe a green box.

04:04I think that would be more helpful, I think, really.

04:07But right now it's like that.

04:10So let's stick to that.

04:12There are no syntax errors detected.

04:14So obviously the check here is the green one.

04:16And of course, now we can simply create this calculation, solve this custom column in here

04:21by simply clicking on OK.

04:23And then you see that now we have a new column here, which is this total sales column.

04:27And it shows here the value.

04:29So for instance, four times this 1848 is this 7392.

04:35And the same is true then for all the other values we have here.

04:37For instance, you want one times 127 is 127.

04:42So besides this, you also see that there is actually currently the column, but the

04:48data type has not been set because it's ABC 123.

04:52And always when you see this, this is an indication that we need to set up the data type.

04:56We need to specify it.

04:59So let's go in here, click on this option here, and then specify this should actually

05:04be in this case, these are whole numbers in here.

05:07So I stick to whole number.

05:08Normally, you could also go with decimal.

05:10But for now, let me go with whole number here.

05:13Now I can see my total sales column like that.

05:17So now this has also been applied and maybe you have already spotted it, maybe not.

05:22But one more time, all the steps we do here, also adding this calculated column is here

05:27recorded in this applied step window.

05:30So we can clearly see that the source was without the column here.

05:35There is after price column, this is the last column.

05:37Then we edit the custom column.

05:39So now we have a new column in here.

05:41And then you can see there, the data type has not been changed and then the change type

05:46and also the data type changes.

05:48So all the steps, the inverse steps are recorded in here.

05:52And now we have our total sales column in here and this can be used in the model itself.

05:58So we could now go under home, click on close and apply.

06:03And then we just wait here and now data is loaded.

06:07And let's go.

06:08So now you can see actually our table, right, total sales column.

06:12And if I go to the report view here and then I want to use this here, I can simply click

06:18on it, take this option and I can see here my bar chart or if I either suggest a type,

06:24I can see this as, in this case, a KPI or of course I could also select the KPI directly

06:30to have it as KPI.

06:31And if I go to the formatting option here, let me go here, currently I only can select

06:37the category label here or unselected.

06:39So let me go to more options.

06:41Then now also here, the formatting pane appears and let's actually go to the callout value.

06:47And then let's maybe show some display numbers here and go here with none.

06:51And then I can see here the number like that.

06:53And of course, maybe I'd like to add here, this again, a formatting which can define.

07:00So if I go to data view here and select my total sales column like that, if I do this,

07:06then you can see up here, new ribbon or this pop-up box is up here.

07:12And under the table tool of the column tools here, there's an option here to specify also

07:15the format.

07:16For instance, if I say these are US dollars, I can click here at the dollar symbol and

07:20then I would see this as a dollar number in here like that.

07:23I can also add here, you can see that by default also the thousand separators have been added

07:28in here.

07:29Maybe for you, this would be commas and this is just a local setting for me, but you can

07:34see that you can form it here, the number that way you want it next to that number here.

07:41This can also be used or sliced now by our, in this case, dimension tables.

07:47So remember, if I go back to my model view, we have a relation between the product and

07:53the sales.

07:54So now you can also see the tool sales column appearing here.

07:58This means that because of this relation here, we can use now our product table data

08:04and filter the sales table.

08:06For instance, this total sales column.

08:09So that means if I go back to our report in here, I could use now inside my can call my

08:15product table.

08:16I could now check for instance, make sure that you deselect this one first, click somewhere

08:21on the white canvas here and then you simply select here from the product here.

08:26Let's do that.

08:27Let's select the product name, tick the product name, and now we have here a list of the product

08:31names.

08:32We can make this bigger.

08:33Also, maybe track this, maybe to the left here, these are the product names, and now

08:37we can simply add next to the product here, add data, and then from the data here from

08:43the, in this case, sales table, we would like to use total sales column, which we calculated

08:47just a minute ago, right?

08:50So tick this option and you can clearly see that in total we can see these 25 million

08:55and so on, which is the same number as here.

08:58And this is now broken down for each of the products we have.

09:02So you can see different numbers anywhere, everywhere, and of course you can also sort

09:07this.

09:08You can, for instance, here to sort and sort it here, descending or ascending, right?

09:13Depending on what you want to do, simply click on it and then the data is sorted.

09:19So this, this way, and this relation only works on filtering data product here and then

09:25the sales for each of the products.

09:27This only works because we have set up our model correctly.

09:31So if I go back to the model view and for instance, I would say this relation here,

09:36if I right click and say I would like to delete it like that, yes, delete it.

09:42So just to show you what that happens.

09:44So products and sales do not have a relationship anymore.

09:48Now if I go back to the report view, we can now see there are 25 million everywhere.

09:54So in this case, because we have no relation between the products table and the sales table

09:59anymore, now Power BI is not able to use data from the products table in order to filter

10:05the sales table.

10:07And because of that, it simply displays here the total sales number, this figure, the 25

10:12million for each of the products, which of course is completely wrong, but that's just

10:16how it is.

10:17If the model is not set up correctly, and this is why I point or put so much emphasis

10:23that you need to get in your head that the model is really the most important thing in

10:28Power BI.

10:29So if the model and the relationships are set correctly, you do not have this issue.

10:34But if you have an issue like that and you can see repeating numbers in your Power BI

10:38report, this is an indication that you need to go inside your model view and make sure

10:44that tables you're going to use and which you combine in your visualization have the

10:48correct relationship.

10:50So that means, and here we can clearly easily fix this.

10:53So we just need to go here and need to say the product ID from here.

10:58Let's use it and drag it down to the product ID from the sales table, create a relationship.

11:02Now it is established one to many, the product table filters the sales table, and therefore

11:07it filters the total sales column.

11:09And that means if I go back inside the report view here, we can all see that we've got the

11:14correct numbers out here.

11:17So now we have that.

11:21Another thing you might have spotted, maybe not, but let me show us, we will show it to

11:25you is if I want to sort my data, for instance, we have here, um, well, easy, easy product

11:32names, right?

11:33Just product one, product two, and so on.

11:34But of course, if I go here and try to sort my product name, I could see that I got product

11:39number one, product number 10, product number 100, and so on.

11:43So obviously the sorting here is not correct.

11:47So if I would like to sort this correctly, like product one, product two, and so on for

11:50a three, right?

11:52This cannot be done directly here.

11:55What you could do is you can click on the three dots and there are sorting options like

11:59sort descending and sort ascending and there's sort by, but the sort by can, this could be

12:07our product name.

12:08And of course you could also do it on total sales column, which we have already done by

12:11simply hovering over the column and click on the little black arrow, arrow here.

12:18But we cannot sort it like product one, product two, and so on.

12:22So the format here is different from, let's go back to the data view, for instance.

12:27And there you can clearly see if I select the product here, there is sorted correctly

12:32like product one, product two, product three, and so on, right?

12:35So the question is, what can we do in order to get the same structure like here?

12:41Now for that, what Power BI needs is it needs a sorting column.

12:46And our sorting column here is the product ID.

12:50So this column, this product ID column helps us to sort the product name correctly.

12:56And that just means that I can select my product name here.

13:00And inside the column tools here, we can use sort by column, this option here, click

13:06on it.

13:07And then I choose, I would like to sort the product name column on the product ID column.

13:13So this is my sorting column.

13:15So I select the product name, go up sort by columns and choose here product ID.

13:22And as soon as I take this option here, I can go back to my report in here.

13:27And now you see that sorting is correct, product one, product two, product three, product

13:31four, and so on.

13:33So if you have a use case, and the default sorting options, like clicking on these arrows

13:38here or going under the three dots and choose sort by, if this is not working for you, then

13:44what you need, you need a sorting column, which you either have to create if you don't

13:48have it, or like we in the model, we already had it.

13:51And then we just need to make sure that we select the column we want to sort, and then

13:55sort by column, and then choose the specific column we want to sort it by.

14:01So now there's another way to get the same results.

14:05And this is instead of creating a calculate column, like we have done here, we could also

14:10create a measure in Power BI.

14:13But before I'm going to show you how to do this, please make sure that you click the

14:16little disc icon if you have not done so far, just to make sure that you save your current

14:21state of Power BI file.

14:22I already did it during the video break, but please do the same, just to make sure that

14:27your file is saved.

14:28Okay.

14:29Especially if you want to maybe continue working on the next day, or as I said before, it might

14:34be a case that Power BI is just closing or crashing, and then please make sure that you

14:39save the file.

14:40Okay.

14:41So having said that, now let's explore how we can get the same result here, this 25 million,

14:46but using DAX calculations and specifically measures.

14:53Now, in the first video, where we did a tour through the interface, there, we also talked

14:58about the measure and the power of your measure, which in the first video was straight out,

15:03of course, is not now available.

15:06Under home, you can see there is an option to create a new measure, this one here.

15:10So we can write a DAX expression that calculates a value from our data, which is actually what

15:15we want to do, because we want to multiply the sales, right, or actually the price times

15:20the quantity in order to derive the sales.

15:25Please note that the new measure option is also available.

15:27If you go to insert one more time, you can also find it in here because they're, I'm

15:31sorry, in the modeling here, you can also find it up here because there is a new measure

15:35option as well.

15:36And as a bonus, there's also a third option.

15:40If you right click on one of the tables, for instance, if I right click on my sales field

15:44here, there's also an option to create a new measure from here.

15:47So there are actually three ways to get the same result, but for instance here, I couldn't

15:52click on the measure.

15:53And if I do that, then now here, as you can see here in the ribbon here in the bar, there's

15:59now an option to create a new measure itself.

16:02So let me do that.

16:03But maybe before I do this, let me actually press escape here.

16:07Let me just select the blank canvas here somewhere.

16:09So I get rid of this pop up window.

16:12Now let me do it again.

16:13Let's right click on sales, go to, in this case, new measure.

16:16So of course, you could also do it from the ribbon if you want, but now we have our measure

16:19in here.

16:20Now, if you need more space, simply click the strop card here and then you have more

16:25space to write your measure.

16:27For the measure itself, what we would like to do is we would like to iterate through

16:32our sales table.

16:34What I mean by that is in order to get the correct value, what we need to do is we need

16:38to go through each row of the sales table, so each transaction.

16:43For each transaction, we want to multiply the price of this transaction times the, in

16:49this case, amount which has been sold.

16:51And then at the end, we want to sum all these individual values up, right?

16:55This is how we derive the total sales.

16:58So to do this, let's actually first give our measure a name.

17:02So let's call this total sales, total sales.

17:06And then we say equal.

17:08And I will go to line, you don't have to do it, but I will do it.

17:12If you want to do it, press shift and enter together, then you go to new line.

17:17And for the total sales, what I like to calculate is an iterator function.

17:21And here we're going to use the sum X function.

17:24So some X, if I tap this now, this give us here a little explanation of what it does.

17:31So let me just get rid of that.

17:34In here, if I press escape, then I can see what the sum X here actually tells us here.

17:39It's what a sum X function needs.

17:40It needs a table, and then an expression.

17:43And here it returns the sum of an expression evaluated for each row in the table.

17:48I think this is a really good actually definition.

17:51Because what we do here is the following.

17:54We say we want to sum up something in a table.

17:58And what we want to sum up is the expression.

18:01Now the table we were going to use is in our case sales table.

18:04So we can type in sales as the first argument sales.

18:08And if you type in an SA, you can also press tab then in this case, and then you can actually

18:14get sales directly or you can write it if you want.

18:17Now the next is the expression itself.

18:20Now what do we want to do with our sales table?

18:22Well, the expression is simply what we did for the calculate column as well.

18:27In our first part, we want to actually use the quantity.

18:32And if I type in to quantity, and here I can select from our sales table, the quantity,

18:38this one here.

18:39And then we want to multiply this with a star, and then we refer to the price.

18:43So if you type in price, you should find also here from the sales table, the price column.

18:49So the indication is always the table name, and then in brackets, the specific column

18:53that tells us that this is actually from the sales table, the price column.

18:58So that's the expression.

18:59And then all we need to do is simply close the parenthesis here.

19:02And that's it.

19:03So that's the formula.

19:06So what happens here is we tell the SUMX function, it should go through the sales table.

19:11That's why the sales here as a table is the first argument.

19:14And by the way, this is true for all X functions.

19:17X functions are iterator functions.

19:19And this means we only always need to give it as a first argument a table.

19:23Because they iterate over a table, and iteration simply means they go row by row to this table.

19:28And what it should do, row by row, is simply multiply the quantity column times the price

19:32column.

19:33And at the end, because it is a SUMX function, it sums up all these individual results.

19:40So let's actually do that.

19:41Let's actually check this.

19:43Click on here, commit.

19:44So we'll check it.

19:45And let's actually make this smaller.

19:47And now you can see a little calculator icon here, which is now our total sales.

19:52And now if we add this also on the canvas, so click somewhere on the blank screen, click

19:57on this total sales option here, and we get our bar.

20:00And if I switch this also to a cart, by either clicking on the cart, or if I simply tick

20:05this option on here, then you can also see 26 million.

20:09And let me turn it off again, just want to show it to you.

20:11And now also, if we go in here, go to the formatting option, in this case, I can't do

20:16it directly here, so I need to click on more options here.

20:19And then I go to my formatting pane here, and specify here for the display units, also

20:24show it as none.

20:26And then I also select this one here, go back to my cylinder here.

20:30And select the measure itself.

20:32And then for the measure, I can do the same as for the calculate column.

20:35I can go in here and say, I would like to see this as a dollar symbol.

20:39And now we can see that I got here value.

20:41And if you take a closer look, you should see that this is exactly the same value, these

20:46two, right?

20:47So what I have here for my calculate column, the smaller is the same value as I have here

20:51for my measure.

20:54And the difference is those measures always have this calculator icon in here.

20:58This measure also works by the way, if I go here, inside my table here for the products,

21:04of course, I can also select wholesale here, click on it, and I can see exactly the same

21:09values, right?

21:12And also, by the way, if I would go just a few, think about that, if I go back to my

21:17model view, and I would delete the relationship between the product and the sales again, then

21:24I will see the 25 million, not only for my calculate column, but also for the total sales.

21:29So this measure only works because our setup here relationship is correct, right?

21:35So the behavior would be exactly the same for the calculate column and for the measure

21:39we have.

21:41Now, you might ask me, of course, so hey, Dan, why do we have these two options?

21:48So which option should I use?

21:50Right?

21:51That's actually a valid question.

21:52And it's a good question.

21:53Now, the point is the point, whenever you can, you should.

21:58So that's best practice, at least.

22:00And that's why I would recommend as well, you should use measures like we have done

22:04here, not calculate columns like this one.

22:08Why is that?

22:09Well, the most easy explanation is actually, if you take a look at the data view here,

22:15for instance, you see that when you create a calculate column, as we have done with the

22:20first option here, then this column gets stored in the model as well.

22:27And if calculate column like this one gets stored in the model, that takes up space.

22:32So it makes your model bigger.

22:35And normally what you want is you want to have a model as small as possible, because

22:38then it's the most efficient one.

22:40And also regarding the performance, the best one.

22:44For the measure, on the other hand, as you can see here, the total sales, the measure

22:48here, this is not stored anywhere.

22:50You can't see the measure in here.

22:53It's not part of the table.

22:55What a measure actually is, is just something like a definition.

23:00And this definition gets only applied as soon as it's used in one of the visuals.

23:06So that means the total sales itself here, the measure itself, that does not exist as

23:10a calculate column.

23:12It just gets calculated on the fly, as soon as it's used.

23:17And you can think of it like that.

23:19The total sales here, this evaluation, like quantity times price, this is calculated for

23:25each row in the sales table.

23:27Then the value is summed up to deliver the result.

23:30As you can see here, for individual products, or here as a total sum.

23:34But let's say the intermediate calculations, like quantity times sales for each of the

23:41specific rows in the table, this is not saved.

23:44This is just calculated.

23:46Then the total sum is evaluated, and then all the intermediate steps are deleted.

23:52So that's why this measure is very, very effective, because it does not quite need any space in

23:57the model.

23:58And this is the reason why normally, whenever possible, you should use calculate measures

24:03instead of calculate columns.

24:05Now having said that, you might ask yourself now, hey, Dan, if the measure is the better

24:09option, why did you actually show us how to create calculate columns?

24:13And why is it actually possible to do that?

24:16Well, the reason for this is that sometimes you cannot use a measure.

24:21And one of those, well, reasons could be that you want to display something on the axis

24:27of a chart.

24:28So for instance, if you create a bar chart like that, if I go to create bar chart here,

24:34and I'm using my sales here, hit this option, I would see it as a bar.

24:38So that's totally fine.

24:40But if I would like to have it as an axis here to slice this, for instance, I would

24:45need to have some kind of calculate column or a default column.

24:49So for instance, if I go here on the dates, I could use here, for instance, the year,

24:54this year column, I can drag this on the x axis here, drag and drop it here.

24:58And you can see that now I'm slicing my data by 2021, 2022 here, and of course, 2023.

25:06And this slicing here, like putting something on the x axis in this chart, for instance,

25:10in this case, the year.

25:11This can be done with a default column in the model.

25:15Or also, if you would have calculated this year as calculate column, this would also

25:19be possible to put it in here.

25:22For the measure, on the other hand, like total sales as a measure here, for instance, this

25:25cannot be placed here on the axis to slice the data.

25:29It can only be used here to show the bar, for instance.

25:33So that's why both of them exist.

25:36But you can see that, for instance, for the total sales, we could get the same result

25:39with both of these options.

25:41And whenever this is the case, whenever we want to use it as a bar or as showing as a

25:46number in here, then we should use measures because they are more effective in this case

25:51because they do not blow up the model, call it this way, because we do not need to save

25:55it and store additional information.

25:57Okay.

25:58So I just wanted to mention this.

26:00And that's the reason for that.

26:01So you have now clearly seen how to create calculate columns, how to create measures.

26:07And that's why I like to say congratulations.

26:09These are your first calculations in Power BI.

26:12So thanks a lot for watching.

26:13And as always, I can't wait to see you in the next video.

26:16Until then, best guys.

Category

Transcript

Recommended