Create a Simple Bill Format in Excel for Personnel Effortlessly

Simple bill format in excel for Personnel

to I'm going to teach you how to create this HR dashboard in po you know bringing in your data from uh cfv file down to transforming the data in part query and actually analyzing data creating a very nice Vis just like what we have right here everything is just very Dynamic click on the particular visualization so all the other Vis that has link to this particular visualization we get filtered and this is how beautiful it could be I'm going to show you how to create it from the stripes so let me take you to the um file that generated the CSV file so it can be some kind of a little bit scary but do not worry what we have right here is that we have every single data we have in one single column and some kind of that like what the heck is going on right here don't get straight because it's very simple to be transformed with a simple uh machine built inside power here and in Exel right here if you've gone that advance so we have par query that we can use to transform any data you want to transform at all so I'm going to show you how to do that in a j so right now what we can do is just for we to go back to our PBI environment so we're going to go back to our PBI environment right here so here we are in the pobi environment so the first thing we're going to do right now so we have other pages so we can actually click and actually navigate through that other Pages we have right here and we have this detail page as well so we click it we can actually navigate through the page we have right on this and everything is very Dynamic we'll click on any of the VIS we have other Vis to actually you know some kind of filter down to that particular vop and clicked so now guys it's very simple isas to be created let us add a new part so I'm going to click on new so let's just get into a new file so right now it depends on the speed of your machine it's going to take a little while to actually come up so let's us give it some time all right guys here we have it if this is your first time when you start up a p this is what you get but you might not you might not have all those files right here because I have a lot of know Works opened and all of that previously before now that is why you actually see what we have right here right now so you might see get data recent is actually all this and you want to open all the report and stuff like that so now I'm just going to close this to show you from the scratch what you can do to get this to for the purpose of those people coming in from Excel and all of that all from any of that analytic stuff to a background so right now let's see what the interface is going to tell us so I'm just going to talk a little about this particular part right here get data so when I click on get data this is what you're going to see you might not be having the same visual as me so I'm going to click right here and actually collapse this and see this is what we have right now so now let's see what is going to happen so give you this particular zoom in so now if I go to get data right here right now I have a lot of way to get my data inside P all of that so you can see all this so if you don't have what you really wanted from there you can actually click on more and it's going to take you down to get a part of it so it's going to take a little work to actually come up let us see what it is so now this is what we have right here we have a lot of way to get data those are all the ways and if you want to be specific you can click on file you can click on database is uh Power Platform Azure online service and all this so you can click on this so if what you really want to get data from like like I think right now they're not that some kind of getting data from uh from YouTube so we have a connector a connector that you can buy online and actually connect to your data on YouTube if you're a YouTuber and stuff like that so right now I want to get data from text CSV I'm going to actually click on text CSV right here and now click on connect so we're going to connect to the file so right now here is our file on my computer computer so we have this F right here on our computer that we can actually get our data from so it's actually TXI as you right here so this is data we want to get to transform and stuff like that so I'm just going to double click it and it's going to take a little while to actually get in right here it's connecting so let us wait wait for it to connect and get our data right for us okay here is what we are looking for it's actually on the wrong Del right now I can correct it and we have a data transform the way it should be but because I want to show you some steps so I don't want to do that I want to have it on this particular run uh the delimiter right here because the limit the delimiter should also have been some kind of T delimiter so I'm not going to have it on it right now instead I'm going to actually click on transform and I'm going to load this into our power query right okay here we are on the powery platform it's a different menine entirely built in both Exel and P to help you transform data the way it should be no matter how R the data is and as you can see we have 35 column being you know some kind of stock inside one single column right here as you can see it right now so but how do we do this it looks some kind of scary like what am I going to do if it is your first time and you have not been taught how to do this before you might get scared away and don't like I don't want to do this right so now you can either click on transform or click on home ed one you clicked right here you're going to see split columns so right now I'm actually on transform previously now I'm on home and you still see that split column right here so I'm going to click on the split column and you're going to have this right here called B limiter this B limiter is what we're going to be using I'm going to click on B limiter and see what we have okay now we have this particular one right here so which is going to be split column by limiter so I'm going to go by tab this is exactly what we need to use previously we have it loaded with by what by I think by comma so which is not actually by comma it can't find comma so it cannot split or separated for us so now want to use by tab so I'm going to leave it right there and let's see how many columns we have right so go Advance option right here click on it and I'm going to see that we have what uh we should have um where the heck is going on I'm going to actually go on full screen so we you go right on we're going to use by column so we click on by column what we're going to have is actually okay we going to click on by column right here so now we have that five columns to be loaded inside our part query and split inside I want to go say by each occurrence so what I'm click on by each occurrence right now it's not giving us how many columns we have number of columns to split into is 35 do not change this else if you change it something else going to happen so I'm going to leave it this way and what I'm going to do right now is that I'm cool with this make sure you don't change the setting it's actually on column don't go on rows mistakenly if you click on it like this it's going to actually def for to something else and it's not going to work the way it should be so make sure it's on column and don't click out else it's going to actually be some kind of rubbish so I'm going to click on okay right here now let's see what happens so it's just happened now you can see the 35 column is actually been right here for us can you see this no extra column being added this is what we have but we not really some kind of cod with it so what is happening so we don't have the head up properly we have column 1.32 1.33 and stuff like that so but we want this ear and roll and stuff like that to be on upon that particular column like the attrition the age the business travel and daily rate and stuff like that to be our column header how do we do that go back to home right here and when we go back to home we're going to see this particular part right here where it says use for through as header can you see that so I'm going to click on that right now and look at what happens so now we have our header right here and the beautiful thing about PBI or par query and stuff like that if you par query in Exel and PBI is is almost the same thing so you're going to see this particular part right here where it says apply steps so now the changes you can make right here is that if you have any some kind of mistake or you want to make some Amendment stuff like that you can always come back and click on this particular setting icon and you go back to that particular as and make your changes and stuff like that so now you can time travel into what you had done previously and you can actually name this part to actually some kind of like keep it very easy for somebody who's don't inherit your report and stuff like that so going back right to right now we are almost down from here for now um uh in a little while we're going to come back here to do some other things so right now we don't want to do anything else for now we want to actually make sure we load this into our P module so how can we do that so just open this and actually say close and apply so it's going to take a little while to actually be applied right here let just give you some time and see what it is okay we just have it loaded right here as you can see we have the HR analytic data right here so if I open it up right now this is what we're going to have and if you want to go to from what this is what from this is a report view right here so I'm going to go to data view so if I click on data view right now what we can see now is the whole data that had been loaded inside this particular machine so now we have all of them all the columns right in here and we can go to the to the relationship view or module view so if I click on it right now we only have one table we can't see any active relationship right now because it is only one table we have we can do a lot right here as well but in my subsequent video I'm going to be showing you step by step it depends on the kind of data we have how to actually utilize every part of PBI so right now we go back right here want to go back to report view I'm going to give you some kind of full screen right now and this is what we have the first we're going to do right now is to take care of this particular environment where we are going to create our repairs so the first we're going to do is for we to make sure we change what we take the back the background to what we really want so we don't want the pure white background I'm going to go to background right here here is my background I'm going to click on it so right here we have the background I'm going to change the color of the background to something like this but as you can see it's not reflecting right on our background so what is happening so what you need to do is just to make sure you actually uh uh get the transparency reduced I'm going to put it on zero so now we have the background the way it should be that is beautiful so the next I'm going to do right now is just like okay now I want some cards right here how do we get that so we to get the card right here the way to do that is actually go to what I'm going to go to insert just like Exel so go to insert and actually go to what go to shape right here and when you get to shape we actually get this and here we go we have something like this so we're going to take care of the size of this right now I'm going to move it down a little bit and actually paace it and I'm just going to make it some kind of smaller I'm going to have a cut of this size I'm going to bring it down so can you see this right now this is what it is this is the C we just got right here but it's some kind of too curvy I might really want to reduce the curve of the card and actually change the color of the card so let's see what we can do I'm going to actually click on it and go to go to the FI color and right now I'm going to give you some kind of pure white like go for this particular white right here and when we go back right now it stand out from the background and this is what it is so we are having some outline so we want to remove the outline from it so let's see how we can actually get the outline be removed so click back on it right here and actually go back and you see outline you have to turn it off from on to Earth all of that so you have this and if we go back right now we don't have the outline any longer but right now I want to have some Shadow right on this to make it some kind of stand out so you can see where we have the locate where we have the shadow so we can say this well because we have some being cut out for us so I'm just going to actually make sure I have my arrow double click and actually shift it to open for us so now I'm going to turn on the shadow now if you go back right now we can see we have a shadow right on it and beautifully it's standing it's standing out the way we really wanted it but sometime you might want to play around with the Shadow and turn into something different maybe you don't like the kind of Shadow you have applied you just have to open right here and play around with all this particular part you have right in here and that is going to look kind of very beautiful on your work and stuff like that so we don't want to touch anything right here because we are really okay with what we have and the next thing we're going to do right now I'm going to have this close because I don't need it I always love to actually make sure I close this and I Collapse it every single time I want so that I can have a a lot of space to teach so this is what we have right now so we just want to make sure this is actually this way I'm going to actually have to bring this down some more so we have something like this and the C is not what we have actually touched so I might not touch the C right now but I'm going to copy this and control V I'm going to have it pasted right now what I'm going to do is just to make sure I have it kind of little bit smaller I'm going to drw it down to this and so now let's see how we can actually make this to what we really wanted so the first thing going to change now is the ship so the car is is kind of too much I'm going to leave it on some kind of nine and see what I have so on nine we have something like this I'm not too satisfied about the nine we have which is for the Cy P right for the Shi C and stuff like that I'm going to go go back and I'm going to put it on something like seven and let's see what we have right here so the seven is actually really okay I'm going to be going down with this out of that so the next we're going to be do right now is to reiz our shap to what we really wanted and actually fix it into where we want to have it fixed and and I'm going to go with something like this let me give it a full screen and we want to have like four of these right here so let's see how we can actually do that now to have it duplicated what you can do contrl c will help you to copy and contrl V will paste it for you and you can actually drag it down to this particular end and when you see this particular uh red um ruler whatsoever you have it released and you past again and you drag it down to this end again and make sure it's actually right on it so make sure you have this particular red showed up before you actually do that so I'm just going to remove and actually get this right here and now I have it so I'm I'm not some kind of too SED with what I have right here all I'm going to do right now is just to make sure I actually move this a little bit like I'm going to move it twice I'm going to actually get to move this twice again I'm going to move this twice I'm going to give that space right here now we have something like this being created I'm going to actually arrange this and actually I'm going to come back to you to see what it is that we can do okay we have gotten this page open and this is what we have and if I go right um zoom in right now you can see what we have as cards but we are not some kind of quiet okay with the color of the card so we might want to change the color of the card all you just have to do is just to click on it right here and go and change the color of the card right here so let me just click on this one to change the color of the card here and see what it is that I'm talking about so now what you can do is go to field color and what we need to do is just for we to apply some kind of transparency into this particular part right here so transparency is going to be some kind of going to add transparency of 29 so I'm going to put 29 right here 29 transparency I'm going to have this so now this is how we want to go by this so This 29 transparency is really okay so what we need to do right now is that once we have gotten this I can actually just click on this and go to this and have it opened so once I open it I'm going to go to home you can see my format pter right here so we can apply it to every part of it with with having to do the same thing over and over again we just going to have to apply this to this right here and this is where I have so we are almost there the next step is to have this copy it again once I copy it I'm going to paste it and actually move it down right here I'm going to have this p as well as this I'm going to have to make bring it down and here we go so we have to extend this this way this is how big you want to actually create this think I'm going to be Co with this right now so the next I'm going to do right now I'm going to have it copy and I should paste it and I should move it all the way down to this particular a and once I have it moved so I'm just going to actually make sure I make it some kind of smaller just this way out of that I can actually make sure we go this way as well so so then right now I'm going to copy this and actually bring it all the way down right here so I'm going to cop it and bring it all the way down okay five far in my position I'm just going to bring this down and have it open out there so this is what I have so what I'm what I need to do right now is just to make sure I have those CED going to have it past instead so we drag this down right here so once we have it dragged down so we can actually create I want to create from here I can actually make this a little bit smaller and uh some kind of I think I have to make sure I bring this all the way down here so we're going to go on shift a little bit so I'm going to go on my AR Arrow key and shift it down to that end I'm going to shift this as well so you're going to do a lot of work to actually get what you really wanted from here out of that so you might not have something too perfect from me right now but don't worry so once you start to create this yourself you will see how you know how how swift it is to actually have it created it's very simple just going to make this smaller and have this right here okay fine the next I'm going to do right now is just to make sure I copy this and actually have it drop down and I'm going to create some kind of giant one right in here out of that to actually get to visualize something okay so you make sure you take it down all of that so you have all these right now so if I actually make sure I actually close every P of it and this is what pads you really have so you can just play around to see what it is that you have and make sure it's actually right you know centralized the way it should be and all of that so you will definitely have a very nice visualization okay the next thing we're going to do right now is to add this and now you have the opportunity to type in here I'm going to type in my HR dashboard here so now for you to have this formatted so what you have to do is just to make sure you highlight it and if you don't highlight it whatever you do is not going to actually have any effect right on it so now you can go right here and change it to what to whatever you know font type you you want to have right on it and actually change this particular font is F type into this particular part here I can make it bold and all of that I can apply Color so I want to make sure I have it right on this part of it's kind of black color so now once I'm done I can actually resize it to what I really wanted so I can actually leave it this way and go back right here and make sure it's being centralized so have something like this is good so very great so I'm going to have it down here so I'm going to have it down here a little bit so I'm going to have to copy and have it pasted and once I've done that I want to have something else right on this I'm going to make sure I move it here as well move it a little bit down so we have to change the text we have right in here and it's going to be some kind of full full Insight so the full insight needs to be some kind of M the way we wanted it so now some kind of Bard again and I'm going to have to reduce this to something like 18 and that is beautiful I'm going to change this particular part to some kind of know something like this so might be some kind of cool right now not too cool though but guys don't worry you can do this so do it yourself and actually find how beautiful it is you can actually get to forat this so but it's some kind of looking weird and stuff like that so what we can do right now is just to make sure we click on it and click on make sure highlight all of them both and we go back to open this particular part here and we see the background we're going to turn off the background and when we go back right here we're going to find something like this beautiful can you see this this EXA what I'm talking about so we have just created something like this and you can actually go ahead and do a lot of things and get this particular part been transformed the way you wanted it so now if we are C with this the next we're going to do right now I'm going to go back right here and go to this particular Circle here and add this particular Circle right at so here we have it so we can now transform this circle into what we really wanted so I'm going to go back right here and change the background the FI color of the circle I'm going to change it into this particular color right here so pick this color and for the outline we don't always want any outline for it we remove the outline and we come back here and actually make sure we hold on our shift key and actually make it some kind of smaller so keep this shift key hold and make it smaller as this I'm going to move it down to my dashboard area right here and this is what I'm going to have I can actually still feel like let me extend it a little bit so make sure you always hold on your shift key while doing this else it's going to get some kind of weird like this so hold on shift key you're going to have it done the way it should be okay guys here is what we have so we about to start to write a logic to actually help us to get what really want from here so right now it's not that complicated I'm just going to show you what it is that we actually trying to do I'm going to go back to the full data for this full one we actually trying to replicate so want to get a total employee you numers of the mail and the percentage and all of that and get it visualized the way it should be just like this so going back right here how can we do that so we're going to go back and actually click on this particular as to have our data shown so now we have the field shown right here and now I always love to do something all my measures I need to actually crowded them up in one single table so how can we add a new table right now so all we need to do is just to make sure we go back home and once we get back home right now we can now see we find where we have enter data can you see that right here enter data so just click right on it and you're going to see a new table being popped out so we have something like this so all we need to do is just to rename this I'm going to rename this you're going to be seeing this a lot in almost all my tutorials I'm going to click on I'm going to actually type in new measure new measures some to like this new Mees get right so after doing that I'm going to actually have to say load you don't need to edit anything so it's going to take a little while to bring itself in right here okay now we have a new measure right in here so what it does is that we're going to start to write all our Logics or our ducks right in here right now so all you need to do is just right click on it and you're going to see this particular new measure right here so we inser a new measure I'm going to show you many ways to do this so now we have to go back to top level right here because now new measure is going to be inserted so let's say that so we just got a new measure inserted so all to do how many employee do we have in the organization don't forget it's about HF so I'm just going to say total so we just have to click inside here so I'm going to say total EMP for employees so going to say equal to and now to get the total employees that we have one single employee existing in the database is not doesn't have any duplicat so all we need to do is to use what we call count RS so count R is going to help us to do that so now what is control looking for is actually looking for table just like what we have right here right now so what is the table is HR analytics data so we have it so I'm going to hit on my top key to get it right here I'm going to have it close and hit enter so now I've just gotten my total employee right in here and the next I'm going to do right now is just that I use some kind of H comma separator right and it's so once you click on that you're going to have it so let's see the next logic we're going to create right now I'm going to have it created I started to some kind of visualize my data so the next thing is going to be some kind of how many male and its percentage how many female and percentage I'm going to say m so equal to right here so now how do we do this don't forget that we just created a measure and the measure is this measure right here total employee which is total em M so now what we need to do right now we need to calculate this a function called calculate right here calculate total so toal employee I'm going to actually have to set this total employee okay we have total don't we name me total let's go back and look at what we have named it okay it's total employee so we go back calculate sorry for that please guys we need to locate where we have it okay guys let us do this again so I'm going to inside a new measure so I think it's just a little Mystic so I'm going to ins a new measure right now so let's see what a measure is all about so we need to actually get a measure right in here so we have a new measure so we just have to say m so equal to calculate calculat so what calculate does is that it's going to make us to reuse the measure being created want to get the percentage of the calculated column and now before we get the percentage of calculated column col we needs to actually get how many male do we have in organization and how many female do we have in organization stuff like that so now once we actually do this I'm going to say total so now this exactly what we are trying to get right in here so I'm going to actually have to get this right so now I'm going to put on my comma it's asking me for the field the field is actually the column of the gender in our data right in our data table so I'm just going to say gender so once I actually click on gender just like this I type in gender I'm going to go down and say h analytics data this particular part I'm going to actually have to say okay now this particular part you have right here from here down to here is actually the name of our table and this is the column existing in that particular table we have just pulled in right now and this exactly what we need to do I'm going to say equal to so now we are about to spe be specific you can you can have like items different items of things right in a single column and you can filter that one single item you really wanted and it to get it pulled away from the other part of it so so now if I start typing mail so now what p is thinking is that oh you're about to actually get to use another function orever so we don't want P to think about that so instead we're going to use this particular double quation the same thing that happens in Exel is what is actually happening right here right now I'm going to type on my M and here I have it so this exactly what you need to do so just have to close this right here and once you hit enter it's just going to S it it's just going to get itself calculated where it should be and now we have done this the next we're going to do right now we want to get for female as well you can put a separator should you have more than a th you have a separator on it and stuff like that so what I'm going to do right now is just to copy this I don't have to reinvent the wheel again and go right here and say new measure so once we add a new measure right now we just paste it and just TW it a little bit and we have our total female now we just have to go right to this particular expert here and just change this to female female and we change this particular title part right here to what to female as well and we are just done from here and once I hit enter right now guess what's going to happen we're going to have this and the next thing we're going to do right now is just to create the percentage of the grand total of both male and female and we create a very nice visualization so guys we go back here again add new measure and you have to understand that we have a calculated column that can do this for us but we cannot use calculated column because when we use that it's going to make our file to be very heavy but there are some time we don't have any choice then to use it so right now what we can do right now I'm going to say percentage mail so I want to have a shorter you know description stuff like that I can now say okay now in this particular aspect what function can I use we have what we call divide so this divide is going to look for denominator numerator and the denominator so what is it the way I want to apply for it so we always divide a smaller value by the bigger one to get a percentage I'm going to say mail and once I tap in mail I have the measure I created for mail right here and once I hit my comma so we go for the uh the second one which is the grand total so now here we have it I hope you understand what's going right here please use the comment section to ask any question you really have I really be very much appreciated so now we hit our comma again and this is some kind of optional but sometime it's very very important should there is any error it's going to default to whatever you actually tell it to do right here I'm going to put in my zero right here who is the alternative in case there is no thing to divide it with or maybe some kind of 100% get end it's going to be zero right here and that is why we have this alternative right in here so once I'm done I'm going to have to close it that is all we need to do hit enter and you have everything you really wanted don't worry and the visualization you will find it some kind of like wow this is what he was creating so now I can actually copy this away from here just copy because I don't want to do this twice again so now make sure you click on this percentage sign right here to have it on and we want to have it on the Zero decimal and we just have to turn this to zero and that is all you need to do so we convert into zero decimal so here we go so now we need to add a new measure again and once we have entered a new measure the next we're going to be creating now is the female gender so we just change a little part of it so we we turn this into a female so for female percentage we just change into what just highlight this particular aspect when once you click on F it's going to go right on it and you hit enter you have this so we are almost ready for our visualization nicely so what we need to do right now is just to make sure we go into percentage and after we have done this the next thing is just to make sure we remove the extra decimal from it because we don't want any decimal point right on it so we are down from here right now so let me give you a full screen to see what is going on and now here we go if I close this right now this is where I have all my measure right on so I'm going to go right here on this particular column as I'm going to have it removed I don't want it we're going to have to remove this from here so I'm going to go quickly on delete from margin and we're going to have it out because we don't have the need of this so we have to remove it away from here so now we have all our measures right here so whatever you want to do you can track your measure please if you've not use this particular techniqu before just go ahead and use it and if you're new right here this is what you should be using it will be very easy for you to find any measure you want to use so right now we can now go and click on this particular total employee right here and it's going to default to some kind of graph we don't want that so we want a card so we're going to look for a card so we're going to pick in pick this card right here so it gives all the total employee we have right in the organization but I'm going to tell you for real that you know I always love to format to my test so I'm going to go right on the formatting right now so let us see how we can format this go go on this particular painter or format or roller whatsoever and go on the what go on the label so once you click on the label we have it on the black color so you want to change it to this particular type of color right here because this is the the L color of our dashboard and once we've done that we can come down a little bit you see this particular one right here so you want to crank up the what crank up the size of the text of the figure right on it so now we have to display what again okay I'm going to have to bring this down and we are the font needs to be changed and what I'm going to do right now I'm going to actually have to go to the top level right here and use this particular area black so we have something like been popped out and this is what we really wanted so after went have done with dat let's Zoom back to the top level and have this particular label being closed and now for the category this is the total employee I want that for now I'm just going to turn it off and I'm going to turn off my turn on my what my title I'm want to put title on it so let's see what the title is going to be like so if I go right here right now look at the top level right here we have nothing on it so we're going to go on title and now we're going to actually have to customize our title so it's going to Total employees to employees so right total employees can click on that oh I think we have it twice been on it so now we have a total employee right here so we need to some kind of go ahead and centralize it I'm going to go down I'm going to centralize it so I'm going to have to change the color to something different like I'm going to go with this particular kind of color so we have something like this and I'm going to actually have to go and crank up the font a little bit so I think something like this will be very cool okay we are almost done from here so now this is what we have so we can make it some kind of smaller just like this so once we actually make it smaller like this what we need to do right now is just to make sure e e we have this closed because when I remove the backround away from here I'm going to take it down to the top level right here and I'm going to go ahead and close this and we look for where we have the background and we're going to have to turn the background up but before then I'm going to actually have to take it to where I want to have it so this is where it belongs so it sits tightly right in here and after that I I go ahead and actually go to the background level here and go and turn up the backround so we don't want any background for it so let me turn it out so now here we go we have it fitting into the uh into the part of our dashboard just like this so we are almost right there so it might look some kind of simple but not too much if you're new right here it's going to take a lot of time to actually get it done so the next we're going to be doing right now is just that we want to actually uh I'm going to actually have to do this once and all contr V we have it pasted right here and I'm going to have to bring in my total Mill so I'm going to go for mail so I'm going to have the previous formatting right here I have to go right here and go to what go to my data lbel and I just have to make sure I have this in some kind of maybe like 20 let's see have 20 202 little I'm going to have this on 40 and let's see what we have on 40 okay 40 is going to be some kind of okay we going to use some typ so you can actually guess what it is that you have to use to make sure you get what you really wanted so I'm going to use that it and once I've done using T it I'm going to actually have to make sure I bring it some kind of you know down like this so what I need to do right now I'm going to have this closed I'm going to go to my title and I'm going to make sure I give it a very nice title is going to be some kind of M just like this m we have to use M and we make it smaller and I'm going to have to copy this down and control V will help me get this down as well so it's not copy yet contrl c contrl v we just go down right so we have to deselect this and bring in our what bring in the percentage of our male and once we have this right here what we to do right now is just to go back to what go back to this particular aspect right here I'm going to put this on 10 or sorry on 20 so we have it on 20 and we change this particular color to something like this so we have a black color like this and we just have to go back to the title expert here so where the title we have to turn off the title because we don't want the title for it already we have a title that actually guides us about what it is so now we can make it some kind of smaller and make sure it's sitting right in the middle of it just like this so we have something like this all we need to do is just to make sure we hold on our control control C and control V to copy and we just have to move this gently down to this particular AIT right here uh to create our female gender there and that is all we need to do so after we have done this we just have to click right inside here and change it to the female okay nicely formatted just like this so now the next we're going to be doing now is that we need some images right here so how can we get those images right in here so all we just have to do is just to like go right here and bring some icons go to insert and from insert you can see this particular image and once you've done that just navigate through your computer where you have the images writing so I'm just going to go on this particular part right here I'm going to scroll all the way down because I know the specific place I have my image so I'm going to be giving you those images to be used all of that so now I can actually make this smaller to fit into where I really wanted to actually sit right so I'm just going to take it down here and give it some kind of a little trick I think it's some kind of cool with what I have right here now I'm going to let it be like this so I'm going to add a second part of the image for the female gender I'm going to go all the way down I'm going to pick this I'm going to have to resize this as well so just quickly resize this to what it is that you can actually get use there um take it down to this particular aspect so if you actually have it seed right in here so you have your image that actually talks about both the gender both genders so make sure it sits and actually align right the way it should be so you might not get it I might not get it perfectly right now but once you're doing this on yourself on your own just have to make sure you do the right thing so click on this and actually get to see where it is so here we have it so we need to have a separator right here I'm going to have my comma separator right on it and here we we go so almost there okay guys we have it so we have a t operator right and so which is very very important for you to actually have it because it's it's on T and we have something like this we can see the percentage of our male and our female and organization and stuff like that so let's see the next level we can actually create because this is getting much more interesting so if we come right here right now you see not default promotion and default promotion both the values and the percentages so how do we do this but if we go back to like I'm going to click go right here I'm going to actually make sure I drop this down to understand what this that I'm doing go to transform and click on transform right here and we're going to go for the promotion column right here that is ear SL promoted you are going to see what we have right in there so um okay I think here is now what I'm what I want to show you I'm going to have to close this and go to the main data that we actually using for this right now so I'm going to go right here and here we go we're going to click on this transform right here I'm going to actually take you to the ER column we that is ear SL promoted and uh we once we actually hit that so the colum on highlight is actually the ear since some of the employee got some kind of promotion in the organization despite some of those particular employee have been working over 30 years so now if I click right here right now what you can see is actually the going actually load more to see if we have more of it so we have people who had never been promoted over 15 years 14 10 12 11 and stuff like that but now what the manager uh what the HR management is actually looking for right now is that whoever had not been promoted since last 10 years to 15 years should get promotion so and they want to know who are those people in the organization because they can't really think about it but they have the data that back them up so how can you help them with this analysis right now so I'm going to click on okay right here or cancel it and now what we need to do right now is for you to actually add what we call a conditional column right on this so now I'm going to go to what I'm going to go to like add column so from add column we have conditional column I'm going to add this conditional column right here so now the conditional column is coming up in a while so now we have a conditional column right here so what you can see now is that this is some kind of a little bit confusing right but don't worry so the column name so the new column we're about to create because you want to add a new column using a particular promotion or yes promoted column so I'm going to type in promotion promotion status right here so promotion status so right here the column I want to actually get some kind of condition is what I need to actually search for right here so all we need to do is just scroll all the way down and you will definitely see what we looking for okay I think this is it here since last promotion so you click on it and we have that here so everything that is actually is greater than or equal to is what we're going to use the operator type we're going to use is this so we're going to be using many of those operator as we keep creating so just keep watching now 10 years 10 11 12 13 14 to 15 that is we have maximum 15 right so it's going to be some kind of needing promotion so want to say deal for promotion oh sorry promotion yeah do for promotion and any other number is left so not deal is what I want to use for it so we just two things two options okay fine we are done with this that is exactly what we need to do I'm going to show you how to add more close in uh subsequent part of this dashboard so what we to do right now to just say okay right here click on okay and we have all that being created for us can you see that all that being created for us so if you want to verify if this if this is really okay no matter how good you think you are sometime you need some kind of verification right so let us see um I'm going to click on select right here I'm going to click on uh I'm going to click on default promotion I'm going to click on okay so now if you have every person that are do for promotion is between age 10 to 15 and you can see right here if I click here what you always see is from 10 to 15 that is exactly how we had conditioned it so I can actually go ahead and cancel this right now because this is what really wanted and it's actually is done for us so now if I were you because I don't want to have a very huge file S I can go ahead and remove this right now because already we use we use this column if we have new data coming in you don't have to worry about it pqu is going to take care of that for you despite as you have this column turned off so I'm going to go ahead and click on delete on my computer I'll have that go away so now we just have to load this and actually get a quick uh visualization right now so I'm going to close uh close and apply it's going to take a little while to actually get itself applied right in here so I told you it depends on this PDF computer but now we're going to have it okay I think we just have the column right in here right now but we can't see it yet but if we go in right on this particular exp or we can make a search to some kind of promotion status we have it so here we go we have it right here dual and not dual and stuff like that but this is not what we really wanted so I can actually get it off so the same thing we had done previously is what we're going to do right now I'm just going to clear the promotion status away from here I don't need it so I'm going to have it some kind of uh going to have it closed so for now we just have to actually start to create a logic in right here and uh I'm just going to show you this for the last time I'm going to have the logic created behind the scene because we're going to be using some kind of same techniques over and over again to get this created and get it visualized so we just have to right click on it and actually add new so in case in the previous one you did not pay attention I think right now it is time to pay attention to what we are doing right now so I'm going to actually zoom in to let you see what I'm about to do and what I'm going to do right now is just going to be some kind of U deal for promotion promotion so how many persons are do for promotion and percentage how many person and not do for promotion and it percentage is what I'm looking for right now so for do for promotion now what we need to do is to just say Okay calculate use calculate function and once we use calculate function what is going to be the next is the total employee so we the total employee column that we just created that was the measure this is the measure we have right here so now once we use this I'm going to put in my comma so I want to bring in the column the new column we just got created in par here is what we're going to actually move right here so I'm just going to say promotion so once I actually click on promotion so we have that particular promotion status right in here so we're going to have to bring this right here and what color what particular part of this particular promotion status are we looking for um I'm going to have in here my double qution uh dual for promotion so I'm going to say du for promotion so mind you if you don't have this Pell correctly you would have to check very well because if you don't have it correctly it's not going to work so I'm going to hit enter right here and that's exactly what we need to do and the next thing we have we have to do again now is I'm going to actually have to copy this away from here because I don't want to create this over and over again once I have it copied so what I'm going to do is just to make sure I create a new measure so I'm going to have it copied copy it so go ahead and create a new measure and once you just created a new measure just have to paste it right uh P paste it in right here and actually track it a little bit so now we just have to say not do for PR for promotion so not do is exactly what we're going to have and here we're going to have to use the particular part of not do not not de so now we have to bring all this in right here so let's see let's see what's going to happen so we cannot go and check it out and see what we've just created if they are right so we just have to check it out and see so we just have created not de I'm going to click on not de right now and see what it is so if it is working we're going to have it on card and see on this particular card so we have it on our card right here and we just have this so what we're going to do right now is to create a percentage uh that is actually going to to that so what we need to do is just to add a new measure and I told you we had done this before and I'm sure again and I'm going to actually I'm not going to show you again to create the other Pad but I'm going to show you how to visualize all of them so we just have to say zoom and actually go and like okay now for this not du not du so to create percentage right now I'm going to say uh divide so divide not deal so divide not deal by what by total total employee and the alternative is going to be zero I'm going to actually have to put my comma and now we have a percentage right on it so can you get this right now okay I think we already have not deal right here before formula before now and what I'm going to have right now I'm having some kind of trouble to get this back right on it so all I need to do is just to make sure I differentiate it with percentage right here so that we don't have the same thing over and over again so if I now hit enter it's going to be some kind of accepted and after we have gotten this some kind of you know uh right on our measure table we're going to have to put it on percentage and um we just want to leave it on one decimal point right now we are cool with one decimal point and that is all we need to do and the next we're going to do right now is just to make sure we copy this and we don't have to reinvent the wheel again as I always my tutoria so I love being lazy around so go ahead and click a new measure and paste this right in here and create for deal for promotion that is what I want to create for right now we're going to have to paste this right here uh it's going to be percentage of what percentage of uh dual for promotion so do for promotion so right here we just have to come right in here and just typ in De so go for defa promotion when I hit enter you can see how fast it is to actually get it done all you need to do for the first time is for you to understand the function you're using and how to use them appropriately that is all you have to do so I'm going to click on my percentage right here and uh don't forget about one decimal point just have to do that and now we are set to get to visualize our data the way it should look like out of that so now we just have to give it some kind of little formatting I know that all right we've just gotten this part done so it's nothing uh it's not nothing it's not much so you can do it I'm just going to have to replicate and actually duplicate this uh I'll bring this down right here so and after bringing it down all we need to do right now I'm going to have to change what we have right here and actually you know apply some kind of for M and you see what it is so du here is going to be not du and right here we're going to have some kind of for not de for it for percentage of not de is what we need to look for right now have it right there so I'm going to have the percentage right in here so uh we can have the percentage on we have this particular one being removed so I'm going to remove it so not the percentage is going to be this can see that it's on 95.1% I was why it was kind of high so what means for this for this kind of for this one right now we need to have a separate right on it so we just have to click on this deal and go right here and add it separator right on it for we to have it on some kind of t on separator and stuff like that okay nicely once we have done this this is the next level we're going to go into right now so we have to visit our power quy again to actually get something transformed and create what we have right here but before then we don't want to have something like this because we don't have anything that separates them to show that this is what this what is for and all of that so I'm just going to go back to insert and from insert we want to go back to what to shape and from shape want to take this particular line right here and we have the line in here so I'm going to bring the line down to something like this so I'm going to have to like make sure I move the line right in between this particular AIT here and once I've done this what I'm going to do right now is just to go to my line outline color and choose the color and go for it and I'm going to go for this particular type of white color like that white okay I think I have it separated right now just have it just get it I love this right so now the next I'm going to do now is this so you want to create a service here right here so how do we create the service here is for we to go back to home and from home we have to choose on transform data and we are back here every most of those particular part of a dashboard of our data that we had been given some kind of numbers and it looks weird when I have some kind of visualizes so we'll have to scroll down till we have the service year right here all of that okay here we have this particular uh year at company which is our service year which is the how many years has a particular uh employee be in organization stuff like that so right now we are going to be doing something very different so what we need to do right now is this so we are going to be doing something different from what we had done before now so we just have to go to what go to add column and you can go you're going to see this particular column from example so you just have to say from selection so once you have done that you're going to have this so we have this empty one right here uh I'm going to type out ahead and I'm going to type service year service year right here and I'm going to come right here yeah we have it we have Dem on numbers so what are we going to do to actually have it where it should be uh the way we like so now this is what we're going to do uh here are service the one we have on highlight is what we want to use so make sure you click on it so this is the one we have on highlight so right now I'm going to click on six right here and once I've done that I'm going to type in ear ears six ear six ears and uh that is exactly what I want to do if I hit enter right now what you're going to see that we have all of them right here so now we have all of them transformed for us so we can click on okay that is what it is we have it we have it that is next one so we have it so now we just have everything we really wanted and I've told you once you are done with what you want to do you can go ahead and truncate that particular part of the column that is all you need to do and the next we're going to do right now is the level job level so we have to locate where we have a uh job level right in here and actually add to transform we have to transform that as well to get it used and stuff like that same thing again we have our job level right here and we only have numbers right in here and to visualize this system kind of going to look kind of Weir and the same thing we had done before is what we're going to do again so add column we're going to say from uh from example and if you want to go on all and you selected some P some particular columns and all of that so I'm going to show you how to use this in subsequent videos so I'm just going to go right here and we just need to add some kind of levels right in here I'm going to say job level job job levels so go in here I'm going to say level so type in level here so and what it is check this column is two and hit enter just say okay guys we are almost done for our service here I'm just going to click on it so we have a service here right here for us so here our service here and what we're going to be visualizing the service here which is just going to be some of I'm going to zoom into this as here so I'm going to go byward service here and we're going to look for total so total employee so we'll bring total employee right here so we have them right in here so the next is going to be some kind of chart so we need to use it on a chart so we go for this chart part right here and we have this chart to be used and once we've just gotten this we're just going to make it some going a little bit smaller I'm going to make sure it sits right in here for us so I'm just going to make it to actually situate itself right here very much so go to actually extend it this way and um I think we're cool so we want have to remove this particular service year and the total employee from here all that so we don't we don't need the y and x axis right on it so what can we do to have that done so we have to go back and go to this particular format right here for the y axis right here so we just have to go scroll all the way down and turn off the title so we does has got the title been turned off so we just have to go and close it back again before we go for the X so we're going to go for the x axis right as well so we just have to scroll all the way down so we scroll down and have it turned off so when want to turn this off that it's been turned off so we scroll all the way down I think we just have to verify if all of them are off so I think we have all of them been turned off right now and the next thing we're going to do right now is to change the color of what want to change the color of the um of the charted sh so we go to data color and once we have landed on data color just have to click on this and what do you want to use for the data color right here so I this is our leing color right here so we just want to use this particular one for the DAT color I think we are going to be cool this so if you want to add some kind of um data LEL Rising it so you have to always close whatever you have opened close it so data lbel is right here so you just want to turn it out turn down so we have something like this all that so let's us check and see the next thing we have right here so you can see we have position so we want to go on position where do you want to have it so you want to have it in the middle inside there is it's going to be like can you see that so it looks kind of so tightly well on it so now this is exactly what it is so there are so many format you can do but right now I think we going to like stop it right here right now and just go ahead and actually see okay now the next we're going to do right now is just for we to actually say the title we needs to be changed I'll go for the title and for the title uh service here is what we just want to use for service here so that is exactly what it is so we have to go back right now and actually go ahead and actually bring this here the service here want to actually make sure we're consistent with what we're consistent with our format and just go right here and actually have it right on this so now can you see that so we have this and we have just lost one of our formatting so we just have to go back and put back that particular daal Bill on so so we've lost this particular dial Bill right here so we just have to turn it up turn it on again so we just have to so the the T Bell is still on it the reason why we can see is that it Chang the color for us so we just have to change the color to what we really wanted so I think we can actually go with this particular kind of color or we just want to make it kind of more whiter than that so we want to use something like this it looks fine so nicely we have it so now we can you see this so we have what to call two tip so if we hover over it want to know how many male and how many female in this particular uh five year range of like how many people are actually uh on service for 5 years ago we have 196 so I want to go down go back here and uh if I go back here right now we can see the tool tip right in here so I want to go right here and actually make sure I select this and now I can actually go for male and bring my male gender right on the tool tip so not the percentage expert so I'm going to go ahead and actually say for my male I'm going to bring it down to the to tip level right here and for the female I'm going to locate