Welcome to Highline
BI348, Class Video No. 42. Hey, if you want to
download this workbook, BI348
Chapter02.75Example02Start, or, more importantly, the zipped
folder with the source data, click on the link
below the video. Hey, in this video we are
going to import big data into the data model
in PowerPivot, import a number of
dimension tables, lookup tables, build
relationships, build formulas, and create some reports. Now, I want to look at the
end result. Now, from five million transactions
from an online website, we need to build
various reports. Here's one of the reports. We need to summarize by
country the total net revenue. We're going to need another
report that shows us for each month and year
the total net revenue, total cost of goods
sold, and gross profit. We're going to need to look
at the percentage change. Notice, here's our months. We need the percentage change
from the previous period. And we need a
report that shows us the percent of each one of
our products as a percent of our grand total. Now, all the calculations
in these various reports could be done with standard
internal pivot table functions. For example, this would
just be Show Values as Percent of Grand Total. But we don't want to rely
on the internal functions and calculations
in a pivot table. We want to use DAXed
formulas in our data model because those formulas
and functions interact with the data model
columnar database to work more efficiently
and quickly on big data. Now let's go over
to our Start file. And we just want to
remind ourselves, the last couple of videos
we've talked about, what is PowerPivot, and
what is data modeling? Here is the five steps. Step one, transform and
import source raw data into fact and dimension
tables in the data model. Then step two, build
relationships between dimension and fact tables. Then step three, we have
DAX-calculated columns. And those columns are used
in other DAX formulas, or as the criteria in the
Row/Column/Filter/Slicer area of the Pivot Table. And then still in
step three, we build our second type of DAX
formula called a DAX measure, or calculated field. And we use those in the
Values area of the pivot table or in other DAX formulas. Then step four, we build
our pivot table reports. In step five, we refresh the
data model and our pivot table reports when the
source data changes. Our step one is to import our
fact and dimension tables. Now, let's go look at the files. Here is the folder you
can download and unzip. We're actually going to
import this price table. And we can double-click
and open this. This has the products, the
retail, and the standard cost. We'll use the retail price
to calculate revenue, the standard cost to
calculate cost of the product, or cost of goods sold. Now let's close this
Excel product price table. And I want to look at
the text files that have all our transactions. Now, there's a Start
text file and a New. The new ones are the files
we'll dump into our folder after the data model on all
the reports are finished. And we'll see that
the reports and data model can be updated easily. But here, let's look in
this Start text files. Here's all the text files. Look at the sizes on these. And I'm going to open up
just the smallest one here. Now, these are text
files, tab, delimited, with date, website, product,
quantity, revenue, discount, net, standard cost,
equivalent, and country code. Now, we have a bunch
of these files. This is no problem for us. As our earlier study
in this class showed, we can simply use Power Query
to get all these text files, dump them into a
single fact table and import it into
our data model. And of course, Power Query
will allow us to just point to that folder,
and then anything in that folder that's
a text file or that meets our criteria
for our query, will be imported
into the data model. So let's start with that step. We want to go up to Power Query. And here is that
amazing From File. But down at the
bottom, From Folder, I think we did this five or six
times earlier in the class, we're going to browse and go
to wherever our folder is. I'm going to click the
arrow there to open up. And we want to point Power Query
to the Start Text Sales file. Now, Power Query,
automatically when I click OK, will try and import every
single one of the files in that folder. I'm immediately going
to come over and name this query, because this is
going to import and merge all the tables together, the query. And I want to give it a smart
name because this name will show up over in our data model. This is the fact table, so
I do a little f, and then Sales, or Transaction, or
whatever you want, f Sales. Now, when we pointed Power
Query to that folder, it's going to always import
every single type of file. We actually are never going to
have anything in that folder except for text files
for this report. So we actually don't
need to filter it. In earlier videos we
saw how to filter this. Now, this is all
information about the files, and we don't need any of it,
so I come over to the Content, right-click, Remove
Other Columns. Now, we're going to use this
double downward pointing arrow to expand. And when I click
Link in Other Videos, it dumps everything here. But it's not as polite as
examples earlier in the class. Power Query didn't interpret
and automatically do a number of steps. But no problem. Were going to do them. For example, it
didn't automatically promote the headers
or field names from the first record up to
Column, Headers, or Field Name. So I come up to the
upper left-hand corner, click the dropdown, and say,
Use First Row as Headers. Now, that only happened
for the very first file, and we have like five
or six text files. Further down in this data, we
have date, website, product, the exact field names from those
files down below incorrectly listed as a record. So we're going to have to pick
one of the columns, whichever one has the fewest
unique records, and come up to the filter. And when we click the Filter, it
tries to show us a unique list. This is a huge data set, so it
says List May Be Incomplete. We definitely want
to click Load More. Now we can see a unique list. And I see the actual
field name, Website. So when I uncheck this, notice,
this is in the Website column. It will go down and remove
all of the websites listed in this column. That will automatically
remove all of the field names in the records below. So when I click OK, boom. Now, when we did Import
From text files earlier, it actually did a bunch of
steps, like promote headers. And it actually
changed the data types. And it didn't do that. You could see visually
this is to the left, and all of these
numbers are too. So we have to manually
go through and verify Home, Transform, Datatype. This is going to be a date, so
I'm going to click the dropdown and say, Date. For each field,
we want to verify. Text, that's fine. Text, that's fine. Quantity, text is not fine. And if we made a mistake
and forgot to do this and imported into Power
Query and tried to do stuff, we probably wouldn't
get the results we want. We can always come back
and edit Power Query later. But we're going to try and
catch all of this up-front. So this one I want definitely
whole number, both revenue discount and net standard
cost equivalent are decimal, so the data type is
Decimal Number and click. This one should be text. And look at this. This column, I'm not
even sure what this is. It says, Blank. I'm going to click Load More,
just to see what's there. Looks like there's nothing
there, like a blank cell. I definitely want to
right-click and remove. All right. That should be all of the steps. There is our name. We're going to
come up and close. And we're definitely
not clicking this, because it would try
to load it to a sheet, and it wouldn't succeed because
it's five million records. Close and Load To. We want to say
Only a Connection. Add this to the data model. And when we click Load, it's
going to try and load it into the data model. And there we go,
5,320,493 records loaded from those five or six
text files in a single table. Now we can go over
to PowerPivot. When we click this, it
opens Manage Data Model. And there's our table. You can see down here it's got
the five-million-plus records. Now, we don't really need to
do anything to these columns, except for I am going to
change the formatting here. So Format, I'm going to click,
let's say this one right here. Now, that's only
one table, so I'm going to Alt, Tab and
go back to Power Query and get our next table. I'm going to go
From File, and I'm going to get the product table. So I've navigated here,
D Product Price table, double-click. There it is right there. I'm going to select
it and click Edit. Now, notice it took
D Product Price. That's from the sheet name. Here's the steps. And notice, it promoted
headers and changed type. This data set is
fine the way it is. Come up and close and Load To. And I want to only Create
a Connection an add this to the data model. Click Load. Now, those are the
only two tables we're going to load
through Power Query. If we ever need to update
or edit or anything, we can come back to
this window, right? That's the Show
pane, Power Query. If we needed to edit this and
change data type or anything, we can right-click Edit. I'm going to close this
for now and go over to the data model Alt, Tab,
and there's our two tables. Now, Alt, Tab we actually
have two more tables. And I'm going to
click a new sheet. We're going to need a calendar
table, because as we will see, you cannot group dates in
a PowerPivot pivot table. So I'm going to double-click
the sheet and call it D Calendar and Enter. Now, all it means is,
for a calendar table, is we're going to need a listing
of every single individual date, like January
1st, January 2nd, January 3rd, for each
one of the years. And then we'll have an extra
column for, like, month, name, and year. Now, I'm going to type Date,
Control/Enter, Control B. And here's a great trick. How do we create
all these dates? Well, it's easy. Ah, ah. But wait a second. First, we need to know our data. And we do know our data. Our data goes from January
1st, so 1/1/2014, all the way to December 31, 2016. So here's an awesome trick
for creating every single date for your calendar table. Point to the little fill handle. And when you see your crosshair,
or angry rabbit, right-click, drag down a little bit. Don't let go. And then drag back up. And now let go and a
secret menu pops up that gives you
the option Series. So I'm going to click on that. And this is so cool. It will fill the
dates automatically. We want to fill it
down through a column. The step value is going
to be 1, because we know that's a serial number there. And the stop value, just type
the date you want- 12/31/2016. And when I hit Enter, or
click OK, just like that, it creates all those dates. Control, down
arrow, there it is. Now, as we remember
from earlier videos, if we want to get an
actual table from Excel into PowerPivot, it has
to be an official table. So with one cell in the table,
I click Insert in table, or use the keyboard,
Control T and Enter. Now we definitely want to go up
to Design, Properties and Table Name. If you do this a lot, it's
Alt, J, T, A. And there it is. I'm going to call this
D Calendar and Enter. Now I click in a single cell. Go up to PowerPivot,
and there it is. Add to Data Model button. So I click this. And there we go. And it has a little link because
it linked to that Excel table. These don't have a little link. But of course, they
are completely dynamic and linked to the source
table through Power Query. Let's come up here, and I'm
going to do the same thing. You don't have to do this,
but I like to-- Formatting. And I want this one right here. There we go. Later we'll come back and add
some extra calculated columns. Now, we have one
other table we need to import into our data model. And I want to go
look at f Sales. So we have date. We have a website name. That's where we've sold it,
the product, the quantity, the revenue discount for each
transaction, net standard cost equivalent. And here it is, Country Code. And if you click the dropdown
to see a unique list, there's all sorts
of country codes. Now, if I were to drop
this into a pivot table row or column or slicer or filter,
the viewer of the report may not know all of
these abbreviations. So we're actually going to have
to create a table, a lookup table, that will be able
to look up these and return the full country name. Now, we know from
our data set, these are ISO, International Standard
Organization, country codes, 3166-1. And so we're going
to open up a browser. And in Google I'm just
going to type ISO 3166-1. And we can look through
here, but Wikipedia sometimes has some amazing data for you. And the one we want
is this Alpha 3. It's a three-character code. That's what we have. So when I click on here,
you can look through here. And sure enough, they list it. Now, we could absolutely
use Power Query using this web address. And we learned how to do
that earlier in this class. It would be dynamic
if it changed there. But two things, I don't want
it because the actual data-- if any of this changes, I
don't want it to update. And not only that, if you
go through Power Query and import it, which I
did, it's a lot of steps. And for some websites that's
fine, but some websites, like Wikipedia, when
they have data like this you can highlight it. And if we were to copy
and paste it into Excel, it might not look
exactly how we want. But I want to show you a trick. We want to go actually over
to our Exhale Workbook. I'm going to insert a new
sheet, call this D Country Code and Enter. Now, I'm going to just
paste, Control V. Oh, that's messed up. Control Z to undo that. But here's a great trick. And whenever I'm in a
hurry, I always try this. Sometimes stock data from Yahoo
Finance or other website type tables, you come up to
Home, to Paste dropdown. And I want to say Paste Special. And if you paste it
as a text, oftentimes, the website designers knew that
people would copy and paste. If you paste it as
text, boom, instantly in a correct, proper data set. It took those three
different columns and imported them properly
as a single table. Now, when you import
it through Power Query, it gives you three
tables and you have to do that append like
we did in earlier class. Actually it didn't give us
a complete proper data set. I'm going to insert a row. You can either
right-click Insert, or there's a keyboard Alt,
I, R, Country Code tab. And this would be
Country and Enter. I'm going to add Bold. Any time you have a proper
data set, at the minimum, make sure the field
names are in bold. Most data analysis features,
if you have no formatting to distinguish the text
field names from the text values in the data set, it won't
know that this is a field name. But as soon as you add
some basic formatting, like Bold, most features,
especially in Excel, will distinguish
between the two. Hey, I'm going to
Control T. And look, it did there, because I had Bold. It says my table has headers. Alt, J, T, A. And I'm going
to name this D Country Code and Enter. Now, I can come
up to PowerPivot. Add to Data Model. And just like that,
there is my D Country, with a little linked. Now we have our fact table and
our one, two, three dimension or lookup tables. Now, that's step one, importing,
getting all your data here. And this is all dynamic. If any of that stuff
changes, we can refresh. Now, step two, we have
to build relationships. So I'm going to go
up to Diagram view. And here is our fact table. And we have product with
retail and standard price. That's the dimension or lookup. There is our calendar table. And we'll put our
D Country Code. And let's go ahead and expand
so we can see all the columns. And this one will actually
have a few extra columns. This one will have a
few extra columns also, later, when we add our
calculated columns. Now, here's product. This is a unique list in the
first column of our lookup table. I drag it over to product where
we have many duplicates here. There are many transactions,
for example, that sell the Quad. But over here in
the first column there's a single entry for Quad. Now, date I'm not
going to build, because I want to show
you what happens if you don't have a calendar table. And then we'll come and
build a relationship and see that it fixes
something quite cool. Country Code I want
to click and drag over to the Fact Sales table. There are many transactions that
were sold in the United States. Over here in the
first column there's a single entry for country
code and a single entry for the actual
full country name. Now, I'm going to
Control S. Again, later we'll come back and
build a relationship there. Go back to Data view. Now, we want to go
over to the fact table. Step three is
building our formulas. We have the choice
between calculated columns and measures, or
calculated fields. And I'm going to pull this up. And the first one
we want to do is we want to calculate net
revenue for each transaction. That will be a
calculated column. And then we'll make a
measure, or calculated field, for total net revenue. And we'll use that in the
Values area of the pivot table. So I'm going to double-click
to add column and name this Net Revenue and Enter. And this is the same formula we
did in the last couple videos. It's a similar data set. One of the differences
we have in this data set is that we have net standard
cost equivalent, because we're going to have to
calculate our costs, or our cost of goods sold. And we have country. But we had revenue
discounting quantity. Now, you can see
a bunch of zeros, because these didn't
get a revenue discount. But you could look at
some of the discounts in our unique list here. Depending on the quantity,
there are different revenue discounts. I'm going to click OK. So we're going to
look up the price for each one of the products. We can do that because
there's a relationship. We'll multiply the price times
quantity, and then times 1 minus the revenue discount. So we're going to click here. And equals R-E, while
we're jumping up to the Formula bar and Related. Related is DAX, VLookup. And because we have
a relationship, we just need the name of the
table, that's D Product Price. And the name of the column
in square brackets, for us it's Retail Price. That's it. The related function, when
I close parentheses and hit Enter, of course, it will
know from row context to look up the product
name over in that table. And because we said Retail
Price column, it will return it. Now we come to the end. We need to multiply that
by-- and our convention, even though we're allowed
to click on Quantity, that's not our convention. Any time we have a
column in a formula, we're going to
put the full table name, and then in square
brackets the field name. We will reserve square
brackets alone only when we're using measures
or calculated fields in other formulas. And we'll see an example
of that just in a moment. Then we need to multiply
price times quantity times, open parentheses,
1 minus f Sales, and down, down, down until
we get Revenue Discount. Remember, that's just the
discount line, 1%, 2%. So 1 minus will
give us 0.98 if it was 2% to give us the actual
revenue, not the discount. Close parentheses and Enter. And as we saw in
earlier videos, we're going to need to
round that also. So I come to the beginning,
and this function is exactly the same in
DAXed and over in Excel. I come to the end, comma,
and 2 to the penny. If you are rounding to the
dollar, it would be to the 0. But 2 because we have pennies. Now, there's a calculated
column on five million rows, net revenue for
each transaction. Now, we're not going to
do number formatting, and we're not going to
ever use Net Revenue over on our pivot table, because that
would be an implicit formula using the pivot table function. And we don't want to do that. We want the efficiency gain
by using DAXed formulas. I'm going to use this
wide column here, net standard cost equivalent
and make all my measures. And we're going to have a
number of different measures, or calculated fields. Of course, when we're
creating a measure, slash, calculated field, remember,
those are synonyms. We type the name of
the new field, Total. And it shoots me up to the
Formula bar, Total Net Revenue. And we have to type a
colon and an equal sign when we're doing a
measure, calculated field. And all I'm going to do is Sum. Now I need to reference in
the Sum this net revenue. So f Sales. And there in our list,
down arrow, down arrow, is our net revenue. That is our calculated
column, which we're now going to use in a
measure, calculated field. Close parentheses and Enter. Now we definitely need
to add number formatting. I love this aspect of
PowerPivot and DAX. Now that I have this formula
in Total Net Revenue, and it has a number
format, you want to drag and drop that
field [INAUDIBLE] into the Values area. It will carry that
number formatting. Now, with our calculated
column and our measure, or calculated
field, again, that's step three, now we're going to
move on to step four and build a pivot table. We'll come back and calculate
more measures and more calculated fields. But let's make our
first report, which is revenue for each country. Now, when I click this
button, it automatically makes a pivot table
from the data model. That means all four tables. I'm going to put it on a
new sheet and click OK. I'm going to move
this sheet over here, double-click and call it
Revenue by Country and Enter. Here's our field list. That is beautiful. We can drag and drop from
any one of those tables. The three-dimension
tables have relationships with the fact table. In here, our first
criteria for the Row area is going to come from Countries. So when I drag Country
from the D Country Code table down to Rows, I
instantly get a unique list. Now we come down to Fact Sales. And there is the net revenue. That would be implicit. I want my measure,
calculated field with the number formatting. And I click and drag,
and, boom, just like that. There is my report. Now I can come up and
Design, Report Layout in either Outline or Tabular. And there is our first report
from our data model using the pivot table user interface. Total net revenue for
each one of the countries, I could-- if I wanted
to sort it Z to A. And look at that. There are some blanks
in this data set. Now, this is going to
be the troubleshooting part of building our reports. Now, what happened was
we went to the website. Our boss said 3166-1,
and we got these. My suspicion is that from
our original fact table here, our country
code, there's probably a discrepancy between that
and what we downloaded over here because, remember, this
column right here, Country, is related through country
code to our fact table. So my guess is there
is some trouble there. So here's what we do. We go back to our
boss and say, hey. I got the ISO 3166, and
there was a discrepancy. So then you have to dig out
the company's original set of ISO's. So that's what
we're going to do. In your download, if you
open up D Country Codes, ISO 3166 and open this up. And so how would you
investigate this? Well, this is from
inside the company. This is the website. These are the exact ones
that the website used. I'm going to Control, Asterisk,
to highlight Control C to copy. And then I'm going to go
over to our Start file. And here's the country
codes that we import. And I'm going to
Paste, Control, V. And since it appears that one
of these is not on this list, this is comparing one list to
another list in the function inside of Excel that we use to
do that, is the Match function. So I'm going to ask the
Match function, hey, is this particular country
code right here, comma, and select this entire
column, Control, Shift, down arrow, Control,
Backspace to jump back up. And I want you to notice
something totally awesome about this. This is an Excel table. And this is called Table
Formula Nomenclature, or Structured References. And it's exactly
like PowerPivot DAX. Look at that. That's the table
name right there. And that is the field
name in square brackets. So now, comma, 0, because
we're doing an exact match, close parentheses, and Match. We'll look up the
country code over here and tell you the
relative position of the item in the list. Control, Enter. So USA is the 235th
item in the list. Double-click and send it down. Those are all the
positions in the list, but when we scroll down, NA
means it did not find UNR. This is what we
use in our website. That's what we want
returned in our pivot table. But guess what? NA means it did not
find it over here. Similarly we have
APR, [INAUDIBLE]. I'm just going to add these two
records to the bottom, Control, C to copy. And guess what? This is an Excel table,
Control, down arrow. The whole reason that
Power Query and PowerPivot require that you
use Excel tables is for exactly this reason. If I had any new records, the
table sees those new records, and when I go over to the
data model and refresh, it will pull in the new records. So Excel tables are awesome. Control, V, just to
paste at the bottom. And check that out. It's already part of the table. The other one, Control,
C, and then click here, and Control, V. That's
also part of the table. Really, the moral of
the story is probably that we should have got this
list in the first place. But this is a great example
of getting your hands dirty with data, right? We were trying to be all slick
getting this from the website, instead I'm going
to get in the file. But we got the file. We updated. And guess what? This is an Excel table. We click Add to Data Model. And it's looking at the table. This extra data won't matter. This data wouldn't matter if we
had used Power Query to import the file and told it the whole
sheet, or something like that. But it won't matter here. Control, S. When I go
over to the data model, I'm going to go to Country Code. Click in the cell. And up here, here's
the ribbon tabs. And look at that. Link table, and
there's the button. Update Selected. And now, I could use the
Update All from somewhere else. But I came here
because I didn't want to spend all the time waiting
for five million records to be re-imported. So this Update
Selected instantly updates just that
one Excel table. And now when I go over
and look at my Revenue by Country report,
the blanks are gone. Now, that's pretty cool. So we made our first report. We ran into trouble,
and we fixed it and saw the whole dynamic nature
of source data, data model, and our end result report. Now, I actually want take
a look at field list. Open up this Fact Sales
table and notice something. Revenue Discount, Net Standard
Cost, and Net Revenue, we're never going to use those. Not only that, but we don't
want anyone to use those. So we can actually go
over to the data model, and for each table,
we can choose which fields to show in
the pivot table field list. Alt, Tab, and let's go
over to Manage Data Model. Now, let's go to Diagram view. We don't want Revenue
Discount, and I'm going to use the Control
key to select columns not next to each other. And now I'm going to right-click
and Hide from Client Tools. So imagine we were going
to give this to a client on a reporting system, right? We don't want them
messing around with these, or see them, or have them
clutter our field list. So we say, Hide
from Client Tool. They are grayed out here. And now when we click on Data
view and go over to Fact Sales, we can see that
they are grayed out. They are still there, and
we can mess with them, but for the time being,
they're grayed out. But more importantly, Alt,
Tab, over in the f Sales, that is a much cleaner list of
fields in our pivot table field list. And there it is. There is the only revenue number
we're going to drag and drop. Now I want to go over
back to Manage Data Model. And I want to talk about
the calendar table. But before we do that, we
want to make a mistake. I'm going to click
on Pivot Table. Put it on a new sheet. I'm not going to name it. I'm going to go to Fact Sales. And I'd like to group by date. So I'm going to drag
Date down to Rows. And now it gives me
that unique list. Those are all the days
that we had sales. I can even drag Total
Net Revenue down here. And that's a great report. That's total net
revenue for each day. But watch this. If I come over, and, let's say
I want month instead of day, right-click. Oh, grouping is not there in
the Data Model pivot table. It is there on a regular pivot
table, but it's not there. This means we have to learn
about the calendar table. So I'm going to
delete this sheet. Right-click, Delete. And we'll go back over
to the Managed Data Model and over to D Calendar. What it means is
that we have to have a unique list of all the
days from the minimum date to the very max date in our
data set, an actual list with every single one. Even if we don't have
sales on some of the days, you want a complete list here. And then we can add extra
columns- Month Name, Year, and various other columns. Now, we're going to start
by coming and double clicking the first column. And we want to do Month
Number first, and Enter. Sometimes people like to have
Month Number, 1 is January, 2 is February, 12 is December. Well, guess what? We can create a
calculated column, and the name of the function
is exactly like it is in Excel. Month, this is D Calendar,
down arrow to D Calendar Table, Date field, and Tab. Close parentheses and Enter. And we could use
that and that's fine. However, lots of
times we would like to see, just like in a regular
pivot table, the actual month name. So I'm going to double-click
up here and call this Month Name and Enter. Now, what we'd like to do
is what we do over in Excel, equals T-E-X for
the text function. Text function over
Excel takes a number. And we can add a
number formatting to convert it to text. But it's just not here in DAX. We have to use the
new function Format. And actually, the
Text function in Excel describes what Text
delivers, which is text. But Format over here
in DAX describes what we're doing to the number. We're going to format it with
a custom number formatting for month. So I'm going to put
for Value, D Calendar, down arrow for Date tab, comma. And the format is exactly like
we would do over in Excel. You have to know custom
number formatting. In double quotes, the custom
number formatting for month, three-letter abbreviations is
three M's, end double quote, close parentheses. And when I hit
Enter, there we go. If you wanted to see
the full month name, you add four months and Enter,
just like over in Excel. I'm going to only have
three, Backspace and Enter. Now, let's try to build
that same pivot table. Put it on a new sheet. This is exciting. I go to the calendar,
and there's Month Name. Oh, what is that? It's not sorting correctly? Oh, my heavens. Pivot tables not only can
group, but they sort correctly. Oh, it's no problem. Let's go back over to
our calendar table. And the way you have
to do it is they have this button Sort by Column. We actually have to
tell this Text column not to sort alphabetically but
to sort by the month number. So we come up with our
cursor inside Month Name. Click on Sort by Column,
Sort by Column button. And it wants Sort Column
Month Name by Month Number, because, of course,
1, 2, 3, 4, 5, 6, 7, that will get our
January, February, March, even though they are going to
be alphabetically out of order. When I click OK,
and there we go. Nothing changed over here. But when I Alt, Tab and go
over to our pivot table, there it is. So that's how you can
use a calendar table. You have to add Month
Number, then the Month Name, and be sure to Sort. I need to tell this not
to sort alphabetically but to sort by Month Number. Alt, Tab. Now we can build our
report, Total Net Revenue. What in the world is going on? So you can see the
yellow button comes up. And earlier in the
class we saw this. There is no relationship. Yeah, when we first went in step
two to create relationships, we didn't create
the relationship between the date calendar
table and our fact table. So we're going to go back
over to Manage Data Model, over to Data view. Now we can drag Date right
over to our fact table. A one-to-many. There is exactly
one of each date. And over here there are many
through that relationship. Now, the fact dates will know
to get the right month name. Alt, Tab, and there we go. Instantly it updates. Now, let's get rid of that row,
Labels, Design, Report Layout, Show in Tabular. Now, our goal here is
to have month and year. So we go back over to
our calendar table. Go to Data view. And we can add yet
another calculated column. We'll call this Year, Enter. Equals, and this
functions exactly the same as it is over in Excel, Year
looks at a serial number. We'll look at D Calendar
and then the Date column. Close parentheses and Enter. And so now we will get the year. Alt, Tab, expand D Calendar. Now we can drag Year
down to Columns. And look at that. We have our three
years and our months. Now, you know, we're not
going to need Month Number, so Alt, Tab, Diagram view. We come over here, Month
Number, right-click, Hide from Client Tool. Data view, we can see that
one is missing when we go over to our pivot table, boom. There it is, the date,
the month, and the year. You know, here's another thing. What about Month Name? That's helpful for us. But probably we don't need it
over here on the field list. So let's go back over
to Manage Data Model. And I'm going to double-click
and change the name. Alt, Tab, D Calendar, and
that's looking much better. But this is not
looking much better. We need to drag and
drop down to Rows. And there we go. Now, our next report is going
to involve a couple more DAX measures, or calculated fields. So let's go back over to
our Manage Data Model. And on f Sales, we're
going to come down here, and we have to calculate
total cost of goods sold. But first, we're going to
come up and do a calculation for the individual transaction,
cost of goods sold. I'm going to call this Cost of
Goods Sold, COGS, and Enter. And it's actually a
very similar format. We're going to have to
look up the product, find the standard
cost from this table, then multiply it by the
same quantity for revenue, and we have a net
cost equivalent. And if you click
the dropdown, this is-- since we're using a price
list over multiple years, we have a net cost
equivalent that you multiply. So we can see here this
was 2% below whatever the standard cost. This is 1%. This is right on, 1
1/2%, above, et cetera. So same type of
formula, round, related. The one difference is that
this is a cost equivalent. So there is no 1 minus. This is the 1
minus the discount. So equals related. There's our VLookup. DP for Product Price. And I need to go down to
the Standard Cost and Tab. Close parentheses and Enter. And of course, that gives me
the standard cost for each. Up in the Formula
bar, times f Sales. And I need Quantity
tab times f Sales. And I need the net
standard cost equivalent. And if I hit Enter, boom,
that will copy all the way. There is our Cost of Goods
Sold for each transaction. But of course, we
want to Round, Tab. We come to the end, comma, 2,
close parentheses and Enter. Calculated column,
we're never going to drop this in the pivot table. We're going to make an explicit
formula down here, measure, or calculated field. Those are synonyms. We type the name of the
field first, Total Cost of Goods Sold, up
in the Formula bar, colon, equal sign,
and then Sum, Tab. And from the f
Sales, we can go down and get our new
calculated column, Cost of Goods Sold and Tab,
close parentheses and Enter. We want to add some
number formatting. Come up here and use English. Now, guess what? We have two measures,
or calculated fields, and we want to
calculate gross profit. So Gross up in the Formula
bar, Gross Profit, colon, equal sign. And guess what? This is the first time we're
going to use a measure, or calculated field in another
measure, or calculated field. So this is where our
convention comes in. We do square bracket. And you can see down
here, it has a big M, that means this is a measure. And I need to take
Total Net Revenue minus square bracket, T. And there it is, Total
Cost of Goods Sold, Tab. We got our two total, our
two measures, and Enter. And now, when we look at this,
we know that that is a measure. We're not confused. We're not thinking those
are columns or fields. Those are measures. So that is our convention. Let's add some
number formatting. And now we have our
three calculations. We can now go and
amend our pivot table. Alt, Tab. This report right here,
Month and Years, we actually want to calculate
total net revenue, and we'll click
our f Sales table. We want to drag Total Cost of
Goods Sold down to the Values, and then Gross Profit. You know, actually, I think
I'm going to Alt, Tab. We're going to come back
over here and amend this. We're going to call this Total. Consistency in naming
is awesome, and Enter. Leads to less confusion. Alt, Tab. We have our two measures,
or calculated fields. I'm going to scroll
down, and here is Total Gross Profit and drop. I want to change this layout
of the pivot table report, so I'm going to drag
Values down under Months. And there we go. Actually, let's do something--
that seems kind of busy. We're going to go up to
Design, and actually we're going to use Compact form,
which doesn't show the field name so that we can type these. And that adds a little
space to the top that makes our report a
little bit easier to look at. I'm going to call
this Month and Year. And that might be self-evident. Let's delete that. But you can't. So watch this. I'm going to cheat. I'm going to put a
single space and Enter. And I'm going to come over
here and type a single space and Enter. That's looking much better. So that is one of
our end reports we needed by month and
year, total net revenue, cost of goods sold,
and gross profit. And if we scroll
down, there it is. Now, I actually want to make a
similar report, year and month, but I want to see
the percentage change from one year to the next. So we're going to Alt, Tab. And I'm going to start a pivot
table, and then it won't work. And we'll come back
and make a new measure. Click on Pivot Table
button from the data model. Click OK. There's the new sheet. I'm going to
double-click and call this something like
Percentage Change Report. D Calendar, Year down to
Columns, Month down to Rows. And we're just going to
look at the percentage in total net revenue. And actually, let's
bring Years over here, because we're actually
going to want it oriented totally vertical. Now, when we make this report,
since we start in 2014, and we don't have
2013, we're not going to have the numbers
for percentage change. So I'm actually going
to collapse this. But here we want--
actually, we'll collapse this in
the final report, but here's what we want. We want to actually
be able to take the January from this month,
the end amount divided by the January from the
previous period and subtract 1. And that tells us the
percentage change. But again, we don't want
to use internal functions or calculations in
the pivot table. So we have to figure out how
to do that formula in DAX. Now, here's the amazing thing
about DAX, Data Analysis Expressions, which means
data analysis formulas. There are some
amazing functions. And one of the functions
is Same Period Last Year. That's actually the
name of the function. It will actually remember, it's
as if it looks at that number and knows to automatically go
back one year and get January. And if you change
the date criteria, it knows to go back to
whatever that period is. It's going to be an
amazing function. But here's the thing, the
way that DAX functions work, and we talked about this
the last two videos, is in that cell is a
measure, or calculated field that sees the criteria
from the pivot table. So it sees January 2015. So we actually
have a formula that can change the filter context. And in our formula,
we'll actually use our Total Net Revenue, that
will see the filter context. But in the denominator, I need
to somehow go back and get the previous year, which means
in that cell I need to actually get the criteria January 2014. And the two functions
that we're going to use are Calculate, the
function that we use to change the filter
context, which means change January 2015 to January 2014. And inside of Calculate, we'll
put the total net revenue, and then in the
filter argument we'll use a function called Same
Period Last Year, so there is a way to actually do that. Let's go back over to
our Manage Data Model. And below here I'm
going to type Total up here in the Formula bar,
Space, NR, for Net Revenue, Last Year, colon, equal sign. And we need to
use the Calculate. Now, the Calculate function
does amazing things. We're going to see a couple
examples in this video here. I have a reference video you
can watch for other examples. But the expression is where
you put the actual calculation. And that's going to
be Total Revenue. So we do square brackets, Total,
down, down, Total Net Revenue. Again, we're following
our convention. Right there we used
our square brackets, so we know it is a measure,
or calculated field. Now, comma, and you can
change the filter context in the pivot table. And here it is, Same
Period Last Year. You gotta be kidding me. And since the filter
in the pivot table comes from the date
table, you just have to put DC and the actual
primary key in that column, and it will know to switch the
criteria to the same period last year. So there it is. There is our filter. There is our calculation
in the Calculate function. Close parentheses and Enter. Now, the answer there. Let me format this. Since it's Same
Period Last Year, if you added up everything,
which is that right there, and subtracted 2014, that would
be that answer right there, because it can't find
anything for the year 2014, because we don't
have 2013 records. Now let's go over and
just see this in action. I'm going to go back over here
and open up our field list. And there it is, Total
Net Revenue Last Year, and drag it right down here. Of course, we get nothing there. But look at that. That's exactly the number
we want right there. Exactly the number we want. So now we have these two
measures, Total Net Revenue and Total Net Revenue Last Year. We can do our division
and subtract 1. So let's go back over
to Manage Data Model. I'm going to do a new
formula over here. I type Percentage
Change, it jumped up to the Formula bar, colon,
equal sign, square bracket, T, for Total Net Revenue. And that's the end amount. And then we divide it
by square bracket, T, and we want Total Net Revenue
Last Year, subtract 1. And when I hit Enter, I'm
going to add Percentage Number format with two decimals. Now Alt, Tab, and we
can drag this new one, Percentage Change. And look at that. We're going to get an error. And how we're going
to fix this is, we could use the
If Error function. And if you've watched
my videos at YouTube, I've talked about the problem
with If Error over in Excel. And it's the same
thing over here. It oftentimes takes a
long time to calculate, because it actually has
to calculate the formula and get the error
every single time. So if there's an alternative
to running the formula and getting the error to
determine whether to turn this off or not, meaning there's
an alternative logical test, you should use it. And guess what? Same Period Last
Year is equal to 0. So we'll use the If
function and simply check, is that equal to 0? If it is, we'll
put a blank here. Otherwise, we'll
run the formula. Alt, Tab, so we come up here. And after the equal sign,
If, and now square bracket, T, and I'm going to arrow down
to Total Net Revenue Last Year. If you're equal to 0,
that's the logical test. It comes out true
or false, comma. And guess what? In Excel we would put
double quote, double quote. But that is text. And since the result of this
formula here is a number, you are not allowed to have two
different data types co-exist. Now, if you think over
to Excel, if you've ever done a pivot table and
tried to group dates and there's an
empty cell, that's an example of where Excel let
you have two different data types, but it caused
huge problems. So that is an actual awesome
thing over here in PowerPivot. You cannot have text and
numbers coexisting together. So Backspace, Backspace. They fixed it, because now
there is a Blank function. And that is neither text nor
a number, so it will work. Comma, and then, of course,
if this comes out false, which means it's not equal to
0, then it runs the formula. So I'm going to hit Enter. And now when we Alt, Tab,
there, it's disappeared. And now we can collapse
this and scroll down. And there are our
percentage changes. Look at this, from
the March previous we just had a huge
increase, and, of course, the following
period we went down. Now, I see that the Row
labels are gone when we pivoted our pivot table. I'm going to do a little
trick with a Space there and then Enter. Now, we have one last
report we want to create. We want to, by product,
show the percentage of each sales for
each product that's a percent of the grand total. Now, one thing that we're
going to have to do, and we can go ahead
and come back over here and click the Pivot Table. Click a new sheet. Double-click this and call
it Percent of Grand Total and Enter We can come over to our Product
Price, drag Product down here. We can drag our f Sales,
Total Net Revenue down here, Design, Report Layout,
Show in Tabular or Outline. What I really want is just to
take each one of these amounts and compare it to the total. Well, if we're talking
DAX, and the pivot table's based on the data model and DAX,
if we have the total revenue, that would be fine in each
cell, because, of course, the formula sees
the filter context. But how in the world are
we going to get access to the grand total? Well, we're going to have
to alter the filter context, and Calculate is
the function you use to change the filter context. Not only that, but just as there
was a Same Period Last Year that got the number
from the previous year. There's a function that can
strip away all of the criteria. It's called All. So now we're going
to go over, and we're going to build a formula
that when we drop it into the pivot table,
has the grand total in every single cell. And then we'll use that
with Total Net Revenue. Total Net Revenue
will be the numerator. This Calculate All
with the Grand Total will be the denominator. And boom, that will be
our Percentage of Grand Total formula. So Alt, Tab, to go over
to Manage Data Model. Click down here. So I'm typing Percent of Grand
Total, colon, equal, and then C, for Calculate, Tab. The expression,
square bracket, Total, and we want the Total
Net Revenue, and comma. The filter? We only have one field
dropped into our Row area, so we can use the All function. And you can read the
little screen tips. They have them just like
they do over in Excel. It will take away all of
the filters, or criteria, from the filter context. You can have a table and
then specify columns, but we're just going
to put the table. What is it? It's Products. So it's DP, and there it is. T for table, Tab,
close parentheses. That filter is the All. The All will say, hey,
strip away all the criteria from the Products table. Total revenue will
then be calculated without any of that
criteria, which, of course, is the grand total,
close parentheses and Enter. Add some number formatting. Alt, Tab. Let's go ahead and just drag
this and see what happens. Percent of Grand
Total, (SOUND EFFECT) the same one all the way down. I'm going to drag
this off, Alt, Tab. And actually, I was going
to come back and alter this. But one of the rule
of thumbs is we might want Grand Total in
another formula, another DAX formula. So I'm actually going
to get rid of that, rename this Grand Total. That will just be
the grand total. Then we can take that
measure as the denominator. And we can take Total Net
Revenue as the numerator. So I come down here, and I typed
Percent of Grand Total, colon, equal sign, square bracket, T. And now, remember, this
is Total Net Revenue. That sees the filter context,
so when it's dropped in there, it calculates the total
net revenue for whatever the product is in the Row area. We want to divide it by square
bracket, G, for Grand Total. When I hit Enter, it gives us 1. It doesn't have
any filter context, so I'm going to click the
Percent to show two decimals. Alt, Tab. Open up f Sales and down at the
bottom Percent of Grand Total. And look at that. There is our Percent
of Grand Total. Now, that's our final report. I want to save this. And now, we're going to
drag some new folders. So when you download
this, here's the text for the
year 2017, copy. In Power Query, we told Power
Query to look at this folder. So if I start dragging
new text files, or pasting new text files down
here, when I click Refresh it will instantly go and
get all of the files, including the new ones. Now, if you're
thinking ahead, it will cause a slight problem,
because our calendar table doesn't have 2017. But that's not a problem either. We'll fix that easily. Now, we're going to
go up to Power Query. That's where we
imported Show the Pane, and come over here and
right-click, Refresh. You gotta be kidding me. Look at that. 7.7 million records. And this pivot table
updated right away, because we don't have any dates. Look at that, 2.6 billion. If we go over to
Percentage Change, you can see it has a
big blank down here. We need to fix our
calendar table. But no problem,
Control, down arrow. Remember, this is
an Excel table. I'm going to do
the same trick we did before, right-click fill
handle with my cross hair, or angry rabbit,
click and drag down 1. Bring it back. It says, Series. I'm going to take it. Columns, because I want
to fill down the columns. And the stop value is
going to be 12/31/2017. And when I click
Enter, just like that, Control, down arrow,
the table has expanded. Control, Home, Alt, Tab to
go back to the data model. This is one of the tables
we imported from Excel. So I come over to the table,
link tables, and I say, Updated Selected. And now it's updated. And when I go over and look
at my Percentage Change, just like that. That is just amazing. It totally updated. And it's running on over
7 million records there. If I wanted to add
a slicer-- and I'm going to go up to Analyze. It's filter group Slicer, and
I'm going to Add by Country. Click over on All. Here's our country. Click OK. I might change this
to four columns. And now I can click
on any one of these. And what's happening
when I click on Change the Criteria is
it's recalculating on the seven million records. I mean, that is just amazing. That's quite an epic video. We created one, two,
three, four final reports. We saw how to take tables,
country codes, and calendar table from Excel and get
them into the data model. We used Power Query to get an
Excel table and seven million rows of data from text files. And of course,
Manage Data Model. On the Fact Sales we, of
course, did our Diagram view to build relationships
between the fact table and our dimension,
or lookup tables. In Data view, we
have our four tables, various calculated columns. We built our measures,
or calculated fields. These two calculated
columns were used in the measures we had
down here in the measure grid. Over on D Calendar we had
some calculated columns. And we used these as criteria
in the Row/Column/Filter/Slicer area. Wow. That was amazing. If you want more
about PowerPivot, because this is kind of
just scratches the surface, I have a video playlist,
and Video 9 and 10 go a little bit further,
especially with the Calculate. But then, of course,
the books that I listed in the PowerPoints,
the reference books from Rousseau and
Ferrari, are a must. We'll see you next video.
