digest
Welcome to Highline BI 348 class, video number 41. If you want to download this workbook, BI 348 Chapter 2.75 Example 1, click the link below the video. In the last video, number 40, we had an introduction to Power Pivot and the Data Model, but here I just want to show you how to get tables into Power Pivot, build a few formulas, and make a pivot table using Excel 2016.
As we saw in the last video, we have these related tables, and we need to get them into the Power Pivot Data Model, build relationships, and create formulas. However, in order to get data from an Excel workbook into Power Pivot, you have to create an official Excel table. So I'm going to press Ctrl+T for Table and Enter. Then I can go up to Design, and up here in Properties, name it. I'm going to use the convention F for fact and then Sales, and press Enter. Now I can click on each one of these, and here it is: Power Pivot. 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. 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 selected for, so I click Add to Data Model. This looks 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. It got the table name D Products. We don't need to mess around like we did in the 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.
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. There's our one, two, three tables.
Before we add a calculated column, 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 View. There's our table, which looks slightly different; it's only the colors. Here's our fact table, and here are our dimension tables. This is a lookup table. In the fact Sales table, if we're going to build a formula that can look up price, there has to be a relationship on Product from the lookup table, or dimension table. We drag it over and drop it on top of Product in the fact Sales table. This is a one-to-many relationship, and that's different. It has a 1 here and a little asterisk. Remember, the 1 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. Of course, Sales Rep can have many transactions with the same sales rep. The same is true for this product over here: a single product appears in a unique list, but many products appear in the transaction table.
All right, so we can press Ctrl+S to save and go back to Data View. Now we can create our calculated column. We're going to need to get the price for each one of the products, multiply it by 1 minus the discount rate, and times units. So we double-click the top and I'm going to call this Net Revenue. This will calculate the revenue for each transaction. I click on the top cell, press the equal sign, which shoots me up to the formula bar, and RELATED is the lookup function we use. That's pretty cool, and it's different than Excel 2013; this drop-down didn't appear there.
Now we need to look up and get the price. I simply double-click, or hit Tab, for the table name, and the square brackets for our field name. I close the parentheses, and when I hit Enter, it will self-populate all the way down, giving me the price. As we talked about in the last video, that formula is the same all the way down, and it works off row context. Since the formula is in this row right here, it knows to look up Carlota in the Products table and get the price because of the relationship. For every row, the row context says, "Go get Carlota and bring back the price."
Now I click on the top cell and come up to edit this formula, because we need to take the price times, in parentheses, 1 minus. Remember, when we click on Discounts, that doesn't follow our proper convention. Our convention is always going to be the actual table name, and I'm going to hit Tab and then 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. These are columns, so we use our convention: table name and field name. Now we go times F Sales, down, down, down, until we get to Units. Tab and Enter.
We need to round this, so we can use the ROUND function just like we do in Excel. We come to the end, comma, minus 2 to round to the penny, close the parenthesis, and press Enter. So there is our calculated column. Unlike the last video, we are not going to add our number formatting yet. 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, which is the word they use in 2016: measure, or calculated field. What do we do? We type the name of the new measure, and it's going to be Total. As soon as I start typing, it appears in the formula for our Net Revenue. That way we'll distinguish between them, because both Total Net Revenue and the Net Revenue field will be in our field list in the pivot table, but we'll know to pull Total Net Revenue. Guess what? We have to type a colon and then an equal sign to get our measure to calculate correctly.
Now I type SUM and Tab. I can down-arrow to F Sales Net Revenue, which is the calculated column. Close the parentheses and press Enter. When I expand the column width, I can see the total right there. I'd like to add number formatting, so I click on this, and now that I've added English United States dollar sign formatting, when I drop that into any pivot table, it will always carry the number formatting with it.
We noted in the last video that the answer to this calculation is nine hundred five thousand seven hundred two dollars and some pennies. When we drop this formula, you can 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: row, column, filter, or slicer criteria, and it will instantly update.
Let's create our pivot table and see it live. When I click Pivot Table from the Manage Data Model window, it jumps over to Excel. I'm going to put this on a new sheet and click OK. Here's our field list, and I can drag and drop from any one of these. I'm going to come to D Sales Rep and drag Region. Instantly I get a unique list.
Now I'm going to go to a second table, and this of course works because of relationships. There's another difference that I love: it has the f(x) to let us know that it is a measure, a calculated field, or an explicit function. We can drag and drop it down to Values. Look at that number formatting. Guess what? If I create ten more pivot tables, I won't need to right-click for number formatting, as we had to do in standard pivot tables. I can drag and drop. Notice I took Region from Sales Rep, but now I'm going to go up to Product and take a different field and drag it down to Rows. Look at that: I can drag and drop from one, two, three different tables. So I have Products from the Product table, Region from the Sales Rep table, and in the F Sales table I have my Total Net Revenue formula.
As we mentioned, the beauty of a DAX measure, calculated field, or explicit formula is that the formula respects the filter. It sees South and Aspen. It actually goes down and filters the underlying table to get a smaller range to make a calculation on. 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.
In this video, we saw how to take our tables in Excel and import them using Power Pivot, add them to the Data Model. That gave us our Data Model with one, two, three tables. We went over to Diagram View and made our relationships. Then, in Data View, we created a calculated column, a measure or calculated field, an explicit formula, and then we created a pivot table. Now we want to make sure, and double-click to call this Product Region Report.
When I come back in our next video, we'll see a true big data example. We'll have multiple related tables. Big data will bring it into our Data Model, and we'll make a number of different calculated columns and measures and then build some reports. All right, we'll see you in the next video.