Ultimate Excel Course #13 - Introduction to the Data Model and PowerPivot | Alan Murray | Skillshare

Ultimate Excel Course #13 - Introduction to the Data Model and PowerPivot

Alan Murray, IT Trainer, Coffee Lover and Lifelong learner

Ultimate Excel Course #13 - Introduction to the Data Model and PowerPivot

Alan Murray, IT Trainer, Coffee Lover and Lifelong learner

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
7 Lessons (45m)
    • 1. What is the Data Model and PowerPivot?

    • 2. Adding Tables to the Data Model and Creating Relationships between Tables

    • 3. Creating a PivotTable from the Data Model

    • 4. Enabling the PowerPivot Add-In

    • 5. Exploring the PowerPivot Window

    • 6. Introduction to DAX - Calculated Columns

    • 7. Introduction to DAX - Measures

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class


This class is #13 in a series called the Ultimate Excel Course.

In this class you will be introduced to the Data Model and PowerPivot in Excel. This is an extremely powerful area of modern Excel.

This class is for people who work with large volumes of data and need to perform powerful analysis.

This is a huge area of Excel so this class serves as an introduction and insight into this feature. It will enable you to understand what it is and how it may benefit you.

In the class we import some data from a csv file into the data model using Power Query. This file contains over 100,000 rows of data. We then create relationships from it to 3 other tables.

Then we create a PivotTable from multiple tables of data in the model. Next we look at enabling the PowerPivot add-in and demonstrate some basic examples of DAX formulas. 

The Excel files used during the class are provided so that you can follow along. You can then take on the project exercises to test your skills in action.

Please post any questions in the discussion area and I will get back to you.

Grab a coffee, enrol and let's do this.

Please note that this is class #13 in a series called the Ultimate Excel Course.

You can find direct links to the other classes in the series below.

Class #1 - Excel Formulas Made Easy - Get up to Speed with Excel Formulas Fast

Class #2 - Essential Skills for Working with Large Spreadsheets

Class #3 - Logical Functions - The Decision Making Formulas of Excel

Class #4 - Conditional Formatting: Make your Data Come to Life

Class #5 - Lookup Functions - The Powerful VLOOKUP Function and Beyond

Class #6 - Validating and Protecting Excel Data

Class #7 - Advanced Formulas for Analysing Data

Class #8 - Excel Charts for Simple and Effective Data Presentation

Class #9 - Cleaning and Preparing Data Ready for Analysis

Class #10 - Formatting your Data Ranges as Tables for Superior Management

Class #11 - Master PivotTables for Powerful Analysis and Reporting

Class #12 - Get & Transform Data with Power Query

Class #13 - Introduction to the Data Model and PowerPivot

Meet Your Teacher

Teacher Profile Image

Alan Murray

IT Trainer, Coffee Lover and Lifelong learner


Hello, my name is Alan Murray and I have been training and consulting in Excel now for over 20 years. I have had the pleasure in helping individuals and businesses from all around the world improve their Excel skills.

I set up Computergaga about 10 years ago to help spread the latest Excel tips and to engage with other Excel experts and learners. It has grown beyond my expectations and I hope to get the chance to speak with many of you soon.

I have a real passion for Excel and I hope that it comes across in my classes. I believe that it helps if you enjoy what you are learning.

I currently live in Ipswich, on the east coast of England. I have two beautiful children. So they take up most of my time away from Excel. If I'm not with t... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
  • Yes
  • Somewhat
  • Not really
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.



1. What is the Data Model and PowerPivot?: hello and welcome to this lesson where we start to look at the data model in Excel and Power Pivot. Now this is a huge topic which deserves his own course. Sit. If section will only serve as an introduction on a kind of eye opening to this, we're going to focus on how it benefits us within Excel, which for 99% of Excel users is all they will need. But as you will see from the lessons in this section, it can go a lot further now. The first thing to mention is that this is only available to office free 65 users immediately at the time of doing this video. If you have a previous version such as Excel 2010 or X or 2013 you can access power pivot by downloading the Adan. So just go to Google on power pivot Adan. You can download it and enable in just a few seconds on. Then it will be available. Where is an office free 65? It is already ready to go. You won't need to worry about that step, so let's look at this example where you will be working from. I have this workbook because store sows on it has three sheets in it. On each sheet has a table. So I have this products table on it is named his products as well. I also have this South rep table and I have this customer's table with information about customers. But it has also got a separate file. If I switch Windows here called sows on this is actually a C S V file at saved away on. What I want to do is import this CS fee file using power query that recovered on the previous section. But this is a large table. This table has over 100,000 rows. It has 106,694 rows, and this just stores the information about the cells. Where is going back to the other table? They have the information about the customer that were sold to about the rep that took the cell and about the product that we sold, where was in a sow's table. You can see I just have the i. D numbers for that. So, typically, from an Excel perspective off doing this, going back to these other tables for a moment. This is a typical V look up situation or an index and match. People would use those formulas to retrieve this data would say, Look for the customer name and put it into this table. Look for the sows rep name and put it into this table, and that would be fine. And that would create this flat file for us to work from. And that's what most Excel users have always known. But because this data is quite large over 100,000 that volume off formulas is all going to take its toll. Increased a file size, slow down calculations in air spreadsheet. So another option we have is merge queries. We saw merge queries in the power query section. That's really useful as well, and that could be seen as an improvement upon V. Look up. But in this section, we're gonna look at putting it into a data model. We're gonna import that C s fie foe, but we're not gonna pull it into the spreadsheet is no even going to exist here, So it's going to drastically reduce the size of a file. We're just going to story in the data model at the database behind the scenes and never going to create relationships between the tables instead of Villa Cups on this is going to make it a lot more efficient. So when we're trying to think off, what is the data model and why would I want it? What is power pivot? Why do I need to know this depending? What use Excel Far You might not need to know. But in a situation like this example, it is a much more efficient way off store. In your data, we can create these relationships between the tables, and that is a much more efficient approach when compared to merge queries or villa cops or index matches. It is a much more efficient way of storing data and also which you'll see in later lessons . Performing calculations. It will be different to write in the formulas on the spreadsheet, which all takes its title. One slows to spread shit down ads on increasing file size. But away we write calculations here. Ultimately in power pivot, they will not be on the spreadsheet, so this is also needed if you're going to deal with big data, said a Day two here is over 100,000 rows, but a sheet is limited to the number on screen, just over a 1,000,000 rose. So especially for those of you who may work with big data, then this is something you want to get up to speed with and learn that data model and Palpa is how we work with big data. The other big reason for use nous is Dax's calculations. So Data Model really is a term for the storage. That database storage, the ability to create relationships between the tables and then being able to write formulas called Dax calculations, which is a rich and vast formula language so that we conform calculations outside of what you get in Excel on Power. Pivot is just the name given to be in ableto create pivot tables from multiple tables. So where is normally we would summarize their data. We would consolidate at data all into one big, single flat file table. A power pivot is how we can crack a pivot table from multiple tables. We simply create relationships between them, and then we can perform it of multiple tables. You could handle over a 1,000,000 rose. It can store its calculations off the spreadsheet on with Dax, we conform a lot more calculations than what we can weave standard pivot tables, so let's start to explore at this area. 2. Adding Tables to the Data Model and Creating Relationships between Tables: in this lesson, we are going to load our tables into the data model and create relationships between them. Now let's begin with that sows see SV file that we need to import. So if a click on data at the top and in from text CSP and it takes me to the right folder would never get to it hears that sis for you file and I will import it in to Excel. Power Query will now pop up. Ask me how it's delimited but picks up the Kama and the opportunity to load it or to edit it now is good practice. I always like to click edit at this point so that we can just check how it looks. Change any data types, if needed, Do any simple transformations if needed. So over on the right hand side are consider name. It's been given this Sow's picking up from the file name, and I'm quite happy with that. We've got the usual applied steps. If we come over and look at the data types, we have a number for the order I d. We've got a date for the day. It's text for custom. My day on good numbers for the product, our day in the Rep I day and also some numbers for units sold on. We could argue a change for these into a text data type, but everyone quite happy with what we've got here. So though I'm not making any changes, I think it's always good practice to click on edit and check it in the editor before you do anything else. And in a different scenario, we might have to do quite a few transformations here, like recovered in a power query section off the course. For now, though, I'm using the drop down arrow for clothes and load. We're going to close and load, too, and then when the window pops up, something we haven't done as yet in this course, and that is to only create the connection. I don't want to put it into a table in this spreadsheet. I'm gonna just create a connection, and I'm going to check the box to add the data to the data model. So it's coming into the data model, and I'm simply creating the connection to the C S V file. I am no actually loading it into the spreadsheet at which is an option for us. But because of the size of this file, that's the route I'm taking. So we'll click OK to that and that will load that as a connection and into the data model and you'll see load up in the right hand side. Here in the queries connections, Pain that is loaded, a table with 106,000 and 690 free rows loaded. Absolutely no problem. Now let's go and load thes other tables. Internet model. By establishing these relationships, I'm going to close this pain on the right hand side because they're not going to concern myself of that connection for out the rest of these lessons. And we can start to credits relationships through the data tab on a relationships button that we now have their from Excel 2013. So there's being this mention off power pivot, and it will come back again in a later lecture. But for now, we don't even have to concern herself with that word. We can just work with Excel Azriel ways have done, and if a click Annette relationships button and we can see we don't have any and then click on New on weaken set up her first off three relationships. Now I don't know your familiarity with these terms and whether you've worked with databases before, but it prompts us for a table and a foreign key on the table and a primary key. So looking at this table dropped down, we have our four tables and the sows table. The one we just imported is the one with the foreign key. We are creating one to many relationships. We have one customer who may place many sows from us. We have one product that hopefully will sell many times, and we have one South rep who can hopefully make many sows. So there's always a one to many relationship to the Sauer's table, so the sows table is out. Facts. Data is F soya fact table or a data table, and that has the foreign key. Where is the other tables? Or and look up tables or add dimension tables, and they have a primary key notice also, how in here it references them as worksheet. Tables for the tables are having the sheets and then a data model table because that south cyst fee has would've been loaded into the model. Very interesting. So two cells and then let's use customer to start with sort of foreign keys. The customer i D. And never in a related table is customers on. The primary key is simply I d in that table and I can click OK on That first relationship has been created and is now visible in this window as the table and in a related look up table, sometimes refer to as a dimension table. Now we will go and create the other two relationships quickly. So the creek on new again notice how now the customer's table is referred to as a data model table. Now that relationship is added. It is in the data model. Let's to sowles again. This time we'll do a product industries product I Day, and then it's going to be the products table, and it's going to be I d and click OK, and then one more time New relationship table is sows. This time it's for the sowed reps I want Rep. I d. That's what it's referred to in this state, or anyway, hopefully you would know your data better worksheet, table sales reps and the key is the rep I d when I click. OK, those three relationships are now created. An active I can close down that window and we're not going to see any difference at the moment in this worksheet. All we know is that we have established those free relationships between these free tables . Andi, that big sows table which was a C S V foe that has been connected in the data model. In the next lesson, we're now going to use that in a pivot table for analysis. 3. Creating a PivotTable from the Data Model: in this lesson. We are going to create a pivot table from the tables in that data model, so we will start by clicking, insert and pivot table, just like we usually would. But look at that as soon as we get to the create pivot table window. It recognises our data model so we could still go and specify an external source or specify a range or table on a worksheet. But it's automatically detected air model, and that's what we want to use. I will change existing to new worksheet, however, and click OK said it creates a new sheet and puts out pivot table on the on the left on the field list on the right. No, I've changed the layout off this filled list a little bit. It's something I like to do when working with tables in a data model, because if we have a closer look at this with the order tables here from a data model now there's only four, and none of them are that large Iver. But imagine you're dealing with a bigger model as you click on the arrows to expand and look at the fields within them that can get quite long pretty quickly. So what I've done here is used this kind of gear icon. You get in the top right of the field list and have changed it from showing the areas stacked two areas on the side off the list of fields. So it gives us a little bit more room to actually expand those tables and work with fields and dragged them the right hand side Robert and drag him underneath. It's just personal preference, says authorities. You can notice the tables have different icons, as it's trying to indicate, as I point over of my mouse that the data source for the sows table is actually a queary That's not on the worksheet is coming directly from Nazi SV file from that Queary. Where is the other three are tables that were on the worksheet, and these icons can change over time, so there may look a little bit different in your version, whether that be because of Yunus in the future or because you've got an older version Now from here, we can use our pivot table in very much the way that we spoke about earlier in the course, and you might be comfortable with. We can simply go into our customer's table and expand it weaken, take a box or drag what we want. So maybe you want to drag the country field into roads and I get a list of all the countries. Maybe I can expand my sows table and drag across the total sows field and put it in his values area for calculation. And now we'll consider Toto sows off our products. Within each region, I can easily move country out off the field list, collapse the table with its causing may at some stress river a lot of clutter around, maybe expand products and look it by product name. So where are able to create pivot tables from multiple tables because of our relationships and because it is model and that's something we can't do without it. We have been consolidated into one big flat file database, and this is what kind of power pivot brings to us now when I put that filled in that values area that is actually a DAX calculation so have now already written a DAX calculation without knowing it, and that's quite useful because Mark Soft of may be available without the user needing, or maybe even caring, to know in the grand scheme of what we can do with power Pivot and we have Dax formulas and the model. This is not necessarily the ideal way of working. This model is quite small, but it was much larger. There are more efficient ways of working that is known as an implicit measure, and we're going to look a tiny bit at measures in an upcoming lesson. However, although it may not be the ultimate exact way that you can work with to get the most out of it, that doesn't mean it's a problem. Andi. The good thing about this is that your casual Excel user can create a model by relating tables using power query toe, create connections and import data from different sources and, after were related, weakening create pivot tables, multiple tables, I mean, get a lot of power on a lot of functionality without necessarily getting into somebody more advanced topics that come with data May databases on about field context and Dax calculations, which no every Excel user needs. 99% of them do not need that or, you know there may not be confident, uncomfortable we using. So there's a lot of strength here without necessarily getting into what are well with the next couple of lessons. But this is really, really useful feature of Excel. This is really just working as a bit of an eye opener at to this functionality. 4. Enabling the PowerPivot Add-In: Now let's begin to have a little look at Power Pivot and that window Now, even though I am used in office free 65 So I have power pivot without needing to install in Adan. I still need to enable it. I don't have a power pivot tab at the top My ribbon yet you will need to go to file on options and in into the Adan's area and you will see power pivot provided in here. So here I have power pivot But at the moment it's inactive. So at the bottom I will change excel Add ins to calm Adan's and click on G O Tick the Power pivot box and click OK, and now I will have a power pivot tab on my ribbon so we can start to explore that interface 5. Exploring the PowerPivot Window: So let's have a quick look at the power pivot window. So we have the power pivot tab on my ribbon. You can see there are a few buttons on here, not too many. And some of them will make sense to such as adds to data model something that we have explored, although from a different root already. And then a button called Manage on the left hand side with the power pivot icon in his data model group. Now that button there on a power pivot tab, is the same button that you can also get through the data tab and over on the right hand side, manage data model and even Siyassah hover over. It says Go to the power pivot window. So you do need to have power pivot enabled for this button to work. But you don't even need to go for the power pivot tab so you can see how this feature has really stepped forward. Teoh almost be a main part off the Excel experience in much the same way that veal cups on normal pivot tables are Now. If a click on manage data model that is going to take me into the power pivot window, and here it is, So I can just maximize this window and you can see along the top. It says Power pivot for Excel on Along the bottom. We have some sheet tabs just like you would an excel. But please don't confuse that the data is not stored like this. This is just how it's presented to us on this power pivot window allows us to get this insight into the data model, so I've got the sows tab here with that 106,000 odd records on. We also have the customer's tab and the products tab on the different tables that we added earlier and then related up to the ribbon atop. There's a whole host off functionality, but the only part I was really interested in right now when exploring the window is on the far right. You've got some different view buttons on at the moment. I'm looking at data view where we can see a little bit like a worksheet with the ability to even add mawr columns in over to the top right. The calculation area has also been selected, so underneath the sheet there's actually this area here which are contrary size, where we can actually write calculations at such as measures which will talk a little bit like about in the later lesson. If I was to move back to let ribbon, there is an option for the diagram view and if I choose diagram view, we will get this. So now we can see the relationships between their tables in a visual way. So I've got this sow's tape with a day to table or the fact table and then we have this other tables coming off it. And then we've got that one too many at kind of you so we can rearrange this so it makes more sense to us visually, which is quite nice. So it's quite common to move these look up tables higher up because you can see these arrows coming off the relatable links, showing that one to many relationship on also show in the flow of data what it could've feel to context. So people like to lay out this way so he can visually see that we can also create our relationships in this window on also edit and delete those relationships in much the same way that we saw at a few lessons ago. We did it within Excel for a but, um, Excel. So that's quite a nice view when you're trying to explore these areas. If I switch it back to date of you once again, is looking more like a worksheet. And that's just a quick look around the power of that window for the moment before we start exploring a little bit off the Dax language. 6. Introduction to DAX - Calculated Columns: So in this lesson, let's start have a look at those Dax formulas, so this will be the first off. Two lessons about Dax just toe have a little introduction to what it's all about. So if we click on the power pivot tab on the ribbon and then this manage button toe, open up that power pivot window on that will take me to this date of you and I'm on the sows sheet. If you're not on the South sheets, just jump across to it because in this first example, I want to look at creating a calculated column. So that's where we're going to add a new column on the end off this table, in much the same way that you can add a new column in Excel worksheets or very similar to the formulas we were writing in the table section of this course and using a structured references celery click in the first cell. You can click on any cell in that in this column here, anything you write is going to apply itself to the whole column. But a martyr's will start with the 1st 1 and I want to write a calculated column that's going to test the order date value off this sow, and I wanted to display the word weekend. It's a weekend. It was a Saturday or Sunday to be specific or week day. If it's a day during the week. A Monday to Friday on where calculated columns come in handy is when you want to create some kind of label that you can then use for further analysis in your pivot tables. Now this is something we could have done that within power query or even in the work shit. But this is an example of tax. The next example will be looking at measures, which is a far more common use off the Dax language in power pivot. But here we're going to see the calculated column. So let me zoom in on this area, and with that cell selected, oh type equals on. We'll see that appear in the formula bar above, and I'm going to write and if function so this will hopefully bring back memories from earlier in this course where he covered it functions or indeed and all function as well because I want to test if that date occurs on a Saturday or a Sunday, said the first logical test. I'm going to use the week day function, which returns a number 1 to 7, identifying a date a week, and I want to provide it with the order date. Now I can click. Any cell with an order date here is going to write the same thing in. I don't have to worry too much about being on the right road like you would in a normal worksheet, and it will write sows, order, date. And you might recognize that from the formulas we wrote in the table. Second of this course, it looks exactly the same, really, at the moment. So that's the date I could in putting a comma set prompts me for a return date. I have the option of specifying one where the Sunday's a one Saturday to seven, or could choose to when Monday's a one and Sundays or seven. And in another option, let's imagine I want this number two. So for my examples, Sunday to seven and Saturday is a six. So let me choose Number two close off the bracket and test of its equal to six cities. The data week off that day a number six is It is Saturday. I can put my comma and pretty much repeat what I've done there. Week, day selected dates, comma to close bracket people seven. Or was it a Sunday? We can enclose off the or function bringing us back to If I'm going for a fair pace here, so would overseen something new with maybe a Dax calculation. It has huge similarities at this stage toe. Other formulas we have written on this course I can put my comma. I want to display the word weekend defense the case. So right now, in my quotes, another comma brings me on to result. If thous argument on I will say Week Day, if that is the case, I can enclose off that bracket and press enter to run that calculation. And if I give it a couple of seconds to calculate, there we go. The whole column is populated with the word weekend, a week day, depending on what data week that sow occurred on. We can now use that for analysis in whatever way we want. I should rename this column, really? So let me just double click on that header and called it parts off weak, so it makes more sense to us. Let me zoom out of there. And as a first example here for Dax calculation, that is a calculated column that I have now created so we can now go and use that in our pivot table. So if I close down this power pivot window back to excel and there's air pivot table over on the right, I can see part off Weak is in that sows table so we could know. Use that I could drag part of week into this rose area and break up the sows of each product by the weekday or weekend. I can see Phil at products that much doing awake that much that we can't that product, these numbers and so on So I can use that as a labour within our analysis. And that's the classic use off a calculated column to create some kind of label. You're not going to use it for aggregates like sums and averages. It doesn't get used for that. It has Row Context is calculating the date on that row like the date if a drag part of the week out. Another classic example you might want to use the result of a calculated column would be in slicer. So my analyzed tab for the pivot table I could insert a slicer look order tables of a good access to in this slicer. I could choose part of week, and now I have. It is a slice up, so I've got product in history. At the moment, I could choose weekday. There's the weekday sells for it. We can is the weekend south for it Now. We've spoke about slices on this course already, so won't bore you with them. For now, it's just seeing how we can use them, even with pivots. Hey was based on a data model on how we can even use Dax to write calculations in power, pivot and then use that in our slices. 7. Introduction to DAX - Measures: in this lesson. We are going to look at a second example off writing Dax formulas, and that's going to be to create a measure. Now this is a much more common approach to use index on. This is where you're going to see it used if you are to take other courses or get a book to maybe delve Maurin to the world of power. Pivot here I'm planning on using two examples on a measure is what we put into the values area off our pivot tables. So that's where we perform aggregates, just such a sum and count. But by using Dax, we have a much more vast availability of functions, then what were limited to in a typical pivot table, where you only get around 12 different functions, But the Dax language is huge now, earlier on in this course, we put this some off total sows value into this pivot table, and I mentioned briefly that's referred to as an implicit measure. Now we're now going to create a measure in the modern itself, known as an explicit measure on because it's not actually stored in this pivot table in its in the model. It's a much more efficient way of working. We can specify the format in in the model. I won't keep having to specify in a pivot table, which you have to do. You have to format your value fields. It will also mean we can reuse it whenever we need to. So at how do we do that? Let's go to the power pivot tab on the ribbon, and there's different ways of creating measures. There's multiple ways, but the main way is to use the measures button. This is your complete way off creating a measure. So I've got the opportunity to hear to manage existing measures. So are edit, delete, view existing measures or for me right now, create our own measure. And it opens up this window, which is why are referred to it as much complete way. We've got a lot of functionality in here at much more than when you write a typical formula . Now, for my first example, I just want to some values, you know, really, really basic function. But I want to see it being used to create measure and by using Dax. So the first thing we have to specifies the table where we're going to store the measure and I'm going to leave that as the sowles table. But we do have the different tables from a model here. Well, then have to give it a name, so I'm going to call it some off south. I already have a filled in the south table called total cells. And I have to be careful not to reuse an existing name that to be unique. Someone referred to it. Some of South I had the opportunity to write description, but I think they're kinda measure named as it all. There on an in this box provided choir big books. We can write our measure, so I'm just gonna type some. I'm sure it didn't come as a surprise, but straight away with my writing. Look how many functions air coming up. You know, you get intelli sense menu, which is really, really handy, and there's functions here that you you're not going to recognize like some ex substitute with index is sub total summarize columns you won't have seen knows before. That is how rich this ducks languages. You don't get these in a worksheet or not at the time of doing this video anyway. So it so's how vast this is. And it always worked with data analysis. This is your dream Tall, especially your working of big data as well. Now I'm going to carry on with just a standard some sign that nice little shortcut to know here. I'm hoping you can see it is quite easy on screen, but you can hold down your control key and scroll wheel on the mouse to zoom in in this area. So I'm zooming. At the moment. You can see the words some getting bigger, bigger and bigger on his menus, kind of in the way at the moment, so I might make it smaller and smaller again. But another really useful short cut their control and rolled in the will to zoom in and out . It's quite small. And then this intelligence menu helping a selector table in a column very similar to how we wrote in a table section of this course. Now I want to some the data that's in the Sauer's table, so type s to shoot fruit and the total sows filled. So in a double quick to put her in South Table Total Sales column close off my bracket. And if I press his check formula button, it would quickly check my syntax there and say that there are no errors, Always good to see. And if I scroll down a little bit, I can specify a format so I can choose that its currency in pounds Yale, keep my decimals and click OK on That measure is created and is also immediately added to the pivot table. So look at this. We have the same information twice because I've still got my implicit measure from earlier , which will canal quite happily, remove. Get rid of that. And I've now got my measure. My explicit measure if you want written using Dax so we can reuse it and it's stored within the model and I could specify formatting in advance. So as I use it again and again, I don't have to tell his format. And here it is, got your FX next to it to prop me that this is a measure and I can drag in whenever I want to. So good it's summarized by products name right now, but we can easily change that and sit by something different, such as customer or by country, or what not has put country in there, and that measure will run. So that is the most complete onder efficient way off creating and using a measure. I just want to do one more example of a measure before close this lesson ounce. So for this next example, I want to do something different to typical sums and averages. I want to do our unique count at something that you don't get available is a standard pivot table thing or formula, really, where some in counting on average in, which is obviously amazing. That's what most people want. We've covered out in different parts of this course, but we've pivot tables but also with different formulas someone to go a little bit outside the box, if you will. So if I click on the measures button on the power pivot tab and choose new measure again once again, I'm going to create it from the sowles table, and I'll give it the name, uh, unique and list of a unique customer. So maybe I want to summarize. So why analysis to see But how Maney different customers made that purchase off that item or in that month for whatever I want to do. So I've got unique customer Andi. I won't worry about description finish, although straight in and write it, not a function we want here is called a distinct count. Once again, look at all those functions that coming up, it's such a large language off off four meters here, remember, we can always go Gordy's function names. You're not expected to know it's called Distinct Count. But once you know this, I cannot selected my list on the intelligence menu. Helped me find that. Now the table is going to be the sows table, and I want a unique customer. So I'm going to select customer idee because that would identify a unique customer to me. Close off my bracket and click and check formula. Teoh show that I have no errors in that formula. I couldn't do some format in such as number and specify a za whole number. Ang click OK, and I have that measure added so at the moment it looks rather silly because I've got my customers. Is my row label on this? Just telling me that is one customer, which is rather silly, but I could remove customer name from the rose area and replace it with something different , like country. And now what can see how many customers are have within each country? This measure can be reused in different scenarios on, especially if we have a larger model here, with different tables and different relationships going on. This measure that we can reuse and create what's called measure trees can be really, really beneficial to our analysis and to a reporting. So this is keeping things simple at the moment and showing you how we can write a little bit of Dax and showing its similarities to other stuff with done this course. So hopefully it'll build your confidence if you're interested in exploring this area fervor and giving you an insight and how rich the languages. But this is really all about working with big data, whether that's come from power query like it has done in this example on how we can create that model with our relationships, create pivot table from multiple tables and then use a brand new language called Dax to interrogate it to very detailed levels.