In this session, we will learn how to
create metrics. What are metrics? They are
nothing but a quantitative assessment
which will help us make decisions. If you
remember Pinali's shopping cart, it had
3 items.
Number 1, working preference of people
between work from home and working from
office.
Number 2 percentage of overall sick
leave to understand to monitor employee
wellness. And the third one was learning
Power BI basics through this project.
See
I think she's already doing that just
like you.
The first 2 items in her shopping cart
are nothing but metrics,
also called as measures in the Power BI
context.
The first metric you will learn to
create is the percentage of work from
home. The second one is the percentage of
sick leave. But there are more. You will
see that during the process, how a data
analyst can add more value by helping
the stakeholders to discover more
metrics.
[Music]
All right! So so since we got the data in
the table now, we're going to create the
measures that that we want to you know
see in the dashboard. Okay, I would uh
recommend to keep the measures in the in
a separate table. So I'm going to call
this like a measure table.
Just to summarize Hemanand click on that
enter data button on the home tab and
he's just entering the data manually.
Yeah, okay.
So,
so first of all, I want to you know the
whole thing is revolving around the fact,
it's it's about a percentage so where we
have the you know the ratio to the total
working case. The the number of number of
present days versus total working days,
number of absent days versus total
working days, number of
uh work from home versus present days. So
the first thing I need to know is the
total working days.
So I'm going to create a new measure
and the total working day should be
should be I think count,
it should be the count of all the
values right? Basically all the things
you have in the value.
But if I if I simply do the count, I
think it is just giving the total days.
But I want I think we need to take out
uh,
I think we need to take out
the weekly off,
holiday off,
so apart from this, everything is a total,
everything is a working day, everything
is a working day. Okay,
so
we'll go back to the Power BI.
We're going to make this
total days this is total days,
I'm going to create a variable, I'm going
to I'm calling this as a variable,
and uh I'm creating a second variable
which is like
non-working days
on workdays,
and
so this is again going to be the count
of
you know the
values.
But in this we need to remove,
Can you increase the font size Hemanand,
maybe control, yes, scroll up. Yeah. Oh
yeah that's a good idea.
I'm just finding the non-work days which
is
I forgot it again. What is the code? It's
WO and HO.
So this formula basically saying
count only the values which has WO and HO
and uh so Pinali when you're working on
this stuff like this, you you need to
understand how the calculate function
works. That's very important. Like if you
understand how the calculate function
works, you can do 80% of your
work.
Right.
Okay I'm going to return return the
final value which is going to be total
days
minus non-workers
So folks that line number 5 uh can
you can you open that again Hemanand? So line
number 5 is more like
SQL's VAR query where you are saying
get me count of final data value where
final data is either WO or HO
It is if you know Excel SQL little bit,
it is sort of like that. That inner
portion is like SQL VAR query.
Yes.
That's right!
Okay,
so if you
just drag this, all you have to do is
like just drag it to the canvas here
and it will try to build a measure.
So you have the total working days which
means you know the total working days
for all the employees in these 4
months.
Right it's it's like a total sum
and from the total working days, I want
to understand
uh
what do we need to do? I think we need to
understand the present days, the number
of days the people are present correct?
So I'm gonna call it present days,
and this is gonna be quite simple.
Use the same formula that you used
before,
calculate the count of value. But I
want the values
only in
uh
I saw I I think I saw it's P right?
Present days is p correct?
Yes, P. Okay
okay, let me think about it.
So if I add the present days,
so we also have work from home days
right? Work from home is considered as
present.
Yes. Okay on and we also have half work
from home.
Yes, if the employee is doing half day
work from home, that's all
we are also considering him present.
Yeah, so I cannot use this formula
because
it will consider half work from home
also as one,
it's not the full working day. So we need
to we need to do something different. Let
me let me do it. So
but let me just add it as a
you know
but Hemanand and you're trying to do
present versus not present. So our
definition of present is
even if person is working half at home
half in the office,
yes for that day the person is present.
So present yes, one. Yes yes, yeah. So let's
say if they are working only half work
from home
right,
and let's say if they take half sick leave
okay, so that is like half a day present
right? Hmm, half day present.
Um how many presents, that is half day
present. Yes, so if I just add it here, it
will consider as full day present. So I
have to be
I have to be very clear in saying that
uh
just give me
you know,
just calculate the present days
where the value is equal to p
But this formula is not complete. We also
need to add the work from home days to
this.
Okay,
so since
so if I use the same approach here, so
what it does is like it calculates, it
counts each value as one. So if there is
p, it is 1. If there is a second p there
is 2. Uh if I use the same approach
here, there is no there is no way to say
here
like if there is half work from home
calculate this as half. We can do this in
DAX but it will become very complex.
So I will
I mean if we can do this in a DAX
measure but it will become very complex.
So what I will do is like I'm thinking
to create it directly in the in this
table.
I think that will be easier for me.
I go to the final data table, I will
create it here. So here I can easily say
if it is uh if it is work from home
write 1. If it is half work from home,
write 0.5 Then as a column to me to
summarize to me to do the summation on
the column, it will be easier.
So I'm going to create a new column same
here,
and call this work
from home count,
and this is going to be
I can use a simple if statement.
If value
if this value is equal to p if this
value is equal to work
from
home,
then it's 1
Okay, so this is this is why I prefer to
use switch statement because I I don't
like writing multiple Ifs.
So I would rather write a
switch statement,
so which is a good alternative to
if and
if it is half work from home,
should be 0.5
and for all the rest of the values,
let's let's have 0.
Is that correct by definition?
Should we do this?
Yes.
So you can see for present it's not
considered as work from home, so it's a
0
But
when you see
let's let's do a quick check if my
formula is working correct.
So when I have half work from home, yeah you
can see it's 0.5
and when I have
work from home,
it will be 1. Perfect! That's that's
what I need.
So I'm going to create a new measure
here calling work from home.
So now this is an easy measure. All you
have to do is like just make a sum of
the work from home count column,
and like I said I need to adjust my
present uh
days
formula,
and
just adding or I don't need to create a
variable, since I already created a
measure, I don't need the variable.
I'm simply hiding the work from home
count here.
Okay, so I'm just pulling the present
days here.
So this kind of looks correct because of
course your present day should be less
than your working days, which looks quite
correct to me
and uh
I already have the first thing that you
got, that you want which is
um
do you want to call it presence
percentage?
Yeah fine. Should be yeah nothing but your present
days divided by
total working days
and since this is the percentage, I'm
making this a percentage.
So you can, Pinali you can think this as
an Excel formula, how you write an Excel
formula like, sum, max and all those
things. You just have to learn the basic
functions it will help you.
So once you once you practice this on
your own, you you will have a better
understanding. So this is our overall you
know presence percentage that that we
have got, and
to to kind of visualize things better,
I'm going to add this slicer and drop
the date
column. So this is a date column.
Okay,
this is the reason. So you see that we
have all these days, date as days like each
day, so I I cannot select I can select for
each day, but let's say I want it as a
month
right, so I'm going to create a new
column here as a date month.
So I'm going to call this format
date value.
I'm going to say start of the
month, date value.
So this kind of gives me the start of
the month and I just go to the format
here,
and say
mmm
yy
So I get this like short format.
So instead of dropping the date, now I'll
drop the month.
Okay.
Then you can change it to horizontal, so
you can have it like this.
So you can select now for April and then
see the presence percentage. You can
select from May and see the presence
percentage and then the June.
So these this forms you know the basic
measures that we will use,
and uh I think the next step is to build
the dashboard out of this you know, and we
can build some measures on the fly
because we have got the basic measures,
and we can build some measures on the
fly as needed for the dashboard.
I hope you had a good introduction to
creating DAX measures in Power BI.
There are a few takeaways from this
session. Number 1 is group your
measures under one place.
The second one is understanding
calculate function in DAX is very
important.
And the last one, you can also create
additional columns to your data using
DAX.
I'm excited for the next session and yes
we will be building the dashboard in
under 30 minutes. See you there!
[Music]