Power BI Tutorial From Beginner to Pro ⚡ Desktop to Dashboard in 60 Minutes ⏰

Welcome to the latest Power BI Tutorial. This Power BI Tutorial is for you either if you are a beginner or if you have started using Power BI but having trouble figuring it all out. I’m Avi Singh, Microsoft MVP and bestselling Power BI Author and in this tutorial . We’d take you from Zero to Dashboard and from Rookie to Pro We’re gonna break it down, make it super easy for you, as we take you Step-by-Step through Power BI. As we build our Dashboard, we would walk you through the Power BI Process • To Author the model • Publish it Online Then have you and other users Consume the beautiful Dashboard and Reports But our main focus would be on Authoring the Model, which is the most crucial step. As you continue to watch all the way to the very end of this video you would learn How to use the Query Editor (The Kitchen of Power BI) to connect to your data and cleanup your data. You’d learn about Relationships and DAX Measures (The heart of a Power BI Model). And how to create beautiful Visualizations Here is a list of topics I’ll be covering and the links are in the description below, so you can jump to a specific topic anytime you want. I’d recommend watching this whole video first (you can change the speed up and down if you like). Then come back and follow along step-by-step using the “Download Files”. This Tutorial is complete in itself, but at times I may mention more advanced material which you can watch next. All the links, including the one to Download files, are in the Video and in the Description. This is a slightly fast-paced video, packed with a lot of good stuff.If you’d prefer a Slower-Paced Tutorial, I’d link to that as well. Or if you feel you already know this stuff: then check out my advanced videos on Modeling and DAX. Now if you get stuck anywhere in this Tutorial and have any questions, you can ask me directly on my LIVE TalkPowerBI show every single Friday. Just make sure to subscribe and click that bell so you are notified whenever I go live to answer your Power BI questions just one tip for the best quality playback throughout this video change the auto quality setting instead select one of the HD options. When you hear Power BI, I want you to think two things. •Power BI Desktop• PowerBI.com. We’re going to talk about PowerBI.com in the end. But we’d start with Power BI Desktop . Because it's one tool that you must master to get started and eventually become a Power BI Pro. Power BI Desktop is the authoring tool, used to create Power BI Models and Reports. There are three phases to developing a Power BI Project Author •Publish •Consume The most critical step is “Author” and you do that using Power BI Desktop. Let’s take a look. sure you have bar bi desktop installed and for that you just Google bar bi desktop download and go from there if you need more help then we're gonna link to a video which gives you more detail and tells you about the different options so once you have power bi desktop and you launch it for the very first time I remember when I did it it was scary I was staring at this big white screen and I wasn't quite sure what I needed to do well this time I'm here to help you so why don't tell you is that power bi think of power bi as a machine as an engine and an engine needs its fuel and what is the fuel of power bi it's data it's your business data and it loves that right the Messier the better and the way to get data into power bi is is this section right here so we're gonna start over here in this section and we're gonna start with the get data button which is right there so once you click on that button you're gonna see that it shows you a few of the common options right there so you can see it right there but we're gonna click more just so we can experience the awesomeness that bar bi has to offer so for one you can see right away that has the ability to connect to lots and lots of different data even if the one that you you were trying to connect to is not listed here which is pretty rare but still you might be able to connect to it using either ODBC or old data feeds or some generic connector like that so pretty much doesn't matter what your data source is you would be able to connect to that again you can see a long list of sources and again we're gonna we're gonna see some example of that but you can connect to more than one data sources and bring it all together into one single model so you can be selecting maybe one file from your sequel server and then one table from your sequel server and then something coming from excel something coming from a SharePoint Online list and on and on and on and all of that you can combine it into this one spot so we're going to start with our data source and we have an excel file in this example so I'm just gonna can select that and click connect now based on the data connector you you choose it's gonna ask you different questions for example sequel is gonna ask you to look in the name of the server and credentials and so forth for excel all we need to do is to point it to that specific file and this file is part of your download so go ahead and use that file so I specified bar bi that hey this is the file to connect and once you specify the data source what it's doing is its examining that data source whatever it is Excel sequel access or something else and it's checking what is available inside of that and that's what it comes back and shows that to you in the navigator not Excel sometimes this can be confusing because it might return what look would look like hey it's showing me the same thing twice and notice here this has this date date key and if you go here these look similar because in Excel it shows you the sheets and if it finds tables Excel tables inside it shows them as separate as well you can tell that by the I can it's using and the fact that tables show up as the table name so in this case we have these clean tables now if you do have a choice like this I would always select the table just because tables have more crisp boundaries whereas sheets sometimes you know how you can mess up the boundary of that so in this case I want you to just simply select the top tables up here and you can see that as you click on something it shows you a preview of that so if your data source has lots and lots of tables there's gonna be a quick way to kind of just glance and check to make sure that you're getting the right thing so we're gonna select all of this and we are going to click load now you see what it's doing right now is it's actually connecting to your data source whatever you would supply in this case it's Excel but could be sequel and what it did is it made a copy of that data set so nothing changed on this screen but let's talk about this layout we zeroed in on this get get data so you might see panels here and we'll talk more about that but already on this fields panel I didn't expand it earlier but earlier this would have been completely blank and now you can see these stables are being shown here what collapses for now and then go over to the left side where we have these three panes there is a report data and model or relationship view so report is still blank and that's okay but if you switch to the data tab you can actually see the underlying table that it has fetched from that data source and you can click through and kind of examine these tables and see how the data has come through what I'm gonna do is save this file and then I'll check back in with you all right so here we have this is the original data source that I had pulled into my power bi file and this is the power bi that I had saved now you can see that the power bi file is is much smaller in size than our original data source which is pretty amazing given that Excel itself it stores the data in a compressed format but all I want to show you here was that power bi is really really amazing at compressing data and that's how it can not only handle millions of rows but hundreds of millions of rows and beyond I'm gonna show you the third view which is the model or relationship view which is right there and if you click on it you will see that you see the tables here as well we'll come back and talk a whole lot more about relationships so far we have brought in a very extremely clean data and in real life that's rarely the case well okay that's actually never the case real is always messy data and you know here is one example of that now the odd thing about this is that you or you know you me may not call it messy what I call this is that often we would find datasets which are human friendly but they're not machine friendly as far as machines are concerned this data is noisy I mean these colors don't make much sense machines don't care about that when I say machine I really mean Power BI. Power BI doesn't care about these header rows again that's been there just to assist us humans and and what is that we have this data spread out on columns that's awkward for power bi it's redundant because the grand total is is essentially repeated it can be calculated from these monthly totals it has subtotals and a lot of other things going on here so again this is human friendly but not machine friendly to power beyond this is messy data let's see how we can clean it up and bring it into power bi so we're gonna go back again to or get a data and click excel from this drop-down and this time we're gonna select our messy file the budget file which again is part of your downloads as well now it does the same thing it examines the file and checks what's inside in this case is just one sheet so it's we're gonna select that but this time we're not gonna click load because we're gonna enter a magical realm my friend the kitchen of Power BI are you ready for that so when you click Edit watch what's gonna happen it's actually gonna pop open a new window so I'm gonna you know make this window a little bit smaller as soon as it gives me a chance so there you go so you can see how the Power BI window is in the back but now this new query editor window has been open and you should think of this as a component of power bi but an extremely powerful component of power bi in fact power bi has two engines that you need to master to become a pro one is the query editor it all starts here all data goes through this this is the kitchen of power bi and the other engine is the model where you need to understand relationships and Dax we'll get to that stay tuned for that so here we are in the kitchen of power bi and remember when I said every data comes through here so you notice here that even when we clicked load we did not click Edit for these queries they still are placed here but there's not much cooking or cleaning going on for this it's it's simply just connected to the data source and just brought it back in but for budget we're gonna sharpen an eyes and get at it but before we dive in let me orient you to what's going on here now for first of all what do you have in a good kitchen you have lots of gadgets and appliances and this is chock-full of that so you've got lots and lots of goodies up at the top in the ribbon a very familiar interface with your work with office Excel PowerPoint on any of these tools and it's gonna be so much fun just selecting our tools here and getting at our data and cleaning it up so that's at the top the ribbon on the left side here of course you're seeing the queries now you can organize them in groups and folders as well I'll let you explore that on your own that's usually needed only when you have lots and lots of queries let's say you end up with 40 and 50 tables going through a to power bi on the right side here we have the query settings pane and it has a name which is really important because this is not just the name and the query editor this is the name it's gonna end up with in your model as well that's what's gonna show up so you want to give it a good name if it doesn't come if it comes with a quirky name or something like that and applied steps the part you see over here this is where all the magic happens now Barnea does try to help us out a little bit here so let's say let's get started and cleaning up this data the first thing I want you to do is go over to the applied steps and just delete this change type and promoted headers so Barbie again was trying to help us trying to make the best guess of what it needs to do with the data but it didn't get it right that time and that's okay we'll excuse it for that all right so now we're gonna get to work now you see the first thing I notice over here are these header rows header rows which are again were are there for humans machines don't eat them this is just you know FII and we would rather focus on the data which is the budget amount so we need to remove these rows and again what I talked about the kitchen of power bi you have all the tensions and in hinds and everything available to you over here so the the one looking for here is this remove rows so I want you to go ahead and click on that and from here select the remove top rows option that's gonna pop up a dialog box and ask you how many rows and in in our case we want to remove the first three so I'm just gonna type in 3 and hit OK now here's where I want you to watch really carefully because if you blink you might miss it so I'm gonna hit OK and watch what happens so first thing that happened is those three rows were actually removed so in the middle it's showing us a preview of the data as it's working on right now so those rows are gone but well you remember I said this is where the magic happens watch what happened here it add a step here I'll come back and talk more about this but yeah keep your eyes you know glued to this section watch what watch what it does so remove the top rows and now what do we need to do oh gosh look at that I mean I don't want it to be called column 1 column 2 I have my headers right here I wish there was a button which would make the first row as headers we Scranton my friends there it is used for asteroid has headers so go ahead and click that you see how easy it is it's just you know I love doing this so you use first or head as headers and again you notice the change in the preview pane but more importantly you also see it as recorded here so promoted headers and again sometimes it tries to help you and adds steps on its own you may or may not need that we leave that in for now all right so we promoting headers now the next thing we need to do is notice here then there are these subtotal rows again they're redundant as far as power bi is concerned so we don't need that to filter these out I want you to click on this this triangles filter icon next to the column name once you click on that it's gonna show you a lot of options now sometimes these options are actually well these options may depend on the type of the data field so if it's a numeric field you would see instead of text filters you would see actually let me just show it to you really quick so it'll say hey number of filters and then you can say greater than less than and so forth but let's come back over here and here we're gonna say text filters does not contain the word total now watch out though because power bi the very editor piece is case-sensitive so make sure the t is capital and you type it just like that and again we're gonna hit okay and watch the magic happens so again it remove those rows but more importantly it added and that's step here if you are an excel user this may seem familiar this may feel like Oh macro recording isn't it well it is but way more awesome my friends for one for me it feels like a time machine I can go step by step and examine exactly how things were how my data looked like at that step now what this does is it does two things one it makes your queries in this process of cleaning shaping and transforming your data self-documenting how many times have you looked at somebody else's report and weren't able to figure out how they exactly did it well how many times does that happen for your own reports where you open it after a month or even a week and say scratch your head and say how did I do that I don't quite remember well that is all a thing of the past because again you can time travel and you can go step by step it's all documented as part of you doing it and not just that this is way more awesome than macros because in macros recording it and editing it is a whole different level editing you got to dive into the code and look at that and hear editing or making changes to an existing query going back and fixing things let's say your business data changed and now you got not three header rows before header rows it's really easy to change the step all you do is click on this gear icon and you can change the exact setting that was for that step you can delete a step just by hitting that red Red Cross there you can insert a step at any point if you want you can just click on the button again it's gonna say hey do you want to insert a step and go ahead and insert it you see how easy it is in power bi our job isn't done here so let's keep going so I'm gonna skip ahead to the last step that we had and we just have a few things left here for one if you scroll all the way to the side then you're gonna notice that there is the grand total column here which again is redundant you don't need that we can calculate the grand totals by summing up all of these values so we're gonna right-click on that and say remove you can also find the remove columns button right here on the toolbar now hey we're on to my favorite steps now first of all this data shape why is that clumsy for power bi again it makes the table very easy to read for humans let's go back to that so why do we humans use this format well because you know we can easily scan across and say oh well how much do we sell in fenders in May and and you know we can it helps us but for machines it's clumsy because imagine if you go to power bi and ask power bi to say show me the total sales for the year well now it's got a sum up not one two three four but twelve columns and now imagine this data if it was spread across not just one year but multiple years you see how the problem just compounds itself just gets more and more complicated what we would rather have is instead of this data being spread out in columns you would rather have it in rows now this is something which was I found it so hard in the old world the BP era the before power bi oh my god I don't want to go back to that and it was incredibly hard to do for me and I did know a trick or two and sequel and pivoting to stop it was never easy I never looked forward to that but of course in power bi let me show you how easy it is so what we're gonna do is we're gonna select the columns that we need to move two rows and that that transformation by the way it's called on bit and guess what yes there is a button for that yep we're in the kitchen of power bi and we have all the gadgets and instruments available to us so we're gonna go in here and click on this unfavorite columns button so I mean let me do that and again watch what happens in the table and on the apply steps I'm gonna click on that and wallah look at that so all my data has been moved from columns to rows and of course that step has been recorded in in the applied steps let's do a few more steps here so one what we're gonna do is we're just gonna rename this column and what I did was double click I guess you can right-click and rename as well there we go and we're just gonna rename that to month and we're gonna rename this one to budget them out and next I'm gonna change the data type off the month column to date and for this you can go up in the in the ribbon here and change the data type from there what I find myself normally doing is I would just click on this this icon and next to the column name and then select the data type so and the one you want to select for this is date great now that that's done we are ready to move out of the query editor and and remember this was a window that opened outside and the way to do that is just to hit close and apply as before it's going to the budget table and boy this was a short table so it made quick work of that and now if you notice on the field side you would see the new budget table you would see it in the data view you can examine how the data came in so that's how a budget table looks like and it also shows up in the relationship view right there once you have all of your data sources connected like that and again you can connect to lots and lots of different data sources and for each data source and data set and table that you bring in you can apply exactly the clean shape and transform steps that you need in a query editor but the best part is from that point on to refresh all of that data from multiple sources with a lot of cleanup steps all you have to do is just click this one button refresh and it's going to go connect to all the data sources apply the steps that you just recorded and pull the data in now if you get tired of clicking this button you can also automate the data refresh in power bi we're going to talk about that later let's talk about the next exciting concept in power bi which is the magic of relationships now for this what I'm going to do is I'm going to delete these lines these relationships and these lines represent the relationships which have been created by power bi by default and rearrange the tables as well and I'm just going to right click and delete this so let me do that rearrange the tables and I'll be right back I am back and our tables are nice and pretty now when I work with my students and my clients I place a lot of emphasis on how these tables are arranged and I always arrange them in a very specific pattern now this might seem silly putting so much emphasis on this but this is important because this underlies really important concept now my friends here is a secret to really becoming good at power bi is you build models not reports most people in power bi all they care about is the visualization and that's the end goal and they have that in mind like oh I'm gonna build this report and needs to show sales by year or whatever I whatever report is building on but every power bi report should be underpinned by a really strong robust model and for that the this that's why this stuff is important so the way I've arranged these tables is who is with the data tables at the bottom and the look-up tables at the top now we're gonna have link to a separate video which with more details about data and look-up tables and their differences and also about power bi modeling best practices but for now I'm gonna I'll give you a quick version data tables record transactions and they have lots and lots of rows and they're really tall so sales can have well this one is short it's sampled data but this can have millions and millions of rows so that is our data tables tall and it's intentional that I have made this tall now our lookup tables are the who what where when how so if you look at sales well who bought it what did they buy the product where did I buy territory when did I buy calendar and sometimes you capture additional attributes in the house so these are the look-up tables and they're usually not as tall not as big if you had a hundred million sales transactions well hopefully you didn't sell it to a hundred million different customers or you didn't sell a hundred million different products it's just not gonna happen in a real data set so maybe you would have a few hundred or a thousand or maybe you know so less number of customers so these are our data tables and these are or look-up tables now traditionally if you are you know are an excel user what you would do next is you would do a vlookup from here to there so you would say vlookup the product key to the product table and just shall bring in everything down and essentially flatten the table create one big flat table now you don't need to do that all you need to do is drag and drop that's how easy it is to can create the relationship between these tables so I'm gonna expand this just a little bit so I can see so I'm gonna take product key and Here I am kind of just dragging it and I'm gonna drop it on the product key over here and again the direction doesn't matter and that creates this line which represents the relationship and if you hover over that it's gonna highlight the columns that it's that are connected I'm gonna do the same with the customer key so again this time again the direction doesn't matter it automatically detects which one is the data which one is a lookup table and if you if your tables are set up correctly well in this data set they are then the way it would look like is it'll have this one on here and star on this side now a little bit of technical speak this is indicating a one-to-many relationship what that means is that the customer key in the lookup table is unique it only appears once and whereas in sales a customer key can appear multiple times because hey the same customer can come in and buy multiple times and the other thing I'm gonna point out is the directional arrow on this relationship now this this again if you did things right this should point down to the data table from the lookup down to the data table and generally your relationships when you're starting out should look like this now there are other patterns there are many to many relationships they're bi-directional relationship and we can link to a video which covers all the details of that but when you're starting out your relationships should typically look like this until you understand when you use or those other types you should not use them let's go ahead and connect our other tables using the keys so I'm going to drag the sales territory key over here and for the calendar table I'm gonna take the order date and connect that to the date column in the calendar table nice now that's done let's also give some love to our budget table and we're gonna do the same thing I'm gonna connect the product key to the product key in the product table and I'm gonna use the month budget data as a monthly level to connect to the calendar date just a quick note here on the calendar table that is one of the most powerful tables in power bi and almost well every data set should have it now this one we're using a simpler version but I am going to link to the ultimate calendar table which is a lot more powerful and you can watch those videos next and learn all about that now that our relationships are in place let's go and have some fun with our data so for this we're gonna switch back to our reporting panel over here and we're not really trying to build a rapport we're just simply trying to have some fun and just explore the data now here if you have the visualization pane not expanded then make sure you expand that and here you're gonna find a chock-full of basic visualizations now you do have the option to add custom visualizations as well you're gonna cover that in a separate video but there's a lot of really cool graphics and visualization available to you we're gonna start with the one that's called the card element which is right there so you click on that and it places it on your report page over here so and and then now you can add fields to it so again here in under that now once I have the selected I have these three panes here let's go through them so there's the fields there's the format and then I think the last one is analytics so first one feels the card one only takes one value so I'm just gonna drag and drop the sales amount field over you're perfect so we can see the sales amount in fact you know what card is not that much fun so I'm going to switch that to a table so that's right there I'm gonna click on that and now it's a table now I notice that the sales amount isn't quite formatted correctly I would rather have that be shown as dollars in fact but even before that what I'm gonna do is switch to the format pane search for size and just make that a tad bit bigger so let's go with maybe 12 font perfect so it's a little bit bigger and again I want to format it as dollar so I'm gonna go to the modeling tab and say I want this to be currency and I'm gonna change the decimal places to zero all right so that's taken care of yep so you can see sales them out here and and and again this is the power of relationships now I can go to my calendar table and slice and dice at any which way I want let's start by slicing it by ear now sometimes power bi does this well which would seem silly to you but hey he's just trying to help you the best it thinks it can but sometimes it'll do something like where it's you can notice here that it's actually doing a sum of the calendar year which of course it's silly for us but you can easily fix that by clicking on the drop down over here and just saying it don't summarize so instead of sum you're gonna say don't summarize and now we see that we have a nice stable off or a salesmat so what I'm gonna do is I'm gonna copy pasted that table so let's just go here copy and paste so I have a copy of that here because what I want to do is I want to instead of sales amount I want to show budget amount so I'm gonna hit X and remove that column and go to my budget table and find the budget amount from there cool so I'm gonna quickly reformat it as well as we had done the other field perfect now you can see that we only have budget for one year so why don't we add a filter adjust for that year so we can dive in deeper in that year so for this I want you to expand the filter pane over here and you would notice two sections in here one is filters to apply just to this page or filters on all pages and eventually you are gonna end up with adding more report pages as and when you need to build additional reports but for now we just have one page so what we're gonna do is we got a drag or here over to this filters on this page and then we'll change this to basic filtering you could do this in advanced filtering as well but basic filtering and we will select the year 2016 and now that we have that the next thing I want to do is I just want to see it by month rather than year so for that let's go over to this table I'm just trying to move them apart great and I'm gonna move in the month right between year and sale so I'm going to drop that here but watch what happens so notice the sort order is a little weird and you can see the sort order it's sorting by sales amount we do want to sort by month but if you try to do that notice how it's sorting alphabetically because that is the default sort order thing up for text columns at least you can change that the hope we don't want or month sorted alphabetically and you can do that by selecting the month column in from your field list and then go to the modeling tab and then select sort by column drop-down and you can tell it to sort it by a different field and the field that we are going to use is the month number that way January has a month number one so it becomes the top of the list and once you have made that selection you will notice that it's now sorted correctly let's do the same for our budget table and this time once you have done the sort by column you don't have to do it again notice that it's already showing in the correct order so you can see the power of relationships here where they essentially work like filter transmission wires so just by connecting it with this table now you're able to slice and dice sales by any of the customer attributes any of the product attributes any of the territory or any of the calendar attributes let's do one more table we're gonna take our sales amount so let's do a new table and let's add our sales amount over there perfect let's make the font a little bit bigger and this time I'm going to go to the territories table and grab the country and slice and dice it by that I might as well collapse this filter pane and again I'm gonna drag in country perfect so you can see how we can slice and dice data any which way we want now the cool part here is then notice here that we had the sales amount and budget amount and we could see them side by side but you do not have to he in fact I'm gonna copy over the stable put it on the side over here and once when you connect your data tables and look-up tables in this manner you can put the the numbers or slice and dice them side-by-side so right here in this table I can also add the budget amount and you can see I can see them side by side in the very same table the only rule to follow is this you can only slice and dice using the connected tables the sales table can be analyzed by customer product territory and calendar budget table can only be analyzed by product and calendar and when you place elements from multiple tables together you can only slice and dice using the common look-up tables follow those rules and the world is your oyster let's step into the magical realm of Dax Dax stands for data analytics expressions and it's essentially power bi a formal language and it is one of the most incredibly powerful features in power bi so we're gonna start slow though and I've cleaned up the other tables we're gonna focus on sales amount here and what you may not realize is you've already created a Dax formula products measure now when you drag and drop a column over over from your field list to a display of to a visual area and it selects one of these options what it has done is it has created an implicit measure for you so the measure has been created for you you can see it but it's it's there for you and of course you saw the options there where you can change the measure as well to some of these options now my advice to you would be to never use implicit measures never drag and drop a column over to that so let me show you the other option which is explicit measure and from now on I'm just gonna say measures and by at that I would mean explicit measures so let's go ahead and create that so for that you're gonna go to the modeling tab and over here you're gonna click on new measure so let's just do that and here it gives me this formula bar so I'm gonna zoom into that and I'm gonna define my measure sales and it's going to be the sum of sales amount and you notice how it autocompletes in fact I don't have to start with the word sales I can say amount and it would find the options for that so it's pretty cool pretty nifty makes typing formulas really easier and you can go up arrow down arrow to select through the list and dab to autocomplete so you can see this is one reason why I fell in love with bar bi and because starting out you you realize that it's quite familiar I mean I was familiar with these functions some min max average in Excel and here I could use them in power bi now of course what I didn't know then was how incredibly deep and powerful it is so that's a good thing to know you know that there are lots of capabilities in there but for our first step you can see how easy it is and I'm gonna hit enter and let it calculate I'm also gonna format it as currency all right so I changed the format to currency as we had done for our CSM on column and now let's go back and now I'm gonna drag this sales to our table over here and watch what happens oh my G all right well actually nothing spectacular happened in fact they might have been loaded down for use like what's the big deal I mean this is you know just giving you the same number but again one of those is implicit measure we'd be dragging the column so again it is still a measure power bi under the hood still define the measure for you an implicit measure and the other one is we defined it now we're not gonna go I'm not gonna have time in this tutorial to download the details but there are three key reasons why explicit measures are so much more powerful and recommended they are control reuse an impact on connected reports so we're going to link to a video that goes into more detail but going forward we'll focus on defining our measures ourselves when we were creating our measure you might have noticed the button next to it to define a different kind of calculation not a new measure but a new column but before we get into that we're gonna save the file and look at the file size now this might seem silly but believe me we're gonna check the file size again and it's gonna reveal something really fundamental about power behinds and now we're back here and let's go on our merry way to create our first calculated column so for this I'm going to stay in my sales table so again I switch to data view and selected my sales table over here and here if you notice that we have the sales amount and tax amount in separate column so what if we wanted to add these two to essentially get a of these two so for that we are going to click on new column and it's gonna give us a formula bar as before so now I have this formula bar but it starts with the column name I don't want my column to be called column so let's change that and we're gonna call it total sales amount and for this one we're just gonna add these two quantities up just kind of how you might do it in Excel so I'm gonna say give me the sales amount plus the tax amount great and actually it is the best practice when you're referencing columns to prefix it with the table name so we're gonna follow that and I'm gonna hit enter and watch what happens over here let me quickly reformat this just so they look a little bit prettier and we'll come back and continue it sorry formatted them and I've also scrolled down just so you look at some interesting data and here you can see it is doing what it's supposed to is just adding these two numbers up and giving us to sum but now let's do the acid test of saving the file and checking the file size again alright so that is our new file size with the calculated column davi added could define the same calculation instead of as a column as a measure well let's try it out so what I'm gonna do is I'm going to come in here and copy this formula and this time we're gonna click the new measure button right there so I'm gonna click that and again it gives me the formula bar but this time it wants me to define a measure great I'm gonna paste the same one and I don't want to give them the sale same name so I'm just gonna say Doral sales and let's see if it works and how already you can see that there are signs of trouble those red squiggly lines are telling you something is off but if I hit enter I get back this era now what's going on I mean clearly that formula was good it was working here why would it not work in a measure I want my friends so this is one of the fundamental one of the fundamental differences between measures and calculated column which is the idea of a row context now I'll admit when I heard these terms ro contacts and there is another one you would encounter filter contacts they scared the heck out of me and sometimes things won't work like oh my gosh I gotta try to figure out what's going on with the road context of the filter context well it turns out that there are fundamentally simple concepts so rural context is simply the knowledge of the current row so when you're going to calculate it column and as power bi is going through these values and just trying to calculate them one by one it obviously let's try that again it obviously knows the row it's on and that gives it the row context now what that means is when you reference a column directly as we are over here we're just saying directly into the column right and this is also called a naked column reference where we are referencing it directly now this works because it has the row context in a calculated column but when you go to a measure notice that the measure you know it didn't show up as a column over here because the measure is not part really part of the table so here there is no row context and measure there is no built-in row context now you can create it but we'll save that for a later video so since it does not have the idea of a row context or idea of a current row when you reference column directly it's saying which value I mean you're asking me to get sales amount but I have lots and lots of them which one do you want again because it's lacking the knowledge of a current row now the way to fix that is to wrap these naked column references and put some clothes on them and you do that by the wrap are we gonna use or aggregation measures aggregation functions and one of them is sum and that's of course what we're looking to do so we're gonna wrap this in a sum and I'm not sure why the squiggly line is still there and I'm gonna wrap this in a sum and hit enter and see what it does not perfect so it calculated now again you don't see it here because again measures are not stored as part of the table that is another big difference between measures and calculated column so to see that really have it sink in we save this file again now after having to find a measure and see what the file size is right that is a new file size and if you noticed that there was only a very miniscule change and that is again one of the key differences between measures and calculated columns calculated columns every single number here that you calculated in this column is being stored back to the file so every new calculated column that you add to your model will increase your file size whereas measures the only really thing that it's storing is the measured definition and it's not stored back into the file instead measures are always calculated dynamically we're going to link to a separate video which goes into more detail about the differences between measures and calculated columns but let's go back to this view and start exploring the new calculated column and measure that we have created so I'm gonna remove the sales amount and sales and instead bring in my total sales amount column and my total sales measure didn't see that the end result looks deceptively similar and it's giving you the same numbers but if you think about the underlying approach if you keep adding calculated columns and I know for Excel users this this feels easier this feels more tempting but understand that you're gonna add blow to your model every single calling me add it increases its file size and that also means that it increases its memory footprint when it's loaded in the model and that's gonna that could slow the whole model down so instead of imagine if you write ten or fifty or a hundred calculations there's a huge difference if you use calculated columns or says measures some measures is the way to go it's more compact more efficient and always dynamic and again click on the linked video in the description to find out more about the differences between measures and calculated columns hey if you're still with me through this part of the video I would really request that you subscribe and hit the like button to show your support for this video in our Channel now that we have all fallen in love it matter let's go ahead and define some more so I'm gonna remove these from over here and I'm gonna bring in my sales measure and also bring in my budget measure oops actually I remember we had never defined a budget measure so we're gonna define it pretty similar to how we have a sales measure so I'm just gonna go to the budget table click anywhere in there click new measure and and just type it out all right we have that measure defined or put that on here and now you can see that we can visually compare and we can see hey when sales was higher but of course we don't need to we can define what I call a hybrid measure which goes across sales budget and defining that is as easy as this I'm just gonna say new measure and really what I'm looking for is variance that how what was the difference between sales and budget so let's go ahead and define that all right now let's add that on there and we can see how easy that was just a minus B and that's it now let's go ahead and define a variance percentage as well now here I could define it using the divide operator so I can say variance divided by budget but this when you use the division operator that always carries a risk of division by zero just in case deposit is zero in some scenario so the way out is use the divide function and you can see it says right there is a safe divide function with ability to handle divide by zero okay so why not I know let's stay safe and here you need to specify the numerator and denominator as these two parameters the last one is an optional parameter and you can leave that that's just what to return when when there is division by zero and by default and returns blank which is good enough for us so and of course let's format it and let's add it to our table now I just want I want you to stop and acknowledge and pat yourself on the back my friend for what do you have achieved here so what we have done is not only defined measures across two different data tables and being able to slice and dice them together but also define hybrid measures which are effectively operating over both data tables now doing something like this in the old in the in the BP and I prefer power bi would have been really nasty and ugly and in fact I had you know I was I would often create two separate pivot tables and have some calculation on the side of the pivot table I call them side table deductions I would let you come over the acronym for that and yeah these were extremely fragile and prone to breaking and they're not robust at all and what you get here in power bi is what I call the define once use everywhere magic of measures so once you have defined this invariance or variance percentage you can take any any of these parameters and slice and dice it any which way you want so I can say show this to me by category or subcategory and all of that just works and let's see it in its table so we can take a better look there we go and that's just scratching the surface but the idea is this that this approach applies to all data tables so I know I'm just showing here sales and budget but doesn't matter what your data set is it could be head come head count shipping or invoice or inventory doesn't matter once you pull it into power bi and arrange it and model it in a structure like the way I showed you with the data and look up tables and connected with the power of relationships and then you define measures and automatically they have the magic of define once use everywhere you can define hybrid measures across any of the data tables that you bring in and slice and dice them any which way you want using your lookup tables our next goal is to go from this blank slate to this beautiful and interactive power bi report but you may be wondering that we spent so much time focusing on the query editor that gets into power bi cleaning shaping transforming data and we spend so much time talking about relationships and tax and only now so late into the video are we finally talking about visualizations isn't bar bi about visualizations well my friends that's where a lot of people go wrong and and and you know that's not the bat to becoming an expert in power bi you have to realize that the strength and power off power bi lies under the hood and it's in these two engines the cranny editor and the model and so you know remember the 80/20 rule eighty percent of your focus and effort as a power bi author power bi model developer should be on these two things and if you do these right then creating visualizations is easy as you gonna see just now so again this is the end report that we're trying to create so let's get started you've already seen that in the visualization pane is chock full off pretty amazing visualization that you can drag and drop or place on your blank slate here but there are also options here to add a text box image and shapes and we're gonna start there so I'm just gonna add a text box and give it a label and next I'm gonna click on the image button here and bring in alright adventureworks company logo the next elements I'm gonna add are these card elements at the top let's go ahead and place the guard element here I'm just gonna click on that and that places it somewhere randomly on the page and first thing I'm gonna do is grab my sales measure that is right there and I'm gonna drag and drop it on to there I'm gonna copy this a few times based based based four of those and I'm gonna make it the sales budget variance and variance percentage so this one becomes budget and actually you can't drag and drop it on there but if you drag and drop it on to the fields you can replace that so that becomes the budget great the next one is the variance and I'm just gonna move that here and this one is the variance percentage to display units you would notice is automatic right now if you like you can change that to none or just show that in millions that's up to you we're gonna change it to none just to see the actual value right there next we're going to place a shape element just to make sure that this really pops out our high-level data so you know size it to change the line we're gonna change the weight to to be really small okay so the line is gone and next I'm gonna change the fill color so for that I would go in here fill and I'm gonna make it really light so I'm gonna increase the transparency as well by 60% and that looks nice and what I'm gonna do is I'm gonna move this thing to the back and and then I can move it behind these visuals that I have here let's go ahead and place the other elements on our report now first I might go a little bit fast here at the point that I wanted to convey was that building reports is Bart art Bart's science so in this part it's not really important if you follow what I'm doing click by click and visual by visual and and take full liberty to create and craft a report to your liking let's start with a bar chart and yeah we're gonna place it right there and here I'm gonna add both the sales and the budget and then provide the access as a product category and subcategory now notice what happens when I add the category there you see the breakdown well when I add the subcategory you don't really see a change but what this allows you to do is it allows drill down so in here you have all these buttons you can use or what I like to do is I just try to use my mouse when I'm on my desktop where I can just say drill down and I can drill down to the the specific categories and bikes and then I can drill up and so forth now one thing I'm gonna change here is the sort order and I'm gonna make it alphabetical so I'm gonna change the sort by to say category and and here I'm gonna say sort ascending perfect I'm gonna copy this graph and instead of sales and budget I'm gonna bring in my variance and what I would like to do here is to show some coloring on here so for that I'm gonna go to the format pane data colors and right now at least in the current functionality what you do is you click on the ellipses here and choose conditional formatting that brings up this dialog box and color scale is exactly what we want although you can see that there are other options available as well but the way we want to color it is actually based on the variance value so we're gonna go ahead and select that and lowest value in the highest value and that looks good and I'm gonna hit ok and here now you can see that if we if we drill into let's say accessories you can see the detail where the worst value is is like a deep shade of red and it's green as it gets better let's build our next graph and I'm gonna copy paste this here and change this to a line chart and instead of category and subcategory I'm gonna bring in my month and from my lookup table that calendar table I'll copy paste that again change this one to column chart and instead of budget and sales I want to show variance here similar to the graph above and I'm gonna add color to it the same way I had done foot for the graph at the top the last two items are we're gonna add our this detail little bit of detail showing our top products and top customers so let's add a table element over here and I'm gonna grab the product name from the product lookup table write name great and I'm gonna grab the sales from this table so I'm gonna do three things here first let's change the size to be a little bit larger and also turn the totals off because we don't really need the total we're already showing the total over the area so we're gonna I just searched for total and turn that off and the last thing that I'm gonna do is I don't want to see a scrollbar here I really want to see only the top products now I can sort it descending my sales but notice how there's still a scrollbar so for that what I'm gonna do is expand the filter pane and I notice here that then a visual is selected you have additional options once a visual is selected you can specify additional filters just for this visuals and that's what I'm gonna do so I'm gonna say product name instead of basic filtering I want to use the top in filtering and in this case I want to have the top ten items but it also asks you with using what top ten based on what and for that I'm going to add back in the sales measure that I'm I'm showing here and apply filter and notice now is just showing me the top 10 values now we're gonna recreate this using customers so now instead of top products we're going to show top customers that's it our report is ready and this is fully interactive so you can click on on something and see what was going on specifically details in that month you can drag thing here and still select to see that and of course we saw the drill down earlier where you can drill down into specific category and select a specific subcategory if you needed to and everything in that report is gonna filter down just to that selection you have come so far we've gone through all these steps and are now ready for the next phase which we would start by publishing a report publishing your report could not be easier the publish button is right bang in the middle on here and all you have to do is click publish now the first time you do this you might need to create a Barbie I power bi account but it's free and it's really easy now here instead of my workspace which you should be choosing I'm just gonna use a different workspace now this is a pro feature which requires a paid license and I'm gonna link to a video which talks to you more about the power bi licensing but again if you select my workspace which you should then that does not require a paid license and that is that you can use with creating a free profile on power bi comm so click select and there goes off sending your model to the cloud and publishing it to power bi comm that's done so what we have done is we have taken the model that we are third with so much it was so much love and now we have put it up in the cloud and that can be used by hundreds of users let me show you how here we are in I'm using a scratch workspace but again for you it would be just your my workspace so go to that and what you would see in here are these tabs and now we're gonna start on the dataset tab right there and this is where you should see the model that you just published now data set in my mind is basically your data model now what you can do here is if you scoot over to the reports tab over here you're gonna see there is a report as well not if you open this report they should look familiar this is what we've been building on all along so your report has been it is there as well but what this does is this concept of separates the model from the reports so if you go back to our main workspace the report just becomes one report that is connected to the data set you can go back to the data set and click on this icon right here to create as many power bi reports as you want so again what this gives you is that this new blank canvas and I can just say give me sales by month and there you go I have created a new report and this is the strength of the power bi platform you have this one data source a single source of truth and now you can create multiple lots and lots of power bi report all connected back to that single model to that single source of truth you can also set up automated refreshes for your data set and that I'll let you explore by going to the data set tab and again once that single source is refreshed all the connected reports will automatically be refreshed and as you can see by the icons here you can also create Excel reports that are connected back to the model and all you have to do is click on this and say analyze in Excel and that will let you create and an Excel report which is connected back to your single model power bi has yet another amazing feature which we haven't even explored yet and that is dashboards now you have seen a report and report is something that you build in the power bi desktop and right now we just have a single page here but you can create a report with multiple pages and that's what identifies a power bi com report you can have multiple pages on here and it gives you this rich detailed an interactive love look where you can slice and dice and click and explore your data the idea with power bi dashboards is to give you a high-level view a snapshot picture which you can easily take and easily look at it on your mobile phone using the native power bi app and hey if something interests you then you can just tap it and go to the detail by diving into the report for you so the dashboard is this high-level view now how do you go about creating a dashboard well you go to your report on power bi comm and if you hover over elements you'll notice that it comes up with this thumbtack icon and in fact if you hover over that it's gonna save in visual now if you click that so we're gonna start with that element it's gonna sit pinned to the dashboard and right now we do not have an existing dashboard so that one is great out so we're just gonna create a new dashboard give it a name and just bin the element there now if I were to scoot over to the dashboard right now it doesn't look very interesting but let's go back to a report and and clip in a few more elements I'm just gonna select the visuals that I tend to like and and hover over them and click on that pin visual button and this time I can just select that existing dashboard and select pin let's do that for a few more elements hey I found pinning these elements a little bit tricky my best advice is to click to select that element and then it should be easier to and to click the pin visual button go ahead and bend the highlighted visuals shown here to your power bi dashboard alright let's go to our dashboard and see what it looks like now now we have these different elements but it doesn't look very pretty doesn't look very well laid out but this is so easy to organize now these individual ones are called tiles and they snap to a specific grid and if I make it small enough it's gonna snap to a specific size now that what that snapping does is it makes it really easy for Barbie I to lay it out in a very beautiful fashion on mobile devices so let's go ahead and just organize our ties a little bit better here we go not not a bad-looking dashboard again again the idea of the dashboard is to give you this high-level view and if you see something that's interesting you can just click to dive into the report experience we have detail rich interactivity where you can slice and dice the data and see what's going on let's go back to our dashboard and I'll show you the key feature the ability to share reports and you do that by clicking shared dashboards and you do that by clicking on the share button and here you can just type in any email address that you want to share the dashboard with now this does require pro licensing a paid license you can sign up for a short trial but I'll link to the video covering power we are licensing in detail your next steps from here we'll decide your fate my friend we have been on an epic journey together to build the model and understand the power bi framework but real learning comes from doing so number one watch the video again and make sure to follow along with the download files use the table of contents in description if you need to number two watch the linked videos to go deeper and number three join me live for talk power bi Fridays when you're ready join our paid training program to learn power bi family to continue your journey until next time bow on my friend

Channel: Avi Singh - PowerBIPro

Views: 5,340,847

Keywords: power bi tutorial, power bi desktop tutorial, power bi tutorial for beginners, power bi desktop, power bi dashboard, power bi training, power bi demo, power bi for beginners, power bi dashboard tutorial, power bi tutorial for beginners excel, power bi, tutorial, desktop, dashboard, excel, microsoft excel, power bi vs tableau, tableau vs power bi, tableau, mspowerbi, Power BI Pro, PowerBIPro, Avi Singh, powerbi

Id: AGrl-H87pRU

Length: 63min 30sec (3810 seconds)

Published: Wed May 01 2019