Hello, my friends.
So today we have an excellent question that
came from Kerry thomas from United Kingdom,
part of our LearnPowerBI.com family
So Kerry here is dealing with headcount data now a lot of times, even in my demos,
I show sales data and you sum up the sales and it works.
But headcount
is slightly different, at least the way the data is often stored.
So in this case we can see that by month,
every month we are getting the right count of the head count.
And you can see in this graph that the headcount was just increasing over a month.
So again, when we're looking at a month level account is correct, but
when we're not looking at a month,
It all goes to hell. So let's see.
So in here 2020,
it's actually adding up and we can see that even in the most recent months,
the largest headcount we've had is 10,944.
So clearly this is wrong. 22,000. It's not right. This is not right.
This is not right. None of these numbers are right.
And um, over here on this side by department, that is wrong as well.
I mean, look at that, engineering does not have 47,000 people. Now.
Again, if we select a specific month,
then the numbers are right, then the department breakdown is right.
But what about when we're trying to look at it by year or by quarter?
It's all wrong. All right.
So that's the discussion we had with Kerry thomas and I'm
gonna show you in this video how to tackle this.
Now, of course,
if you're new to the channel and you're a power
bi a beginner or still feel like a beginner,
then my friend this channel is for you.
So make sure to subscribe and click that bell.
So you're notified whenever we post new videos for
you and answer your barbie I questions again,
huge.
Thanks to Kerry thomas for bringing this to our attention.
Had a great discussion with her. You can also connect with her.
I'm gonna put her linkedin profile, your l in the description below.
You can connect or follow her there.
Uh And if you have your own questions, well then you can just go to learn
FBI dot com slash question
and send us your questions. Alright? So let's
go back and hopefully you understand the scenario here.
So let me take a step back and look at the data center. Right?
So, if you look at the headcount data, this is what we're seeing.
So it has the department
and it has for every department it says month, you know, so october november,
you know, january.
Yeah, It's not sorted, right? But you get the idea right?
So every month it reports the number
now, of course department is connected to this hierarchy, which we also have.
Uh So department can have a leader, entity, division, something like that. Right?
So you can slice and dice in any which way you want.
Uh we're not that concerned about that table, we're mostly focused on this table.
So again, let's take another look.
So here we have we have the department and you
can see there are different departments here and months,
of course different months here.
Right? So oops didn't want to do that.
Okay, so let's go back and see how we can solve this.
Actually before we go any further, make sure to download this file by going to learn
dot com.
You'll get a complete download treasure chest of all the files for all of our videos,
including this one, so you can follow along, try out different things.
Alright, so we do not want this total headcount.
So again, if you look if you were to look at the formula right now
in the head count is just doing some of headcount.
Now I'll say that some is needed, you cannot get rid of the sum and here's why,
because I think this is what's called semi additive.
Let me know if guys if I got that. Right?
So it's it is semi additive because you do want to sum it up.
So let's say if you were to look at a specific month. Right? So let's say
january one.
Well you do want to add it up.
So imagine if you were getting a total head count or remember we
have another look up table which groups department into leader entity division.
So if you're if you're trying to um you know, kind of summarize it up,
we do have to sum up these departments so the sum is there.
But then again, we just need to do a test and say, hey, you know what,
if there are multiple months, you know, only show me the latest.
So for that, let's create a new measure over here.
We're gonna go to modeling and guys,
I'm gonna take it step by step and I'm gonna make mistakes
which are intentional in this video.
But of course sometimes when I'm writing something new,
I do make mistakes unintentionally as well and that's the way to go.
So if that happens to you, it's perfectly okay. All right.
So we're gonna go here and we're going to say, I don't want the total headcount,
I want the latest head count
and for this,
hold on.
Okay.
I wanted to make the fund a little larger for your latest head
count and I know that I'm gonna change the filter context here.
Right? So because the default filter context that's coming in is not good for me right now,
if you need more information about filter context,
if you don't understand what that means, how that works,
I would strongly encourage check out my course at learn
dot com slash training, but I'm gonna assume you know that.
So we don't do need to change the filter context.
We're gonna say calculate that is a magic wand,
that's usually the one that I used to change filter context
and but the first expression is well, yeah, but before we go to the filter context,
what are you actually calculating?
And we still want the total head count, which is summing up the head count,
but we only want it for the most recent month
now or the most recent date. So I think this is what I tried when I started out, right?
So I said, oh that means the calendar date needs to be the max, right?
So it needs to be the max calendar date
and
uh maybe that's a little too big. All right, so you get the idea.
So it's saying a calendar, give me total headcount where date is a max calendar date.
And I think I need one more to close out to calculate. And that looks
good for now, promising, right?
And then I'm gonna go here at a comma here because I know I'm gonna need that
and I'm going to collapse that.
And now let's um let's let's actually give this guy some space
and I'm gonna add my measure here. So here,
and I'm gonna
add the latest head count
now, notice that it does not work, right? So, again, it's like why didn't work?
I'm telling it to do the latest date. So there are lots of ways to debug formulas
one I like to do is
you can use variables
but for now
I'm just going to say, well, I'm trying to use this max calendar date value,
what is that?
So I'm just going to
I'm just gonna kind of return that for a second
and I'm going to comment out the original formula.
Right? So I'm gonna comment it out and and sometimes if I'm being careful,
I do I do not want to yeah, I don't want to leave an indicator for me that you know,
this is not the real formula, I'm just debugging it.
Okay, so let's try that
a max state.
Okay, come on buddy.
Okay, there we go. So here um so let's see what's going on here
and hopefully the formatting doesn't bother you maybe actually
change the formatting a little bit short date.
Great. So
again, we had written the formula as oh,
show me the head count where calendar date is max calendar date.
But now if we look here, what is the max calendar date return?
And
for 2020 is returning 12 31 2020 That makes sense. Right?
And for this one is returning 12 31 2021. So that's the filter context.
And of course, as you know, how filter context works,
is we're actually filtering the calendar table here.
Right?
And that flows downhill through these filters and again,
if you don't get these concepts, I would encourage
go through the course learn
FBI dot com slash training. So
this is filtering down and now if you look at
the head count table you would notice that it does have
Monthly grain of the data, how do I clear filter.
Okay right so it does have a monthly grain of the data but the
way we have designated the month is by using the first of the month.
Right? So this is really not December one. Right? So again the date doesn't apply
But in this case the data set just has December one September 1.
So of course when you send that send 1231
there's no data for that specific date.
Now again,
first thing I would say is that this doesn't mean
that you do something hokey with your calendar table please.
My friends
leave your calendar table as well
as is calendar table needs to be at a at a state
level grain do not create a table with a month level grain
that is not a calendar table. You need a calendar table if you want to learn more.
Um we'll put a link to the ultimate calendar playlist series.
It's the most important table in your model.
Don't mess it up so we're not gonna mess it up. We're gonna let it be right
and now of course if you come back here.
So if you come back to the place where
we were trying to answer the question right here.
So let's come back
and
I did think about
a few things I said great max calendar date is giving me the
end of the month now there is a dax function start of month,
which can be used now of course.
I also thought about I can go into the query editor and actually shift
the data set to for this data to have end of the month.
Power query has a function there too.
Either of those would probably have been okay.
But since we do have a pretty awesome calendar table, not just any calendar table,
the ultimate calendar table, my friend and again we'll link to that playlist below.
So what this calendar table has is I'm gonna give you a demo,
it has a lot of cool fields and one of them
is this month, year and especially month you're numb.
So knowing that our data is at a monthly grain,
I can just go back to the formula and I can say you know what,
I don't really want the maximum date,
I just want the maximum month, year or month. Really? Right?
But of course you don't wanna say December because December could be in 2020, 21,
2022.
So we're gonna change that.
And actually just to show you what that is,
I'm gonna monthly or numb, I'm just gonna show you what that returns.
So when I say give me the maxim monthly or numb. There we go.
So now you can see that it's now doesn't filter down to a specific date,
it filters down to a specific month or 2020 december.
2020
2021 december and so forth. Right?
So uh so this is gonna work because their data is at
a monthly grain and now the specific date is not gonna matter.
Alright, so let's come back here and now we can uh
put it back in.
So let's go to calculate
and
and I'm going to
put that on a new line
and we're saying not the specific date I just cared about the month, year numb.
So that month you're numb
needs to be
the maximum, right?
So give me the most recent month,
give me the value for that and I think I can take the debug stuff off.
I'm pretty confident this is gonna work.
So hit check that box and let's take a look at it.
Uh Do we have? Oh, I thought I added commas, there we go.
Okay. Alright, so
2020 it's saying 1925
And I can scroll, I can actually do this, I can just click on 2020 and I can see December
Is indeed 1 9-5. I can of course also see it in the table over here.
I can click 2020 and that looks good. So yeah, this is looking good.
So I'm gonna just clean up the view a little bit, take,
take off the red and be right back.
All right, look at us. We are looking good.
So again, for all of these graphs, I've switched out to the uh the latest head count,
a new measure that we had.
We have created. And you can see
That it's it's all looking good.
So for 2020 we had kind of checked that and you can kind of see that.
And of course here we have the year and the quarter now.
Of course, if we like, we can change that to uh to kind of the
uh
to have it be drilled down
so year quarter, maybe we can even add months in there.
So I'm gonna go here and grab a month
over there.
And so you can see 2020 or 2022, we can see kind of the data and you can see it's Q15823.
That seems right. I can kind of validate it or 2022
10 94. Alright, so everything just works.
Of course the department works and so forth. And
of course you can do any other chart.
So you can have a kind of a line like this and it's dummy data,
so probably not that interesting, but um but yeah,
you can see for each department how they compare year over year now folks.
Again,
a big thanks to terry thomas for bringing this
discussion up inside or learn barbie dot com community
uh questions.
Uh We love questions because it gives everybody
an opportunity to learn and get better.
So yeah, if you have any questions.
uh make sure to go to learn power bi i dot com slash
question and I'll look forward to answering your question in the next video.
Alright, take care
of our own.