Power BI Dashboard from Start to End (Part 1)| HR Dashboard | Beginner to Pro | Power BI Project

hey guys welcome back to my channel so in today's video we will see the design and development of this beautiful dashboard in power bi software so our today's topic is HR analytics dashboard this dashboard is completely Dynamic and interactive which is developed in power bi software so in our today's video we will see the complete development from scratch to this beautiful piece in power bi so here you can see I have designed several elements so first you can see at the top there is kpis here like five to six kpis are there then we have some charts like we have here the pie chart then we have the frequency chart which is converted after that into the stack bar chart then we have this matrix chart we call it has a heat map also then we have this bar chart and then we have this multiple donut chart so we will see the design of each and everything in this particular video so now going ahead you can see this dashboard is completely Dynamic so above you can see I have added one slicer or also we call it as a filter in the form of buttons so when I click on any of this particular educations you can see the dashboard is changing with respect to that when I am clicking on bachelor's degree in high schools or on the Master's Degree the values will change with respect to that filter and it will show us the that regarding values right so as well as whenever I'm clicking on any of the slides of this pie chart the values uh are interactive and they will give filter out with respect to that as well similarly for this also we are getting the same similarly when I am kicking on uh clicking on this particular bar chart we will see the values will be filtered out with respect to that here as well on technical on medical liquids so we will see the design of this complete dashboard in power bi in this video also I have made a multiple dashboards in some other tools also like another visualization tools like um W cell so the links of those particular dashboard I will mention in the description box of this video so you can go ahead and explore that as well and if you are interested to see this particular dashboard design same dashboard design if you want to uh or you are interested to design it in a tableau or in Excel and if you want me to do then you can add your views in the comments and I will definitely go ahead and make a video all right so now before wasting time we will start the design of this particular dashboard and before that guys I request you if you have not already subscribed if you are new to the Channel Please Subscribe the channel uh like the video and click on the notification Bell so you will receive the notification of this beautiful dashboards whenever I will be making in the future okay so let's start the design of this beautiful dashboard in power Pi so first I will open a new file a blank file for power bi so whenever you will be opening this file you will get this UI so before starting what we will have to do we will have to connect to our data so the data which we are using here is almost having 1500 rows are there and several columns are there so what I will do I will show you the data first so this is the file uh in which our data is there uh this data will this file I will be attaching uh in the description box the link of this file I will be attaching in the description box and you will be able to download from there for your practice purpose all right so you can see there are almost 1500 rows are there for this particular dashboard which we are going to design and there are a number of columns are there where you can see there are number of columns are there so out of this I have uh hardly use 40 to 50 percent of columns in our design and I request you to all uh like you can explore more and more in this particular data and you can design your own dashboard instead of copying mine so you can copy mine for practice workers but uh I request you to explore more and prepare a great visualization out of that and uh so that will also explore for you it will be helpful for you as well uh in terms of practice purpose all right or in hands on purpose so first thing first always remember guys uh the thumb rule in in data analytics is you should always study the data first all right so once you study the data and once you have understood the data the 60 to 70 percent of your work is already done right so so first study the data see what at what level the granularity of the data is there so how the data is going from top to bottom so how the interlinking of one column to another column is there so once you have understood that it will be very much easy for you to design or to bring up the visualization right so if you don't know the data it will be very hard for you to design anything right or to visualize anything in your any tool let it be power bear or tablet so always remember study of data is very much important then and then go for the designing so I request you all to go thoroughly to this file see what columns are there what what information we are getting from here and then start your visualization so I have already gone through the data so I will start the design so I will go in hours power bi file so as this is an Excel connector so we can click either here or we can go and get data and click on Excel workbook all right so we have our HR data and we will open it so it says that the file is already in use so if the file is open in background you will have to close it first and then you will have to connect it so I will go ahead and connect again foreign so it will take some time to load depending upon your system RAM and configuration so once it is loaded you can see uh there are different sheets available in this particular file so I will be using this sheet all right and it will give you the snapshot of this particular data here so how the data will be looking like what columns are there and when you click on load so all the data will be loaded in this particular file so it will take again some time to load the data depending upon your size of data right as well as your configuration so it will take some time few seconds to load all right so all the data has been added so now what we will do we are right now on report page I will go ahead and shift myself to Data Page all right so the data page is nothing but all the data whatever you are having will be uh shown here you can see what are what are the counts of the data that is 1471 all right so now you can see guys here the headers which are available in our Excel data was something else and here the header we are looking like something else so headers are given as column one column two column three so what power biad has done is it or some somehow power we have consider our header as well as headers as a rows right it has considered as our headers as a raw data and it has not considered it as headers right so if we are seeing here like columns column one column two column three up to what 41 columns are there so now we have to change this we cannot design our dashboard with this with this uh like data error so what we have to do we will go again back to the report page then we will click on transform data okay so whenever you will be clicking on this transform data it will take us to the power query editor all right so we are in the power query editor so it is attached directly to power bi so in powerpick we get into what we do we can write some and queries as well we can add columns with on based on some condition based on some formulas all right we can transform our data and many more things are there to explore here right so you can see our first row is actually it is considered by power bi as a raw data right but it should be what it should be considered as in headers right so whenever you are in home type there is one option available like use first row as header right so whenever I'm clicking on use First Choice editor so it is automatically taking what our first row it is considering as header and now our data is for perfectly fine to make our dashboard all right so this is the thing what we have to do first and here we can see there are applied States here right so up to here everything is okay then you can see I have promoted the headers means what what steps we are doing in whatever in power query those steps will be visible in this particular window that is applied steps whenever you have to delete you can delete it from here if you have to change you have to change it from your any formulas whatever steps you are doing everything will be listed here and you can see the what hierarchy is there of what steps we have applied in this particular data all right so this is also important thing like whenever you are working on large data and you have made so many of changes in your data so many of Transformations and you have to go back and change some of them so you will find it here all right so now next is what you will again have to go to the Home tab and here one option is that that is close and apply all right so close and apply means what whenever we are clicking on this we will go back again to our power bi and whatever changes we have applied in power query those will be applied in our our way software all right so now we will go back again in our data Tab and you can see now our headers are perfectly fine right so our power we have taken correct headers with the help of power query so now we will start our design all right so whatever page which you are seeing here I will not uh change anything if you want you can go in view page and you can change the layout of whatever the page you are seeing here all right so let me show you whenever I'm clicking here I will hide this all the tabs so whatever the dotted lines you are seeing here right so this dotted lines is called as so if the area between your dotted lines is called as canvas right so canvas is nothing but all your dashboard will be uh designed and visualized in this particular canvas only and the outside whatever space outside the dotted area so that and inside the dotted area will be considered as a background so this Total Space from end to end will be considered as a background so this is some important point to you know all right so now we will start our design and in first design we will be doing it for kpis okay so for now for kpis what I will do so we have here a number of what we can say the charts and graphs are available here so these are some common charts uh which probably I provide you with this software you can also go here and click and get more visuals which are the certified visuals of Microsoft or some another visuals which are not certified are also available for you to use all right so now what we will do first we will go on card okay I will click on card and you can see our card has been uh seen in the visualization and one tab has been activated that is build Visual and this is the what we can say uh the field tab whatever we are we will be taking this what you can say the dimensions or the measures and we will be tracking it over here so you can see whatever the uh fields in front of them wherever we are seeing the summation symbol those are considered as a major by default by power Pi in a default aggregation has been applied for it all right as well as whenever we are not seeing any symbol those are considered as a Dimensions that is uh those are a categorical field and those are treated as a dimension in power pair all right so this by default we are getting this distinguish in power web always smart enough to identify water measures and water Dimensions what right so now we will start design our first kpi that is overall employees all right so for this what we will do we will take the employee account okay I will take this field and I will put it into the fields in build visual so you can see we will be getting the employee account here so I will just reduce the size of this and I will place it over here all right so what I have to do uh first what I will do I will add a background image right now only so what we are going to do in power bi is we will be designing all the charts in this particular canvas only okay we will be not going on new page and design one uh chart here copy this and go back to our dashboard right so that that should be done in power bi uh sorry in Tableau because in Tableau what we have to do we have to design each sheet and then we have to prepare the dashboard so in Powerbag users Liberty or flexibility to design everything in one page itself all right so we will be following the same I will Design everything in this page only so what we have to do first uh whatever background which I have used here right in this particular Dash but I will first apply the background here all right so to apply the black or background you have to first click on blank space okay so blank space should be selected and then you have your option that is format or report page okay in format you have to apply the image on canvas right we have to apply the on canvas we don't want to apply it as a wallpaper so we will go on canvas and from here I will choose an image here and I have this gradient background that is gradient purple background so if you want to download this on Google you can go ahead and search the like a gradient wall background wallpaper and you can download it from Google all right I will open it but you can see it is not visible here why because the transference is 100 so whatever I will do I will make it as eight percent okay for me I've taken it as eight percent as per your choice whatever design you have to make you can make it all right so I will put it as eight percent and instead of normal feet I will do it as fit that is it will be perfectly fitted in my canvas all right so this is something important first I have already added the background net then I will start designing of this I will format this c one kpi is if one kpis designed the other KPS are very much easy we will be just copying this card and then we will Design the other key so we will go in this particular format your usual C always remember you have to first select your visual then this Tabs are activated so I will go in format your visual I don't want our the title add below or label so I will just off this okay and I will go in general I will go in title and I will on this and I will add my title as Over All for all employees great so I will just make the horizontal alignment as Center and I will change the font to okay so this is the font which I will be using then I will go in the properties and I will change the height to I will be using height as one one zero and width ads so what all right so this will be my width and height and this is my properties then I will go in effects Tab and I will change the background of this particular card so from here I will change the background to I will use more colors and here I will be using this color code for my background of this particular card so I will just copy this Ctrl C I will go in colors more colors and I will just paste it over here enter all right so can you same the same color code depending upon you or you can use some another color as well so this is my color code which I will be using and I will add the transparency as 75 all right so you can see uh I will what I will do some more uh changes here I will go ahead in uh oh General and I will go to visual and call out value what I will do I will nice oops I will change the background color to white okay and in general I will change the title as well the background color of title that is text colorful light white okay some gray light gray all right so now next thing is go to General and we will give border to this particular card so we have visual border here I will on this and I will use the same color which I have used for background and I will use the rounded Corner radius as 15. all right so you can see this our first kPa has been designed guys all right so now what we will do we will just copy this Ctrl C Ctrl V and I will skip it over here all right so what we will be doing we will be using same kpi whatever the design whatever background color everything which we have applied here we will be just copying it and placing it over here all right so that we will be not wasting our time in designing everything again and again all right so now this is our second and now we will go ahead and change uh the title for this so next is our attrition so guys attrition is nothing but uh whenever company let's say company have 1000 of employees so if uh employee of that company is leaving that company so that is nothing but attrition means there is one iteration so light he has left the company all right so we have to find whether the attrition like who how many overall out of overall companies how many of them have left the company till date all right so this is attrition okay now what we have to do we cannot use the same count of employer so we have to use the iteration here so one I will just to control shade all right so now to show here the attrition what we have to do we have to use a formula over here and we have to add a custom column so what we will do we will go ahead in Home tab and again we will go back to transform data all right so we are again in power query editor so you can see there is one column that is First Column itself we have iteration if it is yes means what that employee has left the company if it is no means is still working he is still working in that company all right as an employee so we have to find how many count of yes is there so for that what we will do we will add in column over here so we will go in add column and we will add as a conditional column here okay so there are different types of column which we can add that is custom column or conditional column right so now in this video what we have conditional column we have by default if statement so if I will name it as attrition count so this column will be used as attrition count and if the attrition it is equal to S okay if this is yes then I I want output as one right so wherever there will be yes the output will be given as 1 and whenever there is no if it is no else I will put it as zero okay and I will click on OK so you can see I have got a new column over here in our particular data set is that is attrition count by using conditional column statement all right so now we will go again to home and click on close and apply okay so whenever we will click on close and apply it will take some time and it will show that particular column in our power by data you can see it here all right so now what we will do we will change the data type of this column so it is text right now so we will change it to whole number all right yes okay so now this data type of this particular column has been changed initially it was a text so if we are taking summation of text it will not give us the correct results so we will have to convert it into whole number now we will go again back to our report and now you can see a new column has been added over here that is iteration curve right so this is nothing but attrition count so we will take this and we will put it over first we will select our visual that is and we will put it over here right so you can see it has replaced the overall employee and it we are now we are seeing the attrition count right so very much easy guys now we will see our third kpi control C control V and we will place it over here all right so now our next is what we will change the title of this in general and where we will change it as admission rate all right so now attrition rate is nothing but guys so like out of 1472 37 employees has been left the company so what is rate of iteration means at what rate the companies have uh or the employees have left that company like uh it is nothing but the sum of food like sum of attrition divided by sum of implies like 237 divided by 1470 is nothing but our attrition rate so now how to do that okay so now we will have to add a major over here right so there are two types like either we can add a calculated column or we can add a major so whenever we are working on aggregations we will have to always add an engager right so for this measure we will be using a tax query or the Dax language over here so what we will do we will right click on HR data and we will click on new measure okay so you can see uh we we have a name uh install of major I will be giving the name as attrition rate okay so this is our iteration rate and we will have to write a formula over here so I will take it as sum of attrition count okay it is nothing but the sum of attrition count will close the parenthesis divided by sum of employee count and I will close this all right so this is what sum of attrition divided by sum of employee account so I will just click on this and you can see our new column has been created here and whatever you can see a calculator symbol is there in front of this particular measure so it it indicates that this is a measure which is calculated in power bi using a text language okay so now we'll close this and now what we will do we will put it into this right so it is giving us as Point sixteen so we will have to convert this into a what we can say a percentage value so I will select this column first okay we will have to select this column I will just close this and from here you can say a general we are seeing here so we will click on percentage okay so we are getting here a percentage value and by if you want like how much decimal points you want to show you can show it from here so I will show it with two decimal points so this is our iteration rate guys we have designed our third kpi then we will go for our fourth one all right so we will Design our fourth keypad that is nothing but we will change the title of this first and for this what active employees all right so our active employees is nothing but overall employees minus the attrition means the employees which has left the company so again for this we will have to write down a measure in the form of text language so for that again we will have to right click here and I will choose a new measure here and I will name it as active employees and for this again we will do sum of employee count sum of employee count minus summer attrition right sum of addition count close this okay so you can see new column has been created new measure has been created so I will just take it and place it over here all right so this is our what active employees so the web designed our fourth and we will be designing our last kpi all right so our last kpi is nothing but what we will have to take the average age so out of all the employees which which are at different age so what is the average age employees which are working in our company so I will change this General title and I will change here as average age all right so for this what we will be doing is we already have age column here okay I will just take it and put it over here so we already have this so but you can see we are getting some more here right but we want average of this so what we can do in fields we have a small drop down over here and instead of sum I will click on average right so it is giving me a fraction value so I want to change it to a whole number so what I will do I will go in this particular format visual in call out values okay and I have a decimal point here at 0 I will put it as 0 right so I will be getting as a perfect age and this kpi string has been designed guys okay the strip of kbi has been designed so our we can say 30 percent of work is already completed the next one is to design the charts so next we will start the design of a pie chart all right so for pie chart in our build visual we have a pie chart or whenever click on this uh it will bring us the pie chart over here all right so now in pie chart what we will need we will need one Legend and one valid okay so the legend with whatever we are using here is a department field so I will take this and put it into the legend and I will be showing as attrition count on this so I will take the adversion count and I will put it into the values okay so we are getting this pie chart over here so I will quickly build this and I will go in first in general and I will change the property of this first I will specify the high touch to 60. and I will use the width as let's say 385 all right and now we will quickly add the backgrounds for this so I will just go ahead in this particular and in general I will change the title first I will be using the title as Department wise iteration okay we will be using it as Department wise Expressions reading for it Department wise attrition and I will make it as Center next is we will add a background over here so the background is the last color which we have used is so let me check it this one so we will be using this color and I will be using the background as transferences 75 percent again we will add the visual as well the background border for this same color and with round corners of 15 radius all right so now we can see our this chart has been designed just we will have to uh what we can say build our format our visible more so I will go again in general I will change the title and I will change its color as uh the off gray all right uh so this I will be and I will add the background color as well so I will go in more color and I will be using the color code over here as 8C it's c two eight a zero okay enter so you can use the same code or if you want to use something different you can use it over here okay so this is my what a background of this particular that is title all right so now we will change the font color of the Legends as well all right so we will go into visual we will go into the details and in values we have here and we will change the color to Y all right then for Legends as well I will change the text color to I will use it as yellow okay this is fine right and I will change the form from the tools UI semi bolt all right so this looks good uh let's decrease this not required this is good all right so now a pie chart looks good all right so you can see uh with with the Department wise you can see over here that uh out of we can see in the tooltiplex in sales department 92 people have left the company that is out of total it is like 38.82 percent as well as when we see for HR department 12 people has been left and that is five percent of the total iteration like five percent of total 237 and then the large number of people have left from r d Department all right so this is something worrying about the RND fundament like 133 people have been left that is more than 50 percent right so this is something a good insight for our HR department like from which department the employees are living most right so now next is what uh we'll just take this a little up all right by using arrow keys you can change the position of your uh what we can say the charts which are using here all right so next we will Design our frequency chart which will be converted later into snack Bud chart which where we will see the number of employees in that particular age group with respect to our gender whether they are fail or whether they are female all right so for that what we will do we will take first a column chart over here so I will put a column chart and I will put it over here properly first it's all right then next what we have to do we have to put the field in x axis y axis and whatever other things we require all right so first I will take the agent I will put the age band on x axis so we already have a field that is cfh band I will put it on x axis then I want to see how many number of employees are there in that particular age point so I will take the age band I will take the employee count and I will put it on the axis so you can see with respect to the age band like from 25 to 34 we have this many number of employees like 5554 all right sorry 554 employees are there in that particular age band all right so now what I have to do I would I want to see the break of this particular count of employee with respect to gender so I will take the gender we have a field gender over here and I will put it into the Legends All right so you can see it has been broken out with respect to that particular gender all right so now what we will do we will go ahead and change the title first I will go ahead and in general I will go ahead and change the title of this first and I will name the title as number of employee by age group all right so this is our title then employees all right so number of employee by age group so we will quickly do the formatting I will change the background color quickly you know these steps all the things I will change the background color so this and will change the effect and I will change the background of this I will increase the transparency to 75 I will change the visual border as well and I will make it as fifteen percent uh similarly I will change uh the data labels as well for this so I will click on the data label so I can see what what values it is showing so it is already taking in white color so that is good for us then I will I don't want the y-axis because we are already showing the data labels over here so I will off the y axis I don't want the grid lines as well so the grid lines has been also gone automatically whenever like we are reactivating the y-axis then I will go on x axis I want to change the font color of this so I will go ahead and change the font of these values as and I will make it as of 3 all right and I will change the title to Yellow okay and I will change the font to Sergio semi bolt similarly for this as well hold it and for this on y-axis I will deactivate the title all right then for Legends as well we want to change the font color so I will on data labels so on Legends okay on text I will change the font color first I will change it to bold semi bold and I will use it at yellow all right we want to make the title as white as well in general in effects sorry title will make it as audit so this chart has been designed but one more thing which is very much important guys it is not been sorted uh as per our requirement okay by default either we have options of sorting in here it has it is already either it is sorted by age band or whether it is sorted by employee account okay if you want the manual sorting right if you want one you're starting like uh I should see the under 45 first then we I we should see 25 to 34 then to a 35 to 44 likewise okay so here you can see it is already Disturbed so what we have to do in this case is we will have to create a conditional column into our power query but you can create it over here as well and then we will have to sort that particular field which we are using here with respect to our that particular newly created field so instead of explaining I will show you what what we are exactly doing to do so for this what we will go I will go in Home tab and I will again go back to our transform data so I will go back in our power query editor so with respect to this age band what I am to do I want to give the numberings first okay so what I have to do under 25 I will give it as one number then 25 to 34 second 35 to 44 third then we have and one more that is 45 to 54 as third and over 55 as sign number okay so this is my serial of sorting in my particular chart so however can I give the first numbers or how can I create a column where I can give the numbers of this age group so previously we have done this for dietitian again we will do for here same that is we will create a conditional column over here so this there are so many ways to do this guys I will be using the simpler way because most of you will be beginning beginner over here which will be uh or who will be using this Hands-On uh practice dashboard all right you can create a table and then you can create a what we can say relationship between your tables also that is also one way so first now I will mention it as sort age okay you can give anything uh so I will take it as CF age band so if age band is equal to let me check what is under 25 so make sure your there are no spelling mistake over here right whatever the data uh is there whatever the spell in data is there same should be used here if it is under 25 I want to use it as one I will add one more Clause same the cfh band if it is equal to 25 space hyphen space 34 okay so if it is between 25 to 34 I will give it as number two and I will add a clause again okay in the cfh band The Next Band will be our 35 244 I will give it as third number I will add one more Clause cfh band and if it is between 45 to 54 I will give it as 4 okay and one more is remaining that is over 55 so by default I will give it as else five all right I will click on OK so you can see our new column has been created here okay and by default it is taking a the data type as a whole number okay if it is not whole number convert it into whole number if it is indexed it will not give us the correct result all right so now we have already created a column with respect to our age numbers and now we will go back in home and we will close and apply okay so we have clicked on close and apply it will take some time to load then we will go on our chart and we will select our by which field we have to sort it so I will select this field first I will go in sort by and I will choose the newly created field that we have done in power variables it uses a shortage okay so now I will go in this in short axis I will choose it as cfh random it is selected as employee node I will choose it at cfh matte all right so now uh this has been created so just we will go ahead we have got it into reverse order so we will sort it by ascending all right so what we have got our uh what we can say the age mind very correctly so this is the serial of our age band and in this serial only we want to give our chart all right so like under 25 25 to 34 35 to be 44 likewise all right so this is in serial which we have sorted by using a new column all right so this is our third chart first is kpi then we have designed the five chart and now we have designed our frequency chart as well so the remaining dashboard design we will see in the second part because this video has been very much long so we have not yet added the filters over here uh so like by using that filters how the values are changing we have also not added the interactive filters properly so whenever I am clicking here so those are changing the values are changing but those are not changing correctly right so you can see it is only highlighting the kpis are changing but this is only highlighting so this is everything we will see how to uh sort it out in the next field next part so I will put the description sorry I will put the link of the next part in the to the description of this video and you can see that as well and you can continue with your design thank you guys

Channel: Data Tutorials

Views: 432,367

Keywords: power bi dashboard, power bi for beginners, power bi, power bi tutorial for beginners, power bi tutorial, power bi hr analytics dashboard, power bi project, power bi desktop, power bi training, power bi dashboards, power bi dashboard tutorial, power bi projects for practice, power bi dashboard examples, microsoft power bi, power bi projects for beginners, power bi reports, power bi dashboard design, dashboard, start to end dashboard development, end to end power bi project

Id: -sOHVl_iCHA

Length: 38min 22sec (2302 seconds)

Published: Tue Nov 29 2022