[MUSIC PLAYING] Welcome to E-DAB
video number eight-- Data Analysis and
Business Intelligence Made Easy with
Excel Power Tools. And in this video, we get
to deal with big data. We're going to import 7 million
rows of data from an SQL database using Power Query. And we'll load it to
the amazing Power Pivot. And for the first
time in this class, we'll get to build DAX formulas
to use in our pivot table reports. Now, in last video, we had data
that came from an Excel sheet. And we used the amazing
relationship button in the Data Ribbon tab,
which imported the data into the Power Pivot data
model and automatically created these relationships for us. Then we created our reports. But in this video, our data
is not coming from Excel. It's coming from
an SQL database. We'll use this
connector database from SQL server
database in Power Query. And we'll load it to Excel
Power Pivot's data model. Now, that data model has
a number of components. And behind the scenes, there's
something called a columnar database, which
can take big data and allows compression
of that big data. For example, we'll import
7 million rows of data into Excel. And the file size will
only be 25 megabytes. Now, step one in data
modeling and Power Pivot is loading the data to
the columnar database. But then we create
relationships. And we'll do this manually
inside of Power Pivot. Then to make the reporting
experience more friendly, we'll hide columns that we do
not need in the reporting area. Then we'll create our
explicit DAX measures to use in our pivot tables. After all the data
modeling is done, then we can create our
useful information reports, and visualizations. That means we're going to be
building a data model pivot table and a data
model pivot chart. You can download all the
files and follow along with the video. The zip folder, once
you download it, right-click Extract All. Now, here are all the files
that you can download. There is the start file we'll
use throughout the video. There's notes, a
summarization in PDF form. And even a few homework
problems for you to practice after watching the video. Now, here are the topics we're
going to cover in this video. But be sure to look
below each video. Now, this is Excel Basics 1. But below our video
if you look for that show more button
and click, there's a time hyperlink
table of contents. If you like what you
see, click that subscribe button and the bell icon. Now, anytime we're using
Power Pivot and DAX and relationships, we
want to make sure and use the correct terminology. We're using a data
model pivot table. It works differently than
a standard pivot table. And how is that good? Well, because we can create our
reports from seven million rows and create our own relationships
and custom formulas. Now, the reason that Microsoft
named this amazing tool that has columnar database
relationships and DAX formulas, the reason they
named it Power Pivot, because in the end result,
we're using a pivot table. So they wanted to take the
standard amazing pivot table user interface and
add three things-- big data, columnar
database, relationships, replace v lookup, DAX formulas,
more varied calculations, and number formatting. So when you hear the name,
Power Pivot, that pivot means the pivot
table user interface. That power means big data
relationships and DAX. Now, what are the differences
between a standard pivot table and a data model pivot table? Well, data source. Of course, we're coming
from an Excel sheet or connect to external when we
do standard for a data model pivot table. Of course, it's coming
from the data model. Reason to use data model? The data model offers more
sophistication, multiple tables relationships, and DAX. The calculations will
summarize values by show values as in calculated field or item. Those are all in a
standard pivot table. Data model-- well, yeah, we
could do summarized values by. But as we saw last
video, that will create an implicit measure. And that has some drawbacks. You can do show values as
in a data model pivot table. But DAX-- that's the real power. DAX formulas have
many more options than a standard pivot table. Number formatting-- well,
let's just cut to the chase. You can't add number
formatting to a formula in a standard pivot table. But a DAX formula? You just attach the number
formatting to the formula. You will spend less
time formatting. Can we reuse formulas? Not in a standard pivot table. You betcha. In a data model pivot table,
you can use that DAX formula over and over. Data size? Well, we're limited to 1
million rows in the Excel sheet. In a data model pivot table with
that amazing columnar database, we can have millions up to
even a billion rows of data. So data model just
can hold more data. Multiple tables-- not in
a standard pivot table-- of course, in a data
model pivot table. So the advantage-- we can
work from multiple tables. Relationships-- not in
a standard pivot table. But yeah. In a data model pivot
table, we, of course, can use relationships. We don't have to use as
many VLOOKUP functions. That's an advantage
to the data model. File size-- if you
have data in the sheet, and you make a
standard pivot table, the file size will be bigger. If you have that same data
loaded directly into the data model and build a data
model pivot table, file size is much smaller. So when you want that
smaller file size, the data model's the way to go. Now, as an example that DAX
formulas have many more options than a standard
pivot table, let's go over to the sheet
average daily revenue. This sales transaction table
has 11,000 rows of records. If our goal is to calculate
the average line item transactional sales amount for
each month, we can do that. We take our data, put it
into a standard pivot table. And our aggregate calculation
will be the average function. But if our goal is to calculate
the average daily sales amount for each month,
we can't do that directly inside a standard pivot table. We'd have to take
this data and create an intermediate table that
calculates the daily totals. Then from this
intermediate table, we could create a
standard pivot table where we use the calculation average. So we could use a
standard pivot table. But we have to create
an intermediate step. If we use DAX, we simply
use a formula like this. And it has no problem. From a transactional table and
a date dimension or date lookup table, calculating the
average daily revenue. Now, we'll see this
formula later in the video. Now, from the PDF
notes, I think of when to switch from a
standard pivot table over to a data
model pivot table. When any of these occur, we
have more than one table, data model. More than about
50,000 rows of data-- data model pivot table. Or we want DAX formulas,
because we could make more varied calculations. And although this
seems simple, we can attach number
formatting to a formula. Now, lots of you, who
have created pivot tables, know, if we use a sales
column over and over every single time, we have to
add number formatting-- not with DAX. And, of course, we can
use whatever formula we create with
DAX over and over. Now, what is an SQL database? Well, last video we
saw an Access database. And it's similar. An SQL database is a
relational database. That means there's
relationships between tables that uses SQL code-- that stands
for structured query language-- to either query or
communicate with the database. Now, the database we're going
to be getting tables from is called boomerangs. It's actually stored
on an Amazon server. That's the server name. And there's our
username and password. Now, if we were to go
look at the tables, they look similar to tables we
used a few videos ago-- date, website, product
quantity, revenue discount, net standard
cost, and country code. And we have two lookup
tables or dimension tables-- product and country. But we're over here in Excel. And Power Query can easily
connect to an SQL database and extract tables. We go up to data, over to
get and transform, get data from database. And there it is. The first option-- from
SQL server database. Click-- we have to enter the
server and the database name. Now, I click OK. Now, we want database. We have to enter
username and password. And the password has a capital
E-X-C-E-L, capital I, little s, capital F, little un,
and an exclamation point. And we can select either
the server or the database. We're going to
select the database. Now, we can connect. Now, we don't need an
encrypted connection, so I'm going to click OK. And just like when we connect
it to an Access database, we have a set of tables that
we can choose to import. I'm going to select
multiple items. We want dCountry,
dProduct, and fTransaction. Now, we're going to see
transform data, click. This opens up the
Power Query editor. And here on the left, we
can see all three tables. We want to check each table. These two columns are fine. And just like with our
Access, in this case, this is the one side--
this is the lookup table. So if we were to
click right here, that's every single transaction
that matches that country. We don't need this column-- right-click, Remove. There's the name. There's the steps. Those are good. fTransactions-- we don't
need transaction ID. And we don't need the
last two columns here. So we'll click on date,
slide over to country code, holding Shift, I click,
right-click Remove other columns, checking each
data type and column name. Everything's looking good. We have our steps,
our name, product. Everything's looking
good, right-click, Remove. Now, we want to come up to
Close and Load dropdown, Close and Load To, only
create a connection, add this to the data model. Our data would not
load into the sheet. Data model is the
only option for us-- click OK. Now, our queries pane shows
us that we used Power Query to load 1, 2, 3 different
tables to the data model. We can check that by hovering. And on the left, it
says, load status-- load it to the data model. Now, we have two
different buttons we can use to get
to the data model-- Data Ribbon tab, Data
Tools, that button there, or Power Pivot data model. Now, I'm going to click
this Manage Data Model button to open up the Power
Pivot for Excel window. Now, down here, notice
we have five tables-- the first two tables or for
a data model that already existed in this workbook. The tables we imported are
dCountry, fTransactions, and dProduct. Notice each time we click
on a particular table, it tells us how
many rows there are. Now, up here in the View
group, we're in Data View. That means we're
looking at the tables. This is the fTransactions table. I can pull this bar up. Below each table, this is
called the measure grid. This is where we create our
formulas called measures that we use inside the
pivot table values area. Now, last video, we learned how
to create an implicit measure, and we showed it in
the measure grid. We did that by
Advanced, Show Implicit. And, in fact, we should have
this on all the time in case we drag and drop a field
into the pivot table. We want to see it visually here. We'll come back here
later and create our first explicit measure. Over in the measure
grid in our Data View, we can also create
calculated columns, which we'll also learn how to do. Now, we want to go over
and look at Diagram View and look at that icon-- tables with relationships. So I'm going to
click Diagram View. Now, this is a data
model that already existed inside this Excel
workbook-- fSales and dDate. Here's our three tables we
imported from our SQL database. So step one was to import
these tables into the columnar database. Step two-- we're going to
build our relationships. Now, this is the
fTransaction table. This is the many side. For this dProduct,
not only do we want to use category and
product in the pivot table row and column area,
but we also want to be able to look up
the price and the cost. We can do that
using relationships. So here's the one side. I click and drag over
to the many side-- a one to many
relationship is created. Now, we did this last video
using the relationship dialog box over in Excel. Either way you do it,
it does the same thing. Now, we have a relationship
stored in our data model. Now, we do the same
thing for country. The one side, the primary key,
I drag over to the foreign key, the many side. So we've created
our relationships. The next thing we need to
do in our data modeling, is we actually need to
create a lookup or dimension table for our date column. Over here, we can
have many sales on any one particular day. But we need a lookup
table that will have a first column
with a unique list of every single day that's
possible in the fTransaction table. And then we're going to
add attribute columns, like year and month. Now, over in a standard pivot
table, we can group by date. And that grouping by date
happens in the pivot table cache. In the data model, we
do not want to do that. We want to explicitly
create a date lookup table. And if we're going to
have year and month, we add those as extra columns. Now, in Power Pivot, it's easy. Let's go back over to Data View. Here's fTransaction. I'm in this table. We go up to Design. And you're not going to believe
it-- calendars, date table-- we can automatically create a
date table here in Power Pivot. So I click New. And look at that-- it actually went through the
entire fTransaction table. It found the minimum date
and the maximum date. And it made sure
over here in calendar that it listed exactly one
day for every possible year over in the fTransaction table. Now, the reason
that it did that has to do with internally
in the data model how it makes date calculations. Now, there are a bunch
of functions called time intelligence DAX functions. We'll get to see one of them
called TOTALYEAR-TO-DATE. But unless you have a date table
with all the possible days, many of those
functions do not work. So when we're in
Power Pivot, if we're going to make calculations
involving dates, we use the Date Table, New. Now, a couple things here-- we are not going to need
a bunch of these columns. We're only going to
use year and month. Now, we could leave these here. We could also delete. I'm going to select the
first one-- hold Shift, click on the last one, right-click,
Delete, click Yes. And our columns are deleted. Now, when we clicked Date Table,
New, something else automatic happened. Now, when you're over in a
standard pivot table, when you use months in a
pivot table, well, it sorts perfectly from
January to December. That has to do with internally
inside of Excel a custom list. But over here in the data
model, there is no custom list. So how are we going
to get these to sort by calendar month instead
of alphabetically? Well, here's how it happens. And this automatic date
table did it automatically. If we were to do it manually,
we'd have to go to Home, sort by column, sort by column. And we'd have to say,
please sort this month column by month number. That's why we have to have
month number, because one to 12 will always sort correctly
from January to December. So when we say, hey,
month, sort by this, we get January to December. Now, again, this was
done automatically. But if you ever create a
date table from scratch, you got to do this
step-- click Cancel. Now, the next thing is we want
to come down and rename this. I'm going to get rid
of the one and enter. Most date or calendar tables are
either called calendar or date. All right, this is
our calendar table for our 7 million rows
fTransaction table. Now, let's go over
to Diagram View. Oh, look, the
calendar is over here. I'm going to move it. One side-- primary key. I click and drag over to the
many side, the foreign key. And there we have our
one to many relationship. We can drag and drop month or
year into the row or column area of a pivot table. And then the DAX
measures or formulas we're about to create--
we can drag those into the Values area. And we'll get our calculations
based on month and year. All right, so that's step one-- importing from an SQL database
and creating our date table, and step two-- creating
our relationships. The next step is that we want
to hide some of the columns that we are not going to
use over in the pivot table. For example, the
only column we're going to use from dCountry
is the country name. I don't even want country
code to be an option over in the pivot
table field list. So I right-click,
Hide from Client Tool. We're not going to need
retail price, holding Shift, or standard cost in our
pivot table field list. We are going to use
them in a calculation. So I right click,
Hide from Client Tool. When I hide from
Client Tool, that just means it's not going to
show up in the field list. I can still use them
in calculations. Over in the date, we
don't need month number. That's just for an
internal calculation here, hide from client tool. Now, we'll come back and
hide some columns here after we create
our DAX formulas. But now, let's go
over to Data View, find our fTransaction table,
pull this up if you need to. we have a choice. We can either create formulas
down in the measure grid. Or-- sometimes we want to
create calculated columns. Now, actually, we already
have a calculator column in our data model. If we go back over into
calendar, if I click up here-- oh, look at that. In Excel, we call
this a helper column. Over here, they call
them calculated columns. But there's the year. There's the month. And that's weird. Over in Excel, we'd use the
text function to format a date and show it as month name. But over here,
it's called FORMAT. That is a DAX
function called FORMAT that does the same thing as the
text function over in Excel. So now, back over
to fTransactions. Now, we want to build
our first DAX formulas. There are two types of
DAX formulas-- measures and calculated columns. Measures will build down in the
measure grid like total revenue and use those in the
pivot table values area. And the second type
of DAX formula-- a calculated column. For example, calculating
line item revenue or back in our
calendar table when we created an attribute column
back over in fTransactions. Now, what we want to do now
is create a calculated column to calculate line item revenue. And then we'll create a
measure that adds the line item revenue to get total revenue. That measure will be
used in the pivot table. That's a two-step process-- calculate a column,
then use a measure to add the results from
that calculated column. The second way to accomplish
something like total revenue is to go straight to a measure. That means we can
do both steps-- calculate line item
revenue, iterating over this entire table,
inside the measure, and then add all those results. So we'll look at the
two-step process first. Now, how do we calculate revenue
for this particular data set? Well, we have the quantity. And you can actually
scroll down. This is not a data set. This is a preview of what
is in the columnar database. But you can scroll down. And you see there's
lots of records where we have different units. There's also a revenue discount. What we're missing in this
table to calculate our line item revenue is over in dProduct. We need to get retail price. Now, this is a typical lookup. We have an exact match
situation where we have product. Over in fTransactions, we
actually have the product name. So for the third
part of our formula, we're going to use
this as a lookup value and look up the price. Now, let's double-click
where it says, Add Column. We're going to call this
line revenue and enter. And in DAX, we can't
create our formulas, either measures, or calculated
columns in the cells here. You actually have to come
up to the formula bar. Now, the very first thing
we're going to learn here is how to do VLOOKUP. Now, there's no
VLOOKUP function. And because there's a
relationship between fact table and that dProduct table, the
name of the VLOOKUP function is called related because
there's a relationship. Now, it's much
easier than VLOOKUP, because the only thing we
need is the actual name of the column that has the item
we want to go and get and bring back to this fact table. So I can click with my
mouse, or I can down arrow to dProduct
retail price and Tab. Now, how this works is simple. Because there's a
relationship on the many side between product and over
to product on the one side, for each row, it
automatically knows to look at the product,
which is our lookup value. Then because there's
a relationship, it knows where the
lookup table is. So the only thing
we have to give it is the column that has the
thing we want to go and get-- so close parentheses. And let's just
hit Enter and look at how cool this is in DAX. There it is related. It's the same formula
all the way down. We could scroll
to see a preview. And sure enough, it
got the right price for every single row. Now, there's actually
a special name for how a DAX formula
calculates in a table like this. It's called row context. And it's similar to
the way the Excel table feature works in Excel. For each row, it automatically
can see any of these items. All right, let's
continue our formula. That's the price times the unit. Now, watch this-- I can actually
click on Quantity, which is our number of units. And in earlier versions
of Power Pivot, it just put the column name in. That is incorrect. Anytime you refer to a
full column from any table, you have to put the
table name, and then in square brackets,
the field name. You can see dProduct-- that's the table name. Square brackets-- that's
the column or field name. So that's the syntax
and the convention for referring to columns. Now, if I hit Enter here, this
will give me the full revenue-- units times price without
the revenue discount. So we need to use this column. So I come up to the
formula bar times-- this is the amount taken away. For example, 17.1 pennies
and discount for every $1. So we have to use,
in parentheses, 1 minus, no revenue discount,
close parentheses and enter. Now, if we scroll down
a bit, oh, there's lots of extraneous decimals. And we're going to add all of
these results down in a measure so we want to round. And luckily, it's the same
name as the Excel function. I come to the end-- comma, two,
close parentheses and enter. And there, we can see
everything is rounded. So that's a calculated column. It iterates for
every single row, using row context to
get whatever elements it needs from that particular row
and makes all the calculations for line revenue. Now, let's come down and
create our first measure. We want to add that column. Now, watch what happens when
I start typing the name. It shoots me up to
the formula bar. TotalRevenue without a
space, colon, equal sign-- whoa-- that's different
than our calculator column. The calculated column
only used an equal sign. But in Power Pivot when
you create a measure, you have to put a
colon and an equal sign before you type the formula. Sum-- and then we want line
revenue, close parentheses. Now, it's important,
the colon equal sign, everything to the left. That's the name of the measure. That's what will show up in
the pivot table field list. Everything to the right,
that's our formula. I'm going to hit Enter. There's our answer. I'm going to come up. And in the formatting group,
I'm going to select some number formatting. I'm going to select
United States. That number formatting
follows the formula around. So when I drag total revenue
from the pivot table field list into the Values area,
it will be formatted. Now, before we
create a pivot table and see how magic
measures can be, let's create our second method-- that is one formula that does
both steps in a single measure. Now, there's a
couple things that make what we're about
to do totally amazing. Now, in Excel,
it's almost always more efficient to create
a helper column like this and then use the Sum function. However, we could simulate the
helper column inside a formula. But that would be
an array formula. And array formulas are
calculation-intensive. They take a long
time to calculate. But in DAX, they
created a method that we can use that is not only
fast, but it works on big data. Now, think about
what we did here. We iterated row by row over
the fTransaction table. So in our measure
that we're going to create to
simulate both steps, we're going the need the
table to iterate over. And we're going to need the
exact same formula that we just created. So what's this--
I'm going to cheat. I'm going to come up to the
formula bar and copy everything except for the equal sign-- Control C, Escape. And we're iterating. And then what did we do? We summed. So the name of the function that
simulates both steps is SUMX. The SUM is because we're adding. The X is because
we're iterating. So I type the name-- colon equals sign. And here's this
amazing function-- SUMX. And look at that-- it just
needs a table and an expression. Now, expression is a
synonym for formula. But remember what
we did over here-- we had a table. We had a formula. So in the first argument
of SUMX, fTransaction-- that's the table, comma. And in expressions, that's our
formula, Control V. Now, SUMX-- well, it will take this
formula, iterate down every row, calculate all 7 million
intermediate values. And then the sum is the
aggregate calculation. There are other
iterator functions, for example, AVERAGEX. The only difference is it'll
take those values in average. So that is our epic iterator,
which is like an array formula over in Excel. But when I hit Enter,
much more efficient. Now, let's add our
number formatting. That number formatting will
follow that formula around. Now, there is a difference--
calculated column, sum. That's two steps. When we do a calculated
column, these values are stored in the
underlying data model. That means when you
load the Excel file for the first time and,
thus, the database, or you click
Refresh, that's when these values are calculated. Inside our SUMX, the
values aren't calculated until we drag and drop
this into a pivot table, or we change the conditions or
criteria in the pivot table. The rule of thumb is this-- we're going to be using measures
rather than intermediate step calculated columns,
unless you're having trouble with the
measure calculating slowly in the pivot table. Think about this-- if you
were changing a slice or a row area of condition, and the
formula went really slow, that wouldn't be a
good user experience. So in that case, maybe you
want to store the values in a calculated column
and use straight sum. Now, the great Marco Russo
and Alberto Ferrari-- the smartest guys
I know about DAX-- they said, hey, look, anything
under 100 million rows, in general, you can
choose whichever method you feel more comfortable with. Now, we have two
measures, and we'll come back and create some more. If we go over to
Diagram View, now, I want to highlight
from date, hold Shift, all the way to
line revenue, right-click, Hide from client tool. So step one in data modeling--
import all of our tables. Step two-- relationships,
step three-- hide the columns we don't
need in Report View. Step four-- create our measures. Now, let's create a pivot table. And we can create a
pivot table over here in the Power Pivot for Excel
window by clicking this button. When we click pivot table,
it keeps this window open. But it jumps us over to Excel. So I'm going to
click pivot table. On a new worksheet,
that's perfect, click OK. First thing I'm going to do is
name this sheet, Three Pivot Tables and enter. Now, we need to move the
correct tables over to active. So dProduct, right-click,
Show inactive. fSales and dDate are
from the other data model, right-click, Show
inactive, right-click, Show inactive. Now, when we go over to active,
notice that the fTransaction has a sigma. That's because we hid
all of the columns and list only our measures. That's an efficient way to
structure our fact table, because we really only want to
pull conditions and criteria from our dimension
or lookup tables. Now, our first pivot table
will be year down to rows. And watch this-- I love this--
total revenue down to values. Look at that number formatting. Now, I want to click a few rows
below, go to Insert pivot table or use our keyboard,
Alt, n, v. Now, notice we opened Create
pivot table dialog box from over in Excel, because
we were in an empty cell, and we have something
in the data model, it defaults to the
data model, click OK. Now, wait a second. I don't want to have to move
these tables over again. So I would like to
go hide these tables. And I'd like to hide that
measure because I'm not going to use it. Alt Tab over here. And Diagram View, right-click,
Hide from Client Tool, right-click, Hide
from Client Tool. Over here, right-click,
Hide from Client Tool. Alt tab-- I'm going
to open product. Product down to rows. My measure down to values. And it calculates. And there's that
number formatting. Off to the side, Alt N, V,
enter country down to rows and are measure
for total revenue. And there is that
number formatting. Now, I want to talk about
how a measure is calculated. Now, we just talked
about calculated columns, and how they use row context. A measure uses filter context. That just means that
whatever conditions are in the row, column, filter,
or slicer, they actually flow into the measure and
filter the underlying table. Remember that fTransaction
table has 7 million rows. But when a condition
for any particular row-- Carlota, Eagle,
this quad product-- the beauty of how DAX
measures calculate is when that condition from
the row flows into the measure, it filters the fact
table, 7 million rows down to just the rows for the quad. In that way, the DAX measures
are more efficient when they have to calculate on big data. We can try to
visualize it this way. Here's our measure. Here's the fTransaction table. When it gets to the
quad row, we can see that the quad is
in the product table. We can see there's a
one-to-many relationship. We can see in the
fact table there are many quad transactions. When the quad product filters
the product table down to one row, that filter flows
across the relationship. And when it hits the fact
table, the fact table is filtered down just
to the records for quad. In that way, the
measure then calculates on a much smaller table,
making DAX formulas more efficient on big data. Now, back here in the Power
Pivot for Excel window. In the measure grid, we have
four more measures to create. We created total revenue. But now we need total
cost of goods sold. That's the expense associated
with the products-- colon, equal sign. And we're going to use SUMX. And we want to iterate
for every transaction over the fTransaction
table, comma. And we're going to type
our expression out. We don't have the column for
line item cost of goods sold. We're going to use round. The first thing
we're going to do is look up with
related d product and we don't want retail price. We want standard cost. Cost Close parentheses. Multiply that, and I
can click on Quantity, fTransaction quantity times-- and in this case, we
have net standard cost. It's not the actual discount. It's the number of pennies
for every dollar of cost. So I can simply multiply
it by that column. Inside of number, we
have our calculation-- karma too because we're
rounding to the penny close parentheses that whole
round is the expression. That whole round will
iterate across every row in the fTransactions. But now, it will be calculating
line item cost of goods sold. And then of course,
the sum will add. So when I hit Enter, that's
the grand overall total for cost of goods sold. We'll add our number formatting. Now, we need gross profit. And guess what. We're going to use both of these
measures in our third measure. Total gross profit,
colon, equals sign. And I can simply click
in the measure grid. And look at that. It uses the correct
syntax for using measures in other measures. We use square brackets
only around a measure. We do not put a table
name in front of it. That's reserved for columns. Then subtract. And we click on total
cost of goods sold. When I hit Enter and add
some number formatting, we have our grand
total gross profit. Now, we would like
percentage gross profit. So down below, we'll say gross
profit, percentage, colon, equal sign. And I need to divide. So I'm going to use the
divide DAX function. The numerator, we type of square
bracket and then down arrow to get total gross
profit, comma. And we need to in
the denominator compare that to total revenue. Square bracket, and there's
total revenue, SUMX. Now, I could type a
comma and indicate what should be displayed if
there is a divide by 0 error. If I leave it empty,
it will show a blank, close parentheses, and enter. Now, we want to add some
percentage number formatting. I'm going to decrease
to show two decimals. Now, we have two more
measures we want to create. But let's create a pivot table
that shows these calculations. I'm going to go up to pivot
table and click New worksheet, click OK. Now, let's look at our measures. Here they are. From calendar, year down to
rows, month down to rows. And we want three measures-- total revenue, cost of goods
sold, and our percentage. Alt Tab-- I'm going to
change the name here-- Gross Profit Percentage
and Enter, Alt Tab. Now, let's add a
Slicer, Analyze-- Filter, Slicer. And I want to see
year as a Slicer, click OK, right-click,
Slicer settings, hide items with no data, click OK. Now, we'll move this, click
inside of our pivot table. And I want a chart, so Insert. We want a line chart, click. We can move it and
resize it, right-click, hide all field buttons,
select the legend, Control 1, put it at the top. Now, wait a second. I'm going to select percentages. And, of course,
they are so small. They're all being
plotted on this axis. And I want to plot this
on the secondary axis. That is beautiful. Let's select these numbers, come
over to series down to number. And we're going to use some
custom number formatting. Down here in format-- dollar sign and a 0. And when we type comma, that's
like dividing by 1,000, comma, that's dividing by a million. Then in double quotes,
I'm going to put an m. That way when I
click Add, now, I can see the amounts in millions. Now, next video in
Power BI Desktop, we'll see that, actually,
this kind of formatting is automatic. We don't have to do custom
number formatting like that. Now, I can select 2014, 2015. That is absolutely
amazing clear filter. One last touch-- let's come
up to the top, right-click row 1, Insert. I'm going to put a title. I added a title with
some formatting. Now, let's turn
off the grid lines. And there we have r-4. That is beautiful. All done on 7 million rows
with DAX formulas and the data model. Now, we have two more
measures we want to see-- Alt Tab. And in the measure
grid, we're going to calculate Running Total-- colon, equal sign. And Running Total just
means January to December. I want a cumulative
or running total. There is a built-in function,
total month-to-date, quarter-to-date, and
we want year-to-date. And this function
needs an expression while we're trying to
get a running total for square bracket, the
total revenue, SUMX. And this is one of
the time intelligence functions, total year-to-date,
that works with the date table. So you have to put the
calendar date column. And then total year-to-date does
the rest, close parentheses, and enter. I'm going to add some
number formatting. And now, let's click PivotTable. From the calendar, I
want year and month. Let's do Total
Revenue, Running Total. And that's a running total. That means when we get
to March, it actually added all revenues up to the
last day in March, Alt Tab. And the measure we
want to create-- Average Daily Revenue--
colon, equal sign. And instead of SUMX, we're
going to use AVERAGEX. And here's the cool trick. We saw average daily
revenue in the Excel sheet at the beginning of the video. And we couldn't do it
from a transaction table. We had to list every
single daily date and then add the total. Well, all we have to do in
DAX is put the right table with the right size
or granularity. And the table we want
is the calendar table, because remember, the
first column of that table has a unique list of all the
possible dates from the fact table. So when our formula iterates,
it sees all of the days, comma. And our measure--
square bracket. And it's going to be
our total revenue. That means that measure we'll
iterate over that table-- first day, second
day, third day, calculating the
total daily sales. For every single day, that whole
array of daily sales values will then be used in an
average calculation-- close parentheses, and enter. We'll add some
number formatting. So that's a lot easier
than the Excel example we saw at the
beginning of the video. Alt Tab-- now, we can drag
Average Daily Revenue. And the beautiful thing about
that measure is right here, it sees January. So it only calculated the total
sales amount for every day in January. And then it calculated
the average. When it got to 2014, it actually
saw every single day in 2014. So the measure has the sales
total for all the days in 2014. And then it calculates
the average. And in the grand total sell, it
actually sees every single date for all of the years, calculates
the individual total sales amount, and then
calculates an average. That is a pretty
amazing DAX calculation. Wow, that was an epic video
all about Power Pivot. We saw how to take 7 million
rows from an SQL database, and then use Power Query to
import Clean and Transform, then load it to the
columnar database, create a calendar table,
create our relationships, hide all the columns
and other elements we didn't want to see
in the reporting view. And then we created
our measures. And the end result-- data model pivot tables and
data model pivot charts. All right, if you
liked that video, be sure to click that
thumbs up, leave a comment, and subscribe,
because there's always lots more videos to
come from ExcelIsFun, including E-DAB number nine. We'll learn how to
use Power BI Desktop. All right, we'll
see you next video. [MUSIC PLAYING]
