First DAX Measures in Power BI

expertiseexcelerate

First DAX Measures in Power BI  Master the fundamentals of DAX (Data Analysis Expressions) in Power BI with this beginner-friendly course. This playlist is perfect for professionals, data enthusiasts, and Power BI users looking to enhance their data analysis skills. In this course, you’ll learn how to create your first DAX measures, helping you unlock deeper insights from your data. Through hands-on examples, you’ll quickly gain confidence in using DAX to perform powerful calculations and enhance your reports. What You’ll Learn: • How to create basic DAX measures in Power BI • Key concepts behind DAX expressions and calculations • Best practices for writing efficient and accurate DAX measures • How to use DAX to enhance data models and reports Ideal For: • Data Analysts and Business Intelligence Professionals • Power BI users eager to learn DAX for improved reporting • Beginners looking to get started with DAX calculations in Power BI Subscribe for more tutorials and learn how to unlock the full potential of Power BI with DAX.  DAX Measures 101 Getting Started with DAX Power BI DAX Basics Beginner DAX Formulas Creating Your First DAX Measure DAX Functions Explained Power BI Measures Tutorial Introduction to DAX in Power BI Simple DAX Calculations Power BI for Beginners DAX  #PowerBI #DataVisualization #EPICDashboard #BusinessIntelligence #PerformanceMetrics #PowerBITutorial #DataAnalysis #Analytics #DataInsights #VisualizationTechniques #PowerBIMeasures #DataReporting #DashboardDesign #MicrosoftPowerBI #PowerBIforBeginners #PowerBITricks #DataDashboard #PowerBITips #BITools #Dashboard #PowerQuery #PowerBITutorialforBeginners #DataModeling #DAXMeasures #DAXFunctions #DAX #DataAnalysisExpressions #Reporting #Data

Transcript

00:00All right, friends, welcome back.

00:07Now in the last video, we created our measures table for the current only specific measure

00:12we have, which is total sales.

00:15Now we could also be interested in create a few additional measures, for instance, total

00:20cost.

00:21Now in order to calculate the total cost, let's have a look at our data one more time.

00:26So if I go to the data view here, and I would select my sales table here, I see that in

00:32here I have a product ID.

00:34For the product ID, for instance, this PID, so product ID 2077, I have currently a quantity

00:41and have a price.

00:43Now this price, of course, is a selling price.

00:46Here I have no information about the cost.

00:49However, if we take a closer look at the product table, here, there's also the product

00:56ID.

00:57And if I scroll down, there's a product ID 2077, there it is.

01:02And here, we could see that, for instance, here, we have the cost attached, right?

01:06So here 424.

01:10So obviously, we can calculate the cost by simply looking up the quantity in the sales

01:16table.

01:17So if I go back one more time to the sales table here, we take a look at the product

01:20ID, this ID, the quantity is one.

01:23And then we need to go, or Power BI, in this case, needs to go to the product table, take

01:28a look at the product ID, in this case, 2077, in here, and then look up the specific cost.

01:34And then we multiply the one, which is the quantity for the specific role or transaction

01:38in the sales table, times this 424.

01:42That's the idea, right?

01:43Okay, so how can we do this?

01:46Let's go back into our report view, and then let's create the total cost measure.

01:52So to do this, because I'd like to create the measure inside the measures table, I could

01:55simply right click in here and create new measure.

01:59If you don't create the measure by right clicking here and click new measure, and do it differently,

02:04for instance, by going under home and click on new measure, or go under modeling and create

02:10new measure here, then the home table, so the default table, where the measure gets

02:15created, is the last table you have selected here.

02:19So that's why I personally prefer to right click here and create a new measure directly

02:23in here, so I know that it's placed in the measures table.

02:26But of course, you have seen that you can also move the measure around later on.

02:31So let's create this new measure now, click on new measure.

02:34And then let me just zoom in so you can see that better on your screen.

02:38And now let me just type in here, this is my total, total cost, right?

02:43So total cost is equal to, and again, I will press shift enter to go to new line.

02:49It's not required, but I personally prefer that.

02:52So now I can do something kind of similar.

02:55Because remember, for the total sales, what we did is simply we went row by row.

03:00So through each transaction of the sales table, and then we multiplied the quantity times

03:04the price.

03:06So we can do something similar for the cost.

03:08Because again, we need to go through all the transactions in the sales table.

03:12So row by row, and multiply the quantity times the cost.

03:17The only difference is that the cost now is not part of the sales table.

03:21It's part of the product table.

03:22Okay, so let's try that.

03:25Let's go in here and say we use the sum X function again, because we want to sum up

03:29the prices, or in this case, the costs.

03:32And then we need our table, which again is the sales table, which contains our transactions.

03:37And then as an expression, we can refer to the quantity.

03:41So quantity, there it is.

03:43And I can press tab to select it.

03:46And then we can multiply this, not minus, but multiplied in this case.

03:50And now we can type in the cost.

03:53So let's do that cost.

03:54And you see, the cost is not picked up by Power BI.

03:58So obviously, we are not able to refer to cost here.

04:02So if I type in CO, you can see that there are a lot of expressions, so Power BI expressions

04:06or DAX functions in here.

04:08But there's no cost.

04:09As soon as I type cost, nothing happens here.

04:12There's no intellisense.

04:14So what's the issue?

04:15Well, the problem is that the cost, remember, is part of the product table.

04:21It is not included in the sales table.

04:24And currently, we tell the sum X function to iterate, meaning going row by row through

04:29the sales table.

04:31But in the sales table, there's no cost.

04:34So how can we use the cost in here in our formula?

04:37Well, for this case, there's a specific function in Power BI, which is called the related function.

04:44So if you use it here, related, here it is.

04:48And you can see it here, it returns a related value from another table.

04:52And related here is the keyword.

04:54Because remember, when we set up our data model in the modeling view, we created a relationship

05:00between the product table and the sales table.

05:04And the product table contains the product ID and the sales table contains the product

05:08ID.

05:09So that's our common field.

05:11And because we have created this relationship, and the relationship is active, we can now

05:15refer, as you can see here, if I type in cost, cost, you see that now Power BI is able to

05:21pick up the product cost from the products table.

05:25So if I tap that, and I close parenthesis, so now we have our expression.

05:31So we go each row in the sales table, so row by row, we iterate over it, we take a look

05:38at the quantity in the sales table.

05:40And for each of the sales, we use the related item from the product, and there we refer

05:45to the cost column.

05:46And this can be done because we have created our relationship out front.

05:50That is why, again, it is so crucial to have your data model, set it up correctly, and

05:55the relations are in place.

05:57So now, if I just press enter, or I can click this checkbox here, checkmark, then now we

06:05have our total cost.

06:07And now the measure is also there.

06:09And now, of course, we would like to see this maybe in our view.

06:12So if I go in here, and now I take this total cost, click on it, now you see we have here

06:17also our total cost attached to it.

06:19Of course, they are currently not formatted correctly, so you can go select the total

06:23cost in here, and say this is also, for instance, a US dollar amount, or another currency, if

06:27you want to pick another currency in here.

06:28You can do that as well.

06:29So let me take this option here, and then you see that now the formatting has changed,

06:34and now we can see here the total cost as well.

06:38So that's how that works.

06:40So now we have calculated our total cost as well, and I think we could create a few additional

06:45measures.

06:46For instance, now we have the sales numbers, we have the costs attached to it, so why not

06:50calculate, in this case, the profit, right?

06:53In this case, I keep it simple.

06:55The difference between sales and cost is simply the profit, okay?

06:59So let's do that.

07:01So let's then right click on the measures table, right click here, and say, again, we

07:05want to create a new measure.

07:07So there's our measure, and let's call it simply total profit, okay, total profit, sql2,

07:15shift enter for the new line, in case you want to do that, and now we can calculate

07:19our profit.

07:20And as I said, it is simply the difference between the sales and the cost.

07:25And the interesting thing is now, of course, because we already calculated the total cost

07:29and the total sales, we can refer to those measures.

07:32So a measure, like in this case, our total profit, can be defined by referencing other

07:38measures like total cost and total sales.

07:41So that means I can simply type in total, and you can see there it is, total sales,

07:45and then I can subtract, so minus, in this case, total cost, like that.

07:51Those measures always have a purple color to it, that's normal, that's just a visual

07:55indicator in Power BI.

07:57So this is our total profit, so let's press enter, de-evaluate it, and now we got our

08:03total profit here as well.

08:05And also let's just quickly check whether it works, so let me make this smaller, let's

08:09make this a little bigger, so make this even bigger, and let me drag this a little bit

08:13to the side, like that, so we got a little more space.

08:16So let's also put this inside, so go inside our table, and then also add the total profit,

08:23let's go here, let's do that, and also, of course, we'd like to format this, go one more

08:27time in here, and format it, and now we got our total profit.

08:31So this value minus this value, it equals this value, and this is true here for all

08:36the different kinds of products, okay?

08:40So that's actually the second calculation, so our total profit, and let's maybe finally

08:46for this video also create a third calculation, which is the profit margin, and the profit

08:51margin is here simply the profit divided by the total sales, so let's do that, of course

08:56feel free to pause the video and do it yourself if you want, otherwise let's do it together,

09:02click on, in this case, the measures table selected, so I could also create a new measure

09:06in here, and you see that now the measure also gets created inside the measures table

09:10directly.

09:11So in here, and call this maybe profit margin, so go in here and rename it, so profit margin

09:19is equal to, and let's say for the profit margin, it's simply the total profit, total

09:24profit divided by, in this case, total sales, total sales, here we go.

09:31So this would work, however, as a tip from my side, as a best practice tip, you normally

09:38when you do any kind of division in Power BI, you don't do it like that.

09:42The reason why you don't do it this way is it could be the case for whatever kind of

09:48reason, in this case it's quite unlikely, but if you do any kind of division in Power

09:52BI, it could be the case that your denominator here is zero, and if that's the case, then

09:59you get a division by zero error.

10:01You probably are familiar with this from, for instance, Excel, right?

10:05And in order to avoid this, there's a special function in Power BI, which is called divide.

10:11So instead of using this slash here to divide those two values, we could say in here, go

10:17up here and call divide, there it is, and divide means a numerator and a denominator,

10:23and we can also give it an alternate result, just in case the division returns an error.

10:28That's the third argument here, which we can specify.

10:30It's optional because it's in brackets here, but you could specify something else if you

10:35want.

10:36You can see here, it's called save divide function with the ability to handle divide

10:39by zero case.

10:41That's the case, and that's why this is best practice to use divide instead of simply using

10:45the slash.

10:46So it means we have our numerator, which is in this case the total profit, put in a comma

10:52in here, then we have our denominator, which is total sales, and in this case, we simply

10:56can close parenthesis.

10:57I don't give an alternate result, it's not required.

11:01Even if this would return an error, then it would simply return a blank if you use divide

11:04function and it does not show the error.

11:08But of course, you could also give it a third argument, an alternate result if you want.

11:12So that is that, and now let's actually check that and create the measure.

11:16Now we've got our profit margin in here, and now let me actually select this visual first,

11:22and I'll select the profit margin first, let's actually convert this to a percentage value,

11:25click on display value as percentage on this column, you can specify how many decimal numbers

11:31you want to have, choose the default value, and I'll click on here and check that box.

11:36Now we can see here, this is the profit margin we get for each of the products in this case,

11:41and also, of course, in total at the bottom here, full line.

11:46And this is actually it for creating our first measures in Power BI.

11:50So far so good, and just in case, I hope you can see the values in here, and they're

11:56not too small.

11:57In case they are, of course, we could always go inside the formatting option here.

12:02So let me just go to the view option here and go to the format here, and then for table,

12:06of course, inside the value section here, we could, of course, increase the font size

12:10and make this bigger if you want to do that.

12:12But hopefully you were able to see this, and of course, if you followed along, which I

12:16really hope you do, then, of course, you have it in front of you as well, even during

12:21the video or after you have watched the video.

12:24So that's it for this video.

12:26So thanks a lot for watching and for participating, and otherwise, hopefully see you in the next

12:32video.

12:33Until then, best guys.

Category

Transcript

Recommended