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.