[MUSIC PLAYING] Welcome to MSPTDA
video number 26, yes, Microsoft power tools
for data analysis. And in this video,
just like last video, we're going to see how
to compare budgeted amounts to actual amounts. But last video we used
Excel worksheet formulas. This video we get to see how to
do it with a data model and DAX formulas. Now, last video we used
worksheet formulas. And this formula and the
variance of percentage variance formulas are relatively easy
to create and quick to create. But notice each line is
simply product and month. I don't see product totals,
yearly totals, monthly totals. Now, we could have done
that with the formula, but it would have made it
a lot more complicated. With a pivot table, it is easy. We create our measures,
drag and drop. And look at that, we definitely
have product and month, line totals. But there we have
the product totals. Down here we have
the yearly total. And of course, the ultimate
beauty in pivot tables is that you can pivot it. So I can simply drag
product above year. And now I have a
different report where I see that particular
product, each one of the years and the various totals. So in this video, we want to
see how to create the data model and measures to
accomplish this task. You can download this file
in the link below the video. On the sheet tables, we
have our five tables. We actually have two fact tables
and three dimension tables. Now this is the
budget fact table, and each line is at the
month product level or grain. Over here on the transaction
table, the grain of each line is at the transaction
date product level. Now, problem number one is
we have two fact tables. Problem number two is, I need
to compare monthly product and somehow get the monthly
product from this table and then compare
them side-by-side. The other problem is that
we're going to use star schema. Here's the F transaction table. We'll product and date,
those can filter this table. But guess what, in
our pivot table when I pull a particular product
or a particular month, I want it to accurately filter
this fact table and this fact table. Now this is not really involved. But I'm going to import
this into the data model and show you that the
model we're going to create is not going to work with
other dimension tables. Because fundamentally,
our budget number has two attributes,
product and month. So I have no idea how
to chop that number up to use this as a dimension. All right, so we need
to bring all of these into the data model. Here's PowerPivot. I already brought
these four tables in. So I'm going to click in a
single cell in our budget table and click Add to Data Model. I can see five tables there. Now, we'll come back here
and build our formulas. But right now we want
to go to diagram view. We can move and
resize our tables. And here's our F transaction
date and product. That would be a
normal star schema. So I'm going to drag date. That's the one side
over to the many side. We have one date for each
row in the date table. But of course, we hope we
had sales on many dates. Over here, there's a
unique list of products. But I drag it over
here, hopefully we sold many of each product. Now both of these
dimension tables when we select product and month
will filter the F transactions down to just the actual sales. Now, how do we get product
and date to filter this table? Well, product is easy. This is the one side I can
click and drag to product. Remember, on this side
there are lots of repeats. If there's one Aspen here,
there are many Aspens here, one for each one of the months. So when I select product,
it will correctly filter both of these tables. But what about date? This is the one side-- well, guess what? The end of month column,
if we go look at it. Here's F budget. If I pull this down, well,
there is a 1/31/19 for Aspen. Notice, lots of Aspen repeats. And down here there's a 1/31. So this is actually
the many side. For us, there will be
exactly three of each date because we have three products,
one for each end of month. So over here we can create
a one to many relationship. Now for our product
month pivot table, when I select January,
2019, all the days, that means 31 days will be
showing in this table, that will flow this direction and
get the correct actual sales. But if there's 31 days
showing here, guess what? Only one of them is
going to flow across. It's the end of the month. So 1/31/2019 will flow
across and will show only the three budgeted amounts for
the three different products. If we look at a picture
of a pivot table, here is the month
column, there's January dropped into that row. Well, what happens when we
do that to the date table? It shows all the rows with
January's, which for the days means 1 to 31. That means when the filter
goes from the date table over to the F transactions,
there it is, all of those dates are valid. So of course, the
transactional table will list every single
day from January. Now, what's going to happen from
the one side on the day column when it flows over through
the relationship to F budget? Well, it's only the last
roll, 1/31/19 that flows over. And then in the F
budget table, we see all three days,
January 31, 2019. Now all of that is just
what January for 2019 is doing to our
underlying tables. But there's also the
product Aspen for this row right here which causes
the underlying D product table to be filtered
down just to Aspen through the relationship
that further filters the F transaction. So yes, all the days from
January, but only for Aspen. And then of course, down here,
Aspen is the only product. That means all of
the sales numbers are now used to
calculate actual. And of course, there's
only a single budget number which appears right there. That means this
two fact table data model and these two dimension
tables will work fine. When I select a
particular month, it will correctly filter
both budget and actual. Product will filter
budget and actual. And we're going to be able
to compare the actual numbers to the budget, calculate the
variances and the percent variances. Now we go over to data view. This is the F budget table. And we could put
all of our measures, including adding up
the budget column, we could put them all
in F transactions. But I'm going to keep
one right in this table. So in the measure
grid, I type up in the formula bar, budgeted
sales colon equal sign sum. And we're going to select
over here in PowerPivot. Budget column, I see the table
name and the budget column name. Close parentheses and enter. I definitely want to add
some number formatting. Now we come over
to F transactions. In the measure grid,
we'll create our measure. We'll click on the
Sales column, it puts in the table and column
name, close parentheses and enter, add some
number formatting. Below we'll create our variance. Var colon equal sign
and square bracket brings up the measures
we have in this model. We're going to take actuals. So tab minus square bracket b,
there's our Budgeted Sales tab. Enter, add some
number formatting. And it looks like over
here on the measure grid where we don't have
our filter context, this is the grand
overall variance. So we're about 200k below
our budgeted amounts. Next measure, now we'll
say var % colon equal sign. And I'm going to use the divide
function, square bracket. There's our var. That's the numerator, comma. And we want a square
bracket B. We're comparing in the denominator to budgeted. Tab close parentheses and enter. We'll add percent
number formatting. Now we have a little bit
more data modeling to do. I'm going to go over
to diagram view. I don't want any of the
columns in the budget except for our budgeted sales. Click on the first hold shift,
click on the last, right click. Hide from client tool. I'm going to hide
date because I don't want to slice by day
by mistake because we don't have budgeted amounts. Month number we don't need,
hide from client tool, hide from client tool. None of the columns in our
fact table for transactions, hide from client tool. Now we'll come back to
this one in just a moment. But there's our
finished data model. Up here in the PowerPivot
for Excel window, we'll click the
Pivot table button. I want to put it on existing,
collapse, B3, OK, OK. If we expand, there's our data
model in the pivot table field list. Product down to row. Year down to rows. Month down to rows. I'm going to check actual,
budgeted, variance and percent variance. And that is
absolutely beautiful. If I want to see
the month on top, I simply drag, and there
it is, drag it back. Now customer, I'm
going to drag this over to the customers
in F transaction. Because customer
isn't in our budget. This is going to mess things up. If I come over to
our pivot table, pivot table analyze,
filter group insert slicer. Let's add customer. Well, of course if I select
a particular customer, some of the numbers will work. We'll of course,
actual sales, and these are working but mistakenly. Notice, this isn't
changing at all. We did not budget for this. So we want to remember to use
attributes that were actually budgeted for. I'm going to delete this. Right click, delete. Delete from model. All right, so we
have a little fun with data modeling,
relationships, and our measures to create our budgeted
actual report. All right, if you
like that video, be sure to click that thumbs up. Leave a comment, and subscribe
because there's lots more videos to come from excelisfun. All right, we'll
see you next MSPTDA.
