Welcome to Highline BI348,
class video number 41. If you want to download this
workbook, BI348 chapter 2.75 example one start, click on
the link below the video. In last video,
video number 40, we had an introduction to
PowerPivot in the Data Model. But here I just
want to show you how to get tables into PowerPivot
build a few formulas and make a pivot table
using Excel 2016. Now as we saw in the last video
we have these related tables and we need to get them
into PowerPivot Data Model, build relationships
and formulas. But in order to get data from an
Excel workbook into PowerPivot, you have to create an
official Excel table. So I'm going to click
Control-T for table and Enter. then I can go up to Design,
and up here in Properties and name it. I'm going to use convention
f for fact, and then Sales and enter Now I can click
on each one of these, and here it is. PowerPivot It actually doesn't look much
different except for that. Look at that. Here they changed
the button in 2013. DAX formulas that you
dropped into the Values area of the Pivot table were
called Calculated Fields. Now in earlier versions
they were called Measures. Then they changed it in
2013 to Calculated Fields. And now it's back to Measures. All right, so there it is. But this is the button-- I
want to have a single cell. So I click add to Data Model. And this is looking
the same here. This is the manage
data model window. There it is. That little link
means it's linked back to that Excel table look up. The table name dProducts. We don't need to mess around
like we did in last video changing column widths
or adding number formatting because it won't have
any effect on the final report, the pivot table that we create. All right, I'm going to use
Alt-Tab to jump back over. I click in a single
cell in the second table and click Add to Data Model. There is our second table. Alt-Tab Click in the Fact Table. Add to Data Model. Now there's our 1,2,3 tables. Before we add in a
calculated column-- and I'm going to pull this
up-- and then our Measure or Calculated field down
in the measure grid, we need to go up
to Diagram field. And there's our table. Looks slightly different. It's only the colors, right? Here's our fact table. Here's our dimension tables. This is a lookup table
in the Fact Sales table. If we're going to build a
forum that can look up price, there has to be a relationship. And its product, from the
lookup table or dimension table, we drag it over and we
drag it on top of product, in the Fact Sales table. This is a one to
many relationship and that's different. It has a one here and
the little asterisks. Remember the one means
there's only one here because the Products column
or field is the first column in this lookup table. And it has a unique list. Same thing over here. It has a unique list in
the first column Sales Rep. So we drag it over to
the Sales Rep field. But of course, Sales Rep
can have many transactions with the same sales rep. Same with this
product over here. Single product in a unique
list but many products in the transaction table. All right, so we can
Control-S to save it. And go back to data view. Now we can create our
calculated column. And we're going to
need to get price for each one of the products. Multiply it times 1 minus the
discount rate and times unit. So we double click the top. And I'm going to call
this Net Revenue. And this will calculate the
revenue for each transaction. I click on the top
cell, equal sign shoots me up to the formula bar. And Related is the look
up function we use. And that's pretty cool. That's different
than Excel 2013. This drop down didn't appear. Now we need to look
up and get the price. I simply double click
or hit Tab, table name and the square brackets
for our field name. I close parentheses. And of course, when
I hit Enter, it'll self populate all the way
down, giving me the price. Now as we talked
about last video, that formula is the
same all the way down. And it works off of row context. Since the formula is
in this row right here, it knows to look up Carlotta
in the Products table and get the price because
of the relationship. For every row, the row context
says hey, go get Carlotta and bring back the price. Now I click on the top cell. I want to come up and edit
this formula because we need to take the price times
in parentheses, 1 minus-- and remember when we click on
discounts that doesn't follow our proper convention. Our convention is always going
to be we have the actual table name. And I'm going to hit Tab and the
field name in square brackets. The only time we use square
brackets by themselves without a table
name is when we use measures or calculated
fields in other formulas. And these are columns, so we
use our convention table name and field name. And now we go times
FSales down, down, down, until we get to units
and Tab and Enter. We need to round this so we
can use the Round function. Just like it is over in Excel,
we come to the end, comma two to round to the penny,
close parentheses and Enter. So there is our
calculated column. Unlike last video,
we are not going to add our number formatting. If we were to drop that
field into a pivot table, yes it would work, but it would
be an implicit calculation. It's not taking advantage
of the speed of DAX formulas to calculate on big data. So what do we do? We come down and create
an explicit formula. This is going to be
called a Measure. That's the word
they use in 2016. Measure or a calculated field. What do we do? We type the name
of the new measure. And it's going to be Total. And as soon as I start typing it
appears up in the formula bar. Net Revenue. That way will distinguish
because both Total Net Revenue and that field Net Revenue,
will be in our field list in the pivot table. But we'll know to pull
Total Net Revenue. And guess what? We have to type a colon,
and then an equal sign to get our measure to
calculate correctly. Now I type S-U-M and Tab. I can down arrow to
FSales Net Revenue. That is the calculated column. Close parentheses and Enter. When I expand the
column width, I can see that total right there. I would like to add
number formatting. I'm going to click on this. And guess what? Now that I've added English
United States, dollar sign there, when I drop that
into any pivot table, it'll always carry the
number formatting with it. Now we noted last video that
the answer to this calculation is $905,702 and
some pennies, right? But when we drop
this formula, you could see the formula
up there, we'll see Total Net Revenue
in our field list. When we drop it into
the pivot table, this formula will see the
filter or criteria we have. That means row, column,
filter, or slice, or criteria. And it will instantly update. Let's create our pivot
table and see it live. When I click Pivot Table from
our manage Data Model window it jumps over to Excel. I'm going to put this on
a new sheet, click OK. Here's our field list. I can drag and drop
from any one of these. Now I'm going to come to
DSalesRep and drag Region. Instantly, I get a unique list. Now I'm going to a second table. And this of course is going to
work because of relationships. And there's another difference. I love this. It has the f of x to
let us know that is a Measure or calculated field
or an explicit function. And we can drag and
drop down to values. Look at that number format. And guess what? If I create 10 more pivot
tables, no more right click, number formatting like we had to
do it in standard pivot tables. And I can drag and drop. Notice I took
Region from SalesRep and now I'm going
to go up to Product. I'm going to take a different
field, drag it down to rows. And look at that. I can drag and drop from
1,2,3 different tables. So I have products
from the Product Table, region from the SalesRep,
and in the FSales, I have my Total Net
Revenue formula. And as we mentioned, the
beauty of the DAX Measure or Calculated field,
or another synonym, is explicit formula-- is that
formula right there respects the filter. It sees south and Aspen. It actually will go down and
filter the underlying table to get a smaller range
to make a calculation on. And that is one
of the things that contributes to DAX formulas
being able to calculate quickly on big data. I'm going to be sure to come
up to Design, Report Layout, Show in Tabular. Now in this video, we saw how
to take our tables in Excel, import them using PowerPivot,
add to Data Model. . That gave us our Data
Model 1,2,3 tables. We, of course went
over to Diagram View. Made our relationships. And in Data View, we
created a calculated column, a Measure or calculated
field, and explicit formula. And then we created
a pivot table. And now we want to make
sure and double click and call this product
Region Report. Now when we come back
in our next video, we'll see a true
big data example. We'll have multiple
related tables, big data. We'll bring it
into our Data Model and make a number of
different calculated columns and measures. And then build some reports. All right, we'll
see you next video.
