[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.