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.