Make a Sales Dashboard in Power BI in one hour - FREE Live Masterclass

hello everyone welcome to our power bi session first up please tell me in the comments if you can hear me loud and clear cool i can see the first comment showing up that yes you can hear a big warm and beautiful welcome to you all people from all over the world i was actually on the chat for about 30 minutes now just watching the messages off and on while i went downstairs to prepare a cup of coffee and i am super excited about this particular session for two big reasons number one obviously power bi because people have been asking for a power bi live stream for since the time i began doing the live streams earlier this year so every month i would put up a request on my channel asking what do you want to learn and power bi was like a recurring theme so i'm excited to have this power be a session but there is a second reason for which i am actually excited and thankful to be meeting you all in person on this day this is because when i went to bed last night we had 99 923 or some subscribers and when i woke up we have 100k subscribers so yay a big celebration thank you so much it is it is such a fun thing to wake up to but also uh it is super super joyful to share the news with all of you and personally thank you for being with me on this journey learning from me and supporting the channel and as well as my mission of making people awesome in in your work so thank you so much all right um what we have what we will do is just as usual we will get started i've got a a small presentation that will walk us through the proceedings for the day and then we will jump into power bi i have already uploaded the sample files for our session in the in the description of the video you can check that just expand the page and you will see all the links you are more than welcome to grab these files and practice as i am teaching in the live stream alternatively you can sit back relax watch the thing download the files and maybe take some notes have a sip of coffee or water or beer or whatever else you're having and and then when you when you have some free time later on in the day practice the ideas so uh yeah um okay let's just uh jump into this one here um so this is our sales dashboard with power bi although it does say sales dashboard there is nothing super salesy or marketing about this particular dashboard because this is kind of like an entry level idea into power bi so i'm using retail and sales data as an example but even if you are working in other industries or other lines of work you can straightaway take these ideas and apply them within power bi to your lines of work so this is the specific dashboard that we are gonna construct uh we will try to get as close to the one that i am showing on the screen as possible but um given the constraints of doing it live in a stream as well as you know answering some of your questions and all of that i'm not hundred percent sure how far we will go we will try to get as far as possible but if it gets too long or if i don't um you know if i make end up making detours because of the questions or whatever we will uh i mean i'm giving you the full copy of this file as it is so you cannot actually go and and kind of re-engineer it but we will try to make it as much as possible so how do we make such a thing what is the actual process that we are going to follow the process involves these four steps as far as this live stream is concerned we are gonna look at the data um that supports this analysis we're going to have a kind of a free play session within power bi where and i'm going to show you uh how power bi works how things how you can set things up what are some of the unique things about it how it is different from other mainly popular data visualization tool which is excel how it differs from that and then we are going to create some simple measures and dax the ideas that we cover in the dax space kind of fall back on to what we have learned in the previous month's live stream even if you're not there it doesn't matter because i'm going to cover them again but if you have been there then you would be like oh yeah i get that and then finally we will make the dashboard before we begin there are three things that i want to quickly mention number one download files as i said the download files are already uploaded you can check the video description for the blank data file uh and the completed dashboard power bi workbook and you can right away download them if you want to refer to them as we go along uh alternatively as i said make a note of these things sit back relax and enjoy the whole show come back and get them later on and if you have a question i would love to hear your questions your commentary your chatter so please post them in the chat window if you have any questions please start them with q so that i can actually spot them among all other things that you have been messaging and saying and if you are asking a question please stay on the topic which is power bi sales dashboard for this particular live stream and if you already posted a question please don't repeat it because that will be fairly inconvenient for everybody else as well as i may not be able to respond to that kind of thing all right that's that and uh i would really appreciate if you can support the live stream with uh with a super chat or a super sticker it is really simple to do and uh you can go to the chat window click on the dollar icon and and share your support all right uh we will jump into our power bi but i'm gonna switch back into the stream just for a second here uh so that i can see all the messages that are coming in uh and uh you know kind of read through some of these um so there have been many many congratulations from all of you and again i am super thankful for your love and support to my channel uh as some of you may know i've started my website all the way back in 2004 but kind of pivoted into excel area in 2007 but even my youtube channel i started i believe around 2008 2009 so it has been here for such a long time but i never really made youtube as my my first point of content delivery it used to be my website and my blog but sometime during the last year i kind of changed the track and i said i'm going to look at youtube and make that as my primary mode of preparing content and delivery and i'm really enjoying creating videos and having a lot of fun making them and sharing them and doing this live stream so super thankful for all of your love and support [Music] um all right there are some uh questions regarding power bi that are coming up and uh is it a paid tool anyway uh to power bi desktop software is a free tool you can just download it uh by going to powerbi.com or if you have windows 10 you can go to microsoft or store and and download it from there um all right there's quite a few people all over the world saying hello from mexico from iowa usa from hyderabad from india so again super fun to have you all here i am excited to get started okay what we will do as i said is we will go in and um and get into power bi uh as i mentioned early on if you want to get uh the data for this it is already uploaded you can go and download the data set by looking at the video description below okay um so while i get into power bi um you can tell me what you're having as i mentioned i prepared a coffee in fact just for the live stream i had to buy this cup because every time i bring my coffee in the in the regular cup it would get kind of very cold by the time i could actually take a sip so i went to the supermarket and i bought this one which is kind of like an insulated one but when i took a sip earlier it was too hot like really my tongue was burning so yeah tell me what you what you are enjoying while uh while you're on the stream and uh okay so let's just uh jump into the power bi um so this is uh the dashboard that i have prepared for you uh and uh you can see that it is um showcasing the sales report uh for a fictional made up company called awesome chocolates and this is what we will create but i don't want to overwhelm all of us by showing this and then kind of trying to reverse engineer it we will make it from scratch so that everybody know who is new to power bi you feel welcome do you understand how things work and what happens when you get in but if you are somewhat familiar you can kind of open the file and then see that i just want to give a shout out to bill uh bill by mr for uh for his super chat thank you bill and um and there are already some questions coming in on power bi um regarding how to merge files etc etc uh because we haven't even started the topic i'm just gonna ignore the questions for now but uh we will come back and uh and put those questions answer them later on as we start getting into it so the first thing that i'm doing is i will go and click on new in my power bi but if you have not already opened this file you would just open power bi which is examine the same experience so we will click on new this will open up a brand new window of power bi and this will because i think my computer is running multiple process here it will take a few seconds to show up but eventually the blank window of power bi will show up with this nice little welcome screen and there's some real really positive and amazing messages in the chat window i would just want to highlight some of them chandu you are the person who has helped me immensely in excel throughout my career from nelson thank you so much nelson for that beautiful testimonial and uh bz flower bee uh also uh sent in a message saying looking for hr dashboard as well uh i know there's been quite a few people asking for the hr dashboard i i'm gonna prepare a hr dashboard and share that in a later video for sure um and some people are still uh thinking where's the file the files are in the video description just expand the video description you will find the files okay so when you open the power bi this is how it looks it would have a welcome screen with various things you can kind of close this off and if you have not logged in you would kind of also have a screen asking you to log into power bi um again logging into power bi is not a necessary thing but you can also sign up for a pre free power bi online account through which you can log in and kind of connect which will open up the door for the other side of things like you create stuff within power bi but whatever you create will stay in your computer unless you publish it so that other people in your organization or your team or wider world can view it and that is what really power bi is all about so we'll close this and then we will kind of look at how to get started now some of you might be thinking what is power bi right you know is is it is it like a data tool is it a visualization tool is it what so you could think of power bi as a metaphor you could think of this as it's like power point for data you know it's a very loose metaphor it's not it doesn't really do full justice to what power bi can do but it is a good starting point you can think of um using a more visual medium to talk about your data prepare your data analyze it visualize it as well as share it with wider audience so that's that's kind of what power bi is all about so it is a tool where you can do the data analysis you can crunch you can prepare your data you can crunch your numbers you can make some outputs and then you can then take that you can share it with wider world how do you share it if you have an excel file you share it by emailing the excel file to your colleagues and then they need excel to view that whereas within power bi when you share something your colleagues or your clients or your customers they don't specifically need power bi they can just open the thing in a browser and it will show up like a web page showing you that beautiful information in fact the sales dashboard that i that i prepared it is already shared on the web so i'm just gonna go here and open that up so that we can see how this would look like if we if somebody goes to web and and then sees it the link for this is also in the video description um it says preview the dashboard online in the in the video description section and when you click that in the browser this exact dashboard opens up on my chrome as a page everything is still functional but it is it is online so that's uh that's what uh power bi allows you so it'll let you create what you want and then share that with your audience without having the limitation of having a software you can share that as a web page you can share it as a as a as part of power bi app so that they can see it on their mobile devices and whatnot but how do we get started with power bi when you get in obviously it is a data analysis and visualization tool so you need to start with data and the obvious choices will be where is your data and it will show up these buttons asking pick your data source the key ones are like your excel files or sql server or you can paste the data from copy paste or you can use one of the built-in sample data sets but there is other buttons on the top which have like even more options you can pretty much connect to any kind of data source that is available in your organization or in the cloud and use that to bring in the data for the purpose of this exercise i prepared the data set in excel file because this will be very easy for me to distribute to you and for all of us to use without getting overly complicated and this data set is a made-up data set of this fictional company called awesome chocolates some of you are familiar with this company because i use this quite often in my videos and it shows the information of the chocolate sales by sales person geography product date on which the transaction happened how much amount our customer sales person has sold to how many customers how many boxes and then it kind of goes on for 2000 rows this is uh the 2021 year-to-date data so it goes up to end of may and then we will make our dashboard analysis with this we'll then see what happens when new data is added how to update our power bi dashboards towards the end of the session so you can understand how uh any data sets from your live situations you can use that to prepare the analysis apart from the actual sales data there is also other some other supporting data here so i got uh three individual tables one about my products the other one about my geographical information and then the third one is about our sales persons which team they belong to and their profile picture so we will use all of this to kind of make it a zazzy nice looking dashboard for our data again as i said you can download this file it is linked up in the video description so we go into power bi we can click on the import data from excel button and link it up so i'm going to just point to my blank file open and then that'll connect that all right um i just want to have a look over in the chat window to make sure that i am not missing any important conversations or technical difficulties [Music] so steve says do you have to open any options in excel to um i suppose not but if you have downloaded it from the web usually what excel will do is it will kind of open it in a protected mode so you may have to unprotect the file and or place it in a different folder so just open it in excel and then say that i trust this or something but it doesn't really need to be and carla says thumbs up and keep the coffee coming thanks carla i wish i could send you a cup over there but uh please enjoy one on me and um and helen also sends in his super sticker as well thank you helen uh for your love and support so jerry asks how do i share the dashboard without giving access to manipulate the backend data files this is one of the key components of power bi ecosystem you're only sharing the outputs not the data so i will show you how it will all look when we when we implement this um victor also says thanks for the amazing class thank you victor for your your support um and uh there are some genetic questions on is power bi better than tableau or where would it fit in excel etc we'll talk about these kind of things towards the end let's first understand what this is with an open eye and open mind and then you can see where it will fit into the overall spectrum of things all right now there are some very technical questions on other aspects of power bi i will address them as we make progress because some of those questions will become answer you kind of get the answer as we get into that okay i'm going to turn off my face now it'll all be power bi for from here on for some time and then we will i'll come back and show my face from time to time but uh um let's just jump in now and so here i open the excel file i clicked on excel pointed to my excel file and now it is showing me what is in the file it has these tables and spreadsheets i'm just going to connect to these tables we need all the four tables for our analysis so i'm just going to pick the tables i'm not going to pick the spec spreadsheets per se just the tables because the data in the excel file is actually in tabular format in those tables so that's what we need and then we can just load now you can also transform the data this is where if you are bringing in the data that needs a bit of cleanup either because there are some extra columns or something is not all there or some extra spaces are coming in or whatever else need to be done you would use the transform data option which will open up the power query window through which i can kind of manipulate the data and it is again a very very powerful feature of power bi you can use that to change the data portion of your input so a classic example for this could be let's say you get the data but not everything comes from in in one shape so your sales data is broken down into individual monthly files and you need to kind of combine everything stitch them together into one big table before you start analysis so that's where power query can help you with transform data you can use that to append files and create a large table and then use that to build your analysis in our case we have it easy everything is nice and clean so we don't need to do the transformation option as such we'll straight away load this up now when you bring in the data if you are bringing just one table then you can straight away start analyzing it but if you're bringing multiple tables you need you need to perform an important step here this step is called data modeling this is where because you are bringing in multiple tables you need to take a minute and figure out how these tables are linked to each other what is the relationship between those tables and you can view this by on the within power bi it kind of looks like a big giant blank canvas but there are three buttons on the corner the top one is like the visual layer so this is what i'm seeing now the last one is my model view and i can go to the model view and this will give you a pictorial representation of all the tables so you can see that there's four tables and as i brought the data power bi is smart enough to figure out that this looks interesting i'm just going to connect the tables because there are some same column names here so it did that part and it kind of connected some of these tables let's just double check everything i'm just gonna make it somewhat big so that you can see it but here what is happening is this is my people table and that's my sales table and they are linked up on the sales person column because it's the same column name in both tables power where it kind of took a guess and linked it up so that line when you hover it will show you sales person here is linked up to sales salesperson there same with my sales and the product table here they are linked up on the product column so same product appearing in both places location it was not able to figure out because the column names were not matching so i'm just going to do that by saying jio is nothing but geography and link that up as well so we now have a data model set up with three tables sitting on the outside and one table sitting in the inside all right so this is where normally within business situations this is how your data would always look like you will have multiple tables feeding in and you need to link them up but again there are different ways this happens so for example joe my wife she works as a power bi reporting analyst for one of the government ministries here and within their teams they don't individually do this there is a central person uh team kind of manages this setup so when they try to analyze the data they connect instead of to individual data sources they connect to a built data model and then bring that over as well so depending on how power bi is implemented within your organization you may have to do this step yourself or someone else will manage this and give you the final output like this so you then go in and analyze so you need to see this idea in conjunction with what's happening in your real world there's been quite a few uh stickers coming in i just want to take a second to thank them chad and willis uh sends in a super sticker uh so does ian and nelson's songs as well uh sensing a sticker um in in helen uh as well thank you so much guys thank you so much for your support and love uh and um let's just uh see if there is any other questions coming up okay so things seem to be looking good um so this is the data model time for us well not a joke but something silly so there is this bar in seattle uh in near the microsoft offices where all the project managers usually go and uh enjoy a nice nice drink in the evenings when they finish their work but the bartender really hates the power bi project managers can you guess why it's because they're always joining the tables well that's a silly joke but that's really what we have done here right we took the tables and we kind of joined them by combining kind of saying that this column here is linking up to that column there this way you get to keep your data in the shape that it is and then still do the analysis so this step is called data modeling and this specific data model that we prepared here wherein we got one central concept the sales table and then these other three little tables that are linking up this particular kind of modeling is called a star schema modeling this is because it kind of looks like a star there is a central concept and then there is other these multiple tiny little concepts on the outside of it and when you look at all of them together it looks like a star will not not not a pretty star or anything but just a star and there is actually some special names for these things again it's not necessary to know but this central concept is usually referred to as a fact table this is because it contains all the facts or numbers and these outer tables are called dimension tables because they provide the information in a specific dimension so for example my people table gives me the information in the people dimension this gives me location information and that gives you the product information so each table throws the light in one dimension of the data so that's uh you you might hear these words especially if you if some of your data colleagues talk about them and you might think what the heck is a fact table anyway this is that's what this really is all right so we connected our tables you can see that these tables now pop up on the right hand side as well and now we go back here let's start by playing we'll then understand what what goes on and then we'll define our goal for the dashboard and we'll start building um i'm just checking what's happening uh cheryl sends him a sticker as well thanks sheryl uh and so we got our our blank canvas here and um let's start building something and then you will understand like how things will work we'll make we want to see how much sales are happening within geography so my geography is in the location so i'm just going to go here pick the geography and we will want to see by amounts in a bar chart or something so i'll just pick this stacked column chart click on it it'll add a visual to the canvas this white space is called canvas so we'll put that and then i'm just gonna select geography drag and put it into the axis so this is what we're doing drag and drop and that will add geography as an axis nothing happens now because we only put the axis but we need to tell what needs to be on the value side of things for us to make the graph the value needs to be my amount so i am just going to take the amount and put it into the values area again the very first time you do it will look funny but you can kind of draw a parallel between this experience and constructing a pivot table in excel specifically a pivot chart but we make a pivot table and then make a pivot chart so that's that is kind of analogous to what we are doing here and we will get a nice little graph here with my geographies at the bottom and the amounts showing up as columns it is automatically formatted into millions but that's what it is i'm just going to quickly tweak the font sizes here so this is this area here has three tabs usually three or two the first one is where you set up the second one is where you format um and from here i'm just gonna search for size and x-axis text size i'll make this like a nice 20 points so you can actually read that um and then y-axis text size will make that as 20 points as well so you can see those numbers so this is how you can use the search to look through all the formatting options and then find what you need to do and quickly adjust that you can also on off certain things for example here it says amount by jio that's my title if i don't want a title i'll just turn it off and it's gone from the screen anyhow nothing super amazing super powerful so far right all we made is a bar graph uh well a column chart uh you could do this in any other software as well so that's the first one now my geographies are there i'm just going to look at by teams as well a similar graph so i'll make another graph here uh if i want to have the same kind of font sizes and whatnot instead of making it from scratch i can kind of ctrl c ctrl v so i'll get a duplicate here and then from this one i'll take away my geography and then i will add my team there so that we'll inherit the formatting so we'll be a little bit lazy here and try to do this so then once it is done we have my geography here we have my team's information there two different graphs so the number one first key takeaway this is the place where power bi so far kind of looks like excel or other tools but now it starts to diverge because what happens is if i click on an individual team then instantly the other graph will reflect this team's corresponding proportion like what is the delish teams sales to these geographies so this is the first key idea you can kind of think this as a revolutionary idea or something but once you start using it it kind of becomes second nature and you don't really find it amazing but the very first time you see this you'll be like whoa what it just happened right so that's the key thing where everything is interactive you don't have to set up or specify anything by default the visuals are interactive so whatever you put if you touch something the other visuals will respond by automatically filtering down to the selected portion and then either show only the filtered values or highlight the filtered values so in this case a delish that's the value if i click on for example india i'm seeing india's proportion of sales by these three teams so that's the uh that's how the this the initial exploration of power bi we are learning this particular behavior where everything is interactive so what really happens in the background though is as you click on something power bi runs this engine behind the scenes that will go and filter for indian india values and it kind of goes back and forth so we selected india in the geography table so from here it goes here and then reduces the number of rows within this table visible for the analysis purpose to only india rose and then it will take the people information overlay that split that by individual people highlight their values here in the graph along with the actual totals as well and you can put your mouse on that it will show you team delish amount is whatever 2.8 million and highlighted is 491 000 so this is the first step wherein we are using the amount value and then two different graphs let's see what happens if i put another one so if i go to my product and then i want to see these two graphs but i just want to see this for a specific category so in this case we will add a different type of visual this visual is called slicer a slicer is pretty much like a slicer that you see in excel and when you add a an item there a slicer will give you options for each of the choices that you have within the category i'm just gonna make this one as well nice and big so you have bars bytes and other and then if i can click on one of these then what the slicer does is it will kind of restrict the data that is flowing to all other visuals by saying only look at the bytes so only bytes data is used and that within bytes data this is how much india is bringing and that's how much these three teams are further broken down into that value so this is the interactive effect of power bi wherein everything is interactive certain visuals act differently so bar charts and column charts and lines when you click it will interact for that particular point whereas a slicer is more like a selection thing so you select you're really restricting the amount of data that kind of flows in okay time for us to understand all of this in a very clumsy hand drawn diagram so i'm just gonna switch back to my face and bring up the big guns here so a white board well technically not a white board but just my notepad you can think of power bi as uh we have we have done kind of a little bit of play so you know exactly how it is it's a visualization software but it acts in three different layers so normally when i do like a face to face training in a classroom i draw this on a big whiteboard so everybody can see but this is the best we can do with uh within our youtube world so you can think of power bi as big box and then there are three layers to it so we'll draw three boxes here and you can think that my data is outside it kind of goes through a journey and then you will get insights so that's that's the journey so what is the journey that we are taking here there is a data layer there is a calc layer there is a vis layer so those are the three layers data calculation and visualization those are the three layers through which power bi kind of flows your data into insight the data layer the technical name for this is power query we kind of sneak peeked into this when we were loading the data it said transform the data do you want to transform it we didn't have to transform anything but even still the fact that it was going to an excel file bringing in the data all of that is the power query magic the calculation layer is the one that is doing all the number crunching we haven't really seen it but it was acting really um it was working hard all the while the the dashboard oh no it's not really a dashboard this page that we just created all of these numbers and the highlighted portion values all of these are calculated because of the calculation layer so the technical name for this calculation layer is power pivot again some of you attended my last month's live stream you would know exactly what powerpivot is it's it's it's the calculation engine and the visualization layer is where we are making all our pretty graphs and doing the little funny things of interacting adding slicers and all of that so many times when people think of power bi when people say i'm learning power bi when people say i'm using power bi they kind of loosely refer to the visualization layer this is where the magic happens but you can't have magic here if you have like really poor data flowing in and incorrect calculations happening so the real background things are these two ones so when you want to really learn and master power bi you need to understand the journey you need to understand how to take your data take it through power query power pivot to turn it into useful visualizations so unfortunately because of our considerations with the live stream i'm not going to spend time in the power query space but i do have some resources for that i have prepared some example videos and other stuff and i'll provide them to you later on powerpivot we did cover in the last month's stream so i'm not going to go into two depth but we will show that and then visualization is where we will construct our dashboard all right that was quite stressful because i'm not even sure how it's gonna fly but hopefully it went well um now let's go back into power bi so we will switch back to this and we got this nice little graph that shows india's proportion of various teams and i can switch to new zealand i can unselect this one of the common questions people ask is oh you selected how do you unselect you just tap it on the same button again and it will go back to unselecting the thing so you're back to normal so this is uh that if i want to for example change my mind and i don't want to look at this like this i want to look at it by some other graph select the visual you can see that it is highlighting the this particular one here i can change it to for example a pie chart not gonna look very pretty with the six equally sized pies but you know you can change it to a pie chart or or a donut chart tree map whatever else you want to do you can even do a line or area graph this will be ridiculous to have that in the line but you can do that so this is a little bit of play into this now instead of doing something fairly arbitrary like you know let's do this let's do that let's actually be focused with the direction the direction that we want to go is we want to create this okay it's actually a little bit of uh stretch for us to go from where we are to this place but we will try we will try to go as far as possible as i said so this is my page one i'm going to double click here and name this as play this is my play area where i'm going to test out few things every time every now and then we will add another page this one i'm going to call as my sales dashboard and we will design the dashboard here now normally when you are preparing any dashboards or any complex reports you want to actually sit down reflect on what you're trying to do and get it as close to as what your audience needs because you're not building this for yourself you are creating this so somebody else can read it and understand what the heck is going on in the awesome chocolates company and you can only do that if you have a good vision and clarity of what is needed now in this case of live stream we have the luxury of i can assume a lot of things so i'm assuming this is the design that we want so we'll just go with that design we don't have to really do a lot of thinking we'll just go with a plan that is already made for us and the plan is we want to visualize the key metrics the key metrics are how much money we are bringing in how many customers we are serving how much quantity um sorry i think it's easier if i refer to it how much money we are bringing in what is our total cost and what is our total profit so the three key metrics it's kind of like a profit loss statement here and then profit percentage because we we want to be highly profitable chocolate company how many customers we are serving and on average how much each customer is purchasing from us this is a key metric for us because if each customer decides to suddenly purchase less chocolates then overall revenue goes down profit goes down and all of that so we want to track these six things and then we want to visualize a few other bits and bobs about that so time for us to start some calculations we can kind of do the total amount by taking the amount and putting it here and it was doing that but if i want to control it a bit more like show it in a certain fashion in dollar formatting as well as use it for calculating cost and whatnot we need to take control over these things so this is where you will right click on the table and then you can add a measure so you can use the measure feature within power bi to define calculations on your own so we'll say new measure and this measure will be total amount and then this is equal to sum of sales amount column it's a very simple measure and here we are using the special language of dax data analysis expressions language to create a calculation and attach it to the model okay so when you click this little tick mark or press enter it will attach that and the total amount will come in and sit in the model it will also have a tiny little calculator symbol next to it indicating that this is a calculation and not an actual field in the data okay so whenever you have a calculation if you check that it will automatically be added to the values area of my visuals so because it's a calculation it will be visualized and the sum car format some function here is basically summing it up the amount column what is it going to sum up it will sum up whatever is available to it at the point of calculation so when you set it up nothing will really happen none of the calculations will happen but when you use it in a visual so if i select my amount my team drop this amount and add that one at this point it is calculating the amount but it sees oh on axis we have teams so i'm going to split this by team and each team i'm going to calculate and then you interact by clicking a bar category it will further slice that number into bars if you select a country it will further slice that into that country and calculate so all of these calculations are done by the data calculation engine power pivot and then produce it to you that's why when we set up the calculations also we use the language of power pivot which is dax to create these these calculations so total amount done we want to figure out total cost now for time for us to figure out how to calculate the total cost if i go to my table view from here i can see individual tables so i'm looking at sales table within the sales table i only have amount column so i only know how much money we brought in i don't know how much money we spent in making the chocolates if i want to know how much money we spent to make the chocolate i need to go to the product table and here i got a cost per box so for example milk bars are 5.26 per box and if i go to the sales table i might find that here carolyn sold 45 milk boxes for 5 257 so from here i know the fact that we sold 45 boxes and i also know what is the individual cost per box so i can calculate the total cost take boxes times cost per box so this is how we can calculate the total cost first we will add a column to the table called cost and then figure out what is the total cost here for all the boxes and then we will add that up to calculate the total amount so let me just um add a column you can go to the table tools and from here click on the new column and then from there i'm just going to say this column would be cost this is equal to boxes which is my boxes column here times cost per box where is cost per box it's in the products table so it's not in this table it is in a different table within excel i don't want to constantly say excel but you know it's a easy thing for us to fall back on so in excel we would use a lookup formula to do that here also you can do that but you already have linked up the tables right both these tables are connected so we don't need to specifically look up we will use another concept called because there is a relationship get me the related cost per box for this particular line item so it'll go look at oh i'm in choco uh whatever is the cost per box for alvin choco will bring that and then it will multiply that with that and then you will have the cost item there so for example this line item here we sold 422 boxes of almond charcoal for two thousand dollars but it actually costed us two thousand one hundred and seventy three dollars to make them so we kind of made a loss on that particular line but that's uh how you you calculate the cost so once this is done we go back here and from there we'll make a new measure this will be total cost and this is nothing but sum of that cost column we just introduced okay now of all the things that we are gonna do i'm hoping that the most complicated one is calculating the total cost because this is where the information is not directly available it is kind of we had to bring it from two tables and mix it up and then show it so if you found this bit easy then you're going to find the rest of the video session fairly easy to follow along but if you had some difficulty you don't have to beat yourself up just as i said sit back enjoy and then because the video is on youtube you can come back to the same page later tomorrow or day after when you are uh have a little bit stronger medication available and and watch the thing and it will make sense but what would be a better way of doing this is watch that take the idea go back and do it yourself then you will understand how it all worked okay so that's the total cost one and some questions like for example nico asks why didn't you create this in power query an excellent question and you can create this in power query as well the reason why i didn't want to do it specifically for this particular live stream is because that would mean we will have to introduce another piece of thing and then go through the hole which will make this like a six hour long web stream so i wanted to get as far as possible in an hour so that's fine not for power query william asks oh we created the measure on one table can i move it to another table well absolutely yes you can move it around anywhere a measure per se doesn't really have any meaning on its own or on which table it is sitting because the way we write the measures they're all fairly naked so it doesn't really matter where that measure is it will still calculate to the same value there are some probably very few exceptions but in general these kind of things you will not have any difficulty you can place them anywhere and they'll give you the same value irrespective of where you put them so so there are other more technical questions so for example roshan asks adding a column does not make the file heavy um again this is all debatable if your data is not very big in this case we have 2000 rows so there is going to be practically no difference in a modern computer between having a column here versus there but if you're dealing with millions and jillions of rows of data then you will have to think strategically every little action that you're doing am i going to burden the system or am i going to make it faster so but these decisions the early on ones when you're learning to play you just make your own rules and play but once you become confident then you start to follow more better practices so yeah you don't need to worry about this until you get too far ahead in and start working working in larger data sets and all of that hari prasad asks does the direction of the arrow in the table relationship affect what happens this is a little more technical than what it looks on the screen but what it really means is here the arrow is pointing towards the sales table so this is called filter direction so that means if i filter a thing in the people table so in this case the example is a team we filtered a team then the sales table gets filtered as well so i filter a team my sales table gets filtered because the arrow is pointing this way you can change this arrow direction so that means if you filter something here it will kind of go back there normally within the star schema you don't want your dimensions to be filtered you want your dimensions to filter your fact table so your dimension tables that is all these outer tables they need to control the inner concept and that's quite logical and fairly intuitive so we don't have to think about it but there may be special cases where you need to change that jonathan asks can i purchase an awesome chocolates t-shirt [Laughter] oh well uh probably not i'm not even gonna go and launch any much that's not in my root map but you're more than welcome to figure out a way to make them all right so we created total amount total cost now that we know how much money is coming in how much money is going out uh we can calculate the profit which is amount minus cost so this is an easy measure to make new measure total profit is equal to total amount minus total cost because measures are technically although they are in a table they are not really in a table they can be calculated anywhere this is the reason why when you refer to a measure in other measures you just write them you don't have to specify table name measure name you just write the measures also it's considered the best practice because this way when you look at a complex tax formula you would know what is a table value and what is a measure value straight away by looking at that kind of a syntax but whether you write with table name without table name it will still work so good idea is to not use table names for measures just refer to them as they are so total amount minus total cost enter okay let's set some formatting as well for the profit i'm just gonna click on the currency format and set it to zero decimals we'll do the same for total amount currency zero decimals cost currency zero decimals so far whatever we created everything is in the mind of power bi we have we haven't really seen anything on the screen so i'm just going to make a table visual which is kind of like you can think of this as a pivot table and i'm just going to see it by category so we'll add category to the table and then we will add total amount total cost total profit so we can see those numbers there i'll just quickly make this nice and big so we can see that total amount is 3 million 3.9 million for bars 1.5 is cost 2.3 is my profit overall 4.2 million dollars profit like this all good now let's go and uh calculate profit percentage as well we will take a pause at profit percentage and we will start constructing our dashboard and then we'll come back and add those other two measures later so profit percentage is equal to total profit divided by total amount you can directly divide but you normally want to use a divided function this way if there is a divide by zero situation going on anywhere it will print a blank message so numerator is my total profit and denominator is my total amount and then we will set this as a percentage value by clicking in the measure tools percentage with one decimal and let's add this one to my table as well so you can see that on total we make 52 percent profit but at an individual product category bars is our highest profit percentage with 60 okay so we will now add a a fictional profit indicator so anytime a profit margin is more than 50 we consider that as good so we will say thumbs up for that otherwise thumbs down so we can add such arbitrary things with the new measure as well so we'll say profit target met as a measure is equal to if profit percentage greater than 0.5 50 is 0.5 then we want to have thumbs up message printed so we'll open the double quotes and then using the emoji keypad you can use the emoji keypad within windows by holding the windows button and then pressing the dot key windows in dot that opens the emoji keypad from here you can just search for thumbs up and then same with thumbs down and then close that so you there are two key ideas here number one you can use if formula to test conditions on measures and then return different values number two you can also use emoji emoji works great if you don't overuse it within report situations because they're quite visual and obvious instead of spelling out a big thing you can just put thumbs up thumbs down and that makes sense and i can kind of attach that to my table to see how individual categories are doing and what's happening at an overall level all right let me see what's happening in the chat sessions um tkb sends in a super sticker as well thank you tkb [Music] um vaishnavi says i'm unable to create a relationship between sales and locations the okay option seems to be grayed out how do i fix this [Music] so what i did is i used geography and dragged and dropped it on geography there that's how i created uh not sure how you are making probably you're using like a new relationship option somewhere try that yeah if you go here you can select that and then match neeraj says a bit fast to follow along sorry about that uh it's not intentional we are um yeah we will we'll go along as it as it needs to be but you know i'll try to repeat certain points feel free to ask questions if you have a difficulty following the key thing to keep in mind is you don't want to exactly replicate everything that i'm doing instead you want to take some key ideas see how that will fit into your world because you're not looking at awesome chocolates company for your work you're looking at your own data sets anyway so you want to have that lens and think about like oh this is good how do i use it there so seetharam asks does the table have a primary key concept well technically yes the primary key is like the unique identifier in tables and in this case all the dimension tables need to have one column which is unique based on which you are linking up into the fact table in in our case we kind of made it simple by not having an id or anything we just used the names but with your data you would usually have like a product id a sales person id and things like that and you would link up on those basis so hitesh is asking star stima and yeah i think it's not a question ehab uh sends in a sticker as well thank you ehab is there any option so jayat kumar asks how do i handle divide by zero error this is where the divide function comes in divide function would not give divided by zero error it will always print a blank or alternate result if you provide one when you use that and sakurai asks is there an option to add customized visuals if condition instead of emojis you can do it gets a bit more technical you may have to think about it within the framework of what power bi allows so again in at the initial stages of power bi we'll ask a lot of questions like these but as you learn more you will realize that it is a special tool that has certain capabilities and it doesn't really match with anything else that you do in excel or tableau or other tools so you'll have to learn and approach power bi with a fresh open mind and see the possibilities and then work within those limitations yeah again possible but you will have to think about it steve gives in a sticker as well thank you steve um cheryl says are we able to join tables if there is no unique primary key in the tables um yes and no within the table relationship setup within power power bi the model view you are encouraged to use one-to-many relationship which means one table has a unique value and then other tables has many values so that's the setup but if you have got data where there is no matching thing kind of like a many-to-many thing going on then a good idea would be to bring them to one to many by introducing a third table in the middle or something else through power query and then bring that over here alternatively you may have to use power query to do such manipulations because then you can also do such relationships again it will get very technical and we are talking in abstract terms here unless we have some real data it's very hard to explain that so i encourage you to keep that question in the mind go back watch the rest of the video then i have got a resource on power query so check that out and between this and that you will probably be able to fill in the blanks okay we will make a little bit of move and then i'll come back to your questions feel free to keep them going on and um one more question key mask how do i bring up the emoji um well uh just open the double quotes and then press windows key and then dot key so windows and dot together that will open up the emoji keypad if you are using a computer that doesn't have windows key or probably you're not able to figure that out you can always go to internet search for a website like emojipedia which is basically a list of all the emojis from there copy the emoji that you want control c come back to power bi paste it ctrl v that's how i used to do it before this particular thing was added to windows so we got the profit target met as i said we will stop here we will actually construct the dashboard we'll come back and add those other two things later on so i'm just gonna um delete that table and then so to delete a visual just select and then hit the delete thing and it'll be gone and from here what we will do is uh we will start building our our report so we'll start off by adding the numbers as boxes here so to show an individual number you can use the card visual the card visual will be perfect to show single numbers so we'll select the card move it around uh size it appropriately and within this card i want to show my total amount so we'll put that now notice that on this report there is nothing so 8 million here refers to our entire revenue total revenue for for all the data in the data set so eight million dollars we can go and add the rest but i'm just going to apply some formatting here first that way when we we don't have to do the same formatting again and again we'll just do it once and done so we'll select this visual go to the format thing here and then we will add some background color background is already on so i'm just going to pick a dull color like that so that that will look like this the next thing that we will do is we will make the data label maybe 36 points it doesn't have to be so big but oh sorry not uh yeah and then change the font to one of the bold ones so that the number is actually stronger and if you want you can also change the color i think i changed the color to blue color in my final thing and then category label uh this is fine we'll just leave that there so once you are happy with this visual you can then ctrl c ctrl v copy and as you move them along power bi shows the guidelines for you to properly align um we only have four values there so for now we'll we'll do four pastes and then later on if needed we'll add more so this one i'm just gonna replace this with total cost a card can only contain one value so when you place drag and put another thing it'll kind of go away the original one this one is my total profit and this is my profit percentage so all the four measures are shown here at this point if you think uh uh we need a bit more detail four million four million doesn't look alright you can go in and and then where it says decimal points you can add a decimal point to this particular card and that will add a little more precision to the numbers depending on how how your data is you can also decide whether you want to see it in millions or thousands or billions or actual value whatever you want you can set that up from there okay so this is done um and then let's go and add a a trend graph that shows how this money came in on each of the five months in the year so to do that we'll use either an area or a line chart i think i went with the li area chart in my original dashboard so we'll use that so we'll add a area chart bring in the date onto access so for each date how much money came in and then total amount into my values when you add any date value power bi will introduce these additional pitch fork buttons on the top so these are called hierarchy buttons and what a hierarchy button does is because a date kind of goes into a tree structure like you have year within here you have quarters within quarter you have months and then days so that that hierarchy is put and anytime you put a field that has these hierarchy it will automatically put that at the highest level so in this case it kind of took it all the way up to my date year level so it's showing me 2021 your total amount is eight million dollars let's see it by the month so we'll kind of drill down one level and then one more level so this is the monthly breakdown of the amount the access is actually from one 1.8 to 1.3 that's why it looks like in may our sales plummeted but they didn't really plummet they went from 1.8 to 1.3 and that's uh that's that so once this is done you can kind of size this and as you size certain things on the graph will disappear if you make it too small nothing will appear so [Music] that's that so we'll make it uh probably something like that let's just tweak it a little bit i'll turn off the title i will for now we will leave the rest as it is and then we'll put it like this so this is my amount trend and then i can control c ctrl v this move it like this to see similar graphs for each of my four metrics now this is basically manual process wherein you copy paste and then you select this drop the total amount at total cost uh drop the total amount and add total profit and finally take away total amount and then add profit percentage so you can see that even though amounts are going down simultaneously we also have costs going up which is why our overall profit is kind of going down and then profit percentage also took a hit so it tells the story in a more richer manner rather than simple numbers there again uh you can tweak all the formatting pick a different chart whatever you fancy you can do traditionally in a dashboard you you want to have like a big headline numbers and then support them with some sort of trends or percentage changes or something else so that it gives you an indication of how good that number is or how bad that number is you don't want to just leave the people with eight million dollars as big number you want to explain that that it's 8 million but it's not all good news because things have been kind of slowly going down since the start of the year so then this is done we can then go and look at uh how each of our people have done i think in the actual dashboard we had a sales by country kind of thing we will we will do that later for now let's go and make a table because this will be a little more exciting thing to do first so we want to see how each of our employees are doing how much money they are bringing what is their profit uh percentage and whether their thumbs up or thumbs down in terms of profit targets so we'll add our salesperson this uh these are the names and then we will add we want to see their picture as well so we'll add their picture now when you add the picture it will show up as a url but these are the real faces right we put that in our internet server so that we can show that in this case they are on my website but you get the point so all you have to do is go to the data view pick on people select the picture column and then every column within power bi power bi treats that as like the default setup but certain columns you can tell power bi hey this is actually a url treat that as a url this is an image treat it as an image so select the picture column from the column tools data category here we will tag this as image url this is one of the options so when you do that power query power bi now things every time i'm displaying this column on the screen i will instead of showing the actual value of the column i'll go and fetch the image and show that in the screen so this will now show the images of our people in the report for the basic setup the way i have shown the images need to be on a publicly available website that is accessible from your computer so it can be like a public website it can be a local intranet website as long as from your computer you can go there it will show up here there is more complicated ways of doing this to link up to your outlook profile or windows profile and fetch that and you'll learn that as you make progress there so for now we got name and the picture this becomes a little more visual and let's add other bits there we will add total profit and then profit per percentage profit target thing so we can see by individual what is happening let's adjust some of the formatting of this table will turn off the total row so we can go here into the formatting thing from there total off we don't need grand totals because that's kind of printed here anyway we can go to the grid and from here you can decide how big this image can be by default it will be 75 i don't know what 75 means but it is that big so if i want to even bigger image i can make it like that if i want something small and tiny just to provide context we can bring it down because this way the important ones which are numbers will get highlighted so we have total profit profit percentage and whether the target is met or not you can again adjust some of these text sizes and things like that to make things pop a bit and even add coloring on headers and borders and all sorts of things that you can do on a table but for now that is good enough for us it gives you a fairly good indication of what's happening within the team i'm just going to adjust some of these columns so that everything fits into the space that we have there and next up we want to see how much profit these people are bringing and who is our most profitable person so you can apply a sort order and that will give you in the profit order so i can see for example cane is our most profitable sales person he also has 61 percent profit so thumbs up but further down marnie and bar and carlin they have lower profit amounts and also not meeting the 50 profit target that we have set for ourselves this is everybody but now we will go in and add the team layer so i can pick a team and then just see for that team how these numbers look like you know how to do this add a slicer and put team into that and then i'm just going to move this place it on the top so that i got my team there and i can pick one of the teams to see just what's happening within the team so delhi steam this is how they are doing that's their total amount across the company because it's all interactive like you can see that when i place my mouse it's kind of highlighting i can click an individual and then i can see their trends and everything as well so that's the interactive bit of power ba so this is how that table can be created um [Music] all right let me just quickly uh check in on the chat window to see what is happening um we have some royalty here as well excel on fire some of you might know oz du soleil uh who who is a very good friend who runs a popular channel called excellent fire is here as well hey oz how are you becky asks can we select more than one item and move them i suppose you are referring to the visuals yes you can you can hold down the control key and then select the items so that's how you select and then you can move them but there is more to it you can also if you want to treat all of these as like one big object because then every time you move you want to move all of them together you can um you can kind of click and drag to select them first once they are selected you can right click and then group as one object so they are still individual charts but now there is one group so this will give you a bit more visual freedom to move them around in one go and and place them and and work on them so that's uh that there is actually another sneaky trick so let's say you're not happy with this blue color anymore you can select all of them instead of individually changing the blue color select all and then go to format from here data label i can change the color to this bright red color it will apply for all of the visuals when you do this it kind of shows the lowest common denominator so if you select different visuals and they don't all have common properties whatever is common only that can be adjusted so i find this particular trick of combining them to format is a lot better than individually formatting there is also another thing let's say you now decide to add one more card that shows [Music] let's just pick customers so it shows the total customers i want this card to look like that card as well you can select this card click on the format painter and then click on that it will apply the same formatting rules it won't match the size but it will apply the formatting rule so i can then resize and move them around as well so that's some of those things let's see faisal asks thanks shandu question why do some time when you export a table it gives a summary view and sometimes detail view not sure exactly what that is where are you exporting wood are you able to clarify that maybe post another comment why so i'll watch for that hanik asks can you copy paste visuals to word or something i've never tried that but i'm going to copy this ctrl c open my powerpoint and then see what happens i can't even paste it here so yeah probably not but there are other ways to share like i think you can save it as a pdf or something uh or maybe take a screenshot or maybe there is something else that i don't know we know that ask can we conditional format emojis of course yes that's actually one of the new next things that i was going to do anyway we're not going to conditional format the emojis because it's going to look silly to have uh i mean the actual color of the thumb itself is is determined by windows so we can't change that but we can add a background color there instead we will add conditional formatting to the total profit row so it shows a bar how depending on how big your profit is it will have a bigger bar there so to add conditional formatting select the visual on which you want to add it works beautifully with tables and matrix type of visuals where in a table is like this a matrix would be more like a traditional pivot table with row and columns so select the table pick the column that you want to add it so in this case total profit click on it and then there is a conditional formatting there you can add any type of conditional formatting so i'll pick the data bar and and then we'll just leave the default setup and click ok and it'll add the data bar like this which will show a bar chart right inside to indicate so this is how even though tables tend to look a little boring you can make them exciting by adding pictures adding data bars and all of that and everything will look a little exciting there so that's that and everything is interactive needless to say if i pick a different team i'll get their numbers their bars will come up and all of that uh steve asks could you have copy total amount and the graph and then paste it and then change it unfortunately not because there are two different visuals even when you group when you change the thing you will have to still change it in two places it is not that smart probably it will get there very soon because one of the key things to remember about power bi is every month microsoft releases a new version well they don't really always do but most of the time every month there is a newer version of power bi so they put in new features every time they watch a lot for a lot of feedback so if you are regularly using power bi i encourage you to share the feedback with microsoft by i think there is a in the help ribbon submit an idea button is there so you can do something like this and they watch for that and they will go and add those things uh venkat asks will the pictures appear in matrix visual as well to be honest i never really tried that i'm going to try this now so we'll add a picture oh yeah they do so you can do that you can add for example region to the column and then profit percentage so you can see individual profit percentages by geographical regions is a matrix view of 60 percent in americas and 33 yeah so they do all right uh i am just a little lost because there was a follow-up comment but i'm trying to see where that original one was but maybe i'm lost now so hanniki sends in a super sticker as well thank you anike for that and let's uh go and add another table here that way at least the dashboard kind of looks complete and then i'll go back to my original one and then i'll connect the missing dots there so here we have uh this thing now when you have a table like this if i want to make it narrow because i want to use this space for something i suddenly lose my column heading as well so here is a sneaky little trick that you can use to make things tighter in your report one is you can resize the columns you can just place the cursor in the middle and drag them just like a regular table but if you say profit target met you know that's the measure name that we use to convey the idea but it doesn't really have to be that showing on the screen so you can go to the visual fields bucket this is what the fields thing is referred to as fields bucket and from here double click on the profit target mat and type whatever you want so i'm just going to put question mark there and that's that's what we the column heading here so it's not going to rename the measure the measure still stays as profit target mat question mark but when it is displayed in this particular visual it will be shown as question mark on the screen which we which means i'll have uh space to move around and and adjust and play things as well okay so this is uh those three things are there now let's go and add another table that shows uh by product what is happening because we're talking about people we're talking about overall metrics let's talk about products as well so we'll add another table and i'm going to bring in my product name here these are all our products and then for these products we will see what is the total revenue we can add more fields but there is almost no [Music] value so we'll just add the emoji thing here as well so that you can see which products are thumbs up which ones are thumbs down and how much is the amount and let's try on the amount column a background color based data conditional formatting so we can go select the table total amount down arrow conditional formatting background color and this will give you a color scale option let's just select maybe this color thing and that will give you kind of like higher numbers will have darker color and lower numbers will have that will turn off the totals as well and adjust some of these formattings and everything and you will have your nice little sales report ready for presentation now let's go and make it a little more presentable it's not perfect because it doesn't really look exactly like how i promised it would this is how it would it is supposed to look like but a lot of what you see here is exactly the same thing it's just that we have added a little bit more formatting one or two more measures etc that's pretty much what it is so i'm going to instead of doing spending the time like 30 minutes formatting this we will look at learning more about the software formatting is easy once you know where everything is you can spend time formatting uh until christmas to do these things so we will insert a title on the top which is very critical for having any reports so we will add a text box this is where you can bring in ad hoc things like text boxes images shapes to type what you want so we will say sales dashboard i'll just call this as year to date sales dashboard and then resize this move it to the top and place it like that select it i'm just gonna make it bold 32 points and and then from here in when you have a text field there's nothing else to attach here so it will simply show the formatting options on the right and from here we can add a background color as well i'm just going to match it with our blue color theme and that comes up there now as i put the header i realize oh it's actually kind of blocking my team selection slicer so how do i send this to back so we select that and then we go to the formatter button from here i can just send things to back so that that goes back and now when you click away you will have your team selection thing available to you when you click on it because you're editing it will pop into the front but it is in the background uh available on a visual layer so this is how you can kind of lay things one on top of another to create a different picture than how it looks how do we kind of set a box container behind these well same technique you will insert a shape let's insert a rectangle shape let's place this there and kind of like that and this box we are just going to fill it with a dull color like that you can even add a shadow effect this is what i did i added a shadow effect and then select this go and send it to back so that it's it's behind these cards and this is how it looks you need to tweak some of the sizes and things like that but that's pretty much how i would have done it for the for my actual dashboard that i showed you the problem with having boxes like this is whether you realize or not everything within power bi all these table visuals and all they also have a white color background added to them so this is why even when i put this looks white so to kind of make this transparent you need to select this go here and from here background disable the background on the table so the table itself per se doesn't have white background it's just the box in the background that that kind of now shows through so that is a little bit about the process now we'll go here and then i can kind of deconstruct what is happening here and how this is different from that everything else is same you can see that you know we kind of put this i've added some headings on the visuals so that they become more presentable and i put some additional labels on the first graph and then disable the labels on the others i have colored them blue for income red for cost and green for profit so that it it is easy for you to spot them wherever they're presented in the dashboard and kind of synchronize the colors again takes a few more clicks but that's pretty much what uh it is and these three other supporting measures i have kind of grayed them out so that they're not really taking up too much space and same by sales by country same visual uh that and some of these like dollar per customer you can calculate them with additional tax measure uh check check the file and then you will figure out that logic so this is how the actual dashboard is constructed now there might be some questions so i'll look at your questions first and then i'll come back and talk a little bit more let's start with this one cherry asks can i can i date range be specified absolutely yes so we'll see that in in the construction file here so we can add a slicer because you want to select a date range so we'll add a slicer and then put the date into the slicer when you put a date into the slicer it turns this into a slider wherein i can specify the dates that i want so i don't want to look at all the way from jan we know that at the start of new year people buy lots of chocolates for gifting or whatever so we don't want to consider that holiday season so we can bring this down to start of web and then that will impact all the visuals immediately and i'm only looking at web sales you can drag this you can select here and from the calendar pop-up you can pick the dates that you want and that will give you that you can adjust it from both ends you can even change the way this slider itself is displayed by clicking on that little arrow key and instead of between i want to look at either before or after or even a relative date which is very useful uh let's say you just want to look at the latest 30 days every time so you say last if i put 30 it's not going to work because we are on 26th of june and the data only goes up to 31st march so i'm going to say last 60 days 60 and then days so we are only looking at last 60 days but this is a relative so if you open tomorrow it will pick a different range and it kind of always moves so it's perfect for building a thing that you don't have to touch again and it is always showing you a moving window of what's happening and you can get even more creative with these things so that's uh that sunni taro asked can we zoom in and out of dashboard yes you can if you want to for example look closely at this particular thing click on the point your mouse on the table there is a pop out focus mode and if you focus you kind of zoomed in now you're looking at this graph alone and you can go back to the report which will take you back so it will add another layer in the front and when you are building you don't think about this but when somebody is reading this on the browser like i'm reading this on my browser now you can see that this is chrome i can kind of go into focus mode on that and i'm looking at huh what's going on with australia why are they eating less chocolate and then go back there uh dinesh asks how do we do weighted average calculations you can do everything with power b power pivot uh it will be very complicated to explain some of those things right here but it is possible you'll just have to set up your model and then figure out the logic using either the dax or uh follow some of the resources that i'm going to mention shortly that will give you more insight into working on those kind of things michael says will this video upload or not uh it's kind of like a tricky thing as you can see when i'm explaining there is a lot of uh like this will only make sense for people who are watching all the 1 hour 42 hours video so and i i'm going to leave that on i'm not going to make it public on my channel but it will be there so if you come back to the same page again tomorrow or one week down the line it will be there but if i especially for certain some of these ideas because if i feel like okay this is very good i am enjoying this i might actually take it down and make it a mini series of two or three videos that will explain the process but in a more concise and edited manner so that people don't have to sit through two hours to understand how things are happening hope that makes sense but yes it is going to be there on youtube [Music] so roshan says can we send this by email to a colleague and still be interactive yes you can i think that kind of really segues us into the next part which is what happens when you finish building this right you want to make something and then how do you share it so there are a couple of ways to do this number one if you logged into power bi you can see that your username pops in the top chandu.org for me but whatever your username is you can click on publish button you will first have to save this so i'll save this on my computer so sales tb demo and then i can now click on the publish button at this point if you logged in it will it will open up your online workspace and then say where you want i have only one workspace so it says my workspace and i can select that and and then it will publish this to my online workspace from there i can share that with my colleagues or clients and ask them to watch it so here now it is there i can open this in browser this will only work by default if you logged in so even here i am logged in as chandu so you can see that it's kind of going into my profile and showing the dashboard it has all my pages available so i can go to any individual page and everything is still interactive i can select somebody to see what's happening i can select product to see what's going on with that product and all of that beautiful but if i want to share it with general public then you need to get into more specifics of the power bi licensing and how you can share who gets to see it who gets to see what and all of that and it gets a little more technical but all of that is possible so sunit asks how much canvas area we have and how do i increase it so the default canvas area is it's not really a specific size in pixels or anything uh it is basically 16x9 so widescreen canvas that's what you have and everything is zoomed in but if you have a big screen everything goes big if you have a small screen kind of shrinks down so that's how the canvas works but if you want to format it for a specific page style like an a4 page or four by three resolution or something you can click on the white space in the canvas and then go into format settings and from here you will see page size page information so i'll go to page size 16 by nine is my aspect ratio i can change it to four by three letter tool tip or i can put custom and then i can give my own dimensions to canvas and that will give you a different layout for you to put things in philip says those headshots are in play date for different people as some of you might know i run an online power bi class called power bi play date where i teach people how to use power bi uh in a like a self-paced class and i use these images for a different data set as well because these are open source images so i got same uh 100 images that i kind of reuse in different situations vikrant says is there any way to stop table from auto adjusting not really sure what you mean by auto adjusting it if you once you fix the layout you kind of adjust the columns that's what it is so if you place your cursor there and drag and put that that is how wide that column is going to be irrespective of what happens elsewhere so if i pick a different team even if their names are long it will kind of obey that i think so there is still some questions on how to publish when you publish to your workspace it needs to have both data and and the visual output go but when you share your audience are not going to look at the data they're only going to look at the outputs so the link that i put in my video description is a share link so that means when you go there you have no way of going into the data you can only see the report that i presented to you and that's pretty much it you can't really go back and check my data and everything because i'm not provided access to you for that whereas when you work in a team chances are you create a report and you collaborate with few others and or you you want your teammates to be able to edit things as well change things or maybe modify the data or calculate new measures there so then you will have the workspace where everybody has access to it and they all can go in and see and work on that and make changes or make new reports from your data um in gajandra ask can we ask to access restrict access to a particular user yes you can so for example this report if i go and [Music] share i can't really share in my free account i need to have a power bi pro account to be able to share but in organizations you will usually have a power bi pro because your entire organization or teams will have added you to power bi licenses so when you have that from there you are sharing uh to a specific people so you will say uh these email addresses or people in this access group or this user group are the only ones who can read this report and that'll that is how it works so bradley says the sort is not maintained maintained in the people table it could be because we sorted on picture or something like that so for example if i sort it in the sales person alphabetical order when i switch it will still obey same with profit if i sort it on profit if i switch it will always be sorted on profit victor sensing a sticker as well thank you so much victor for for your love and appreciation um sam rs would you be conducting any workshop on shiny r if not mistaken you had shared a blog on our tools sometime earlier uh thank you so much sam it was it's been a while i haven't done any r programming in almost one year now um yeah i'm not super confident with r i i like to kind of dabble and make things for fun but i think running a session like this means i would need to be more confident and comfortable with the technology and uh yeah probably not anytime soon but you never know it might happen down the line because i might get more creative with that and pick up the technology later all right paul asks can i update the data to tables uh i suppose you mean pushing data into the database if so you can do that by adding uh power automate uh powerapps which is like an additional layer of interactivity added to power bi so through that people can do things or change things in the back end and all of that again it would be too technical for me to demonstrate right now but that is possible but if your question is more on the lines of what happens when the june data comes in well it's very easy i do have june data so i'll open that here is my data additional data for the month of june so i'm just going to ctrl a ctrl c this close this go to my excel file that has the data go all the way to bottom now this is pretty much how we can imagine this works in real life right so we paste the data you could think of this as your database connection to a sql server database things change in the backend so we our excel file changed we'll save this by the way i provided extra data as well in the video description so if you want to test this bit you can go and do so now i come here so my excel file changed how do i tell power bi that go and refresh simple just hit the refresh button here and it will go run the same query again get the data update the numbers uh and and now everything is changed we haven't really made a note of what was the numbers before so uh i'm just gonna clear everything if you remember we had like eight million dollars revenue before but now it's 9.4 so that's uh yeah it kind of changed all right there's more questions there how can we add a comment note to a specific report you can add normally the way it works is time for me to bring back my notepad to introduce few more ideas into your mind so i'll go to full visual mode so the way power bi works is in in in software like excel so we'll imagine the top bit is excel in excel you create stuff in excel file so this is excel file and normally in the data analysis space there's two kinds of people like there's creators there's viewers so you could think of yourself as the creator you are the one who is making the data analysis but the viewer is not you it's usually your manager she's the one who is reading it so this is how that within the excel world creator makes stuff in excel viewer also reads stuff in excel so they both have access to the full environment of excel file so you make a pivot table your manager is reading she can go and drop something or add a slicer if she wants assuming you've not added protection and stuff like that so that's the general dynamic in excel now let's flip this up and let's try power bi within power bi creators normally use the power bi desktop software the one that we have been using so far so you use power bi desktop as a creator so creator power bi desktop but the viewer they are not really opening stuff in power bi desktop once you create you publish you share so the viewer is really reading stuff in the browser or the app and this is how the uh thing is so for the viewers viewers are the ones you usually want to make notes or add comments on things right you're not as a creator you're not really you you just create stuff and then viewer is the one that might find oh if i click here and click there i might find i'm finding something useful so for viewers in the power bi uh power bi.com workspaces and and published dashboards they can add comments they can add notes they can save views and all of that so that's how you can do it but if you as a creator you also find something interesting that you want to highlight you can put that as a text box you can just put a text box and then type whatever you want so for example i can say cane is not doing well and then just add that as a layer on my output and then that can be there but i don't really find this that useful mainly because this is a static text whereas everything else is interactive so i can click on that i can touch and i can change but this is not going to change so this is where it is kind of static they have come up with a new feature called smart narratives wherein power bi will generate kind of artificial intelligence sentences that describe what is going on in the screen and provide more narrative as well feel free to give that a try if you want to build more interactive storytelling concept all right let's see what else is happening area sends a super sticker as well thank you array uh subpt says is the data still dynamic from the source if an update was done um [Music] not sure what you mean but it everything is dynamic like you just have to hit refresh to do it and once you refresh it will come through and then everything still behaves interactively so hardik asks how to compare data from two versions of the same table created at different times you can use either power query to do such comparison and then bring the differences or whatever or you can do more advanced analysis within power bi for that amit asks can we upload pictures directly from my laptop into power bi instead of creating a photo url there is another option so if you don't want to link pictures to individual people or anything you just want to show a corporate logo or something you can go to insert and then from here you can insert an image you can put any image there for example if you have your awesome chocolates logo you can add that i do have one though but i don't have that in this folder but you can put that there but if you want this kind of behavior wherein the image is really attached to an individual item in the data then it needs to come through as part of the data and the default thing is as a picture column but as i said this is not the only way there is more ways to do it so you will have to find some other techniques [Music] using so a common way people do this is they link your profile picture in the windows directory active directory and then use that when they show information about people another common technique is they might have like a catalog of all the product part images or floor plans and things like that and you can bring them over as well there are other formats that you need to use because those images are not technically images they're stored in the database as blob and you'll have to bring that and render that here uh jill pablo sends a sticker as well thank you so much for your support continued support really i really i remember reading this name few other times in my other live streams thank you so much and excel says i need to leave thanks so much for extremely helpful you're welcome can i share this dashboard with any url with you viewers you can so you just have to find your way around that i'll have to so because i am using a free version of power bi online there are limited support for that but with paid version you have more freedom it doesn't cost that much i think it's like eight dollars or nine dollars per month so for an organization it makes tremendous sense to have that but if you go to file embed report public to the web this is where you will you will be able to share it to the wider world you don't want to share anything personal or your organizational data with wider world this is all made up and randomly made up data so i don't mind sharing this but you want to be careful when you do this okay gender says super special thank you thanks a lot chandu guru thank you so much kadender alyami says how do you visualize comparisons between budget actual and prior year you can do that there are multiple ways to do this for example there is a a kpi visual if you use this it kind of gives you built-in comparison between target and actual which is like budget and actual but you can also use line graphs and other things you just need to have the data in the data model for both budget and actual then only you can compare you can think of our up thumbs up and thumbs down as as a way of doing this right that's pretty much what it is we are comparing uh where the profit has met 50 so instead of hardcoding it 0.5 you could have another measure that tells you what the target is and then compare with that all right i do have few more things to share with you before we start wrapping up the things so i'll go back to my slides for a quick session uh here and [Music] so you might be actually having more questions than answers at this point like you might be thinking oh this is all good but i have i now know a little bit about what power bi can do but i have more questions so how do you learn here are my favorite power bi resources feel free to take them and then use them for your thing there are a couple of very good youtube channels my favorite ones are guy in a cube and karbal and then microsoft pa power bi official channel these three are the places where you can find good high quality information on power bi of course you also can find some things on my channel but these people they all talk about power bi much more than i do and the websites microsoft power bi official blog this is because every month they release a new update with certain features added certain features enhanced and you want to just have a read to make sure that you are using the better and newer ways of doing things to learn more about power p power pivot which is the dax language we are using to build calculations uh use accelerator bi which is matt ellington's website and obviously search on youtube and google to learn more i'll just quickly uh switch back to my browser and open up a few more resources now these are not the only places i also have some stuff on power bi so for example here is my detailed power query video this is something that i did last year i put a link to this in the video description please check this if you want to learn how to work in the data layer of the things like what is power query how do i use it uh it's a very in-depth video it's not a live stream video so it's like a continuous flow for an hour and 20 minutes so you could think of that as live stream but without any other interruptions any other questions no disruptions of chat or whatever just watch the whole thing download the files practice and you will be very good within power query the second resource is my page on introduction to power bi i took quite a bit of time preparing this it's a very very big web page that walks through the entire journey of power bi helps you create a a simple hr dashboard with sample data it gives you more information on how to use various visuals in power bi uh even a link to my youtube video that i did a while back uh and more on technical terms and links to resources of the things that i previously mentioned along with some book recommendations and other things the last but not least i also run an online class on power bi play date it is in a waiting list mode currently but you're more than welcome to go through this page and add yourself to the waiting list and i will probably reopen that sometime later this year so that is how you can learn power bi if you if you want to get good with that so i'll switch back here to see some questions and give a shout out to people who uh send in some stickers as well javi's uh census ticker thank you so much james and uh ravi asks is that you in picture i suppose you're talking about the thing here well that was me uh i'm not wearing the same shirt but that's a different shirt same room this is last year when i decided to grow my beard and just go full wild during the lockdown times sabine says thank you from netherlands thank you so much sabine anthony says avi singh has a youtube channel yes of course avi singh has a very good channel as well uh l h says great information thank you so much lh uh let's see this question can you recommend some youtube channel where i can get sql tutorials also as interactive as yours that's a very very good question unfortunately i am not familiar with any sql channels this is not to say there is no channels it is just that i learned sql all the way back in 96 97 when i was first learning computers sql is one of the very first things that you learn anyway and then i kind of became a sql pro when i started working as a software engineer in 2003 so it's been a while since i learned sql and once you learn sql you kind of know sql so there's no need for me to go back and learn a sql uh so unfortunately i don't have like anything but i do have a couple of links that i referenced earlier on in one of my other videos so i believe khan academy has some good videos on sql so probably start there or w3schools i'm just scanning through your questions uh sunit says thanks a lot for this awesome session and answering my questions thank you so much sunit i hope everything went well and ashish says would you make videos on tableau probably not anytime soon but i might actually make some videos on tableau or at least do a comparison between tableau and power bi uh this is one of the recurring questions that i get every time i talk about power bi like how does it compare with tableau how does it compare with sas or click or whatever else and i mean i'm kind of biased towards power bi mainly because i have been using microsoft technologies for such a long time but i find that tableau offers an equally strong and powerful product uh there is almost no value in uh kind of having an allegiance towards one technology platform over another if i am a business analyst or a data analyst and i'm looking to learn these tools i would go and learn both tableau and power bi uh in parallel or at least i will know what is what in each of them and then i'll go in deeper into one of them depending on what is happening in my life at that time so yeah pick both and then do tableau also has a free desktop product that you can download and use it to analyze data they have a very very good online community probably more deeper and stronger technical resources for helping and all of that but at least from my interactions and my experiences what i find is tableau is slightly weaker on the data side of things like preparation and calculation side but it is very very good for outputs that might have changed in a while now so yeah okay let's see what else is happening brent says another great session thank you chandu thank you so much brian thank you i'm glad you enjoyed it and uh we've now gone past two hours so as you could kind of guess my throat is a little bit itchy but i'll switch to my that one instead of the beard one says i missed your tutorial from the beginning do you have a recording uh what you could do is you can kind of like the video or bookmark the youtube page and then refresh it after maybe 30 minutes when the stream is over then it will be available for you to watch from beginning and you can watch it again pawan kumar says goodly is a best youtube channel for dax yeah goodly is a very good channel chandip who runs it has got some pretty awesome information on dax and various things so yeah feel free to check it out uh gb bill says enjoy the weekend thanks for barcelona thank you gb thanks for your support and cheering all the way ravi ramakrishna says thank you so much for a great session i'm absolutely new to power b and today i'm going with some knowledge of power bi great session and hoping to see you in other tutorials thank you ravi thank you so much and i hope to see you again some other time so this one comes came up a couple of times in aws ecosystem which is better quick site or power bi i i'm not really sure what quick site is but i assume it is something similar to power bi i haven't really got any opinion on that so i can't really talk about it sorry any cheat codes in power bi like we have in excel power bi is a very good software there's a lot of fun little things that you could do and it is quite deep and wide so there is so many powerful things that you can do but yeah i uh i think you will build your own cheat codes as you learn and use it i would hate to for you to miss the joke that is on the white blackboard behind uh so i just put that there i was being cheeky here and i put my guy in a cell as a thing on the back because i really admire guy in a cube which is a popular youtube channel for power bi and i wanted to kind of even invite them for the live stream but i know that they would be too super busy and the timing may not even work then i thought oh well if they're guy in a cube i'm going to sell because i come from excel so i put that there nira says very nice session chanduji your channel is one of the best things that i came across on youtube doing a great job thanks thanks thanks thank you so much neeraji i really appreciate your support and love uh and and the passion and it i'm really uh thankful that i'm finding this community and i'm enjoying my journey on youtube and sharing my voice and my ideas with all of you thank you zaiba says 100k subscribers thanks congratulations uh can you make a video on mis reporting sure i will and thanks once again for you and all the hundred thousand others who subscribed to my channel and stayed with me in this wonderful journey uh and i wish you all the awesomeness thank you chandu for being so helpful though i missed the tutorial i hope to go through it later as you have just explained yes uh thank you f e garaga and uh i hope to see you in the comment section so if you're watching this in the replay feel free to post your questions or comments there as a comment i'll watch that for the next couple of days and i'll try to help you out as well now we're getting into new zealand specific questions here how is university of auckland for masters in data science i don't really know anything about their data science program but i do know that university of auckland is one of the best universities in this part of the world so yeah if you get a chance probably email the head of the department they're very responsible responsive and helpful ask your questions ask them to put you in touch with some of the previous students so that they can guide you stefan says you have a nice style of teaching chandu thank you thank you so much and kapil says thanks from rajasthan and says your videos is very learning and help to increase productivity thank you couple youth girl says i got your channel recommendation from code basis oh big thanks uh to code basis uh for for supporting and sharing my work all right uh anthony say subscribe to your channel today but your email instructor about two years ago thank you anthony uh welcome okay so just to wrap up you can download the file the file that i provided you at the start of the session is this one yeah not this is this one and it has uh the sales dashboard the completed one but the one that we created in the video is this one it's kind of looks similar but not as functional feel free to uh download the original file and after some time i will upload this file as well and link it up in the video description so probably in about two hours or three hours when i go downstairs and chill out for a bit uh so go and check that and uh and use that in case you have some questions big thanks to mohit for the super sticker as well thanks mohit and james jamie says thanks shandu for session it's been really interesting can you suggest any platform to check out in case we need any consultation while building a dashboard well uh my suggestion to you is power bi is quite playful so start by going for the lowest hanging fruits and building slow and then add complexity as you gain confidence so if you try to attack the big big report or big business intelligence thing it will you will have more frustration but if you go small then you will have successes and then you'll gain confidence so do do it like that but as you learn uh it is really important that you focus on on the on the first two layers and build a very solid foundation there so this is the diagram that i showed earlier on in the stream uh if you if you have watched from the beginning you might remember but otherwise uh the the data layer where you need to understand how power query works and build confidence there and then the powerpivot layer where you need to know how to write dax formulas how to customize them how to optimize them and all that so these two is is where the real magic happens visualization is just the pretty part that's the face of the report but once you have good things going on in the background you can do awesome things there so focus on those two learn some good solid resources that's where i linked the next videos to watch for in the video description so once you have all of that you then have you will obviously have questions even i have questions every time i use power bi so this is where searching online or watching some youtube videos or reading a book or going to a forum could be the next point of contact i think uh vivek professor vivet gupta says vivek always memorable to hear your malifious voice dealing clarity thank you so much for what you do so very well thank you professor thank you so much for your support and and and your love and appreciation and your blessings always says thank you so much for your time and energy and this spent over here thank you so much alpish uh again a big big thanks to all of you for tuning in today it was uh it's quite quite an interesting session i really enjoyed it i hope you've learned something valuable and useful in power bi journey and i hope you're feeling a little more confident to tackle the power bi beast now if you have not yet downloaded i highly recommend that you download the free power bi software uh from your microsoft app store or the power bi dot com give it a go even if your job doesn't involve using power bi immediately i think just understanding what it is what it can do for you will really open up the doors in your mind to new possibilities and you never know where that will take you anita asks hello from canada thanks for great presentations jobs ask for certification any suggestions from where one can get there are a couple of places which offer certificate we power bi courses i am quite hesitant to recommend any like a textbook style certificate courses mainly because they tend to test things that are not really practical or applicable in business situations i don't want this to be like a self-promotion thing but if you are looking for a certificate course i recommend that you consider the power bi play date which is kind of designed with the data analysts in mind i run this course and it you get a certificate as well not sure if a job advertises for power bi play date certificate needed but uh you know you could try that i think microsoft also has like a couple of certification options but as i said those exams tend to be more on the on the theoretical side rather than the practical implementation side of power bi so yeah mbk says i'm very new to powerbi where do i start uh well this video is probably the best place to start but once you finish that go and check the video description i have put some resources for you there check them out you and then i will add these additional links as well yeah i'm saying so you make the weekend good by teaching good stuff wishing you happy weekend thank you best wishes from yahya dubai thank you yeah i i really enjoy my weekend i look forward to this weekend of all the weekends in the month for two big reasons number one i get to do my live stream number two after the first weekend like the last weekend of the month is when i usually have my ice cream i only eat ice cream once a month so i look forward to that as well of course there's lots of other fun things that i do in the weekends too so thank you so much i'm gonna go down now and uh maybe drink a little bit of hot water because my throat is already itchy after talking for 2 hours 15 minutes but everything really went well thank you so much for all of you for tuning in and i just want to also once again say thanks to all the subscribers for uh helping me get to 100 000 subscriber milestone which is uh gonna put a big smile on my face throughout the weekend uh and uh yeah i'll post some celebration pictures later on in the channel thanks again bye

Channel: Chandoo

Views: 31,533

Keywords: power bi, power pivot, power bi training, power bi tutorial for beginners, power bi dashboard, power bi for beginners, power bi demo, learn power bi, microsoft power bi, microsoft power bi tutorial, Sales dashboard with power bi

Id: mXD0Peot4hY

Length: 135min 21sec (8121 seconds)

Published: Fri Jun 25 2021