Welcome to the Highline Excel
2016 class video number 22. Hey, if you want to download
this file-- business 218-video22-start-- or the
finished file or the PDF file, click on the link
below the video. Hey, we have a great video here. We're going to talk
more about PowerPivot, and in specific, we
want to learn more about data modeling
and DAX formulas. Now let's go look
at our end result. First, I'm over here
in the finished file. We actually need to
calculate total revenue, total cost of goods
sold, and gross profit using DAX formulas. And over here we're going to
need to calculate gross profit percentage and gross
profit percentage compared to the same period last year. And we're going to do all those
calculations with DAX formulas. Now certainly we could
calculate total revenue just with a pivot table. And, in fact, earlier in
this class, we did that. But let's go look
at our data sets. When you download
your files, there will be a zip folder called
VIdeo22-ImportExcelFiles, and inside there'll
be two folders. I'm going to
double-click on Start. Here are the first three
files that we're going to import into the data model. Let's go look at one of them. This is from Flying Boom
Incorporated for the year 2014. We have date, product, revenue,
discount, net cost equivalent, country code, and units. So we do not have the actual
amount for the transaction, so it's actually something we're
going to have to calculate. Now you can see
through this column, sometimes we don't give a
discount on our retail price. And sometimes they do get a
discount on their retail price. Similarly, we're going to have
to calculate cost of goods sold, and we have
a standard cost and we're going to use
our net cost equivalent. Sometimes when we multiply
1.01 times the standard cost we get an amount bigger
than the standard cost. Sometimes when we have
0.98 and we multiply that by our standard cost
we will get a cost less than the standard cost. So those are a bunch of
different calculations we're going to have to
do with DAX formulas. Now let's look
down at the bottom of this data set control data. So here there's like
800,000 records. I'm going to close this. So we have three
different files here, and later we'll have
more files that we'll dump into this folder. And then we'll
update our data model and all the reports will update. Now let's go over
to the start file, because we still have a couple
more tables we need to look at. On dCountry we have a
lookup table-- country code and country. Remember, over in the
actual transaction data set we have country
code, but we want for our report to show country. Not only that, but
over on dProduct we have a lookup
table for our products where we have retail price,
standard cost, and category. So these tables will have to
get into the data model also. And, of course, as we've
seen earlier in the class, we will create relationships
between these lookup tables, or in databasing we call
them dimension tables and/or transaction
table, or in databasing we call them fact tables. Now before we start
importing all those tables and doing DAX formulas,
let's go to the PDFs and go to the section
what is data modeling. So data modeling is going
to first be importing data into PowerPivot data model
as proper data sets, either using Power Query when we
need to clean transform or when we have lots
of external files and we need to consolidate
them into a single table like we do in this example. And then we're going to
use the Add to Data Model button in the Power Pivot
Ribbon tab if the data is small and it's in Excel. Second, we're going to
create relationships between the dimension
tables and fact tables, or lookup tables and
transaction tables. Third, we'll create
our DAX formulas. We've seen how to create
a simple, sum-function DAX measure in earlier videos. In this video, we'll see
a few more DAX measures. We'll also learn how to create
calculated columns, which will really come in handy
for us in this example. Because if we have
millions of rows of data, since we can't have
that in an Excel sheet, we can't build a helper column
like for calculating revenue. So we'll get to see
calculated columns. And then step four
in data modeling, we're going to hide all tables
and fields that are not going to be used in our pivot tables. We'll still have access
to them, they just won't show up in our
pivot table field list. Than we'll create our pivot
tables and pivot charts. Then we'll refresh
our data model when the source data
changes, and then we edit the data
model as necessary. All right, let's
go back to Excel. All right, so we're
going to start this off. If we have a bunch of
Excel files to import, just as we saw last
video, we go to Data. Get entrance form, which is
in 2016 the group in the Data ribbon that represents
Power Query. Now we go over to New Query from
File and down to From Folder. All this should be familiar. We did this last video. I'm going to click on Browse. We're going to browse
down to the Start folder. Remember, there's three
Excel files in there. What we're telling Power Query
is to please import everything in that Start folder. I'm going to click OK. Click OK. Click Edit if you get
this intermediate step. Immediately I'm going to come
over and name this query. This name will be the
name of the query in case we need to edit it
later, but it will also be the name of the fact table
or our transaction table over in the data model. I'm going to call
it fTransactions. The f means fact,
and then Enter. Now, last video we had to
worry about filtering out different file
extensions, and we also had to extract certain
parts of the file name. But we don't need to do
any of that in this video. We're only going to have
.xls files and we do not need information from the file name. So I simply come over
to Content, right click Remove Other Columns. Now these are Excel files
in this column here. And because Excel files have
lots of different objects like we talked about
the last couple videos, we need to add a new column--
so go over to Add Columns, Add Custom Column. And in order to get
out all that data, we're going create a new
column called GetExcelData. Tab, and we're going to use
the Power Query function-- Excel.Workbook(. Remember, case sensitive
and it has to have that dot. Now we double click on Content. And now I want to promote
all of the headers in every single Excel table
to a single set of field names in our data model. So I type a comma, and
in the second argument for Excel.workbook, I in
lower case type true). Now I can click OK. Now we don't need content. Right click Remove this column. Now I'm going to click
the Expand button, and there's our
different objects we talked about the
last couple videos. I'm going to uncheck Use
original column name as prefix and click OK. Now last couple videos we had to
filter these to get just sheets or use the sheet name, but we
don't need to do that here. There's only going to be exactly
one sheet in each workbook. So I come to data. I'm going to click
on Data, right click Remove other columns. Now I can go to
the expand button, and there are our field
names from the tables. We see these here
because we used that True in the second argument
of Excel.workbook. Uncheck that, click OK. And just like that, we have
our field names promoted, and there's no rogue or
extra field names down below. Now we could click a
particular filter button on a field that has the fewest
number of unique records-- and I'm going to come down here. There's a lot of records here. I'm going to say
load more, because I want to see a unique list. And if I scroll down, I can
see there is no country code extra field name here. I'm going to click OK. Now, we very carefully need
to go through each column and notice it has
a little icon there that says I don't know
what data type this is. So we want to, on the Home
ribbon, go over to Transform and I want to click on Data
Type and point to Date. Now I can see a
little icon for date. Now I'm clicking on
Product I'm going to change this to
data type Text. And there it is. It has an ABC icon. Revenue discount-- this could
be anywhere from 0 to about 0.5 or 0.6% revenue discount. So these are definitely
data type decimal numbers. And there it is, the little
icon for decimal numbers. Net cost equivalent--
same data type. We're going to say
decimal number. Country code is Text. Units-- these are
definitely whole numbers. All right, so we have
all of our fields with the right data
type, all of our records. We need all of these
columns for our analysis, so we're not going to
eliminate one of them. We have the right name. Now I need to come up
to Close and Load To. And I select the second one
here to open up the dialog box. I'm only creating
a connection and I want to say Add this
to the Data Model. We definitely could not
dump this in a table. In the first three
files, there's about 2.7 million records. I'm going to click Load. And there it is. It took a while,
but there it is. 2.6-- about 2.7
million records loaded. This is our workbook query. We can come back and edit later. Of course, if it wasn't showing,
we could say show queries. Now we need to go
to the data model. I'm going to notice
that data is there and we have Manage Data Model,
so the keyboard is Alt, A. And that always gets
us to the Data ribbon. But notice it's D, M,
so that's an easy one to remember-- A
because it's data, D, M because it's Data Model. DM. All right, here's our
managed data model window. And you can see down
here, we definitely have all of the records. We could mess around with
the columns if we want. We don't really need to change
any of this, but you could. You could come up and
say hey, the format is a straight date
instead of date and time. Now we have our one table here. We need to go back over
to Excel-- Alt, Tab, and we go to the
PowerPivot ribbon tab. And we're going to use
the Add to Data model, but now I want to go
over to-- actually, I'm going to add
the dCountry first. Now I've already
converted this to a table and given it a smart
name, that way that'll be the name over
in our data model. All right, so going to
PowerPivot, Add to Data Model. And there it is. You can see a little link, which
means that linked to Excel. Let's go back over to Excel,
click on dProduct sheet, click in a single cell. This already has a smart name. There it is, dProduct. Product. PowerPivot, I'm going to
say Add to Data Model. And there it is. There's a link, dProduct. All right, now we have one last
dimension table or lookup table that we need to add. Now before we add
that dimension table, we want to come over here
to the PDFs, and here it is. Calendar table, dimension table. I want to click on this and talk
about what a calendar table is. Now a calendar table will
have a unique list of dates, and it will have things like
month name, fiscal quarter, fiscal year-- all
the different ways we might group sales numbers
based on certain date categories. Now, you might be
asking the question why do we need a calendar table? Why can't we just use the
Group by Date feature? Now we used that feature
earlier in this class, and actually, Group by Date is a
brand-new feature in Excel 2016 for the data model. It used to be that
we could not right click Group and Group by
Date in the data model, but you can do that now. Now the drawback is that
it only has a month, year, and a standard quarter. Well, one of the requirements
for our reports in this video is that we do fiscal quarter. So the group by date
feature will not work. That's where calendar
table comes in. Now calendar table
has a few advantages. Actually, the
biggest advantage is that we have access to time
intelligence DAX functions like same period last year. One of the calculations
we're going to make is we're going to see the
percentage change from one day period to another. And the beauty of
the same period last year DAX function--
which, by the way, is not in a normal pivot table
and it is not in an Excel spreadsheet-- this function
can look at any date, weather a day, a quarter, a fiscal
quarter, a year-- and it will automatically know to
go back to the previous year and get the previous amount. That way, we'll have a
function that we can always use to compare between
two periods from two different years. Another advantage,
of course, is that we can calculate fiscal
quarter using what's called a calculated column
in our calendar table for our fiscal quarter. And then also number three
for big data, any time you have dimension tables or
lookup tables and fact tables, the actual columnar
database and DAX formulas work most efficiently. Now, a few requirements
for our calendar table. You have to have-- the first
column in the calendar table has to have a unique
list of all the dates, from earliest to latest,
with no missing date. And then, of course, we'll
create our calculated columns and we'll calculate month
name, fiscal quarter, and fiscal year. All right, let's go
back over to Excel. And there's a sheet
called sheet 1. I'm going to double
click that sheet and can call it
dCalendar and Enter. Now in cell one I'm
going to type date, Control Enter, Control B, Enter. And I've already looked through
the data set, and the earliest date we have is 1/1/2014. Control Enter. Now I need to get
from this first date all the way to
12/31/2016, so we're going to learn an
amazing trick in Excel. Home ribbon-- I have a cell
with the first date selected. So home ribbon over
to Editing, Fill, and I'm going to click Series. Now this dialog box
shows up, but I'm going to show you a
faster way to get to that. Any time you have
your first date, you actually point
to the little fill handle in the lower,
right hand corner. And when you push your cursor--
when you see your crosshair or angry rabbit cursor--
right click and drag down one, and then back. And instantly a
secret menu pops up. Now I'm going to do that again. It's right click,
drag, drag back, and up pops our secret dropdown
with Series, so there we go. Now I want to fill this
series down the column, and the step value's going
to be one day at a time. And watch this. The stop value-- you just type
your final date-- 12/31/2016. Now it doesn't really
quite fit, but there it is. That date. When I click OK, instantly
I have the full column. If I Control Down
Arrow, you can see we have all the way to 12/31. Control Home-- I'm going
to convert this to a table, because if we're going to
import it into the data model from an Excel sheet it's
got to be an Excel table. Control T and Enter. We want to make
sure to name this. I come up here or use the
keyboard Alt, J, T, A, and I'm going to call
this dCalendar and Enter. Now why is it always required
that you have an Excel table whether you go into Power
Query or PowerPivot data model? Of course because
if this changes, this object is a table
and it's dynamic. When this changes later, the
data model won't recognize it. All right, now I click in
a single cell, PowerPivot, and Add to Data Model. And there it is. Link dCalendar. Any time we want to update
any of the Excel tables, we simply come to
link table and click that, which we will do later. All right, now we
have all four tables. Now we need to
build relationships. I'm going to go over
to diagram view. We're going to put the
fact table in the middle. I'm going to surround the
dimension tables or lookup tables around the edges. And actually, the
fact table is going to have some other
columns later. And here's the dCalendar. And actually, the
dCalendar will have a bunch of calculated columns later. But now let's build
our relationships. Contrary code gets
dragged to country code. And instantly we have a
one to many relationship. There's a single country code in
the first column of our lookup or dimension table. Over here there's many repeats. Now let's do the
same for product. There we go. We have many products
over here, right? Because we sold
many quad boomerangs and aspens and sunsets. But over here we
have a unique list in this lookup or
dimension table that will allow us to look up and
get retail price standard costs and category for
our DAX formulas. That'll be over in
the transaction table. And finally, let's link date. Lots repeat dates over here. We'll have a single
date, and we'll have things like fiscal
quarter, fiscal year over here. All right, so this is
part of our data modeling, building relationships. I'm actually going to
drag this up a little bit. We can go back to a data view. Step one is
importing the tables. Back to data view. Step two is building
relationships between the tables, dimension
or lookup tables and our factor transaction table. Now back to data view. Step three is our DAX formulas. Now we're going to
build DAX measures and DAX-calculated columns. We're going to start
with calculated columns in the dCalendar table. Now, the first column is
that unique list of dates. I am going to add some
formatting to this. Go to the Home, over to Format
and click on that Date Format. Now our first calculated column
is going to be month number. Then we'll do month name
and then fiscal quarter, fiscal year, and so on. Now to create a calculated
column in the dCalendar table, I'm simply going to double
click where it says Add Column, and I'm going to name it
month number and enter. Now similar to our an Excel
table formula nomenclature that we learned
earlier in the class, there's no cell
references in a column. We're going to
create a formula here and it will automatically
calculate down. And it will look the same
in every single cell. Now notice, when I
click in the first cell there's an equal sign. We actually have to
create all of our formulas up in the formula bar. Now we want month number,
and over here in DAX, there is a function that works
the same as over in Excel, the month function. It simply wants the date. Now I want to show
you something. I am allowed to click
on the first cell. But notice, it only puts in the
field name in square brackets. That is not going to
be our convention. We're going to reserve square
brackets without a table name only for measures. So I'm going to backspace
and I'm going to type it out. DC for dCalendar, Down Arrow. There is table name. And in square brackets,
the field name. Now notice, there's icons. That means function. That means the whole table. And that icon with the
little shaded column or field means the entire field. So there it is,
exactly like the syntax we used back in Excel tables. Now when I close
parentheses and hit Enter it auto populates down the column. In every single
cell, the formula will look exactly the same. We could actually scroll
down and notice it's working. When it sees June,
it delivers a 6. Now two different
types of DAX formulas. This is called a
calculated column. Well, how in the
world does it work? That is not a cell reference? How did that whole field
reference or column reference know to look at that
particular date? And as I go down, notice
when it gets down to here, it's looking at that date. In DAX, it's called row context. That means every one of
these calculated columns knows because it's in this
row to look at that date. Row context is how
calculated columns calculate. Now I'm going to
scroll all the way up. Now we already know that
because we've already used this exact syntax over in
Excel when we used the Excel table feature. But that's the technical
term, row context. Now let's calculate
our next column. Double click. I'm going to call
this simply month even though it's going
to be month name, because later we're going to use
this column in our field list in the pivot table but
we'll hide this one. So I'm going to hit Enter. Now, if I click on the top
cell, if we were in Excel we would use the
text function, we would click on
the entire column, and then we would use the
custom number format MMM. Well, guess what? Over here in DAX they didn't
can call this function text, they called it format. So format is going to do
the same thing as the text function. It wants the value which
it is the whole column, DC, Down Arrow to get table name and
field name in square brackets-- notice it says value-- Comma. And then the format,
just as over in Excel, custom number format has to
be in double quotes-- mmm. Now, if we put three
ms, it will show us three letter abbreviation. If I did four, it would
show me the full month name. But I'm going to do
three and double quote, close parentheses. Now when I enter,
there is our formula. Row context tells
this calculated column to calculate a different
item for each row or record in the table. Now, I want to build
a quick pivot table and show you that
there's a problem with this calculated column. I'm going to click Pivot
Table over in the data. And I'll just click OK. I'm not even going
to name the sheet, drag the field list
over, and dCalendar, I'm going to drag
month down to rows. And lo and behold, it's
sorting alphabetically. And that is not what we want. Now I'm going to come down to
the sheet, right click, Delete, and I'm going to delete it. Back over to our calendar
table-- no problem. Once you calculate month
name we can come up to the Sort and Filter group. Sort work by column-- actually,
I can just click the button, and watch this. You say sort column month--
that's this one with our text-- by which column? Month, number. The whole reason that we
calculated month number in the first place
was so that we have a hierarchy from 1 to
12 that we could later use to sort our month name column. So I click OK. Now notice, this is more
complicated than the group by date feature. But when we're doing advanced
calculations in the data model like fiscal
month, fiscal year, and we'll see some
other calculations, this is how we do it. Now if I create a
quick pivot table, click OK, you can see, sure
enough, calendar, month, and it's going to sort perfect. Right click Delete. All right, now
our next column is going to be year, so double
click Year and Enter. I see my equal sign up
here, and guess what? They named this DAX
function smartly. Year-- I say dc Down Arrow
to get the full date field, close parentheses and Enter. And there is the year. Now we're going to calculate
fiscal quarter and fiscal year in a couple steps. And the first step
is calculating the standard quarter. Now, back in video
number nine, we did all of these calculations
in an Excel spreadsheet. The same exact functions and
formula logic can be used here. All right, so for
quarter, I'm going to double click Add Column. And this is just standard
quarter and Enter. Now as we did back
in video number 9, we said equals-- well, I need
to take the month number. So DC, Down Arrow
to get month number, and I'm going to divide by 3. That was one of our tricks. Now notice, one
divided by 3 is 0.33. When we get down to the
second month, it's 0.666, and we get down to the
third month, it's 1. So what do we do? We rounded the calculation up. So up here in the
formula bar there's a round up in DAX, just as there
is over in Excel, and comma. Number of digits-- we round to
the integer with a 0 and Enter. And so that gives
us our quarter. If we look through
our table all the way through the third month,
it's quarter number 1. And, of course, when
it gets to 4, it is 2. Now we come over,
double click, and this will be a fiscal quarter. And enter. Well, our fiscal
quarter is defined as January, February,
March-- is the fourth quarter of the previous year. Now remember, back
in video number 9 we talked about why
fiscal quarters exist. Companies pick the
slowest time of the year to do financial statements
oftentimes associated with tax times. I'll give you another example. Here at Highland College, our
fiscal year starts July 1st. That's in summer. That's the slowest time
when they can do all of their financial statements. All right, so for our company,
the first three months are quarter four. So I'm going to use the
DAX if function, equals if. And our logical test is
going to be month number, so DC Down Arrow to month number
any time that is less than 4. That means 1, 2, 3, comma. Then for the value
of true-- wait a second, this is
the if DAX function. Over in Excel it
says value if true, here it says a result if true. Well, 1, 2, or 3 months
is the fourth quarter for last year, comma,
result if false, the standard quarter, which is
DC Down Arrow to get quarter, minus 1. That means quarter 2 would
be 1, quarter 3 will be 2. There we go. Closed parentheses and Enter. And so now we have
our fiscal quarters. If we scroll down
all of these are 4, because these months
need to be associated with the previous year's
financial statements. But down here, April
1st is the first day in our new fiscal year. Now we need to calculate
fiscal year and Enter. We can use similar
logic, equals if, DC, and if the month
number is less than 4, Then I need to take the
year-- so DC Down Arrow to get the year-- minus 1. So it's going back
to the previous year. Otherwise, result if
false DC this year for the calendar year,
closed parentheses and Enter. So now we can scroll down
and clearly see the 13, and then 14 is associated
with June, May, April, all the way to the
following year-- January, February, and March. Now we have one last column. I'm going to double click and
this will be a fiscal period. So this will be the
year-- the fiscal year-- and the fiscal quarter combined. So right here I'm going to
come up, DC, Down Arrow. And I to start with fiscal year. That way, these labels
will always sort correctly. And then I'm going to join it,
just as we would over in Excel. Double quote, Space, capital
Q and double quote, and I'm going to join it to
DC-- the fiscal quarter. And there is our text
formula over here in a calculated column. When I hit Enter, that will help
us get the quarters and years to sort in a pivot table. Now I can see I actually
want a dash here, so I'm going to come over
and edit this-- dash, Space and then Enter. And there we go. This is our calendar
table, with 1, 2, 3, 4, 5, 6-- 7 calculated columns. All right, now we have
one last calculated column we'd like to create,
and we need to go over to the fTransaction table. Now remember, we don't
have revenue, right? So we need to look
up the product. So this would be
[? fund ?] [? fly ?], and we're going to have to get
the price from the dProduct table-- retail price column has
the price we want to get back over on fTransaction. Then we need to multiply
whatever the price is, not times the discount, but
times 1 minus the discount, and then multiply it by units. Now, we're going to
do this two ways. We're going to do with
a calculated column and then a sum function to add
the calculated column, which we can then use in
our pivot table. And then I'll show you a
method where it's not required to use a calculated column. So we'll start with
calculated column. I'm gonna double click and
call this revenue-- and Enter. Now we come up to
our formula bar. Now we'd like to use VLOOKUP
to look up product name, right, and retrieve the price? There's no VLOOKUP
over here in DAX. Luckily, there is a
relationship between the fact table and our product table. So guess what? There's a relationship, so
we use the related function. And the cool thing
about this is it knows there's a relationship. So I simply type DP
for the product table and Arrow Down to a
retail price and Tab. Guess what? Related will do the rest. And how is it going to do it? Well remember, row
context will allow related to see each one
of the product names. And then because
there's a relationship it'll automatically know
to go to the other table and get retail price. When I close
parentheses and enter, there it is-- the retail
price for every product. Now we need to
multiply this times-- and I need revenue
discount, but not quite. I need-- in
parentheses-- 1 minus. And then ft to get to our fact
table, Down Arrow to revenue discount, and Tab. Close parentheses. When I hit Enter, that will
give me-- for this case there's no revenue discount,
but further down there are definitely some products
that have discounts. So there we have a net
price for one product. But now we need to finish this. Times and then units. So ft and then down
arrow to units. When I hit Enter,
there is the revenue for each row or transaction. Now I definitely
want to scroll down. And here is a transaction. Looks like 1.98 millionth
row here or transaction. Here, we need to round this. And just like over
in Excel here, in DAX we have a
round function, which we'll do the standard
rounding-- there's the number, we come to the end-- comma, and
we're rounding to the penny, so we put a2, close parentheses. And when I hit Enter, instantly
the whole 2.7 million rows gets our updated formula. All right, now we have
our calculated column. Now I want to come down here
and create a DAX measure that we can drop into the pivot table. Now, we have a couple ways
we can create DAX measures. Earlier in the class,
if we go over to Excel, we want to PowerPivot--
calculations, measures, and did New Measure--
and this dialog box is great. It's like a one-stop shopping. Tell it what the
table is the measure name a description of the
formula and then the number formatting but let's
learn an alternative for creating DAX measures. You simply choose whatever table
you want the measure to appear in over in the pivot
table, [INAUDIBLE] on choosing fTransaction,
click in a cell, come up to the formula bar. And you don't type
in equal sign. If you remember back to our
prerequisite class, Business 216, when we did access
formulas, in our query window we had to type the name of the
new field, a colon, and then our formula. So up here I'm going to type
total revenue and then a colon and then an equal
sign, and then S-U-M. So it's very important
that you have the colon and the
equal sign-- that's called an assignment operator--
to get this formula to work. Ft, and I'm going to arrow down
to my new calculated column, closed parentheses. Now one disadvantage of
creating it over here in the measured
grid is that we had to know to put a colon
and an equal sign. We didn't have to know that
over in the dialog box. Now when I hit Enter, you
could see it down here. We still have to add
number formatting. So now we can just use in the
Home ribbon formatting group, I can select dollar sign and
that adds the formatting. If we wanted to add a
description from the measure grid, we'd have to
right click Description. And now we could
type our description. DAX measure was some function
to add calculated column-- I'd better put an n there, and the
name of that calculated column is revenue. Click OK. Now, just to see that it
does the same exact thing, let's go back over to Excel. And if you ever need to edit and
you want to use the dialog box, you come over to measures
and then manage measures, and all of your measures
will be listed here. I can click on it and Edit. And sure enough, there it is. There's the table. There's the name. There's the description,
the formula, and there is the
number formatting. Click OK. Click Close. Alt Tab. Now, I want to show
you an alternative to creating a calculated
column and then a DAX measure. We're going to use something
called the sum x function. Now in order to calculate
each one of these revenues and then add them,
we actually had to go through each row in
this table using a row context and calculate each
individual amount. If we go down and use most
DAX functions for a measure, it can't do row context. DAX measures are good at filter
context, which means they see the criteria in a pivot table. But actually, there
are a few functions that can iterate
over an entire table and calculate each value for
each row or record in a table. They're called the x function. So we're going to use sum x. So I'm going to come up here. Actually, before we
do that, watch this. Here is really what
it's going to do. All we have to do
is tell the sum x the table that it
should iterate over and the formula
that we would have used in a calculated column. So watch this. I'm going to cheat. I'm going to come up here
and copy this entire thing without the equal sign,
Control C, Escape. And now I'm going
to come down here. Up in the formula bar we
have to type a name first. And I'm actually not going
to use this formula later on, but I still want to
know it's total revenue. I can't type total space
revenue because I already have that name down here. So I'm going to use total
revenue with no space. We still have to do our
assignment operator-- colon, equal sign, and then sum x. So if you don't want
to do the helper column we can use sum x to actually
calculate that same formula but down in the measure grid. We need to tell the sum x which
table to iterate over It's ft-- and notice I'm selecting
the entire table. There it is. So table, comma. And the expression--
that's the whole formula we would have used-- Control V--
if we had done a helper column. Now we're doing both of them
here just to illustrate, right? But that's it. That's how you get row
context down in a measure. Close parentheses and Enter. Now we can go up and apply a
number formatting, right click description, and I'm going
to add a description. DAX measure with [INAUDIBLE] to
iterate over the fTransaction table, row context, and
calculate the revenue formula row by row. And then I listed the
actual formula, click OK. If we drop these
into the pivot table they both give us
exactly the same answer. Now when would you do
a calculated column, and when would you do
a straight DAX measure like this with an x function? By the way, we use
sum x-- there's average x, max x, and a
number of other x functions that can iterate over tables. Well, for calculated
column, this would be stored in the
columnar our database. It'll take a unique list and
store it, and that uses RAM. That means when
you load the file, it's loaded into RAM,
random access memory. If we do a straight
sum x, the values are all created exactly
the same as if there was a calculated column here. But it's not stored in RAM. Those values are only generated
when we drop the DAX measure into the pivot table or the
criteria in the pivot table changes. That means the central
processing unit will calculate all of those values. Now for our small data sets,
even millions and millions of rows, it's not
going to really matter. When you get up into 50
million or 100 million rows, then you might have to figure
out which one is better-- storing the values in RAM, or
getting the central processing unit to calculate this when
you drop it into a pivot table. So really, it's your
preference on smaller data sets which one you'll use. Now, we actually have a
few other calculations we want to make. I want to calculate
total cost of goods sold. Now, I'm going to do this
one straight as a sum x. What we're going
to need to do is we're going to look
up the product, but we're going to
get standard cost. So there's the standard cost
right there for each product. Then we're going to multiply
it, not by 1 minus-- this was an actual discount. This is the net cost equivalent. So here the person paid
86 pennies for every $1, and then down further their
some net cost equivalents that are larger than 1, which
means you're paying more than the standard cost. We need to take that times
the price and then the units. All right, so I'm going
to come down here. And watch, if I can just click
down here, as soon as I start typing, I jump up to the formula
bar-- total cost of goods sold, colon, equal sign, sum x. I need to tell it which
table to iterate over. This is the FT transaction--
comma and the expression. Well, we know we're going
to have to round all these, so I'm going to put the round. And I know I'm going to have
to look up using related. And this is the dp Down
Arrow-- we want standard costs. So related is simply looking
up the standard cost, and because it's
sitting in the sum x, it'll iterate and get
every single standard cost for every product in this table. And I'm going to multiply it
times net cost equivalent ft Down Arrow to net cost
equivalent Tab, times ft and the units, Tab, comma. And now I need to round
it to number of digits to, close parentheses
on the round. And I'm watching
my screen tips just like I would over
in Excel, I see that's the whole expression. I wish that we could click
on this like we can in Excel and highlight everything,
but won't let us do that. Close parentheses and there is
my sum x that will iterate over this entire table. But down here in the measured
grid when I hit Enter, there's the total
cost of goods sold. Add some number formatting,
right click Description. DAX measure with
[INAUDIBLE] to iterate over the fTransaction
table row context and calculate cost of goods
sold formula row by row. All right, now we
have one last formula we're going to calculate. And actually, we're going
to calculate gross profit. And it's going to
be total revenue minus total cost of goods sold. And we'll get to see an
advantage of creating formulas over here in the
measure grid type. So the total gross
profit, colon, equal sign. And now I need to take one
measure minus another measure. And remember, our convention
is to only use square brackets. But watch this, I can simply
click on the cell with the DAX measure, and look at that--
it appears in square brackets, minus, and I'm going to
click on the other cell. That is pretty beautiful. Now when I hit Enter I'm
going to add some number formatting and a description. DAX measure for total gross
product calculated from DAX measures total revenue and total
cost of goods sold, click OK. All right, so step
three-- we started out over in the [INAUDIBLE]. We calculated a bunch of
DAX calculated columns in the calendar table-- that
in fTransaction transaction we calculated one DAX
calculated column and a number of measures, including learning
how to use sum x to iterate. Now step 4 is we want to go
over to diagram view and look. Now notice, we have a
bunch of new measures here. And I love these icons
which appear in Excel 2016 or later for our DAX measures. Now what we want to do here
is hide any fields or tables that we're going to not need
over in our pivot table. Now we need all the tables,
but let's just come over here. I'm never going to
use country code, so I'm going to select
that field, right click, Hide from Client Tool. Retail price, holding Control,
and selecting standard cost. Right click Hide
from Client Tool. You can see they're grayed out. That means they won't appear
over in our pivot table field list. Now, we're not going to need
any of the actual fields from the fTransaction table. So I'm going to click
on the first one, and before I click on the
last one, I hold Shift and then right click
Hide from Client Tools. Now I actually want to
hide this one, too-- this is a measure, right click,
Hide from Client Tool. Remember, we just looked
at how to do sum x, and then this one was our
sum of the calculate column, so that's the one
we're going to use. Now I come over here. I'm not going to
need month number, and I'm holding the Control key,
clicking on Year and Quarter, right click Hide
from Client Tools. So over here in
diagram view we can see hidden fields and
measures are gray. If we go back over to
data view, watch this. The columns are
actually grayed out. And if we go over
to fTransactions, all of these columns are hidden. And look at that, that
measure [? too. ?] That means we're ready to build
our pivot table and pivot chart reports. Now I'm over the data model. So I'm going to click on
the Pivot Table button. I wanted a new worksheet,
so I'm going to hit Enter. And now let's expose
all of the tables. And sure enough, there are
a bunch of hidden columns. Now, this is our hard work
of building a data model. We imported those tables,
built relationships, built DAX-calculated
columns and measures, and then showed only the
fields and tables that we want in our pivot table field list. Now we're going to create
a number of pivot tables. And the first pivot
table will be category. So I'm going to
click on Category and drop down in rows--
instantly I get my unique list. And now up here,
there's our measures. And by the way,
there's those icons that appear in Excel 2016, that
f of x means that's a measure. Now I'm going to
click, click, click, and sure enough, they get
thrown down to the values area, 1, 2, 3. And there are our DAX
measures-- total revenue, total cost of goods sold,
and total gross profit. That is amazing. Now notice, there's
that number formatting. We applied it over
there in the data model, and it shows up automatically
in our pivot table. Now what is a DAX measure doing? Remember, DAX measures are
based on filter contexts. That means this entire
field here, total revenue, is actually the same formula in
each cell in the pivot table. But notice, when I'm
in the cell right here, it sees the filter context,
which, in this case, is the criteria
in the row header. But that formula sees
whatever criteria we drop into the row,
column, filter, or slicer. Now, in particular what the DAX
measure does with the columnar database is in this cell right
here it sees competition. Now remember, we drag that from
the lookup or dimension table. So instantly it would go
down to the product table, and the product table would be
filtered only down to category. That means there's
one record here. That filter would carry over to
the transaction table and only the records for competition. That means when the
DAX formula calculates over this table in the
underlying columnar database, it's calculating over
a much smaller table only for the records
that contain category. That's one of the amazing
things about DAX measures columnar database
and our whole data model for how it can
calculate quickly on big data. Now we want to do a couple
things to this pivot table-- row labels. I'm going to go up to Design,
Report Layout, Show in Tabular. Immediately I'm going
to go over to View and uncheck the grid
lines, back to design, and I'm going to pick some
style, like this one down here. Now I want to create a
couple other pivot tables, and I think I'm going
to click in H 3. And instead of going
back to the data model to click the Pivot
Table button, I'm simply going to use
the keyboard Alt, N, V. And check this out--
because we have a data model that defaulted in 2016
is hey, dialog button is already selected for that. And it has the cell, so I
simply have to hit Enter. Now our next pivot
table is going to be based on
fiscal year, so I'm going to drag it down to rows. And for this pivot table,
we only want gross profit. So I send it down there. Design, Report Layout,
Show in Tabular, and we'll use the same style. Now we have one more pivot
table, maybe an L, 3, Alt, N, V, Enter. This one we're going
to show products, so I'm going to drag
it down to rose. And here I want all
three measures-- 1, 2, 3. Now for the time being,
I'm going to close that. Design, Report Layout,
Show in Tabular. Now one thing as I'm
looking through here, notice-- there's a bunch
of decibels showing and I don't want to see
pennies in a high-level report like this. So this is one great benefit
of using the data model and DAX measures. I'm going to go back
to the data model, and watch this-- I'm going
to highlight all four cells and simply go up and
decrease the decimals-- 1, 2. And now when I go
back to Excel-- Alt, Tab-- look at that. All of the DAX measures are
updated to show zero decimals. Now I actually want two slicers
for all three pivot tables. So I'm going to click in
whichever pivot table. Go up to Analyze,
Slicer, go over to All. I want to say fiscal
year, and I want country. Click OK. Now I'm going to mess with
these in just a moment, but for the time being,
I want to make one last-- not pivot table,
but a pivot chart based on fiscal year. So I'm going to click
in this pivot table. Insert Charts, and over to the
line, and maybe this one right here. You do not need the legend. I'm going to click
on it, Delete. I'm going to click on the
title and type fiscal year, gross profit, and Enter. Right click. Hide all Field buttons. I don't need this
axis here-- Delete. I'm going to come over to
green plus data labels. We'll fix that in a little bit. Now I want to name each
one of these pivot tables and then connect them
all to the slicer, so in turn, I'm going to
right click Pivot Table options for each one of these. This one will be
called category pivot, and I'm going to uncheck
auto fit, click OK. Right click Pivot Table
options, and this one will be called fiscal pivot,
uncheck auto fit, click OK. Right click Pivot
Table options, and this will be called product pivot,
uncheck autofit, click OK. Now I'm going to
come to the slicer. Right clinic, and I'm going to
point to report connections. And I'm going to check
all three pivot tables. Click OK. Right click Report Connect--
check all three, click OK. I'm going to insert a bunch
rows above these pivot tables. Right click Insert. Move our slicer or up here. Up under slicer options I'm
going to add 10 columns, change the width, right click. Slicer settings-- I don't
want to check hide items with no data. This slicer-- how
about 1, 2, 3 columns? Something like that wide. I want to fit it in between,
and I'm actually going to cover up that pivot table. I don't want to show
that pivot table. Move this chart up here. All right, now let's
see if this works. I'm going to click on Bahamas,
and everything is filtered. Unfilter everything
that I really want. 14 holding-- so I click on 14,
hold Shift, and click on 16. So I'm going to click on
the green plus data labels. There we go. There is our dashboard for now. Now, step 6, after
building our reports, is if the source data changes
we can refresh everything. So back over in
Windows Explorer. There is our folder. Remember, we have [INAUDIBLE]
data model pointing at start. So I'm going to double click
Add Later, copy both of those, Control C. And inside
Start I'm going to Control V. Now the data
model is looking at start and it has new files. So now I go back over here,
and I'm going to go to Data-- refresh all, or use the
keyboard Control Alt, F5. And now if we look at our
workbook query window here, we can see we now have
4.7 million records. That means it spans
from 2014 to 2018. Now if you look back over
here, there is a problem. And we did this on purpose just
to show that for our calendar table, if we Control, Down
Arrow, there it's 2016. But it really needs
to be 12/31/2018. So I'm going to do the
same trick we did earlier. Right click, drag
down, drag back, and there's our secret menu. I point to series. Now I want to fill
this down the column, so I click column,
one day at a time, and our stop value is
12/31/2018, and Enter. There it is, Control,
Down Arrow, all the way to December 31st, 2018. Control, Home-- now we can go
back over to our data model. And if we go to
our calendar table, it should have already updated. If it didn't, then you go
to Link Table and select Update Selected. If we go down to the bottom,
sure enough, it has updated. That is amazing. Now we can go back to our
pivot tables and sure enough, everything is updated. Now I can hold Shift
and click on 17. And there I have from 14
to 17 on my line chart, my pivot tables have updated,
and I can slice and dice as I would like. Now that's the six steps, right? We imported our tables,
built relationships, built formulas,
hid whatever fields we didn't want to know our
pivot table field list, built all of our
reports, and then updated when source
data changes. But we have a data
model, and let's say we want to create some
more DAX formulas. We can do that. We can come back over here. We're going to go
to fTransactions. Now we have two more
formulas that we want to be able to drop
into our pivot table. Now the first one, I simply
want to take whatever the total gross profit is and
divide it by total revenue to get gross profit percentage. But then I would like a second
calculation for gross profit that shows me for a particular
date period, the change from the last period. So, for example, if I have
fiscal period for 2015, I want to see what the
percentage change is from quarter 4 from 2014. All right, so we'll start
with gross profit percentage. I click in the cell, I
come up to the formula bar, type my label-- percent, gross
profit, colon, equal sign. Now I could just
take division, right? Gross profit divided
by total revenue? But instead, I'm going to use
a new DAX function divide. It takes the numerator
and the denominator, and I'm going to do
our little trick. Numerator is total gross profit,
and there it puts the measure in with our convention of
square brackets-- comma, the denominator, total revenue. Now if you leave the
third argument empty, if there's an error, it will
show nothing, close parentheses and Enter. Now this is going
to be a percentage, so I'm going to come up. And notice, here's the cell. I'm going to click on
this percentage button. Now this percentage
button over in Excel does percentage
with zero decimals. If I click this
in the data model, it does percentage
with two decimals. Now, our next calculation,
which actually is going to be percentage
change for gross profit-- we're actually going to
have to do it in two steps. And I need a formula, a DAX
measure that will always know to go back to
the previous period and get the actual amount so
I can use it to compare it to this period's gross profit. Well, the way we do that is
with the calculate function. But we're going to
call this gross profit same period last year,
colon, equal sign, and we're going to use the
amazing calculate function. Now, the calculate function,
you can put any expression there which is a DAX measure. And for us, it's going
to be square brackets. And I'm going to do
total gross profit. Because what I need to
do with calculate is I want total gross profit to
see the filter context, which means it will see the fiscal
period for this particular row in the pivot table,
comma, and then we add a filter which will
change the filter context. So even though total
gross profit and calculate will see whatever date period
sits in the row header, I'm going to change
the filter context by using-- you gotta be kidding
me-- the same period last year. And this function
works on dates only, so it says dates,
please give me, and you always put
in the date column-- that's that first column
in our calendar table. And together, same
period and last year, calculate, and
total gross profit. That whole formula will
always get the amount from the previous period. Now I'm going to close
parentheses and actually hit Enter, and we're going
to build a pivot table and prove that this
formula is doing what we hope it's going to do. I'm going to click on Pivot
Table-- New Worksheet is fine. So I hit Enter. Now we go to Calendar,
and I want fiscal period. There's all of our
fiscal periods. Now in a pivot table,
if I drag a measure and I'm going to drag
total gross profit down in values-- of course. Each one of these formulas sees
the criteria in the row header. So right now it
sees 2015 quarter 3, and that's the correct
amount for that quarter. But watch what happens when we
use calculate and same period last year acting on the
gross profit measure. Right here, there it is. Third quarter, 2015. That's the amount
for this quarter. But check that out-- 26 million. That's exactly same
period last year. There's the third
quarter for 2014. So that formula will
actually be used to compare, then, these two amounts to
get the percentage change. Now I'm going to right
click and delete this sheet. Back over to the data model. Now, there's a few ways we can
calculate percentage change. I'm going to take the end amount
divided by the begin amount minus 1. Now I'm going to come
up to the formula bar, and we're going to
call this-- I called it gross profit percentage
change, colon, equal sign, square
brackets-- and it's going to be total gross profit. So for any row in
the pivot table that would be the
current amount, and I'm going to divide
it by square bracket. And I'm going to arrow down to
get gross profit, same period last year. And then subtract 1. Enter. I'm going to add some
percentage number formatting, and I'm going to
do the same thing. I'm going to go look
at this because there's going to be a problem
with that formula and we'll have to figure
out how to fix it. So I'm going to create a
pivot table, new sheet, fiscal period, drag
total gross profit so we can see all of them. Gross profit, same period last
year, and percentage change. Now the formulas here
worked correctly. It took 8.5 million divided by
that amount, subtract the 1, and that was a big decrease. But look at up here. The reason we're
getting a num error is because for these periods,
there is no previous period. So we need a way to show
nothing in our pivot table, so I'm going to delete
this sheet again, go back to our data
model, and up here in the formula bar we're
going to use the if function-- equals if, and the logical test
is going to be square brackets, Down Arrow, if the gross
profit, same period last year, if you are equal
to zero-- that's going to be our logical test. Comma, result if true,
I want to show nothing if it's 0 last period. And we don't put double quotes,
we use the blank function in DAX, comma, and then
the result [INAUDIBLE] false is, there it's
going to run our formula. Come to the end, close
parentheses, and Enter. Now I only want the percent
gross profit and gross profit percentage change. I don't want this
one, so watch this. Right over in the
measured grid I'm going to right click
Hide from Client Tool. And there it will
no longer show up in our pivot table field list. Now I'm going to click
pivot table, Enter. And now with our
new DAX measures, there's our fiscal period. And all I want is total gross
profit, percentage growth profit, and there is our gross
profit percentage change. Go up to design, report layout,
show in tabular, and maybe a little style. So there it is, two
new DAX measures-- one for percentage gross profit
for this particular period, and then here is the
percentage change compared to the previous period. I'm going to come down
here, double click, and call this gross profit
analysis, and Enter. Wow, that out was an epic
video all about data modeling and building DAX formulas. Of course, we started
over in Power Query importing multiple Excel files. Then we went over
to our data model, imported, built relationships,
built a calculated column here in the transaction table, also
over in the dCalendar table, and then built a
bunch of measures, built our pivot
tables and reports. And when the source data
changed, everything refreshed. All right, we'll
see you next video.
