Microsoft Excel - Excel with Excel Power Pivot, Measures & DAX Formulas! | John Michaloudis MyExcelOnline. com | Skillshare

Microsoft Excel - Excel with Excel Power Pivot, Measures & DAX Formulas!

John Michaloudis MyExcelOnline. com, Stand Out From The Crowd

Microsoft Excel - Excel with Excel Power Pivot, Measures & DAX Formulas!

John Michaloudis MyExcelOnline. com, Stand Out From The Crowd

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
17 Lessons (46m)
    • 1. Intro to this Power Pivot & DAX Formulas course

      1:38
    • 2. Installing Power Pivot in Excel 2010

      2:47
    • 3. Enabling Power Pivot in Excel 2013, 2016, 2019 and Office 365

      1:01
    • 4. Import From Workbook

      3:19
    • 5. Import From Worksheet

      3:12
    • 6. Import From Separate Workbooks

      2:31
    • 7. 05 CreateRelationships

      4:43
    • 8. Create Relationships via the Diagram View

      2:03
    • 9. Create a Pivot Table

      3:35
    • 10. Insert a Slicer

      1:41
    • 11. Intro to DAX Formulas

      1:50
    • 12. Intro to DAX Formulas: Measures vs Calculated Columns - Part 1

      5:11
    • 13. Intro to DAX Formulas: Measures vs Calculated Columns - Part 2

      1:41
    • 14. DISTINCTCOUNT() - Create a "Unique Order Dates" Measure

      3:33
    • 15. SUM() - Create a "Total Sales" Measure

      1:43
    • 16. Combine 2 Measures to get "Average Sales Per Date"

      1:31
    • 17. CALCULATE() - Filter & Show Individual Product Sales

      4:28
  • --
  • 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.

182

Students

--

Projects

About This Class

Power Pivot is an Excel add-in that was first introduced in Excel 2010 by Microsoft. It allows you to harness the power of Business Intelligence right in Excel.

In a nutshell, Power Pivot allows you to use multiple data sources for analysis.  So it is a Pivot Table on steroids, meaning it can handle more data and create more powerful calculations using Measures & DAX Formulas.

Power Pivot gives you the power to work with large sets of data that are OVER 1 millions rows!!!  

You can import, consolidate and create relationships from your data and perform enhanced analysis. The beautiful thing with Power Pivot is it allows you to work on Big Data with NO limitations.

Imagine getting data from multiple sources like SQL Server, Oracle, XML, Excel, Microsoft Access and analyzing these all into one awesome Pivot Table!

This course will introduce you to the key concepts of getting started with Power Pivot and it's various features for enhanced calculations, so you will be confident & comfortable in using Power Pivot on your data.

--------------------

POWER PIVOT IS NOW AVAILABLE IN ALL EXCEL VERSIONS!

In July 2018, Microsoft  announced that Excel Power Pivot will now be available in all Windows editions of Excel, which is awesome news and a step in the right direction.

Previously in the 2016 version of Office 365, you needed to have purchased the Office Pro Plus or Office 365 E3 to have access to Power Pivot within Excel.

So now you can get Power Pivot in Excel 2010, Excel 2013 and all Office 2016 products, such as:

Office 365 Home, Office 365 Personal, Office 365 Business Essentials, Office 365 Business, Office 365 Business Premium, Office 365 Enterprise E1

--------------------

EXCEL POWER PIVOT INSTALLATION GUIDES:

Excel 2010 Power Pivot Install Guide

Excel 2013 Power Pivot Install Guide

Excel 2016, 2019 & Office 365 Power Pivot Install Guide

--------------------

DOWNLOAD ALL EXCEL POWER PIVOT WORKBOOKS HERE

--------------------

PREREQUISITES FOR THIS POWER PIVOT COURSE:

You need at least an understanding of what Pivot Tables are and how to insert a Pivot Table and create a few calculations.

You can view my other courses on Skillshare which show you how easy it is to create an Excel Pivot Table!

--------------------

EXCEL VERSION THAT I AM USING:

I am using Excel 2010 in this course but the layout is very similar to Excel 2013, 2016 & 2019.  Any variations in versions between Excel 2010 and the other versions, I explain in each tutorial.

--------------------

In this course we will go through the following Power Pivot features:

IMPORT/LOAD DATA

Import From Workbook

Import From Worksheet

Import From Separate Workbooks

TABLE RELATIONSHIPS

Create Relationships

Create Relationships via the Diagram View

PIVOT TABLES & SLICERS

Create a Pivot Table

Insert a Slicer

INTRO TO DAX FORMULAS

List of various DAX functions

Intro to DAX Formulas

Intro to DAX Formulas: Measures vs Calculated Columns - Part 1

Intro to DAX Formulas: Measures vs Calculated Columns - Part 2

MEASURES & CALCULATED COLUMNS

DISTINCTCOUNT() - Create a "Unique Order Dates" Measure

SUM() - Create a "Total Sales" Measure

Combine 2 Measures to get "Average Sales Per Date"

--------------------

I am sure that this course will make you a more confident Excel user that will make you STAND OUT FROM THE CROWD!

 

Meet Your Teacher

Teacher Profile Image

John Michaloudis MyExcelOnline. com

Stand Out From The Crowd

Teacher

Hello fellow Excel lovers/beginners/experts/users!

My name is John Michaloudis and I am the founder and Chief Inspirational Officer at MyExcelOnline.com and long time lover of Excel.

I hold a bachelors degree in Commerce (Major in Accounting) from La Trobe University in Melbourne, Australia and speak English, Greek and Spanish.

My journey in to Excel began after I graduated and have since helped many of my colleagues around the world understand this underutilized but powerful tool. Now its my turn to teach the rest of the world with these high quality and easy to understand tutorials!

I am currently living in the North of Spain and I?m married and have one cheeky boy. You could describe me as a football lover, wine & beer drinker, food indulger and occasional... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
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.

phone

Transcripts

1. Intro to this Power Pivot & DAX Formulas course: get guys and girls and this is John Michael Lewis and I am the chief inspirational officer over at my Excel online dot com and I lucked Teoh welcome you to these marks of Excel, Power Pivot and Dax formulas Course are having teaching. However, at my excel online dot com for the last five years, this awesome power pivot and Dax course is gonna show you how you can combine multiple data sources. For enhanced data analysis, you'll be able to create more powerful calculations using measures and Dax formless you be able to work with large dinosaurs that are over one million rose off Dada, you be able to import, consolidate and create relationships from Madonna to perform in hand analysis. This course is gonna make it confident and comfortable in the using power pivot on your small or large diet up, and you will be able to stand out from the crowd and show your boss that you are the real deal in Excel, which is going to leave two more promotions in the higher pay rights. So if you're in excel, beginner, intermediate or even advanced student, and want to learn this awesome and powerful excel feature that not many people now are they . Then I suggest that you click and your excel skills and professional development are going to skyrocket just with it one day. So I look forward to seeing you inside the course. 2. Installing Power Pivot in Excel 2010: in this quick tutorial, I'm gonna show you how you can install power. Pivot into your excel 2010 there. Stop application. So I have Excel 2010 he at its open. It's just our brand new workbooks called Book One. And we need to go. First of all is to find out, reached Excel big perversion or using I fitted to or a 64. Now, the duties that file and then help. And then here you can see that I have a 32 bit version and this is Excel 2010 so were necessary to beat. And we're gonna use this information in the Ural download. Now, in the description area, you have a link and that will go directly to the markets off office website where you can download palpable for excel 2010. Let's get into that website now. Okay, so we are in here. So here is the download link. All you gotta do is click on download. Now, the next step is important. We have a 32 bits for any 32 big versions. You need to click here. The 86. If you had a 64 beat excel 2010 application. Then you just need to click on here. But we have a 52. It's going to click on there and go to next, and all you gotta do is just follow these steps. As you can see here, I'm using Google Crime says, just downloading on the bottom left hand corner, depending what Web browser you're using. It's going down like this file that you need to open. I just clicked on that second open and it says, preparing between store. Well, they just click on next and just click on the default options and press like I. And then once you do that, it's going to install. It will take a few minutes and it's been install it on your computer, and the next step is to go and open your Excel 2010 workbooks. So let's get into a now. The next thing we need to do is activate the com, adding, To do this, we have to go to file and options, and in here you got a click on adding on the left hand side and a where the bottom manage, you need to click on com addicts and press go. So here's a list of the calm marriage you see have got power Pivot. That's one we just installed. Click on it. Press like I and you'll see on the top power pivot. It's there you can see on out ribbon many, and you have the awesome adding in Excel 2010 and you could start doing your pivot table analysis. 3. Enabling Power Pivot in Excel 2013, 2016, 2019 and Office 365: in Excel. 2013 2060 2019 and office 365 Power pivot is already installed in the Excel application. The only thing is that you need to click on the adding, so you get activated. As you can see up here, we don't have power giver. It's not visible. We need to go to file then options. And then under Adan's you to go under, manage here and the drop down click on com, Adan's and Prisco. Now this brings up a list and you can see here, Margaret soft power pivot for itself or you gotta dig is click on it, press I k. And then once with you that you will see on the top here that power pivot is going to be visible, please, like a And there you have a power pivot is available for us to go and become awesome in Excel 4. Import From Workbook: now I'm gonna bringing died up into power Pivot from an excel workbook. So we have our customer data as he up products and sales and they're all in an excel table . We know that because when we click in these, the table tools comes up, won't go out of it and go back in. You see that? So it's called Consult that workbook and we're going to import that information into power people. Let's close out of these and open a blank workbook. So we're here into the new blank workbook. It's called Book One. Let's go into power pivot and the Power pivot window. And from here you've got these options under get external data we got from database from Report you got from Is your data marketplace from data fees from text from other sources, and it's from other sources and you got all these relational databases. There's a whole lot there. That's what he had text files import died up from an Excel fire or people died from a text phone. Now workbook that I showed you before is in the next fasteners. Click on that and press next the friendly convention name we can call it excel or just call the Excel so they data except far path it. Just click on that and browse Teoh, where Outward Book is located. Now here it is. Here it's called Console that workbook DoubleClick, and then you get cheesier used First World as column headers. Let's pick up that because the first Royal Waas calm head, it's and then press next, and you have all these different tables that we saw before so we can choose which ones to bring in here. There we are, and then we can go preview and filter. So in here we can preview and filter, which is the sales shade here. What to bring in So we can say, Hey, if you don't want to bring in the units, we can just uncheck that and it's not gonna come in. You got to hear the clear road filters. We want everything. So let's press. Okay, now let's go in here, the product reviewing filter, and once again you can see everything that's going to be imported. You can choose what to bring in. You can feel tore out items as well. You can say, Hey, I want to see the Cabinet products. Take that out. It's not gonna bring it in. Let's go back in there and I'm gonna clear the filter. So I want everything in there and the same thing. Customer. I want everything in there. So what? A person out is finished. You can see that customer table, 10 rows transferred product, 20 rows. Sales 49 90 Sight. We're happy with that. Let's press clothes and you bring it in here. You see that? The customer table, the products and the sales. You see all the road numbers there, the records. Now we didn't have to manually write that. So that's just that another way to bring in Dada instead, from an Excel sheet, we can bring it in from an Excel workbook that sits on another file path. 5. Import From Worksheet: to start an example. We're going to load some data that in one worksheet now this is called consulted it data and we have a customer details. We have product details, and we have our silos. Details. Let's expending a little bit about each so the customer details has a customer number all the way down here. Its name. Address the city in the country. Now the product has a product. Kay goes all the way from 1 to 20. The product names. They got different names in there, and each product has a cost associate toe and the list price. Now the sales is a download that we got from our pay system, for example, and it's got a lot of data to go the way they uncontrolled down. It's got about 50,000 rows of that, and that's typical off a data dump that we get from a cannon software or management software or I t department. So we have a product kay here, and this is related to the product key under the products should here. We're gonna use that to do a relationship later on. So just mark these in yellow that it's related to another sheet The older date all the way down here. We have transactions for each that we have multiple transaction, for example, on the third or 1st 2000 and that's fine. Initially, it's customer number. Now. This customer number is related to the customer number here, and we're gonna do a relationship on that later on. So let's just marketing here we have the units and then a cells amount here. So this is our died a table here we have three different sheets, and now we're gonna do is uploaded into power. Pivot. So to do these, first of all, he's let's create a excel table for each of these here. So do that First control T just always good to have our daughter in a table. The same theme for the rest. Okay, they would have it. So let's go into our first ship and under power pivot Go to create a league table. So what it does is it puts it into power pivot, and we can name these that cliff and cooler customer. Now let's go back to excel by clicking here and do the same thing for product. Create length table. You see that here It's boring here. Let's rename these two products and it's got back to excel and sales and from here create length table. It takes a bit of time because about 50,000 rows of data that you go there 49 97 exactly, and let's name the issue to sales. So now that we've imported out data into power Piver we can make relationships and then start creating our measures and our calculations. 6. Import From Separate Workbooks: the other way that we can bring in dying to power. Pivot is if we have separate workbooks now. I separated the sheets before into separate work so you can see them here. You can see that the customer product and sales that just happen. One and they're all in on Excel Table and sunny. One seat. And it's the same thing for the other ones that you see the product here and the sales. Now we're in our new workbook. It's called Book One. It's gonna Power Pivot and Power Pivot Window. We'll go from other sources and skull the way down from Excel File Weaken Double clicking man. Let's put in a far path and putting the customer he is first rose, calm, hideous press next and in here. We can preview and filter President Chi and then finish and close it and it comes in here. You see their customer. We do the same thing, bringing the rest Excel file. Browse his first rounders. Calm head is next and we just say finish and it brings it in your seething that and finally this Getting the Siles click just the source type with Silas Fun way just ignore the filter database and then press finish and press close. So we have all our three work boots imported into power. Pivot, not receive these. We can go into the design tab and have a look at existing connections. And you can see the existing connections here, the data source and the fall path sites in my C drive and you can see it all the way of it . And then the product and the sales. Now we can double click in here, and he goes back into this wizardly Mac that just cancel out of that press. Yes. So under existing connections, we can have a look at our connections that we can edit them and we can change the far path . If we like, We can refresh if the data gets updated or weaken. Delete it from there as well. This was close, and they would have a 7. 05 CreateRelationships: we have our work. We call Consulate data, which we created by importing these three sheets into power. Pivot, weaken. Check that by going into power pivot and popular window, you could see the roll in here and they got a little link here, which means the link tables. Now we want to create a relationship for these three tables. So let's go back here to x out. And what I want to do is want to link that product key that is, in the sales to the product that's in the production and the customer normal it in the Siles to the customer number that's in the customer sheet. So let's go to the palpable when they go back here. To do this, we need to go to design and create relationship. Now here is where we credit look up relationship between two tables. So now was created the 1st 1 So the sales to the customer now the table here is usually the data table. So when I mean data table, we means the table where it contains all the sales values or the budgets, values or the inventory values. And we were gonna do the measures from so it contains the numbers. In example, here, the sales table contains all of our transactional history, and you could see that's what about 49,000 transactions it so usually in the table here we pulled in a table that has all of the transactions. It has a lot off rose, and that's what we were used to create our measures. So this choose tables and the columns is going to be. We're going to connect it to the customer. So the customer number is the column that's in common. The related look up table So they look up table is going to be the customer, and the related look up column is going to be the customer number. Now we can press the old and tab and and go in here and have a look so we can actually get out of power. PVA. And have a look in here. So it is customer number. Let's just go back in here. So customer number. So that's the look up cold. Now the look up table here, the related look up table. This has usually fewer rows, and it's where our customers products or calendars come from, and these are what we're gonna use in a pivot table to put in our rows columns, every port filter or out slices, and it has a unique value, say unique, distinct values. So when you have I many to one relationship, this is the one side, and then the many side is the table here, which has many transactions. So the related look up table will always have to have your distinct values and is going to feed into the table here. This first create and that's created that now to have a look. The relationships go to manage relationships here, and you can sit in there. The sales table is related to their customer table, so this is the one to many relationship here. Now we can editor, and we can delete it from that. It's close out of it. Let's do another relationship now when I relate the product key with the panicky here, so create a relationship. Siles Turkey and the product table, and then the product he there and Chris create. Now we can see that the two different relationships that we created and we can also go into the home tab and the diagram of you in here. So we have the sales in here. Let's bring the product down there and that in May. So if we click in here in there, it highlights that customer number in sales and the customer number in customer table are related. If we hover over here and clicking, they're the product in the sales table is related to the product key in the product. Now, another thing that when it's many to one relationship, is that this dot here means many, so many to one. So with era and is the one and again in here is our many to one relationship. 8. Create Relationships via the Diagram View: another way that we can create a relationship is within the diagram. You now or in the diet of you here lets go of the diagram. You and you can see our related tables delicious movies around like this. And sometimes it makes it easier to do it in here cause you can see all the columns that pertain into each table and before making relationship. Just want tonight that in cells we have product key and then product. We have the product as well. Now I've done that on purpose. Just make it easier to teach and show you that these tables are related by that column. But in a normal scenario, you may not be that lucky and have the same column name. We think relatable items, for example, Cells may be called product key by the product. May have a different names. That product. It could be called SK or Proud number or any other name. But for our purposes, I named these product Kate and product just to make it easier. It doesn't mean that I have to be. The columns have to be named the same. To be relatable. No, it's just to make it easier. Teoh teach this concept, so let's connect our tables. So I want to connect the product key with the product in here. I'm gonna choose a product key and then go to design and create relationship. This is already put it into the table area, then column area. Mel Mina. Look at the look of value or want to get product and the product Kate and Chris create. You see it's created there and we can see it. Now. Let's do the same thing for the cells and customer. Wigan. They use the customer number and customer number press, create relationship, customer customer number Chris create. Our tables are related, and now we can go into the fun stuff off doing measures and calculations and backs formulas . 9. Create a Pivot Table: Now that we have out three tables, all connected via power pivot and the relationship feature, we can create a pivot table. We are in Excel here and for go to power pivot on the popular window. We could see that the three tables are here, and they're all connected. As we said before this kind of diagram view, we could see the rock connected there. That's perfect. Now let's get out of this here and go back in Native Excel on the Power pivot tap. We could just click on, create a pivot table and put in a new worksheet so you can see here on our pal Pivot Phillies that we have the three different tables there now. If you weren't using power giver, then you can only see one table here in Native Excel and when you're using just standalone pivot tables without power, Piver. But we help you. But we can see the three different tables, and that's the power off this and help to take items from each off the tables and create our own pivot table without having to do any very lookups. That is the power of it here. So as you said sales is connected to the product via the product Kate and also sells connected to the customer by the Aspen Emma. So that's putting salesman him in the values area there. We said that the customer number is connected with sales. So as you can see here, the customer number is connected with a customer coming here. Now we're gonna putting the customer number. But because it's connected, we can take any field from in here so we could take the name, the address city in the country. Now let's put the name in here. Let's put in customer name and let's just put it here and we can see that it's take it the name off each of the customer and used the sales table to give us pay some of sales. Now, we also say that in the sales table that's connected to the product key. So the product a here so we can take any other column here that's connected to the product table. Let's take the product names. They go back in here and let's take a product name and put it in the road later said. So you see the product name on the letter inside here and all off our customers on top. Callous. Make this a little bit smaller and I can hardly at all this. Just a week. It might get a little beat better. Lookie, comma. Okay, again, How little this? We're gonna put it in the middle like that So you can see that by the relationships that we credit before we can take items from each of the tables and create these pivotal here. And that's the power we feed power. Give it. You don't have to use any V look of function or copy paste because the time was well connected. We can do our analysis, and it gives us the power to do analysis that we couldn't have done using the standard line pivotal feature. 10. Insert a Slicer: now we're gonna do is add in a slicer into this pivotal here in Excel 2010. The slices are like headed here within the power pivot film these Nan Excel 2013 in Exile 2016. These are not here. These are under the pivot table tools, So when you click in your pivot table, you got people have with tools on the options in Excel 20 City, it changed to analyze, so it's not called options. It's just a name change. Everything else is the same, so we theme here. You'll find the slice, as you can see here as well, but within himself, 2010. You can put the slicer directly from the field list all the way in this area here. So let's put in the country and put into the slices vertical. You can see it gives us a vertical year off a slice on. We can move it all the way there, and it brings up horizontally up here and what we can do. We can choose in here Australia, and it filters out the information that pertains to customers were in Australia got Canada , South Africa, United States and by prisoners. But in here, it clears the filter and you can see everything from the start again. Now, if you click in the country, he gives us a slice of tools options from in here. You can change the style just to make a little bit better looking, if you like. And the columns of two here, you can put in three columns for or you could just keep it like that so you can change that . You got to change the hive as well, so slices are cool and you should always use them, and it makes a report interactive and allows you to see the information that is being filtered. 11. Intro to DAX Formulas: So Dax stands for data analysis expression, and it is a new formula. Language in power pivot in power bi I Now there are 135 decks formulas, and 71 of them came out of excels function library. And there are new tax formulas being added each year. Now the decks apparatus are similar to excel. So you get the plus the minus the multiplication sign, divide and assange the power to the equals the bigger than and listening. There are two different places where you can write these Dax formulas. One of them is the calculated column, and that is inside the power pivot grid. We're gonna show you that later on and the second place where you can create these decks formulas using measures in a pivot table. And we're also going to show you that in the lessons that coming up now, some of the dax formless are familiar. That comes shredded of excels, function library, and you can see there we have some the maximum minimum, the count. If and that value, Captain eight. Many more. And there are also some new ones. More powerful formulas that have come out, and these are the calculated filter. All distinct count. Now we're gonna go through a few off those decks formulas in the coming listens, and you're gonna see the power off these super tax formulas. 12. Intro to DAX Formulas: Measures vs Calculated Columns - Part 1: now we're not explain the differences between measures and calculated columns. Now, first of all measures. Okay, now the name measures is seen in exile. 2010 now in exile. 2030. It changed the cattle in the fields, but even 2060 went back to measures. And it's there now, so we'll talk about measures as being that calculated fields. Now these are the Dax formless that he ad into a pivot table so they're only available inside a pivot table. You can only use them in their values area off the pivot table as well, so you cannot put him into the row column or report fielder. They have to be in the values area for them to work. Now. You're years measures when you want to aggregate calculations. Now you will be used measures when you want to create aggregated calculations, for example, some average or the average sales price across all your transactions. Now the measure will only calculate when a pivot Havel changes. So if you're using slices or you feel Torri your refreshing or rearrange the pivot table that that's when the measure will be calculated and changed, it's it's very powerful as you're making changes, the value changes, and that is super super helpful. Now a couple off measures that you can calculate you could do the average sales per day. You can get the cells versus the budget percentage and the change versus the prior. Just a few of them. There's a lot more measures it can do. It's very powerful now. We kept a lot of columns. You can only do them inside the power pivot Greg and these decks, formless that you add to eight transaction on rights. They say you have thousands of red dot up, and then you have a column that, and you want to calculate or do a calculation just for each right off. Dina. That's when he will use a calculated column You could only use the calculated columns in rows and columns were poor filter and slices in the pivot table, and also you can use them in the values area. Okay, the calculations are made on each road as a said. So, for example, if he had ah column that had ALS, the dates of their and then in your new column unity a calculated column and you want to say return me the week numb. So the week number, for example. For the first off, the 1st 2000 you put in the wake nom equals and your reference a cell and then press enter . And then he gives you the weak numbers for every transaction, not just for that. Sell that in all the writhing in that gives you for all the transactions. And it's a great way where you can get values for a transactional basis. Now these calculations are pre calculated, and they are stored this part off the file. So once do the calculation, it stamps the value and it stores it as part of the file. So that means that you know your file size may increase, especially have lots off transactional values there or rose. Now the answer is, they never change when the people terrible changes. So if you put your calculated column inside a pivot table and then you have some slices and you choose different dates for example, 2010 on between 11 2012 the values or no change calculated column will not change because they are standing there so they're not very useful inside a pivot table. Now, another thing is the answers being the calculated column that you have inside the power pivot greed there will only change when the doctor sores gets updated. So say your data sources connected to an outside source on every day there's new data that comes in or gets refreshed. That's when the values of or get changed. Also, the calculated columns can calculate the following, so if you wanna group data, then you will use a calculated column. And if you want to find out, what is your net margin or your net profit off each transaction? That's when you use a calculated column. You do your calculations that gives you the net margin of profit off age row off Diana. So two different scenarios measures calculated columns. It depends on what your result is. Won't you want Teoh use on what values you want to get out of your diet so either one is good, But mine measures more powerful because as you people trouble changes, then that measure changes. And that is a very powerful analysis tool to have 13. Intro to DAX Formulas: Measures vs Calculated Columns - Part 2: Now there are two kinds off formulas you can create using decks. The 1st 1 is a calculated column and that can only be created in the power pivot Greek. Now let's go into the Greek god of power pivot window and we're in agreed here, and you got the ad column. So just click in there and then you could insert a function that way there if you like, or just from in here, you can put in the equal sign and then poured in. They function. Then you can reference the columns from you may and press enter, and I just brings it around all the way to the bottom neck. So that's how you do a calculated column now the 2nd 1 used to create a Dax measure, and that's created in a pivot table. So let's go into excel. There is the first thing used to credit. Give a table. Got you work seat. Now, from in here, we can create a Dax measure so we can click in our table. He have sales and then go to new measure. So in here is where we create the measures within a pivot table. Now, Dax measures are calculated fields, just like in native pivot tables, but they are much better than calculated fuels and much more powerful. So there are two ways to create decks. Measures one ease as a calculated column within the power pivot greed and the other one as a measure within a pivot table. And I'm gonna show you in the next tutorials had a great these decks formulas for a scenario. 14. DISTINCTCOUNT() - Create a "Unique Order Dates" Measure: So now we're gonna create our first measure. Now let's go in now. Power pivot Window Here and in the sales table here we can see we've got 49,967 transactions. So that's different transactions here, but we want to know how many unique dates there are here. So instead, off counting age, transactional value, which we have here 49 97 we want to know how many times each day appears. We can do that now. Let's go into excel in the power pivot tab lets credit give a table and put you work. See, since credit measure, we could just go to a new measure in there in the taboo, namely cells, because it's selected or but you just select the sales name that right click and press at new measure from in there just like that, and this brings up the measure settings. So has a table name equals sales and the measure name. It's good putting their name that you can recognize is pretty unique. Quarter dates, the more the better. And in here you put in your formula so by person in the control key and then scrolling up. You could make this a little bit bigger sake. Just show they formula a little bit better. Now, let's type in distinct count. So you've gotta in Michigan chooser like that and we have three tables will have the customer the practice of sales. Now, if I put in see you for customers. See you. You can see him. There you go, the customer table in yellow and it's got all the different columns. So that's how we create a formula. We're referencing each table and the column within the table, Back to products P R o. You can see all the product columns. That and if I have sales, the same thing that now I can just with my Eric a go all the way down. And what I want is border date and then compressed Hap. So I want the distinct count for the Siles ordered islets. Pleasant parentheses. And in here we can put in a formatting number, currency, whatever. Like now this is a number, so we can do that and then put in my super if you like and press okay. So I could see that there were 4000 and 18 unique order dates throughout all these cells. Data here, which ranges from all the way from 2000 to 2010 now over and put in here is the customer name. So I could drop that in there in the row labels, and it gives us how many unique order dates pertain to eat customers. Well, now we can take that out and we can put in the product name if we like, and it gives us the values. They're a very powerful formula. And once the mission is credit, as you can see here, it's depicted by is calculated. There you can click on it and then go to delete measure. We got a measure settings to change it. And also you use Major Sue, create other calculations, and that makes a very powerful and we're gonna talk about that in the next tutorials 15. SUM() - Create a "Total Sales" Measure: So we have our unique order dates measure here and now. What I'm gonna do is create eight, another measure called turtle Sales. So let's just take all these out of here. Just drag it out on drag this out. I'm gonna right click in cells again and get add new measure, and I'm gonna call it Total Sales. And in here I'm gonna hit the control, but and with a mouse, make it bigger, so I'm gonna put in the some. So the some off what the table name is called saith also start tapping s a l. And he gives us the option, and I'm gonna some the cell amount, close parentheses. It's gonna be a currency and a dollar sign and other places and press. OK, so there you have it. The total sales mess up, it knows that it's a value to put to the record in it. So what I can do now is get the product name, which is in another table, and drop it in here, and he shows us the total sales for the product now in Native Excel. We can't do that. We can only do calculations in the table that worried. But in the power fever, we can actually grab in another field from a related table and bring it in here and use the same measure to see the results. So let's take the pregnant out and let's go to our other table and bring in nine in here and there you have it. So the measure, terror cells, is still working when we drop in a different field from a related table. Now that's the power off power pivot. 16. Combine 2 Measures to get "Average Sales Per Date": Now that we have two measures, we have the total sales measure in a unique order. Dates measure. We can see that by the calculator in here we can combine those measures to create a new measure. So let's first putting the unique orders in the values area dropping neck so you can see the unique order dates that we have there and then the total sales. So we want to get the average sale per date. To do this, let's go to power pivot and a new measure. And let's call it every sale, her date and in here, What we're gonna do is to bring in a measure. What we can do is press the brackets in there and you can see under M. It's depicted as a measure over there so we can put in the total sales like that and then divided by and then once again, the brackets and we complain unique or the dates and this is gonna be a currency and for us Okay, so they would have it. We have our average sales per date, and we've combined to measures to come with our new analysis column, and it just shows you the analytical power that is available within using power. Pivot 17. CALCULATE() - Filter & Show Individual Product Sales: have the countries in the road labels and a total cells measure in the fellas area. And I've got the park named here and I slice it so I just choose that slice and he shows me the different values it happen. Also hold the left mouse button and select the whole road like that. And I do the same thing there and he had been clear. So we want to find that ease. The percentage off sales off Mars bars Teoh the total sales. Now we can look at it like v Sindhu and unclear and find out that we were that It's hard, but what uses, they calculate function within power diva. So what it does is eat filters a measure so you can look at it like eight. Some if formula. Now let's bring it up here and I'll show you exactly how this works. So let's go to new measure. And then let's call this MAS by sales and the formula is gonna be calculate and we're good here. Expresses the expression means the measure, so they measure the we're going to use, and the fuel thing is what field up do we want to use so the filter can be bigger than greater vein equals two. So the expressing is our measure up. So let's putting out themselves. We've got out total sales here, and our filter is gonna be our products. So let's type in their fraud ukt and the correct name. What I want that product name to be. We wanted Teoh equal to Mars. So gonna put in People's Mars bar in double quotes because it's texts mas my like that. And then let's close the princess. So we are looking in the measure Court of sales and our filter is gonna be the product name which equals Mars passes. Just gonna give us the sales for the Mars bar products. The currency is gonna be dollars and press it. So we have eat Mars bars. How's it? And if we click in the slice up, this massive ourselves always stays fixed. It doesn't change anything, so that's pretty cool. But the total cells does chance, but that's that's great. That's that's what we want show. So we want the Mars ourselves to be fixed that all the time, so shows us the feel to sales for Mars Bar, and it's within that people table. Now we can putting that another calculation to get the proportion off Mars bar sales per country. So it's going to put in a new measure and putting their percent off sales. And the formula is gonna be our two measures to put an image up, open the square brackets and let's putting their the Mars bar sales. And we'll divide that by the total sales. And it's gonna be a percent. And Chris OK, and let's just go back in here and putting. I know that's more place measure was put in to their small places. Okay, so just makes it better to see that. So now we can see the percentage off Mars bar sales to total cells. They not to make this better. What? I'm gonna do these animation instead, 1/4 percentage of cells. I'm gonna blow up Mars, but sales and president just so he can be more distinguishable. So what I can do now is I can actually take out of the total salary from one out of here, and there doesn't have to be that I can just keep those two measures and I can also take out the Mars bar sales could take that out. So they go. We've created a calculation there just by using the calculator function, which is great to filter out measures to give us extra analytical powers.