Analyzing two data sets together, this used to be amazingly difficult and
problematic in the old world. But it's really easy in Power BI, so easy it feels
like magic. Keep watching, by the way I would like to
point out that something magical just happened and we all missed it, frankly I
missed it. But this is so cool sales and budget together in the same
pivot table essentially. So what I'm going to do is I'm going to go back to
my Excel and kind of try to show to you here. So think about the old world, let's
refresh it, there we go, now we see my budget. In the old world I would have my
sales and let's say I would have it by the product category and subcategory
let's do the same ones, great and let's change the layout
and down I would have my budget, screening budget here and I'm going to
bring in a slicer for a year, and I'll connect this slicer to both pivot tables,
perfect done, and I'm going to say English from 2016, alright. So now it's
apples to apples. But really this is how it used to be done. If you had two
separate tables go back to the relationship you had a sales data coming
in and budget data coming in. You would flatten sales out, you do vlookup the
heck out of it, get all the product attributes and get everything else
whatever you needed, and then you had budget data in another table and then
you would vlookup whatever you need to be look up and then you ended up with
two different flatten tables gray and then you would do two different pivots.
But now we can have things in the same pivot so let's see how that's why that's
such a big deal. Let's go back to our model let's go here because not only can
we have sales and budgets side by side and we can slice and dice them side by
side. So let's go to month and I'm going to add month as a slicer I'm going to go
here change it to slicer, and while we're talking about month notice that it's
messed up it's alphabetical let's quickly fix that. I'm going to go to the
data tab the calendar table and all we have to change is change the sort by
column. So if you notice here this one says sort by and you can override the
sorting which is by default alphabetical. But I can say hey, the month actually
needs to be sorted by the month number. So January is 1 December is 12 and so on
and now if I go back, working on it, okay there we go,
now sorted correctly. By the way for those of you who have physic
fiscal calendars, Microsoft obviously I worked for them for a long time. So their
calendar runs from July through June. So July is really the first month you can
easily set that up. We'll probably cover that in the later module but you, that's
really easy to do. Custom calendars which are not really, do not really follow
month boundaries they're four week 4 week 5 week or something like that, those can
be handled as well. So we'll cover that in separate modules but for now we have
the month slicer so, so here not only do you have sales and budget in the same
pivot and you can kind of slice and dice them easily, perfect, great. But we can
also define subsequent measures which reusing the measures we just defined. So
we have sales and budget, but now I can define, let's go to the budget table,
great. We're there, we can define kind of variance and that can be sales, zoom in a
bit. Sales minus budget, alright great. So before we go any further I wanted to
point out the way we use the syntax is if you have a column it's always
preceded by table name, and if you have a measure as we had in this case it does
not have a table name. Now sometimes you can get away by breaking these rules but
I really want you to follow this and measures stand by themselves. Sales minus
budget but column names always have the table name as a prefix, so great. So this
is the variance and if we go back here we can add the variance here.
Let's see what it looks like let's get rid of the decimal places and notice how
this is grayed out I just switch to something else come back to it and now
I can change it, I don't know why it does that.
All right, great so variance we have that you can also apply fancy coloring in it
on it or things like that. We'll cover that later when we talk about
visualizations. But really what we're seeing here is reuse of measures so
variance, I just reuse my existing measures and
this is going to be huge, all right. Get in the habit of it if you have something
defined don't redefine it. So here, if I wanted I could say yeah, sum of sales
amount right and minus, lets close the brackets, minus sum of budget amount I could
have done that, but why? Well and be lazy because lazy is efficient so here, oh I'm
just going to cancel it. Here we just reuse the measure sales minus budget. This is
what I was talking about earlier when you build things like Lego blocks. Simple
queries in the query editor you combine them to make complex queries, all right.
No, I'm not going to say complex awesome queries because they healthier
purpose, right. They get you closer the way you need to go. So here if you want
to define variance we're just going to reuse the underlying measures use them
as Lego blocks. Great, so we have that and now we're going to define one more
variance percentage variance percentage. So we're here and this one we can define
as variance divided by budget. We could do this but division always has a danger
of dividing by zero. So instead of this I almost always use the divide function if
you notice here the autocomplete tells you that yeah, that's what exactly what
it does is a safe divide with ability to handle it divided by zero a case. So
great, so you have that and instead of this you don't need this anymore
this is a function call you say numerator comma denominator
and you don't, the last parameter by the way is optional, so you don't need
that, you can just close it. What this means is that if it's budget ends up
being zero instead of giving you an error or something like that it's just
going to return a blank, that's the default for the, for the third parameter
here. See I almost never specified, so divide this by this, perfect and I
have that and again I'm going to switch out switch back to it again. Change that
to a percentage, see how it looks over here, great maybe I'm going to make it
just one decimal place, perfect. Hey, keep watching more videos
and keep learning Power BI. But if you did enjoy this video I would love to
hear from you. So leave a comment, like, subscribe, all the good stuff. Power
on my friends.