Power BI - Creating calculated measures

Hello, friends and welcome to Tuttle's Point

In this segment of the video, I'm going to show you how to create calculated measures in Power BI desktop

So a calculated measure is a measure that already exists in your Power BI data model

So what do I mean by that? Whenever we create this new measure, we can use this measure as an expression whenever we want and however we want

So how to create this particular measure? It's quite simple. You simply have to go under the report view and under the report view you simply have to go to the modeling tab and click on new measures

And the moment you click on new measures, the formula bar gets activated and out there you have to type the DAX expression

Now the most wonderful thing about this DAX expression is that, now first let me tell you about this DAX expression

The DAX expression is nothing but a data ytics expression, a language. It is basically a language which is used in Power BI model

And the unique feature about this is that it has so many useful function and among them the most useful function is the ear to date function and the ear over ear function

Now that is the unique feature that we'll be using in our practical approach

So how to create this new measure? We'll be having a better understanding of this in our practical approach

Hello friends and welcome. In this video we are going to see how to create calculated measures in Power BI

alright so let's go ahead and see how it is done now before that let me show you

one simple table in this table we are going to create a table in such a way

that we are going to have a matrix table in which we'll be having the month

name and the year along with it as you can see in this table I have the

month name and the respective years along with it also I will go ahead under the

sales fact table and select this column that is a revenue column and drag it out

here okay so the moment I drag it you can see under the values column I have I have put the revenue column in such a way that I have the revenue across different years altogether with respect to each month all right so this is showing me revenue across different years in different months altogether all right so this is a basic table but now out here we are going to create a new measure a new calculated measure which helps me to calculate the year to date revenue

all right and in order to do that we will go under the home tab and under here under the

calculation group we will go under new measures the moment I click on new measures see what happens all right now a measure has been created under this formula bar and it is asking me an expression so we have to build in an expression for

this will be using a DAX formula that is nothing but a data ytics expression

with the help of which will be able to calculate the air-to-date revenue so let's go

and name this measure as YTD that is air-to-date revenue all right so we have done that let's go ahead and you'll use a expression

now as you can see the moment I type total you can see all the related

functions that us are associated with power bi I will simply selected which

helped me to calculate the total YTT and I'll hit tab button the one the first

thing it is asking me for the expression so out here these are my different

criteria to fulfill this particular expression all right so this is similar to what we see in Excel we have the formula and we have the arguments

is the same way out here as well in the DAX expression in which we'll be

entering the formula and the argument or the criteria along with it so I say

let's I want the sum of let's say the revenue so it's the sales fact revenue

which I will be picking up all right and I will close this bracket comma the date

it is asking you for so I will go under the date tab and

I will simply select this date hyphen 2. All right. So and I'll simply close the bracket

Now we have created a new expression to calculate the year to date revenue

All right. Now the moment I hit enter this expression will be created in the columns

All right. So the moment I hit enter see what happens. Alright now I've entered now on in my date table you can see this measure is being seen in the the

date table we can simply shift this particular column in any of the table which we wish for and for that we have to go under the modeling tab and under the mob modeling tab you see this properties i will simply click on this drop down menu and i will select this measure and i can drop it anywhere let's drop it to the sales fact table so the moment i drop it to sales fact table it has jumped from here and it went to the sales fact table and it is added as a column out here let's go ahead and

remove this revenue so I have removed the revenue and if I drag this

YTD revenue that is here to date revenue it is giving me the measure and

it is giving me the right now figures out here in my matrix that is my table all right guys so this is how you can create a new measure in power b i now this is a very simple expression in which we have created the ytd that is year to date

revenue now let's go ahead and create a new measure which will help me to calculate the last

year ytd and later on what we will be doing out here is we are going to subtract the year to date

revenue for this year and the last year revenue and find out the difference so that i can get the

understanding whether we are going in a profit zone or in a loss zone all right guys

so let's go ahead and do that as well so I will as of now I will just uncheck

this I will keep my table like this all right so let's go ahead and create a new

measure so I will again go under the home tab and we'll create a new measure all right

and now out here you can see this expression builder is right in front of me now

what we have done to save a little bit of time I have already written down the

formulas I will simply copy paste it from the notepad which I have out here so just give me a moment all right

guys so I have this table in which the notepad in which I have the expressions

ready with me so the one which we want to calculate is the last year year-to-date

revenue so in order to do that I will simply highlight this expression and copy it

go under the home tab create a new measure all right and I'll simply paste it

all right so I'll just do a control V out here alright so it has all the expression which I require

so let's go ahead and see I'll just hint backspace just give me a moment all right so

it needs to be in the proper format if we have made any kind of

spelling error it can be seen out here as well so I'll simply again open my

notepad and just get this expression Control C and get back here and do a control V

Now the moment I hit enter a new measure will be created

This will be again under the date table. I'll simply go ahead and drop this under the sales fact table

so that every new measure that we have been creating can be seen out here as well

Okay, so I have the last year to date revenue. We will go ahead and also create one more expression

I will go under the home text. create new measure I'll refer to my note path all right and what I we are going to do

is we are going to calculate the variance so that is love here to date sales

variance all right we are creating a new measure and that is nothing but my last year I sorry year to date revenue minus last year to date revenue so that is the difference that we are going to find out so I

simply control do a control C and a control V out here it's simply same right

all right now I'll hit enter and this is being added all right this has been

added let's get back to the modeling tab and shifted to sales fact this

time we'll not make that mistake we'll simply select sales fat so that the new expression that we will be creating will be added out here itself

Okay. So again I will go to my notepad. Now the last expression which is seeing is year to date sales variance in percentage

So I need to calculate the percentage. This will show me the difference and this is it will help me to calculate the percentage

I'll simply remove this nine and let me quickly copy this. I'll do a control C go under the home tab

create a new measures all right and do a control we all together so let's see is

there any okay there is some error let's go ahead and check what's going wrong

right take this again divide yTD revenue that's right that's right

the one which I will be looking forward for and simply close the bracket now

this looks right so what we are going to calculate is the percentage so we

am using the divide functions so I'm dividing the year to date revenue divide by

the last year year to date revenue all right the moment I hit enter I have this

new expression which is being built again under the date tab so I will simply go

ahead in the modeling tab move here to back to my Seals fact so I have the

the way to date revenue the various the difference between it and the percentage all right as of now I'll just simply drop

this year-to-date revenue under the this table all right so I have this table

ready with me okay this that's look great also if I want to add a slicer to it

so that I can see for which product that how much is the revenue so what I will do

I will go under the product table and simply pick the category drop it back here

make it as a slicer if you can see this a slice slicer visualization which get me to slicer let me all right resize this so if

you click on rural so it will give me the figures for all the rural revenue if I

click on urban it is giving me all the revenue which has been done under the

urban category and under the youth category