Welcome to Excel Magic Trick number 1,407.
Hey, if you want to download this Excel workbook
Excel Magic Trick 1,407 finished file or the start file
so you can follow along, click on the link below the video.
Hey, back in Excel Magic Trick 1,405,
we saw how to create a monthly revenue and add cost report,
but the sales numbers were from a table that had daily sales,
and the ad costs were from a table that had end-of-the-month
amounts.
Well, for formulas, it was easy, because we could point
our formula to the right table.
Notice, there's the criteria, the formula's
pointing to the sales table.
Over here for ad costs, there's the criteria,
but we're allowed to simply point to a different table
to summarize.
Now in the comments section below 1,405, Matt asks,
how do we do the same report but in a data model pivot
table using DAX measures?
Now the trick for a pivot table and using the data model
is that these two formulas are going
to be looking at this condition or criteria.
These two formulas will see all of the dates in January--
January 1, 2017 all the way to January 31, 2017.
But both formulas will be looking at the same table,
whereas over with formulas, we were
allowed to just point the formulas to different tables.
The point is, both these formulas
need to see that criterion somehow
calculate the correct number.
No problem, it's actually going to be pretty straightforward.
We just have to create a date table,
and then from the sales table, connect through a relationship
the date over to the first column in our date table
with a unique list of dates, and then
do the same for end of the month for our cost table,
build a relationship between end of month
and the first column in our date column.
Then it's as simple as dragging the month and year into the row
area and summarizing with a simple SUM
function, ad costs and sales.
Now I'm going to close this finished workbook
and we're going to go over to the start file.
So the key for us is going to be building this Date table
and then importing all three tables,
creating the relationship, and then using a simple SUM.
All right, it's easy to create a Date table in Excel.
I've already put the field names at the top,
and there's the first date.
Now I had to look through each one of these data sets
and make sure I have the earliest date possible,
and for a date table, you have to have
a complete list of every single date
from whatever the min to max would
be in each one of your tables.
All right, so I put 1/1/2017.
Now watch this, I want to take my selection cursor
and point to that little green fill handle,
and when I see my selection cursor
turn to a crosshair or angry rabbit,
I'm going to right-click.
So I'm right-clicking, I'm going to drag down one cell, then
back up, and let go of to right-click
and up pops a secret menu.
I point to Series.
Now I want to fill this series of contiguous dates
down a column, so I select Column, Step value one
day at a time, and check this out, there's the stop value.
I simply put 12/31/2018, and when I click OK, boom,
there's all of the dates.
Now we're going to need month number,
and we'll see why in just a moment.
Month.
That looks at a serial number date and we'll report 1 to 12,
Control-Enter.
Actually, I should have hit Tab, I'm going to hit Tab.
Now we're going to need the name of the month.
So in Excel, we use TEXT.
It looks at a value-- remember, all dates have serial number
values underneath, comma.
And then we have to know the custom number format
for showing the month name.
Well luckily, of all the custom number formatting,
dates are the easiest.
M is for month, D is for day, and y is for year.
So if we want to see a three-letter abbreviation,
we simply in double quotes put three m's, close parentheses,
and now I'm going to hit Tab.
The year equals YEAR function.
It looks at that serial number date, Control-Enter.
Now I'm going to highlight all three formulas and double-click
and send it down.
Go to the last cell, Control-Down Arrow, F2,
Shift-Tab, F2, Shift-Tab, I'm checking
to see if each formula is working, it's looking good.
Control-Up Arrow.
Now, we have to convert this to an Excel table
to get it into the data model.
So I go up--
with one cell in the table selected,
and I go up to Insert Table or simply use the keyboard
Control-T and Enter.
Now I need to get each one of these into the data model,
so I go to Power Pivot, and with one cell selected,
I click Add to Data Model.
I could see, there's the fSales.
Alt-Tab.
This is the fAdCosts.
Click in one cell, Add to Data model.
There's are two tables.
Alt-Tab, click in one cell on the Date table.
Ooh, can't believe I almost forgot
to do this, the most important thing when
you're using Excel tables--
Design, Properties, that is a terrible table name.
You always want to name your table
because the name of the table is used in formulas over here
in Excel and over in the data model.
So I'm going to call this dDate and Enter.
Now I have a single cell selected.
Power Pivot, Add to Data Model.
I have my three tables.
Now I go over to the Home ribbon, View, Diagram View.
I see my three tables, and look at this--
we're going to have to fact tables,
and there is one date table.
Now to create a relationship, I simply drag-- and remember,
date has a unique list in the first column here.
I'm going to drag it over to the end of the month.
Now there's a one-to-many relationship,
there there's exactly one of each date here,
but over here we have multiple end-of-the-month dates.
That allows us when we filter this table
to have the filter go through the relationship
and filter down just to the actual Ad Cost amounts we want.
Now we do the same thing for our second fact table--
so I'm going to click on Date and drag it over to Date.
This is a one-to-many, there's a unique list of dates here.
One day may have potentially many sales over here.
When we filter this table--
for example, when we filter on January 2017,
all of those dates will pass through here-- well
there's only one date here, so the end of the month of January
will show, but when the filter gets passed
through this relationship, all the days--
and we potentially have sales on all of those days,
but this table will be filtered down just to sales for January.
All right, now we need to go back to Data View.
And I'm going to choose to make our measures on the fSales,
so I click on fSales, drag up the divider
between the table and then what's called the measure grid.
We click on the measure grid, and we
can create our DAX measure.
Now we simply start typing the name of the measure.
Total, I immediately get shot up to the formula bar,
Total Revenue, and we have to use a colon and then
an equal sign, and then we put whatever formula we want.
We want a simple SUM.
So I type SUM, open parentheses, F-S
to get to the fSales, and then down arrow to Sales.
Close parentheses.
So when you have a DAX measure, everything
before the colon and equal sign will
be the name that shows up in our field list;
everything after the colon and equal sign is our formula;
and whenever we have columns, we have
to put a table name and then in square brackets the field name.
Enter.
Now here's a great thing about DAX measures.
I can add number formatting to this formula.
I'm going to click on English United States.
That number formatting will follow this formula around
now let's do the same thing for Total,
it shoots me up here, Ad Cost, colon, equal sign, SUM, type F,
and I immediately see from the first item in my list Ad Costs
table and then in square brackets our AdCost.
Close parentheses, Enter.
Add some number formatting.
Now we're done over here, and you
can come back over the Diagram view and see sure enough,
the measures are added to this table.
We could have just as easily put them down there below.
We could have put total ads up there, total sales here,
but I've put them both in the same place.
In fact, just to make sure that we don't accidentally drag Date
from here and we only want our dates from the Date table,
watch this.
I'm going to click on the first date,
hold Control, click on Sales, because I don't
want anybody dragging sales either,
I only want to use my measures.
Right-click, Hide from client tools.
Over here in the Ad Cost, I'm going
to click on AdCostID, Control and click on End of the Month,
right-click, Hide from client tools.
Not only that, but we don't need month number
we'll see why we need it, but we don't need it over
in our field lists, so right-click,
Hide from client tool.
Now let's Alt-Tab.
I'm back in Excel, I want to create a new pivot table
from the data model.
So I click in the cell I want the pivot table
to go in, go up to Insert, Pivot Table,
or use the keyboard Alt-N, V. Because we have a data model,
it assumes we want to use it as our source.
It already has the correct cell right there, so I click OK
or simply hit Enter.
And I'm going to scroll over, and check this out.
If we look at each one of our tables,
we only have the fields that we want.
Now we want Year.
So I'm going to drag Year down to Rows.
Then we want Month, and we're going to run into trouble here
when I drag Month down to Rows.
Uh oh, it's sorting alphabetically.
No problem.
Alt-Tab, over to Data View.
And I need to go down to dDate, so I'm in dDate,
and I need to tell this Month Name column to please sort
by a different column, and that different column
will be Month Number.
So the reason we had Month Number here,
and so now January will be associated with 1
and it will appear first in our sorted lists.
So when I click OK--
now April, of course, is 4, so when I Alt-Tab, there we go,
April's in the fourth position.
I don't like this layout, so I go over to design.
Layout, Report Layout, Show in Tabular.
Now this is the magic-- we have our two formulas,
Total Revenue.
I drag down to Values.
Look at that, including the number formatting, and Total Ad
Costs, boom, it's working just like our formulas.
Now each one of these measures, remember, those are formulas.
They're actually looking at a filtered date
table showing only January.
Here, it's only February.
So if we go back over to the data model,
Diagram View, in that pivot table cell,
this table behind the scenes in the column to our database
is filtered down just to January.
That passes through here, and since 1/31/2017
are the only dates here, this table
is filtered down just to those three costs.
Here, all the dates for January 2017
are passed, that filters this table.
Now we don't have a big data set, and that is fundamentally
the reason that this data model, DAX measures, and relationships
calculate so quickly on big data,
because when the tables are filtered down to a smaller data
set, faster calculating.
Alt-Tab, there you go-- how to do revenue and ad
costs from two different tables, but with the data
model, pivot table, DAX measures, and relationships.
All right, we'll see you next video.