Getting started with Power Pivot & DAX - 15 Useful Measures - FREE & Live Masterclass

hello everyone it is super awesome to see you all before we even jump into the stream and boy i do have a wonderful stream prepared for you today just tell me in the chat if you can hear me loud and clear awesome i could see from some of your messages that it is very good good morning everyone good morning good morning and good evening good afternoon wherever you are i could already see from the chat messages that you have all been trying to catch up and say hello to each other which is really awesome that is the whole point of our community and uh and i got a special guest for you i'll first introduce her because she's um she's quite finicky um so before this is this is our dog her name is excel and she she gets up with me every day morning and then she tries to actually go downstairs and spend her time but she was kind of going up and downstairs today so i figured i might as well just ask her to sleep right next to me in the office room so if you hear her bell jingling every now and then don't mind it and she might even actually ask me to let her out uh so i'll open the door if and when she needs that but let's jump into our stream here um big thank you for everybody for tuning in from whichever part of the world you are and in case you're having any audio issues um let me know but a big warm welcome to our power pivot and dax session and what i have for you is a very small presentation and then i will jump into excel files wherein i have built uh some sample data and then we have got uh 15 measures that i'm going to show you that will take you through a journey of power pivot in fact it has been almost a decade since i started learning powerpivot so i was quite nervous like you know how am i going to teach this to somebody who is approaching power pivot now or reapproaching it after a few years of break so what i did is i asked joe my wife because she she started learning power pivot and power bi a year ago for her new job uh here in new zealand and she has been kind of mastering it and doing pretty awesome things with her with it off late and some of the questions that she asks me i can't even answer i have to tell her you know go and refer to the italians so i i told her my agenda and i said you know do you think this is enough and she was quite happy with the outline so that's where i got my uh seal of approval and i'm really looking forward to our session um it would be awesome if you can um if you have not already figured it out please look at the video description i've put the sample file link there so feel free to grab them i'll come back to that particular topic in a minute but let's start our live stream with the standard message like tell me what you're drinking i've made a big cup of coffee and i even got uh some some water in case my throat gets a little bit itchy but uh that's uh that's what i'm gonna use uh and i even made an inspiring court eat sleep dax up there uh to keep us all going um all right um let us let us start by sharing my screen um i could see from your comments that everything is going to be with audio and everything um and you know we know these having a hand can uh enjoy it my brother and um sylvia is having a cup of uh coffee pooja is having tea uh it's pretty much a tea time well it's a little late for tea but you as i say as they say like in india it's almost never late or never really for tea you can have tea like any time of the day [Music] and arturo says uh i'm having a wine glass uh enjoy it cheers and uh oh thomas is having a hazelnut coffee well good on you and um let's get into excel here i will um before we get into excel i got like a small presentation that will walk us through what is it that we are going to cover set the agenda for us and then we will jump into excel um and uh prakash asks in telugu bhagavatu uh chalabag which simply means i'm doing really well thank you so much for that message and um let's um let me just set up my screen sharing on screen one okay so this is the sample file that we will be using i will jump into that in a minute but for now we will get into the presentation so that i can introduce our topic so today we will be covering power pivot 15 useful measures um it is we've got quite a bit of ground to cover um but the good thing is we will be staying in the same data set and covering these skills now even if you are attending this for the first time or you are coming in um thinking uh why are we learning this in excel the beautiful beauty thing beautiful thing about power pivot is it is a technology that can be used both in excel and in power bi so whatever i'm teaching you today you can take that you can apply both to excel and power bi without even changing a single uh character in the formulas that i'm showing you or the approach or the thought process so this is a very versatile skill to have as as a data analyst so what is it that we are going to cover i will start by exploring a little bit on what power pivot is at as a technology and then i'll uh talk a little bit about our data the the data that we have we will be using for this particular exercise and we will build the 15 measures and i will answer your questions so throughout the session you can post your questions in the chat window and i will check on them and i will address them as we make progress now before we begin i got three things for you these are about the downloads and how to ask questions and how to support the stream so please check the video description i have provided a blank data file this is super helpful if you would like to follow along in the stream and maybe take some notes or try to replicate one or two of the measures or even all the 15 measures for that matter but on the other hand if you want to see the final workbook you can also download that in the video description i've posted it already so you can download it and i will construct another file as part of the stream just so it won't exactly match with what what you see there but it does have everything and if you have a question please ask it with cue you can of course say hello or chat or share what you're having or how your day is going and all of that that is fine but questions please add a queue at the front so i can actually monitor them when i'm going through the messages and let's stay on the topic uh you are welcome to ask questions on other things related to excel or power bi but i may not be able to help you out immediately if you kind of stay away and if you have asked a question and i haven't got back to you please don't repeat because that can clutter the message box and you know not useful for others and you are you can support the stream by purchasing a super chat or a super sticker this is super helpful for me because then i can go and invest a little more on some of the other bits and bobs that go into preparing the stream like lighting or mic or whatever else and this is very easy to do all you have to do is go into that little buttons underneath your chat window and set up the super amount there like the way i have shown on the screen all right um let's power it up and let's just understand what power pivot is so power pivot is is a technology that kind of takes the concept of pivot tables and then add some additional features on top of it so that you could do more with your data analysis and it is a it is kind of a very neutral technology so you can take these concepts and then you can apply them for other business intelligence tools like power bi and maybe in future something else as well so that is what power pivot is in a way you could think of this as like your pivot tables but with a little bit more the first time i learned about powerpivot the way i approached it is i i started by asking a question because it's almost around the time frame of 2009 and 10 when microsoft released it powerpivot to the wider audience and i asked the question does power people really wear it under its underwear on the top you know like a superhero so that was the question that i asked i even made a youtube video with that question uh you could probably go through the archives of my youtube channel the older videos and you will you might even find it there so that was the question i asked there and in 2009 and 10 i played with it a little bit but i wasn't super impressed by it because i didn't really grasp the technological impact or the idea behind powerpivot at that time but some of my other friends and call and collaborators on the web like rob cauley they were all kind of latching onto it and they were utilizing it in a big way already but fast forward to 2012-13 and i have started using more and more power pivot in my work because i've been doing some consulting work training work where power pivot has become real big so i won't tease you more i will get into excel where i will show you so uh let's go over to excel and then we will come back to this presentation because because i thought powerpivot being a very complex technology is very hard to really learn and master in one stream or one video so you might require some resources so i have prepared a list of resources that will help you in your journey uh to power power pivot awesomeness so i will share them with you at the end of the stream once we finish that so we will go to excel but let me quickly jump into the chat window to see what else is happening and how things are going on there so all right we already have um started getting um some questions some of them are fairly serious like how do i make certain tax measure we will get into specific dax related questions once we actually jump into the data we started building we understand the vocabulary and everybody speaks the same thing and then we go from there meena asks is it possible to see subtitles as well i'm not really sure how this would work because it's all live stream and i haven't got like a stream of staff members who are typing subtitles or anything the subtitles are usually automatically generated by youtube and google so you will have to see if that is available but i don't see suppose that is available there um [Music] so nitin asks what is dax like you've been saying dax dax what is dax dax stands for data analysis expressions okay so it should be really dae but they went with data analysis expressions and then they took the x from that and made it dax so that's what it is it is like a is formula language that you can use to speak to your data and ask questions on what is this what is that and it will return the answer we will see more of that in the excel file that i'm showing on the screen in a minute and um so i don't say so is it in both power pi and excel yes you can use whatever you're learning today in both pixel and power pa so that is there and okay there are some very very specific questions uh which we will get you in a minute but for now i'm going to jump into my excel file so that we can start actually understanding the data set and build our first power pivot table see the journey and then i'll come back and see more of your questions big thanks to everybody for your love and support in the chat window i could see so many uplifting positive messages there and that is really awesome to hear and and and you know it feels it makes everybody feel good so thank you so much and um let's uh jump into excel i'm just gonna turn off my face so we can all look into excel but i will come back and show my face from time to time so here we have our raw data file this is the blank file that i provided to you in the video description as well feel free to download and follow along so here what i have is a fictional made up company called awesome chocolates i'm trying to use the same data set in most of my videos so that people don't have to figure out what column is what everywhere so you will see more of the awesome chocolates company from here on so it has some columns and some information you could think of these like individual transactions it's a very weird company wherein they were they have 10 or 12 employees and all they do is every day they pick up the phone they call their clients in other geographies and sell them boxes of chocolates whatever sales they do on a daily or weekly basis they will put into this kind of a data set so you could see that for example here gigi sold caramel stuffed bars worth 4 452 dollars 281 customers and that is actually coming in from 179 boxes so that's the individual record and it kind of goes on for 600 rows but you can you could have any number of rows it doesn't really matter if i want to know more about any of these information like you know what what what is the actual cost of this box or something that information is not available here this is usually how businesses maintain their data as well parts of data is in one table other parts of data is in another table so how do we take this and and link it up so here what i've did done is i have added actually another hidden table tab you can unhide it and see that it's called other data and here i got two more tables i got my product table that gives me a bit more detail like what kind of product this is what category is it a bar or a bite or other types of product and cost per box how much is the cost for us to produce that kind of a box we also have another table where the geographical information is explained a bit more like india new zealand australia are in apac usa and canada are in americas and uk is in europe so this is um useful if i want to ask questions like you know what is our total sales in the apac region then i can use this table to map it out so this is how usually our business data looks like we got a bunch of tables each telling us a part of the puzzle and what we will now do is we will start building the power pivot we can kind of do it right away but we will start by doing one of the best practices which is to turn this data into a table so we'll just press ctrl t on this and that will make this a table you could see that i've already kind of made these other two tables my product and geography are already tables this is product table and that is your locations table i'll go here and then i'll simply tell this as a sales table so we now have three tables sales product and geography okay so time for some squiggly and fairly ugly drawings i'll go to a new worksheet here i can just uh do a bit of drawing so we can kind of understand data from a bird's eye view all right so new worksheet i'll just disable gridlines here and i'm gonna go to insert and let's just see if i can actually draw a box so we can imagine that our data has three tables right so i'll draw three boxes and move them around this table is my sales tab okay so i'll color this in bright yellow and let's just make this nice and big and and then these two tables are my product in location so when you have multiple tables you could kind of imagine these tables as individual boxes sitting somewhere in the weird mind of excel or power bi or whatever tool you're using and even though we didn't really specify they are kind of connected to each other so it's a good idea to at least have the mental picture you don't have to draw it out anywhere or you don't have to explain it but you know have it somewhere so that you know exactly how these things fit in so here i can go and connect my sales to product by just drawing a line you know we're not really connecting anything we're just saying this and this are linked up like that so then when i'm seeing this i can see that our sales table and product table are related to each other on what basis based on the product name the name that appears here if you follow that name to here it will give you more story so i can go here i can see caramel stuffed bars i can go here i can find the stuffed bars it is a bars category product of 5.7 per box so given the cost and given the fact that gigi sold 179 boxes i can then calculate the cost for all of these 179 boxes which is 179 times the cost per box and once we know the cost once we know the amount we can go and calculate other bits like profit or profit percentage and all of that so this is where that diagrams will help now in the world of power people there is actually a technical name for this they call this as a star schema wherein you got couple of tables and they're all connected in this case it is called start schema but in general this is called like a relationship diagram wherein it explores what connections are there in the tables let's not get too technical here we are just trying to understand data from a higher level perspective so this gives you that view now let's go back and and actually start building such relationship in excel so we can go to our data ribbon and from here there is a relationships button you can click on that and you can actually set these relationships up so we'll say this dialog is fairly small but you can understand what is happening here i'm saying disable sales product column needs to link up to product stable product column okay so that's the relationship we will click ok this is the first time if you do in in excel what excel does is it will construct the relationship it will also parallely build a data model which is like a mental model that excel uses to deal with power pivot so it will already construct that bit for us let's add another relationship which is my products sorry which is my sales geography maps to location geography so we have two relationships sales product goes to product sales geography goes to location and then we close that all right now ladies and gentlemen we will construct our very first power pivot so we'll say i'll add a new tab i'll call this as pivots and simply insert a pivot table now here is a secret there is no such thing as power pivot when it comes to excel your regular pivot tables are really power pivot tables all you have to do is know where to find them and enable the feature the trick is when you try to insert a pivot table from your data excel will by default always try to make the table from that data but when you already connect the tables and then just go to a blank screen and click on pivot table excel will say you haven't picked any data what pivot table should i make oh wait a sec i got a data model in the workbook i'm just going to use that so it will just use the data model which is nothing but those three tables and the connection between them and it will set up our pivot table so it will give you this on the field list here you can see that it has already listed all these three tables as available and now i can go and build that for the purpose of this example we are using the data that is already sitting in excel but theoretically well not theoretically really you can just use data that is anywhere and bring that into data model directly without even having to type in the numbers in excel okay all you have to do is use tools like power query to connect to the data and from power query you directly push the data into data model so we will go and we will build something very very simple all i want to know is by product how much we are selling so i will put product into the row label and add amount into here okay whether you believe it or not this is a power pivot table it doesn't look anything more powerful than a regular pivot table but this is power pivot table because the calculations and the tech underlying logic everything is flowing through the power pivot engine in the back end of excel in starting excel 2013 microsoft has kind of bundled power pivot into excel so it was already there all the time all you have to do is enable it and start using it okay i'll stop here for a bit let me quickly go through the chat messages to see um and exactly what is happening so this is liquid steel asks must data reside within table format in order to be used by powerpivot or can data reside in plain cells it should be in the tables because otherwise excel won't read i mean it's not exactly this or that i think with newer versions they might actually change it but i have always used it in tables like i said it doesn't even have to be in excel so if you are bringing in from a network file or something you would simply load it through power query and connect it on the on the data model level so you don't have to go through these steps maybe at the end of the stream i'll show how to set up the same kind of thing in in power query because it is kind of trivial and i've covered it in other videos i'm not really showing that [Music] so there are some more questions on that theme like bala ganesh asks the same thing and michael asks does it matter which table is on the left side and which table is on the right hand side of the relationship when you create it doesn't matter excel is smart enough to figure out which is the because when you define a relationship in the world of data there is actually um like usually the relationships are either one to one or one to many so there is a one side and then there is a money side and excel will figure out what is what based on on the nature of your data and and everything you don't have to spell that out um but if it helps you may want to do it okay so that is uh one thing um and acts 89 asks mr the create relationships button whatever where is it um oh well uh we all miss relationships but here is the data ribbon and from there there is a relationships button it is quite small on my screen depends on how your screen is layout laid out it would be bit but it you you are looking for in the data tools area there would be a via table with two things that's the relationships button all right [Music] so [Music] is powerpivot available in excel 2016 if not which version it is available in 2013 16 office 365. as i said since 2013 microsoft kind of sneakily put it into excel without telling anyone so it has always been there uh for a good past now so check it out uh the relationships button should be there unless you're using like a student version of excel even then i suppose it might be there um and a big shout out to victor for uh for our super sticker victor thank you so much for supporting stream and there are some people saying it is a little too fast on the click side that's because what we are doing is we are just setting up the data i'll slow down and there is a lot of repetition of the concept not too repetition but at least you will see the same concept applied in two or three different situations so it will sink in better and there is sample file so feel free to grab that and uh and go through that over another cup of coffee i mean it's not an easy technology power pivot to learn so i recommend that you give it uh some serious time and attention if you want to really learn and use it for powerful things in your life what i will show for sure is building some solid useful things right out of the bat but you can take them and extend them further stefan says is it being recorded can i watch this again of course yes you will be able to come back to the same youtube page where you're watching this and and repeat the stream or review that again okay um so there are more questions which is like jumping ahead because we have we are still building let's get into excel here again i'm just gonna hide the messages there are uh some more peop please keep them coming and i will go back and address some of your more some more questions in a minute but for now let's uh see that here when i'm creating the product and drag the amount i didn't tell excel i want to sum it excel kind of assumed that oh this is amount column the default behavior for numbers is summing them up so i'm just going gonna sum it up and and show that total to you so that is what excel is doing this is a kind of where excel without use specifically saying yes or no excel will figure out an implicit logic and write something for you so the technical term here is it's called an implicit measure okay but excel also offers a way for us to kind of tweak and say things that that we want when we are calculating so that is where the dax language comes in and from here on we will actually write explicit measures or the measures in other words and create the necessary dacs okay big thanks to bhartram as well uh for for the super sticker thank thank you and now let's see how to create a same measure the sum of amount but by writing our own formula okay at that point you may not find it super advantageous because we've already built the sum of amount why do we need to do it again but this will immediately prove to be very valuable skill and once you know that then you can see how that will all extend up so you can in the pivot table fields list right click on the table and then there is an add measure option so this is how we will be adding extra dax so we will add measure this opens up the measure screen and it will ask you what name you want to give this i'll say this should be total amount okay and then here we will write the formula here is a little handy trick which i find it useful when i'm presenting but maybe some of you might find it useful especially if you're writing or clearing you need to clear your mind you can hold the ctrl key and then scroll on your mouse crawl wheel and that will change the font size of that box okay so this is what i'm doing to make it big so that you can read it better but at the same time you know it's always useful for us to read the text bigger on the screen uh when we are working on formulas so what we want is we just want to sum the total column so i say sum and and then sales amount okay this formula will sum up the sales amount column right it's a very very simple formula you can even kind of think uh it does look like my excel sum formula there is nothing new here and that is a possible and an easier way to understand this language because the syntax and the way you write these is fairly similar to how we write excel formula so it always looks like this but it does get progressively complex as we write more things for now it does it is a very very simple straightforward plain english formula one of the advantages within this screen is when i write the measure i can also say how i want it to be shown on the screen so i can say it needs to be displayed in the currency formatting because it is currency values and click ok if you're not sure whether your dax formula would work or not you can use the check dax button and that will give you an instant feedback on whether you have written something that has syntax errors or not okay so we will add that and you can see that in the sales table now i have a fx added called total amount okay so i can click on that and it will be added it'll be exactly same as our sum of the amount column but it does now have that little currency formatting applied on top by default so this is how you can both specify the logic and the appeal uh on and the appearance of that in one go so you don't have to right click and format or anything the beauty of this is irrespective of which pivot table i use the total amount in it'll always have the currency formatting added whereas if i do it the formatting way here i'll have to do it again in other places so total amount is our very first measure very very simple one but we will we're kind of making progress here okay now let's go and drop the original sum of amount so we will now look at just the total amount and we will do one more of these so that you can get a sense of how this works so this time we will say total customers okay and some of sales customers and that is my total number of customers that i have sold to because each transaction we sell x number of items to y number of customers so we are just adding up that number and this is a number that needs to have a thousands separator so we specify the behavior we specify the logic and add that okay another advantage you may have realized by now is because we are giving the measure a name how it should be called that is what excel will use here so it's not saying something ridiculous like tote sum of amount it is saying total amount so it does make more logical sense when you have that in the pivot table so we will add that again that also comes up total amount total customers now let's take this up one step further okay let's see what is happening in the chat place so nathan asks my table still reads as table 3 instead of sales this could potentially happen if you made the connections before and then went ahead and changed the name because sometimes excel is a little not excel in this case power pivot is a little late to catch up on the changes that are happening on the excel side um and if you could try to refresh all and that might trigger the change otherwise you'll have to rebuild the data model which is a little more like three more clicks than what we did but give it a try and then let us know um okay so it all seems to be going nice and we will now go and add our third measure which is average amount and you'll see where this is going very soon it is again a very simple measure all we do is add measure so you can right click on the sales table and add the measure and this is average sales so just as we are using sum of sales we could also use average of sales so we'll say average sales amount okay if your table name is table 3 or table 27 just use that name you don't have to use the sales as a table name you can really call it whatever you want so average of sales amount and that will give you another measure in the table and you add that it will show up here now as you do this you realize this is not helpful at all let's say i want to really know how much is an average customer purchasing that is what we wanted to calculate but here what we ended up calculating is an average of all these numbers for a specific product so that itself in is not terribly helpful or relevant for an analysis perspective so while this is a good measure to calculate it is just not useful in in in our data analysis situation what i really want is i want a number that is this divided by that okay so we can call that new thing as amount per customer okay so we will add this new measure and we'll say amount per customer and this is where now at this point i need to go to my full face so i can show you the props that i got imagine your amount as as a lego brick so this is my orangey sorry green lego brick right likewise you have also a total customers a black regular brick so you got two bricks and each one doing its own thing so this is doing my total amount total customers now i can create a third complex brick that has both yellow green and black like that by just combining them this is what we do right we build individual things and then we combine them to create something new same thing can be done within power pivot we don't have to redefine the same logic every time once you create a measure it will be part of your data model so it will be part of your assets like it is there and i can use it on its own or i can take it and mash it up with something else to create something new altogether okay so this kind of translates into a world where as a data analyst all you have to do is build some basic assets things that are like two by twos and one by twos and two by ones and then you can combine all of them and you can create something completely beautiful or fun in very short amount of time because you every time you don't have to go back and do this so over to excel wherein i can actually uh show you what that means but a big thanks to aditya for for the super sticker there thank you aditya and let's uh jump back in so we are here amount per customer i don't have to write the formula as sum of total amount divided by some of customers instead i already have that logic built in my two measures so i can open square bracket and then say total amount which is the measure that i calculated divided by total customers okay now what i found in excel power pivot is it's fairly annoying i can kind of zoom this so that you can read it better so that's what i've been doing but the moment i do anything and press tab xl will take the zoom back to zero percent so i think it's like a ui bug there but this is my amount divided by total customer so i don't have to write the individual logic again i can reuse the logic that's the point i was going for we'll make it currency with one decimal place and then add that and let's load the amount per customer into my pivot so i can see by each product how much is the amount per customer that they are spending we could then go and even apply some of the usual pivot stuff like largest to smallest to see which products are bringing in more money per customer so spicy special slims is one of our products and it is bringing in as much as 32 dollars per customer at the lower end almond choco is getting me only 19 per customer right and at this point you might think ah this is good but what if i just want to see this i don't want to see any of these other things well you can drop these things i won't really drop them i mean i'll drop them for now but i really want to keep in mind that they're all still there so even if you get rid of other stuff from your pivot tables this thing is still calculating still showing up uh you may have to restore the sort order because kind of pivot table was rebuilt but it is it is always working and it works perfectly it is working for all the products you can kind of even add a slicer on something else like geography and go into new zealand and then see which products the new zealand customers prefer and love more and then you can see why chocolate there or what about india dark chocolate as much as 89 per customer or canada or australia for that matter and and that will give you a completely different view of the data because the calculations are happening at that level okay time for our second prompt so you might be thinking uh how does all of this work right we got our data and now i'm only looking at canada data like how is this really happening behind the scenes so this is where i got two props to kind of explore and think so you could see this as a scientific calculator and you can think of each of your measures as a little calculator right so it is doing it's a calculation does something some logic and then shows you the value so that's what each of your tax values are average amount amount per customer is a logic that you've built a very simple calculator but that's what it is doing it will give you the total right but before your numbers can actually go into the calculator imagine someone comes in and then says hey don't look at all the numbers just look at only the canada data and then tell me what that that number will look like so imagine something else kind of triggers in the middle that is what a filter is okay this is my t filter that we use to strain the tea leaves when we make chain in chai at home so this is the other other thing that kind of works so in a way the the way you can understand power pivot is it's kind of like a two-part act part one is filtering data and part two is calculating so every time any number that you produce on the screen whether you're doing it in excel or power bi for that matter there's always these two things that act so this and this is saying give me only the data for that current situation and then this will calculate the values for you all right i won't over simplify this we will go back to excel and i think this will all make sense uh and if not you may have to kind of take a couple of steps back watch it again or do it a few times and then it will immediately trigger in your mind um what is going on okay so back to full excel now we got our canada amounts per customer and and i can go and multi-select or do whatever other things that i want for now i'm just going to delete the slicer so we are looking at everybody and we will focus on more dac so we got amount per customer calculated that is our fourth measure total amount total customers average amount and now amount per customer okay so time now for us to bring in some additional columns remember we got other columns in the product table right we got our cost per box so let's say we want to analyze how much is our total cost now the way total cost can be calculated is here i got my product id and i want to know what is the cost of product so that i can then multiply that with how many boxes we sold cost per box and then how many boxes we sold to get the total amount right that's the logic logic flow that we need to follow okay so this is when you may have to go and really add an additional column now if you are doing this in excel we would be tempted to go here and say something like vlookup or xlookup look up the product go to the products table and then do that and get the cost per box here and then multiply that with number of boxes but because this data is already pushed into power pivot and we have connected it we don't need to do this we got better ways of dealing with these kind of situations and that is where we will use the manage data model button now i'm not really sure if this button shows up in all versions of excel and if it doesn't that doesn't mean you don't have this feature it is always there it is just that it is hidden away uh so that people don't accidentally probably get in there i don't know exactly why microsoft would do that but if it is hidden what you could alternatively do is you can go to either developer or from home options and then add-ins so whichever way you prefer you can go there and then click on the com add-ins and enable the powerpivot for excel option okay this is a little too much so in case you're not seeing that button you can kind of ignore that bit it's not super important but you know if you can add that that will give you another uh another way to talk to your data and manage it better so i'll show you but you can always go for specific instructions you can google or search that but it is fairly simple as i said go developer com add-ins and enable powerpivot you may have to either save and restart excel but i don't think that is necessary but if it does you'll have to do that so we will go and uh click on manage data model and this will open up a window this is similar to jumping from excel to vba right you have excel and you want to see the code you go to the view code and then you see the vba code power query we do this we go to power query window same for data model you use power pivot window to manage the aspects of data model we got a few super stickers so i'm just going to shout out these names um for the super sticker sorry if i pronounced your name wrong and um trisha for super sticker big thanks to both of you and uh i think manjunath as well thank you manjunad for your support and cheering brother and yes thank you and so we go to this little powerbeat window and from here i can manage the aspects of data in a better more controlled manner okay it's not necessary that you have to go here you can do a lot without even getting into this window this is why i said if you can't go there that's not a biggie but certain things like adding columns this is very useful so i can see that here it is kind of similar to our excel but it is only giving me one tab per table each tab gives me the view of that table data okay and from here we can add a column which will give me the total cost so that is what we will do for now as you learn more power pivot you will find that there are advanced and better ways of doing this without even introducing a column but for now when you are starting the journey this is a good way of learning and understanding the language so we can add a measure we can also add a column but whichever way you're going you will use the same language the dax language to specify this so we'll add a column and in this column i'll just double click and name this as tot sorry cost per box and this would be equal to right um whatever is the product cost for this product that is shown there okay in excel we do look up and stuff like that because the tables are already linked to each other i can just use the the relationship feature and all i have to say is related this is a special word in the world of dax product cost per box so when you write it like this power pivot says so you want to know what is the cost in the product table for this particular product that is related there so it will go and do it for fruit and nut bars and then it will go for peanut butter and caramel stuffed salties and all of that and it will give you that cost item here so we get a cost per box now and then the second one will be cost just as we have amount cost is this times that so this can be easily done by adding this column which is sales cost per box times number of boxes so a simple arithmetic operation that will tell me how much is the cost for that particular transaction so here my gonna sail to india he brought in 3682 whereas the cost was one two four six it's a bit small on the screen but we you know you can understand the concept so that is uh how you introduce additional columns into your data model again you don't have to do it you can use vlookup or other other techniques that you know to introduce these columns into your data directly you can also do this at a back end level through power query or sql or any other things that you have got going on so you don't have to always go and add them here but if you are not able to do it anywhere else then this is the place where you would do such things so when you make these changes just close the window and go back here and you can see that cost per box and cost are now part of our date model they will appear here okay excuse me i just want to shout out to pedro and guy 3 stefan kumar and mari for your awesome super stickers and support on on the stream thank you so much so now we got my amount uh cost information added i can go and calculate total cost as well which will open up the doors for even more interesting and powerful analysis so i'll go right click add measure now all this while we have been adding measures on the sales table but you can also add measures on product and location table and here is a secret it doesn't matter where you add your measure you can put them on any table and excel will still calculate the answer excel in this case powerpivot because the way this works is the measures are not really measure is like a definition of business logic so it doesn't matter where you put it it will still work out the way it it should and and give you the answer but it is a good practice to put them where they belong so that you are not confused and others are also not confused okay so we'll add this measure and this measure will be total cost and this is nothing but sum of my sales cost okay we'll add this as a currency and i can now see how much is the total cost per each of these things i can add total amount so we can see that fifty percent dark bytes is hundred and eighteen thousand costing us but hundred and nineteen thousand is their own so in terms of profit it is quite low whereas this one has like seventy percent dark bytes thirty thousand cost hundred 000 is the sales amount so it is quite profitable so this is where you might think hmm maybe it's easier if i calculate the profit itself so let's go ahead and do that um and this is very easy to do because we already got the basic ingredients right total amount total cost third composite measure is total well i'm just going to call this as profit and and this is going to be total amount as you open the square brackets you will see these measures pop up and you just have to tab out i'm not using the tab thing often because it kind of resets the zoom factor on the screen but when i type it myself i always use that and i'll just set it to currency and we will add profit here i can see my profit information i don't have to see anything else so i can drop some of these other columns so that it's tidy on the screen and we are looking at profit if you want to calculate profit as a percentage so that you can see which are our most profitable products i can do that but before we do that let's switch it up and go into a new pivot table so that you can understand the true power of the power pivot okay so i'll go and add a new sheet i'm just going to call this as more pivots and here i'll put it now as i'm as you may have already realized the downloaded completed workbook doesn't follow this structure it kind of has everything in one place but same logic same setup we will insert a new pivot table here this time i'm gonna just put it here this time instead of product focus let us look at the people that we have and understand what is going on with them so i'll add my sales person and now i want to understand which sales persons are most profitable okay so there is a blank sales person i wonder what is going on oh there's some blank rows at the bottom of my table um yeah it doesn't matter so we got our our sales persons here and now i can see profit for salesperson so even when you defined the measure while looking at product side of things what we did is we kind of defined it in such a way that we were not really talking about either product or geography or anything we wrote logic in such a naked and simple fashion that i can now go and look at things uh for anything else so i can look at my sales persons i can look at my geography i can look at combinations and anything like that and i can get the answers so that is the true power of power period once you build a measure like this little complex lego piece that has both green and black in it you can then take that and put it anywhere else and use that in that context so this opens doors for some amazingly powerful analysis and insights into your data we have got quite a few things going on in the chat windows i'm just gonna quickly catch up on that um big thanks to aditya abhay our single super fan of your work thank you single thank you so much for your love and uh durva and prasanji thank you chandu thank you thank you all and marry whitney thank you what a dedicated educator you are uh it's really humbling to hear all of this love and appreciation i i'm really enjoying this and i i i just enjoy talking about data and helping people so thank you so much for all your support thanks for uh staying in the uh in the stream all this while and enjoying and uh yeah mark as well for supersticker thank you all now let's see if there is any questions so costco says is it a good practice to create columns in a model every time we need some analysis so this is where when you when you have small sets of data like what i have here is just some made-up data with 600 rows so there's not much and boy it has gone real cold now um when you have small sets of data then it doesn't really matter whether you add columns or do whatever you want you know whatever works for you you do you but as your data grows bigger because power pivot is built as a technology to be like a beast okay we are using it at a very high level we are only scratching the surface there but it is really the engine behind some of the more advanced data analytics and analysis and reporting that is happening everywhere in the world like you know big big companies use this so when they use it for their gigabytes and megabytes of data you don't want to half assedly add columns or think do things every decision will come with the cost of both processing and time and waiting time and cpu cost and all sorts of other things so you would need to carefully evaluate your decisions and in your in your journey as a data analyst chances are you will grow to that stage where you will have to make those decisions so what i recommend is being mindful of the actions you are doing and see and at least constantly thrive to improve that so i'm not saying adding columns is good or bad all i'm saying is there is a place and time where you may have to evaluate should i add column or should i do this somewhere else and and all of that will be things that you will have to consider okay i'll leave it there i'll come back to some of these additional topics on my resources section where i'll recommend to you places where you could go and pick up more skills on power pivot because power pivot is an extremely powerful technology and it requires proper guidance to be able to uh take your skills to a good level where you are confident of things that you are doing and you are able to do the right things all the time so it does require a lot of practice and learning so while i'm providing as much as i can but it's it's a stream so there is not really very far that we could go i want to set that expectation as well thanks and carol for for your super sticker and thomas for for the sticker as well let's see um vasantha asks how did you get the slicer on the play page i'll show you again here on this thing just uh right click on your pivot table fields list on on on an item that you want slicer so in this case let's say we want to go by geography i can add a slicer i'm just gonna add a slicer from my region here in fact so that you can see that we can actually slice into one of these regions because they're linked i can now look at profit by apac or americas or europe and then see that as well so right select the pivot right click on this field whatever you want and add it as a slicer i'll just delete the slicer so we look at everything and we're not confused by some arbitrary slicing that is going on on the screen okay so we built our profit measure let's go and build the profit percentage measure because then i'm not really swayed too much by this or that i'm really looking at as a percentage of your sales volume how much profit you're generating okay so right click add a measure in a way profit percentage measure is like amount per customer but i will introduce some new concepts now so profit percentage as a measure is equal to now technically the definition of profit percentage is profit divided by total amount right now as you have seen just now from vanitas question uh sorry vasantha's question um when you when you add slicers you can get into situations where maybe there was no amount no profit so we can also get into zero divided by zero situations because the profit is zero amount is zero it is possible so so what you want to do is you when you're writing things like this you want to see oh would it be like sometimes it can get into an error so there is actually a safer divide option in powerpivot so it's called divide i wish excel also has this and so we'll simply say divide numerator and denominator and you can leave it at that alternative result you can provide if you don't provide it will be defaulted to blank so we'll just leave it like this divide is another operation that you can use within dax it's like another word in the dax language so think of new language right imagine your you just landed yourself in france or andhra pradesh or some other strange place and you're picking up the language so we've learned the words like sum average and now we are learning the new word which is divide you've also learned the word related okay and with these words itself we are able to speak so much and get things done imagine if you learn all the 500 words and combine them then you will get more and more but we'll just do this and we will do a number percentage formatting one decimal so that we set the display behavior for this which is it should always be in a percentage format and now i can add my percentage i'll see at a profit percent level how we are doing overall we are like 60 profit which is extremely good maybe this is what i should do right instead of doing youtube i should start selling chocolates uh just kidding so here i have got my sales persons i can sort these i can kind of say who are our most profitable geeky chess and curtis is one of our lower profitable employees for this particular set of data so this is profit percentage now let's say let's let's get a little more interesting here we are doing some analysis like this and i want to define some arbitrary profit target we'll say anything more than 55 percent target profit is good you know for purpose of this exercise so i want to see which sales people have met the target and who haven't met the target of 55 so here i can add a measure time to learn a new word so we'll call this as profit target met and we can use the if word if profit percentage is greater than 0.55 yes no okay so we are saying if my profit is greater than 0.55 or in other words 55 percent the target has been met so i want to print the word yes otherwise i want to print the word no again if is the syntax here is exactly same as xle formula but it is used in the context of dax and we just leave the formatting as general because this is a text one and here is a kicker you can now print words into the pivot table right so this is another fun way to use and i can see uh everybody except curtis advani has met the targets but this is for all data now if i get into an individual product category so i'll add a slicer on my product category and if i look at for example bytes you can see that certain people have not met their profit targets for the bytes product category whereas bars pretty much everybody did for other again we didn't in fact the profit target met as a measure we have defined and we are looking at it from people perspective i can then take this and look at it by product or geographical perspective and and see that as well because that measure is defined in such a way okay so that is the if formula now we'll add a little bit of fun factor to this uh you can you don't have to use the words you can even use the emoji to print in into the pivot table again there is almost no practical immediate applications for that but you will never you never know you might find things useful like this in elsewhere like i've always been doing some of these in my power bi reports because it adds a little bit of visual indication of what is what so if the target is met what i want to show is i want to show the thumbs up otherwise i want to show thumbs down you can go to a blank cell and press the windows key and the period key dot key to open uh the emoji keypad i think i've shown this particular trick in few other videos and streams so i'm not going to go too detail into this and find out your thumbs up and thumbs down emojis it will be somewhere in fact it is somewhat tricky so you can just type thumb and it will search into emoji keypad so that is thumbs up that is thumbs down so you type these two emojis select them and copy into your clipboard ctrl c now we go here add a measure and then we will say profit target met i'm gonna call this as emoji measure and if same as earlier which means profit percentage is greater than 0.55 double quotes and then paste these words and just adjust your double quotes as needed okay the only disadvantage of using the emoji within excel pivot tables is they're not colorful like the way you would normally find them but it is still useful and fun to have these things so we'll define something like that and we will add that little thing so i can see thumbs up or thumbs down you can then add some conditional formatting to change the color of thumbs up or thumbs down uh on on this while we are on the topic of thumbs up and thumbs down it seems corny but hey we are on youtube so feel free to give this video a thumbs up if you haven't already done it uh but i know most of you would have and even if it don't it doesn't really matter but i thought you know that's a nice segue anyhow let's see what else is going on in the chat and then i'll come back and and add few more measures we have made terrible like tremendous progress here in terms of what what we could achieve within powerpivot in just 70 minutes so i just want to say thanks to thomas for the sticker and i suppose this is tamil or malayalam i can't really read but big thanks to you and vasantha for for the sticker and mr for a super sticker as well um big love to all of you uh for for for watching learning and you know cheating me on this on the stream now let's see what is happening in terms of questions so here is an interesting question jose or jose asks i have some work wherein i have got a unique id but this id is unique only within the company and they are working across multiple companies so what happens is so you got like a unique let's say even the pro product or customer like you know what we have what i have shown on the screen ram mahesh now ramahesh is unique in our data but imagine we got like three other companies and ram is selling in all the three companies and i got all the data then no longer ram is unique because there's multiple roms but everything is still you know if i summarize it'll get like this whereas i want to see ra mahesh of awesome chocolate company or whatever so how do you establish like this so these kind of things in the world of database are called composite keys where you have unique available only if you consider two or three columns now what you may want what you have to do within the realm of power brt is it's not able to deal with them natively so you would have to use a tool like power query to construct a unique identifier by taking this and that so either concatenate this and that or create something else that is uniquely identifying and use that as a key column in your relationships in powerpivot it is a bit more technical but i suppose uh jose already knows what i'm talking about so yeah so that's that question um and subhash asks i've got only one data with remarks of paid and unpaid and how can i calculate percentage on it this is an excellent question and that is the next topic we are going to talk about which is everything that we are doing is dealing with all the data all the time so how do i kind of tweak it and play god by saying only look at that and get me what is happening so that's where we will have to learn the most important word in the vocabulary of dax at your starting point um and and get in there so i'll show that to you in a minute let me quickly scan through remaining questions okay this is a very good one if you want to review the formula that i have used in a measure how to revisit the measure in all consolidated view so i'll show you how to do it for one and then i'll show you how to do all so if i want to just review one of these measures i can right click edit the measure and i can see the logic that i it i can see the display condition that i used i can even change anything here and it will all go downstream as well so if i change the definition of sum of sales total amount then any other measures that i kind of stacked on top of another like my lego bricks they will all float together and automatically update so that is a beautiful thing that is one thing but what if you got like 600 measures you can't really right click 600 times so that is paying like click tax so you can go to data and click on manage data model this is where having the power pivot add-in enabled in excel is useful in power bi you would just go into model view and do this and from here if you kind of move this up and scroll down you will see all the measures listed down there there is no rhyme or rhythm in which order they will be listed they will probably be listed in the order you create but sometimes they will be here sometimes they'll be there but every measure is there so you can kind of quickly eyeball by selecting in fact while you are here you can even create new measures directly in this view you don't have to go to excel to do that but i tend to do the measure creation in excel because it's a bit more visual i can immediately put them in pivot and test my values and feel comfortable that yes it is doing what it is supposed to do so that is that all right um let's uh big thanks to srilatha krsna's for me for super sticker and danny kalas for super sticker as well thank you so much and and peter asked we can't duplicate a measure you can duplicate a measure i'm not sure whether you're answering someone's question you'll probably have to call it a different name that's all and anyhow we'll go into the next level of our tax learning journey so you've seen some basic measures i think we've created about 10 or 12. now i will show you a few more in the downloaded file there is there are examples there and so in case these get confusing you can always refer to that okay for for this i'm just gonna clear my slicer so we are looking at everything and i'll drop some of these other things so that it is a bit tidy and so we're looking at profit and this profit percentage tells you what is the percentage of profit for bar funny as against his transactions whereas what i really want is i want to know what is that number 138 000 as a percentage of this value okay i did say i need to delete this so i'll delete yeah slicer is no more i think screen was a little late to catch up so 138 000 as a percentage of grand total right how profitable bar funny is in relation to others that's what i'm trying to get it one way to do this is instead of showing the value like this you can use your pivot table tools and then say show value as a percentage of column total you know this is one of the things that you could do but the problem with this is it is only visual so it is only applicable in this pivot i can't really use that percentage for some other calculations that flow down so that is a limitation so let's go and add a measure that will tell me what is this as against that now the problem here is everything else that we did so for example if i add total amount and total cost you will see what i mean is to calculate this we had to define these two measures and create this as this minus that right that's what we have done we've set up these two and then said a minus b is c that's the logic that we used so we need to kind of use the same approach so by that what i mean is if i could get another measure that will show me this 1.5 million all the way through then i can calculate this as a percentage of that so what we need is a measure that will give me this value right but i've shown you previously through the power of this awesome props that the way powerpivot calculates is before it can calculate the values this is the scientific calculator by the way it must first filter the data right so it's saying oh give me only barf on his data don't show everything else so barf on his data flows through to the calculator and then calculate calculates calculator calculates the profit percentage or profit amount now here what i want instead is i want to say when i'm looking at bar funny i want to know what was the amount here now how one more way of looking at this amount is to ignore any filtering that is happening and look at everything so we want we need a way to tell excel or powerpivot a way to ignore the filter or add our own filters so all of these can be done by using a special word called calculate which will kind of like an override switch it you can tell powerpivot that you need to don't pay attention to this and instead calculate it like that so we'll say profit all okay and here what i will do is i'll say calculate this is a very generic formula and it can get very confusing or complicated as you learn and get into more and more power pivot but you know hopefully it's not too confusing and if it if it does get a little complicated i recommend that you use it a few times and play with it and try it in different situations until the concept gets into your mind and then you can use it so we say calculate what i want is i want to calculate profit as if i'm looking at everything okay well we'll keep it simple we'll say as if we are looking at every sales person so we will say all sales sales person i hate that every time i tab it'll just reset the screen zoom so we'll say calculate profit all sales sales person so here what i'm telling ex power pivot is can you get me the profit as if i'm not filtering on salesperson so all is the word that we use to say get me everybody yeah so you would wish that it is actually saying remove filter but instead think of this like all and just set the formatting for this as currency as well let's see if this works so it will now give that 1.54 million everywhere because everywhere i look it is all the sales people so every time all the people are appearing and it will give me that it even at grand total level it will be the same thing so that's what all is doing it is kind of not paying attention to the filter okay time to go back to our amazing props and maybe this time i'll try like 50 50 split so i got my calculate measure and i got my filter so at this point when it's looking at barf on his data filter says wait a sec i'll give you barf on his data and then this measure needs to calculate but the measure has calculate inside it and it has all in there so this might says cat you don't i want everything so it'll get rid of one of these filters the filter that we want to get rid is just on the sales person column so imagine if you put any other filters through by means of slicers or report filters or something else they will still be respected but the only thing that power pivot will ignore is the sales person filter because we put all sales person if i want to ignore all p all filters then i will simply use all sales because then it will give me all the data in the sales table okay again this might be slightly confusing or kind of like mind-bending but you know you will latch on to this very very quickly because this is a very powerful concept so now i got this and i got that i can calculate my percentage i'll say add measure profit percent we'll call this as profit and i'm just thinking what should we call profit percentage of all profit it's a bad name but we'll just roll with that and then we'll say divide profit with profit all so we're saying take your profit and then divide it with everybody's profit to know what that as a percentage and that measure will give you so even though this person is 60 profitable in their transactions they are actually bringing only nine percent of overall profit whereas certain other people have been bringing 12 percent of the profit so it is giving you a different view of uh of the information and at a grand total level it all adds up to 100. stalwart says where is the example file can anyone please tell me well i will tell you it is in the video description just uh click on the button and expand the video description and you will be able to download that um bix uh thanks to nadeem khan and um and jose for your super stickers thank you so much uh deeney as well um and uh and rajan says i always feel calculate is the most powerful function in the dax well it is actually quite powerful and extremely versatile uh it's quite uh you know very uh simple sounding you hear the word calculate anything or you'll just calculate something but it is actually like a very very uh versatile and powerful function and it is where uh your your journey into powerpivot kind of massively diverges from the excel formula language because up until this point we might be still connecting the dots with some of the other things that we do in excel like sumifs or sum or average if those kind of formulas but the moment you get into calculate you need to really switch in your mind how you approach these things and that's why i that is why i didn't even say the word sum if sub until now because i don't want you to kind of draw parallels to what excel is doing in in excel world instead you want to approach powerpivot as a fresh thing but calculate is is like one of the key things that you need to understand appreciate use multiple times to get a good hang of what it is what it can do where the limitations are and all of that and mahabhu rahman asks why is there a blank well i am surprised as well why is there a blank because it wasn't part of the final file that i gave you but i think for some weird reason i had some blank rows at the very bottom of this table either because when i made it a table excel somehow added these extra rows and i don't i feel like i should delete them but if i delete them halfway through the stream then what happens is first half of the stream has this pivot tables with blank and the others won't so i'm just going to leave that there this is a good problem to have so you can see what happens when there is blank data and uh phil uh sensing sticker as well thanks phil so ashish maher asked how all is different from filter remove filter appreciate with examples i wish i can give you more detail uh the the way dax language has evolved over time because as i said it has been out there since 2009 time frame and i started learning it in about 2012-13 so there are many things that microsoft itself is adding to dax over time some of these things are mere replacements of other things some of them are shorter ways of doing other things so while well there is probably a reason why they have added a remove filter and it is exactly same as this or that i don't want to commit the sin of saying they're same because i don't really know exact technical inner working of this versus that there are people who know this and they'll tell you exactly why that is the case which is what i will recommend in the resources section like where you can find more deeply technical stuff but it's a good question maybe you can go into the dax documentation and read the wordings for this and that or somebody else who is more experienced in the comments can tell us which way the they are different um georgie sends in a super sticker as well thank you georgie um and three that says wow with a lot of w yes wah we need right power power pivot is extremely beautiful and amazingly powerful so i'm i'm always finding it interesting just yesterday i showed it to one of my colleagues at a consulting place that i that i worked and um and he hadn't really heard about power pivot up until that time and he's fairly advanced in excel like doing all sorts of index match and other beautiful things and he's so powerful he's like huh you could do this this saves time and uh so that's there you go and there's a couple of questions on the duplicates aspect we haven't got like duplicate transactional level data so it's not fairly appropriate to show that and even if i show it might be confusing but just as you have sum and average there is count distinct uh function that you could use to deal with duplicates so give it and try with your data anyhow we will go and do do one more calculate so that you kind of solidify not solidify but at least see you calculate in a different light as well uh and and understand so for that purpose i'm going to add a new pivot just because this one is getting too cluttered um and uh you may already realize that i have an obsession to not have things on a1 so every time i put anything in new worksheet i must always have some space on the top so we'll add a new pivot table here and just let's zoom it up here and what i want is i want to see by product so these are all our products how much amount we are making that is already there and then what percentage of that amount comes from the apac region okay that is india australia and new zealand so this is total amount i want to also create a measure that tells me what is the apac sales okay so here in the other example the total profit all what we did is we kind of ignored the filter we said look at everybody and tell me what the profit is so we wanted the grand total value to appear here whereas here now i want what i want is i want a portion of that number to appear here that means i want to introduce an additional filter into my calculation logic okay so you can use calculate to add filters change filters override filters or introduce or remove filters all of those things so that's what calculate is for it will calculate a thing by throwing an extra light on it in the filter context okay so that can either change or modify or ignore the filter so in other words i want to kind of slice this by region i want to keep the 119 000 but i just want to see the 69 000 as well i don't need to see 38 and 11 000 so i want only these two numbers okay if i go and filter this to apac right the graph total changes so i don't want that to happen i want the thing to remind there so that means i don't want the region i just want another calculation here that tells me total amount apac so let's do that add a measure this would be our last measure and then i'll switch back to questions talk a little bit more and then share the resources because i think we've made extremely good progress and i feel like once you have learned all of these you should go and do a bit of practice before you come back for more and this data set gives you enough items to practice so definitely recommend doing that so total amount apac region where is calculate total amount and then we will say locations table region is equal to apac okay so this is the formula if you observe we are saying total amount in the earlier situation we said all sales person but now we are saying locations equal to apex so this is how i am introducing a new filter into the calculation logic so i'm saying calculate total amount but just add this extra filter now i wish i had two filters to do my clever illustration but i don't have them i just have one t filter so you'll just have to imagine this now so total amount is calculating and i have already added 50 dark bytes as a filter in the front but now i want to add one more filter of apac region and filter down the data okay you could imagine that in the back end power pivot is running like an engine wherein before it calculates any number it will first go and filter away the data that needs that it needs to look at and then apply the calculation logic on top so we are telling that engine that this introduced this okay i won't elaborate more on that and we will add total amount to a pack region so i can see the 69 000 alone so now i can go and calculate a percentage of this versus that to see that i did say the last measure but i'm just gonna do the apac sales percentage and then we can just say divide a pack total amount apac region by total amount and we will just say this needs to be a percentage so that we are taking the apac region value and then dividing that with the overall amount to get a percentage representation of apac sales and i can add this i can see which products have higher sales happening within apac region uh which products have lower reliance on apac and depending on that maybe we choose to run some marketing campaigns on you know promoting i don't know caramel stuffed bars to all the new zealanders and australians and indians because they like them right so this is how you would analyze the data get what you want and needless to say once you've built this measure you can use it in some other context so we are looking at it from product i can take this and i can apply this for my salespersons or something else altogether all right that was a big journey in terms of creating the measures i hope you found all of this useful don't uh go because i got more interesting things prepared for you now let's see the questions and uh let me um so there is a still a couple of people who are asking where is this download file the mythical download file uh i'll just uh if you go to the video screen you will see that there in fact i think i made a mistake um if you just expand the video description you can find that there is a download link maybe i should run this banner again so serum nakumar asks assume my sales amounts are having multiple currency how do i show the sales amount in common currency now obviously if you got some numbers in euros some in pounds and some in dollars and some in i don't know gold ingots you can't just add them up because that will be just wrong you would obviously need some sort of a mechanism to either do currency exchange conversion to a common thing so i guess that's what you are asking if you just suppose adding them then it doesn't really matter what you do so in that case what you need is instead of just amount alone you would also need currency of sale as as a column either if the currency changes per sale or if it is just relevant on the geography in which you are operating then maybe in the geography table you need a currency as well so either place you would need to set that up once you got that set up then uh you will use that information in your relationships to figure out what the currency is and get the exchange rate and convert it into common currency as added columns don't do that here but do it in the add column feature of powerpivot and do it there in fact if you got such a thing you may want to first clean up the data bring everything to one scale through power query or some other tools because the exchange rates are not something that you are inputting they you may have to get them from an api or or a web service or or some other source that is a bit more reliable and changing over time so if you got something like that then power query is your best friend first use power query to pre-process data clean it up bring everything to one thing and then use it there okay so that's the thing maria uh sends in the super sticker for 10 euros thank you maria and um amara has asked stress precedent trace dependent or feature of excel can we use them in measures not really if your measures are not working as expected you will have to use some other tools there is an excellent tool called tax studio and dax profiler and these kind of things are available in power bi so you could use them to figure out what is happening where things are going wrong and all of that and debug or see the process flow like if something is too slow then you can understand how many which part of your measure is is taking up too much cpu time and fine tune unit and all of that [Music] so that is a little bit on that muhammad asks can we manage measures in a separate folder so that we can easily get in and adjust them or keep track of them yes and no there is a lot of conversations you will go if you go online and search up or watch other videos you will find people advocating for having a measure table versus not and all of that and this is actually something wherein as you grow and as you start building more and more measures and building complex data models you will have to find something that works best for your organization or your situation um for me i haven't had to really create a measure folder even when i create like 30 40 different measures i try to put them in the relevant tables so that i know where to find them and it's not super hard like if you go to pivot table you can use the search box to search for what you want so uh it isn't all that bad but i would completely understand if you have like 60 tables and 900 measures then i wouldn't use any of these i would probably try and do something better there are some visual options available so that you can disable certain fields from appearing in the front end so only measures to show up and all of that but yeah definitely read up and and figure out what you can do um okay they so yeah mark asks about the currency exchange rates because i think that is something that some of you are dealing with um and this is again relevant on how you want to analyze and present the data so for example certain accounting or financial statements i'll give you purely a new zealand example just because i live here and i had to deal with this in a few times the way i had to do that is the reserve bank of new zealand publishes official exchange rate uh benchmark i think every day or every thursday or something like that so when i try to report on all the sales that i generate from my us customers for my new zealand tax purposes i can't just say i made six thousand dollars or nine billion dollars or whatever i'll have to take that on that date and find the relevant exchange rate or the average rate and convert that so this is where there is an official policy that you will have to follow when you are doing this from reporting perspective but if you are doing it internal reporting for data analysis there might be something else like you may especially like international corporations where such data analysis is performed there might be some policy guidelines saying um one euro is i don't know 1.2 dollars or whatever and then you just use that if you have such handy conversion rates you i still recommend using power query and parameterizing that so that it's easy for you to tweak them and change them uh if you've got like changing over time kind of thing then you will have to add a third component to your unique identifier so you got currency code you got your amount and then the third one is the date of transaction and then use all of these three to figure out what should be the rate on that date okay um so sivakumar says excel 2010 does not have relationship in data tools well i got a dad joke that's because it's too old to have relationships jokes aside excel 2010 also has power pivot it is available i believe as an add-in in 2010 so you'd have to just search up powerpivot add-in and go to the official microsoft website don't download stuff from openweb and download the powerpivot add-in for your version of 2010 install that and then you will be able to see that the pattern of relationships won't be available but you can go to manage data model and from here there is a diagram view button which is basically that weird diagram that i draw at the start of the stream that you can construct and link up things so there you go uh that can be done there all right vim asks can these data sets be located in other files of course yes that's the whole point of using these tools so that you don't have to bring everything into one place in fact as i said early on that is the thing that i want to show so i'll just save this as my stream version and i'm going to open a new file blank file brand new file and from here just go to data and we will actually connect to our sales analysis blank data file fetch the data and then build our power pivot again here i'll do it so quickly because we have already gone through the process um but in just in case you want to see the first few steps of data relationships and in the total amount column this is a good place for you to do the recap so we'll go from file workbook but it doesn't have to be workbook it could be an sql query it could be a database it could be a cloud source whatever you want so we'll connect to that import and this opens up the power query view wherein i can connect because that other worksheet is hidden it's not showing up so i'm just gonna quickly unhide that for for a minute unhide okay and let's do this again from scratch so from workbook blank and now i can see all of these so i'll say select multiple items i want my locations products and my data and there's a bunch of blank rows on the top we'll say transform and quickly do some adjustments on this now this is not a power query stream so it is i think and then i'll just say delete any nulls and then remove these columns use first row as header okay so we did some quick cleanup of the data and then call this as sales so that the data is there and i'll just say load to and i'm not even going to load this data into tables i'll say directly send this to data model so i don't even want to see my raw data in excel i just want to be able to build pivot on top of this so this is how you do it you send it to data model and now your data well i thought i did but something funny happened no this is so just add to data model but create a connection so we don't have any data anywhere but the data model would already have and then from here now i can go and relationships make a new relationship between sales geography and location geography i don't have the product table here because i forgot to bring that but now if i insert a pivot table it will show me these two tables but now you can see that the same table has a little database logo next to it to indicate that it is actually a connection coming from somewhere else and i can put my let's just put product and then i'll say right click add measure total amount sum of sales amount currency and then we can add that so this is how you would normally do it for your data sets because chances are if you're dealing with let's say 600 000 rows of information you don't want to keep it in excel and maintain it you'd rather just keep it where it is which is in a cloud database or or a crm system or whatever that is and just fetch it through power query and then you will also have control to filter out and clean up the data and fetch the columns that you want and all of that so this is how you can connect through power query and do it now if you have never heard about power query or you're thinking what is it this weird thing that chandu just did i recommend that you watch my getting started with power query video on the channel it is an hour and a half long so it is exactly like the stream but without all my dad jokes so you can go and watch it and uh and get a complete understanding on power query i highly highly recommend that you learn power query before you jump into the deep end of this power pivot pool because power query will help you manage the data powerpivot will help you analyze the data and you can't analyze data if your data itself is so you need to have good quality data coming into the system and that is what power query can do it can be a gatekeeper it will let you manage the data better set up all the things in a nice and beautiful fashion so that things will work better in power pivot all right that is uh that i want to just say thanks to greg for a super sticker as well and and uh muhammad says worth watching the live session wash hands thank you and um pradeep says thank you so much really insightful i'm glad you enjoyed it now we're not really done yet so don't don't wash your hands yet i got a couple of slides where and i want to quickly talk about how can you learn more about powerpuff and i have a few more props to show you so i'll just quickly switch to my presentation and let's talk about some resources now power pivot is a very deeply technical thing and i find that while a video demo or playing in excel works best because it's a somewhat technical you also need time to think about it as you're learning it and that is like you know you need to reminisce the things that you're learning so that you actively processing it and on a screen when we are learning what happens is we see things we are also tempted to try this or that there's too many distractions so that is why i find that learning power pivot through books is good and these are my go-to books so supercharged power bi and the definitive guide to dax are my go to books for for power pivot now i'll be honest here i wanted to really um put a link to this books in the video description but i forgot i'm just going to update the video description later when the stream is over but these are the books that i'm talking about them they used to be right here behind in the bookshelf but i had to take them out so that i could kind of show them to you when i when i do the stream and if you are new to powerpivot which is probably majority of you in the stream i recommend that you start with something like this this does say supercharged power bi but matt ellington is is a is a very good friend and he lives just across the ditch not the physical ditch but across the district in australia and he has got a green book as well just exactly same book screenshots changed so that powerpivot on excel is shown so whichever powerpivot you're using you can get the book but i recommend that you at least give it a try on power bi because that way uh you kind of do the proverbial kill the two birds with one short thing which is you're also passively learning power bi and you are learning how power pivot in power bi can be applied whatever you learn you can pretty much apply in excel anyway so might as well do that and power bi tends to have latest and greatest power pivot features so many of the new functions they add they appear in powerpivot for power bi first and sometime down the line they might choose to ship it into excel whereas if you want some real hardcore power pivoting this is the book for you this is by the italians marco russo and alberto ferrari and this will give you deep technical understanding as well as solid business practical knowledge on to powerpivot and i'm not recommending these books because i'm friends with matt arlington or i admire marco's work or i get some kickbacks from them i genuinely read these books many many times probably not the not the big one because it is kind of very very technical and even beyond my head sometimes but i do read them and refer to them every time i get stuck with my information now in terms of websites i have got a few websites where i would normally go and search information on powerpivot i will add all of that to my video description when the stream ends and i get time to wash my hands so that is a little bit on the resources side of things so uh i hope it all worked very well for you and you enjoyed it shari says outstanding master class as usual big thanks to sheri for tuning in to this as well as my other master classes i do this stream every last friday of the month so if this is the first time you're coming in and you enjoyed this feel free to come back next month the last friday of next month but there's a ton of other videos on my youtube channel and on my website as well now i will watch for some questions so feel free to hang around share your questions talk a little bit about something or if you have enjoyed any particular aspects of the stream uh you can tell me that as well and yeah improve the show joseph says thank you so much shandu truly amazing content i'm glad you enjoyed it brother thank you so much jhp says merci beaucoup thank you thank you so much and uh thank you so much love you as well uh could you please add a link to the i think uh i'm not able to directly add like a link to chat but when you replay the stream you you have the option to view the live chat on youtube so it depends on which device you're watching but yeah you could certainly try that chakora says great learning easy to follow clear explanations good metaphors thanks well that is good joe my wife was laughing when i showed when i was bringing this upstairs last night like why are you taking this upstairs what has gone wrong with you like this is the filter of powerful and like what you couldn't get anything better than that palladio says thanks great info as always with a super chat thank you palladio i really appreciate uh sticking around watching this and cheering me with the super chat message manjunad says thanks chandu but you didn't answer my last question feel free to post it again manchester there is quite a few messages so it's very hard for me to keep track of everything your master class is the perfect way to start the weekend i'm really flattered thank you so much i uh i had loads of fun as well i wouldn't really call it like the perfect way to start my weekend as well because it does start at 3 a.m when i wake up and i prepare like a cup of coffee get ready set up the lighting and everything i could kind of do this last night but last night i was out drinking with some of the friends because as i said i do some contract consulting work for some of the government ministries in new zealand and yesterday was my last day for one of the projects here so we had some favorable drinks and i was kind of nervous like you know what if i get too drunk and you know don't wake up in the morning fortunately i didn't i had a couple of beers but i came back and took a good good long sleep and hi chandu nice to watch your online session i learned many aspects of excel usage from you thank you so much um that is good to hear mujamil scythe says can we replay this when the stream ends of course yes you would just have to go to the same youtube page where you're watching this and it should be ready for you to replay and you know you can use that you can pause me probably don't pause me while i'm in the middle of a joke i don't really like that but yeah i'm just kidding uh pause me and then practice the things come back julie says will you do a class for power bi of course i do a class for power bi in the let me show so here i do a few classes on my website if you go to the file download page you can see power bi i played it as one of the options and this is like a it does say power bi i played it but it is actually a comprehensive class that covers power query power pivot and power bi everything is covered um the only thing is it's kind of like in the waiting list mode now because uh i've had a good session and i'm just trying to figure out if i should re-record some of the videos because power pivot power bi changes every month so they've added new things and the screens changed and all of that and i'm in the middle of doing that but i'm also doing youtube videos and streams and working or elsewhere so there's a lot on my plate and this is kind of pushed back but i try to relaunch this in a month or two that's my plan so feel free to go there check it out and and add your name to the waiting list so that you can you can hear from me whenever this class opens yeah that's about that joy says i've been following you for since 10 years you're my inspiration thank you joy this is what keeps me uh inspired and motivated as well i watch you guys following me and learning from me and i feel like you know i need to learn all of these people are learning i should also be learning i should also be sharing and i really enjoy the aspect of thinking about things working on it and talking about these on my site or my youtube channel so i'm really glad to have you along in this journey brother ed brand say sends in a super sticker as well thank you sherry asked where is that at i suppose she's talking about the power bi play date so if you go to the video description there is actually two links one is a blank excel file and the other one is is the completed file and when the completed file one takes you to my website page where i got my files and the stream link as well and if you click on the classes and go to power bi play date you will you will find that information there uh let's see what other questions are there do you think excel will have the same presentation features as power bi um they are not comparable and there is almost no point trying to either push excel to power bi or power bi to excel i view them as two different tools so this is what you want to do as well see what is working in power bi for you and use that for that and use excel for what excel is good for there is more and more overlap between the functionality and the abilities of these two tools and recently microsoft added facilities for you to build a data set in power bi and then analyze it in excel uh and you know vice versa like excel data in power bi and all of that so definitely try some of these things but not the visual like the interaction abilities maybe they will appear as well because um excel has like a really large user base whereas power bi is kind of still very niche it is growing but because it is such a specific application for business intelligence users whereas excel is more generic chances are we might actually have like interactive charts down the line with especially office 365 every month or every six months microsoft adds new features so won't be long before they might think um let's make these charts a bit more better so that's that rio uh sends a super sticker as well uh thank you and [Music] [Music] um all right uh there is a lot of interesting cheerful messages i'm just going to talk about them uh chandus are your session is a gateway source of knowledge thank you so much i i hope you enjoyed it um muhammad nazar can't ask shandu sir are you knowing hindi of course yes i i know hindi i'm not like super fluent or anything but i can i can watch bollywood movies i i can have a conversation in hindi uh and yeah i i enjoy hindi music and all of that you know i i i did my studies uh most of the studies in andhra pradesh which is like a telugu speaking region but for my mba i had to go to indoor which is in the middle of the india and it is like a hindi language speaking state if you don't know hindi you can't even get a haircut so the first time i went for a haircut there they're like he asked me something like very specific in proper hindi and i didn't know exactly what he meant i said uh tk and then he gave me like a weirdest haircut of my life so yeah i speak in the so that's that and um adash kumar asks sir please tell a little about unpivot column in power query now this is not a power query stream but we are at like the tab end of this so i feel like i should show it but because i have already got a power power query detailed video i recommend that you search that up on the channel i do also have one or two unpivot specific examples on the channel as well so maybe watch them and i'll give you very good understanding of unpivoting vikas says uh i really loved it thank you uh i'm really glad for that because i really enjoyed doing the session as well i find it uh really fun fun to do it i enjoy talking about data and the whole aspect of doing it love live means there is a little bit of buzz involved like i'm excited i'm i'm slightly nervous that oh maybe the tech will not work or maybe something goes wrong so there is always all of that but it is good and now uh zaive asks are you know now unfortunately i i understand tamil i got many good friends in tamil tamil nadu and i i lived for a couple of years in chennai and those were like i considered them the best time of my life because i started working in chennai and that was when i got married and um newly married people like you know there's a lot of freedom we are not living close to our parents so everything was just perfect and beautiful and there are many many things that i uh picked up from there i can follow tamil conversation but i cannot talk because i have not kind of lived there long enough to pick up the language and of course i enjoy tamil music and one of my recent favorites is the enjoy and jamie song i kind of listened to it on loop several times so that is a little bit of tamil uh and uh says you so much that is basically telugu for um very well explained i've been following you so thank you and mujama says are you still cycling of course as i cycle uh i got um i purchased an electric cycle last year so i've been using that more often when i go to the client's place in the city which is about 15 kilometers from here but there are quite a few hills on the way down and up so it's easier on an electric cycle because wellington does get super windy so if i get like a 40 30 kilometers per hour wind either head wind or tile wind that is like a normal day in wellington like it's pretty much every other day is like that but sometimes it does get crazy windy so it does help to have an electric cycle but i enjoy cycling i do that like twice a week at least chandrasekhar says many thanks and have a great weekend thank you chandra for for that comment i glad you're enjoying that and uh we're now well past two hours so i feel like i should stop but there's some good comments coming in so feel free to send a message or a chat thing or you know feel free to go back in the stream and watch some of the sessions ashish says fantastic master class thank you thank you so much now i just want to remind those of you who are still here that i run the master classes every last friday of the month so obviously i want you to keep that in your mind and tune in for the next month but more importantly around the middle of the month i run a poll on my channel asking what topic should i run it on so if you got some suggestions or if there is a topic that you desperately want me to present please share that suggestions in the chat window or in the comment section of this video or in the poll so that i can i can observe them and then i can consider the democratic demand of whichever one has more words i'll just pick that up so i just want to say that and if you're watching the stream in the replay in the chat comment section please uh you know say something nice or you don't have to say something nice but at least say hello to me and and tell me where you are watching this from oshie says sir i switch it to data science please suggest what all should i learn now this is an interesting topic because i see that many more many more data analysts are moving away from traditional like looking back type of analysis to more of exploration as well as looking forward type of analysis and that is where kind of data science fits in it is a much wider than what i simply try to summarize in terms of the key skills and the technical stuff that you need to learn i plan to actually make like a video on this topic because i've been getting more and more emails and and comments on on that but in summary i feel like whether you are a data analyst or a data scientist or some super awesome machine intelligence learning specialist you will have to start your journey from data so a good practice would be to i don't mean to do that a good practice would be to start thinking from the data itself so how do i prepare and manage my data that is the first key skill that you need to process and polish and this is where skills like either automation tool or not automation but you know more programmatic tools like sql or power query come in but if your line of work involves working more on the programming side then understanding how to use either python or r or some other programming language to first manage the data forget about analysis managing the data so how do i construct the data frames how do i combine data how do i scrape data how do i clean it up and all of that so that's one skill the second thing skill that you need to develop is how do i ask good questions okay so once you have clean data then you start asking what is the right questions to ask how do i get into the core of the problem so start with good questions and this is where developing strong intuition and business knowledge is vital so you need to have a good feel for the data good understanding of what stakeholders want and there is uh and that is something that you will develop over time but actively work on that like watch others do it and then pick that to pick up those skills third is more on the programming or the technical side of it and this is where the tool that you use will come in handy so let's say you're using power bi to do your data science stuff then you would obviously need to learn a bit of dax understand the visual layering things and all the aspects of setting up things and using uh various features of the tool all of that if you are using excel for let's for suppose for your data science thing then tools like pivot tables advanced formulas and even stuff like solver and maybe a little bit of vba will help you if you're using python or r or other tools then that technical knowledge is needed and then the fourth skill that you need to polish and hone is presentation or communication skill wherein whatever you are creating you're not making the data science stuff for yourself you're building it for others so how do i present it how do i make it visually compelling and tell the story and this is where again investing a little bit of time on preparation of charts and slides and presentations so that your stakeholders can see your information and then say yes we need to take a decision now okay so that is in a nutshell there's more to it but probably you can either wait for my video where i will do a bit more research and come back but otherwise that's that um akhilesh meshram says you have been very inspiring personality for me following you for many last years and less life science are the best way to interact with you thank you akhilesh i really appreciate your kind words and uh this is kind of what motivates me as well like i need to learn and share so that others can also feel empowered and awesome in their day to day ravi shankara says thank you so much following your last date missed my sticker sorry ravi shankara i think there was a time where there were too many things for me to scroll up and down and i didn't feel like you know i should look on this screen when the other thing is here and the camera is in the middle so i'm really sorry i missed your sticker but big thanks for supporting me all this time rajan says do you still think pq is not useful for big data i don't i don't know exactly when i said that but i didn't really mean it in that way what i feel is like if you have got like a gigabyte of data then power query may be one of the last options that you want to use because there are other tools much closer to the source of data like a database engine are more equipped to handle that volume of data whereas power query runs unless you do it on power bi workspace kind of things it is still predominantly running on your computer and your computer is much less powerful than a database server so if you got lots and lots of data and you're doing multiple joins and doing all sorts of things try to run that process on the database server or a much powerful computer than than your own pc or laptop deepak asks can you make a video on completely covered advanced excel and data analytics it's a very generic thing to do such a large video the other big challenge that i find is if i shoot like a video for 90 minutes or two hours or three hours then what happens is uh the actual video will be like 20 not 20 but at least 10 12 hours long and then i have to edit it and i have to polish it and that is a very time consuming process so um i am trying to avoid doing such long videos just because they're very tedious to make and i'm using live stream for such things because there is no editing here right i'm talking you're watching so i'm not even thinking about uh should i cut here or should i skip through this step i'm just going through maybe i'll i'll try to add more detailed videos on the channel but i find that short videos combined with the resource on my website is probably a best way for you to get the full picture and i do run online classes wherein it kind of goes into much deeper detail so if you are looking for something that is a solid complete advanced excel data analysis again i don't mean to plug this in or anything um but i would highly recommend checking out the excel school program and this is a a completely online course that has 24 hours of video material on all aspects of excel that goes step by step and covers everything from data analysis to power query to writing formulas pivot tables charts and advanced concepts and even dashboards so yeah maybe consider that if you are looking for a place to learn everything in one go kate says uh thank you chain they've been following you for many years uh from beginning of the time and i always find your videos so powerful thank you so much kate i really appreciate those kind words and a big cheer to you for being a follower all this time joy asks how can i clear da 100 exam please guide i saw this question earlier as well so i didn't address this because i'm not even sure what da100 exam is i suppose this is some sort of a data analyst 100 exam or something like that so yeah sorry i can't really help you because i don't know what this exam is are you planning to make a python master class oh i would love to do that but i my python skills are very rusty i haven't actually done any python coding in the last six months and i tried to teach python well not teach but learn together with my kids because we all thought you know it would be a fun exercise to do during the school term break but it is uh it's it's a very tricky uh thing for me because i'm very still learning so i'm not very confident of teaching it yet and i don't want to feel it like a an amateur hour of python coding with shindu so i'd rather focus more on vba or other things for now uh but yeah maybe in future you never know because it's a very beautiful and powerful language and i'm learning uh some interesting things about it so maybe one day all right that is all now my throat is already itchy so i don't want to cough up and make it sound like i'm having a attack of something so thank you so much for having uh me in your weekend and enjoying this master class i hope everything that i taught you so far in powerpivot made you a bit more knowledgeable and you found the where part pivot fits into the spectrum of things in the data analysis space and how to use it i highly highly recommend that you download the practice file give it a go apply one or two of these concepts into your workflow and then see where you can take it from there and if you're looking for a book check it out one of these books i will add the links in the video description shortly after we are done here thank you everyone and have a beautiful fun weekend uh i'm just gonna go downstairs take a nap uh and um and then yeah spend rest of my saturday and sunday doing household stuff uh watching its play and all of that thank you see you again sometime soon bye

Channel: Chandoo

Views: 78,141

Keywords:

Id: eCuPRqQNe6Y

Length: 137min 52sec (8272 seconds)

Published: Fri May 28 2021