Power BI Tutorial for you. Learn How to Analyze Crime Datasets Using Power Desktop | Understand DAX

hi everyone welcome to my YouTube channel today I'm going to show you how to create this particular beautiful dashboard if your aim is to learn how to create beautiful and some kind of clean report or dashboard in both Excel and poier this is your go to channel so I'm going to tell you welcome okay so we're going to learn how to create this but before we actually start to create this from the scratch we might need to know exactly what are the problems we are trying to solve using the data that we have that is exactly how we learn on this channel okay before we go on this particular report is about crime analysis as you can see we have dangerous crime time here that we expect people not to actually go out on this time because they don't want to actually fall a victim of any of those crimes available so if I click on here now you can see different crimes we have here that we can actually drill down to and look at it so let's look at this particular veal crime here and if I actually you know go ahead and filter my report by this now you can see the crime is high on what is high on Friday Wednesday and on Monday so using this particular kind of chart helps us a lot to actually tell the story the way it should be okay you can see this particular year-over-year change right here so there is an increase in 2022 and over here we have decrease and you can see the month increase and decrease right here the red shows decrease of crime in that particular month and the red the green color shows the increase of crime so first of all before we dive into the creation let us look at the problems we really need to solve you can go ahead and pause this and read through this for you to understand exactly what we about to get into all right with the TX you have just seen we're going to use that to actually guide ourself through on how to create this particular report you have on your screen okay those are the couple of colors we're going to be using to make our report look very beautiful and uh we're going to use part point to create this and actually take it to pop p and create something very very much beautiful so if you're interested in learning and you have not subscribed to my YouTube channel before this is your best time to do so and if you're a returning subscriber I'm going to tell you welcome and let's Lear together quickly let's get to our data I'm going to click over to import from Excel to go get my data they'll navigate to where I have my data writing YouTube post and here I have crime dashboard so I have this particular one which says other data I'm going to open as you can see we have lots of tables here so the only one I'm going to work with is actually uh the C Prime type so I'm going to get this one so in the future we're going to be using all other data or tables we have here but for now let's go with this one and we're going to grab one more table again from a different uh file so click on transform all right here we are quickly before we do any other thing let's quickly go back to home and click on this particular new source and click on Excel workbook then we grab the crime data itself so I'm going to click on open it's just a single table so click on the data and just click on okay here so that will actually move it to par environment for us now we have this particular data here so the very first I'm going to do is to actually turn on my view and go to column quality just to see the qualities of my columns all we actually expecting to see is actually 100% on the valid area right here here that is only if we are expecting the column to be 100% so the 100% on this one right now affects it it doesn't give us something very meaningful as you can see if I open it up right here it's giving null so which means this column has nothing very important so we can just go ahead and remove it to do that right click and you're going to see remove you just click on remove so we have actually taken the column of you get it if I move this way again now you can see we have this particular one here which is actually giving us time so what are we going to do to this we need to actually get this time in a time format and this time now is mixed up with date but what happens if we try to turn this into a date and time here we have you know some couple of Errors what do we do to this okay fine it's very simple I'm going to remove this step so under this particular part here is where you can actually come back to the steps you've actually applied and make any changes if need be to it so right now I don't want this one I'm going to remove it so I use the X I removed it so what I'm going to do now is to actually come here I want to show you something go to move and move to the end so moving into the end right now you can see we have the same date here but different format that was why when we actually turn this into into either a date or dat and time we're going to have some kind of Errors right so what do we do to this now we don't need this particular date we have here all way there is the time to get a time from here now what we need to do is to go to the add column so when you get to add column we're going to extract the time so what do we do H I think that shouldn't be the way to go let's go to home and from home we locate where we have split column on that split column we're going to use by limiter so you need to look at what the limit you want to use to extract this let me move it a little bit so now with this now we have a space delimiter our space delimiter is between this date and time here so we have other delimiter like the dot or the full stop delimiter so we have this delimiter as well so we're going to use the space delimiter to actually get what we want extracted so I'm going to come over here and choose space so when I choose past leaveit at each occurrence here it's fine maybe you want to specify at the left or the right hand corner depends on what you want I'm going to click on okay so can you see that now we have our time our time now is with a and p.m. so for that we don't need this this ought to have been date right so we have the date column here so this is actually not going to be useful to us again so we have removed that the next thing I might want to remove from here now is actually getting my am and my PM so what I'm going to do right now to get the AM and PM in my previous you know video somebody says I don't know how to do that and I did it in the video I don't know why you couldn't do it so what I'm going to do right now is to actually go to add column you see extract here click on extract and um you must make sure that you have consistency of what you want to remove with two letters three four or whatever it is so I'm going to actually choose the last characters so the last characters is either AM or PM so consistently is two I'm going to put two over here then I extract so I have my AM and PM right here right now on this particular one here I'm going to name it crime time so crime time oh sorry that is D mistake all right okay for the am andp we can name it the moment like moment okay fine this is beautiful so make sure you have everything on the right data types right so if you look at it now everything looks fine looks very good and okay so this is what we want to actually use all right the next thing we do now is just check for our crime type here and make sure we have it on the right okay that is cool so we are good to go so we can now close and apply this is going to take it up to pobi desktop all right here we are at the pobi desktop the next thing we're going to look at is actually check the relationship if we have it rightly the way it should be I'm going to click over here to The modle View and um okay now we have it so crime and we have the data here so we can move it anywhere let me have it right here right now okay all right the first thing we want to build now is for we to build this right here I'm talking about building something like this so if you can see here what we have is trying to look at the differ between this particular one and this one so it's actually the crime went down by 205 that is crazy so we can decide to show it on percentage just like the way I have done right on the month area here is your choice I'm just trying to give you two options percentage or numbers whatever whichever one you want but how do we actually build this before we build this we need one thing and that is going to be our date table right let's go ahead and build it all right click on this particular table View and uh let's go to new table here we're going to add a new table we don't need to build very complex dat table it's just some kind of simple you know Dex to be used here so I'm going to call this one date table for my date table I have ADD column so I'm going to have calendar Auto again right so add column and calendar Auto so this calendar Auto is going to actually help me to locate where I have my date column right in in all the tables I have like if I have 100 tables it's going to actually go ahead and extract but it's going to be difficult when you have different date columns in different tables it's going to be crazy so in my previous video I've shown you how to actually point it to the actual table you want to actually get something for you so right here I'm going to put my comma Here and Now what do I need I want to extract the year so to do that we we come over here we type ear so now comma we use the ear function so for the year function we use the date so this date is actually the date I'm going to show you where the date is coming from so we can go ahead and close this go down and close our add columns then if I hit my ENT ke right now we should be expecting two columns beautiful so we have the two columns if you look at these two columns we have right here I don't know what actually this one is let's find out we are having 1904 where is this 1904 coming from let's go ahead and verify I'm going to open up this particular part here and if I click on my data my data has let's verify the minimum and the maximum so if I scroll down now you can see my data is actually three years of data from 2001 to 20 2021 and 23 so where am I now seeing this huge you know chunk of this from that's crazy that's exactly what I'm saying so let us verify on crime type here nothing like that here but if I come over here I see this how do we actually control it right how do we control it so maybe it's actually sensing that we have a DAT column where we have this one removed from that is where we extracted this particular time from and it's still like okay I knew that you have something here what do we do to save our sell from that's the chunk of that so we go to the DAT table here let's do something like this so at the part of the above here we're going to use a variable so this variable is going to be called u mean mean date so for the main date I'm going to store here the main date and I'm going to call it column I want to extract the main date from that will save us from getting 1904 then we just need to actually get 3 years of data so in case you don't get what I'm talking about right now let me actually show you what it is I'm going to just cancel it now open up here can you see now we have lots of date a lots of De so we want to control how much years we have in this particular part to control that I've shown you previously in my other classes but I'm going to show you again so we just type here a variable inside this variable we call it minan date equals for the minan date we're going to have to bring here then we have to actually use the main function so the main function is going to go into our data and go to Crime dates so crime date is a column in data table right so all we have to do is to close and close your ear and then AR in after this we actually create the second one the second variable will actually extract the max max date then equals we type in the year and uh we are going to get the marks then we need the crime that so after getting all of the right now the next thing we have to do is to use this particular variable above here to actually filter our calendar or a DAT table so we want to use it to filter this particular column but we are not going to reference this very column here instead this is the column we're going to reference whatever happens to this particular column right now affects any subsequent columns we're going to be adding right so let's find out okay where do we do that I'm going to calm down here and the next thing is to return so when I return I'll go after my add columns I'll actually use the filter function so I'll wrap the filter function around the calendar Auto here so beneath the calendar Auto what I'm going to do I'm going to type in the ear function here so I'm going to say go into the crime date and I find the ear so when I actually bring in the ear here I have to bring in the date so this date here the date I have here right now is this particular date we have over here this very one so this is the date I'm going to say go into this column when you go into this column use this particular column coming from this table crime date and only make it to match the es we have there instead of H have having this multiple years you get it now let's find out so I'm going to actually have um I think have this one closed so with this one closed now what I'm going to do next is to actually see where it is actually greater than or equals to what to the mean that's underscore mean date and so let's step down again where the year the date we close is less than or equals to what now to the max so maxed it so we have to close for our filter here and now we need to actually apply a comma to actually get everything ready let's let us hit the enter key and look at the behavior of this can you see that now beautiful so let us take a look at it in detail so if you look at it now it it has been filtered down to 3 years of data 2021 to 2023 this is what we are expecting to see so right now if we come over here this is actually in some kind of um detent time so we hit this we don't want to do this right so let's come here to data type and change to date okay I don't like this view as well so format we change it to a short dat type beautiful we have this I told you we don't need to do much right here I'm just going to actually extract one single column and we call it a wrap over here so I'm going to add a new comma so now I'm going to extract my month month and if I do that now have my comma here so instead of me to use the month function I would rather use the format function because I want to actually see my month not in number but in text so using the format function is going to ask you for Value the value is our date column you still remember that date I'm going to place my comma right here what format do I want so inside the double quotes I'll type mm three that's going to give me the short format of the kind of date OB sry month I'm looking forward to seeing then I go ahead and close this and if I hit my inter key right now I should have my month oh no something is wrong yeah nice silly so just come here and close this then hit the enter key right now now we should have everything solved still battling with this what this Rong let's let us look at that month we should have format okay let's wait okay now when you make any mistake to close either a quote or some kind of um a bracket whatever it is so the system automatically helps you to close it but does that in the wrong direction so look at what happens here the system automatically closed using those two for us so we don't want them we have to remove them before we have what we want correctly so if I hit my ENT key right now we should be clear now I told you okay now to those of you who are just getting into P for the first time I wouldn't do things that you don't understand you'll be like asking yourself question you don't see who to answer your questions so let's come over here now and use the date table and see why we're going to be doing the next step we're going to be using right if I come over here to date table and I come to date here I bring in my date so this is what I have so this is some kind of default date that PBI would generate for us so Pia will go ahead and like okay you know what you have here you have quarter you have month and you have date so we don't want this we want this to be in something like um January 1 20 24 or 25 whatever it is so we don't want to actually be in this particular format so how do we actually make this happen so we need to tell P that you know what we have our own dat table so we want to Mark our territory let's go back so we back right here okay let me c this for you to see it better so when you click on your date anywhere any of the columns go to table two and now let's click on Mark as dat table don't forget please watch look at what we have here we'll make a change and let's look at what it's going to look like Mark dat table so click over here you must see that if you don't see that that mean something is wrong so validated successfully click on okay all right so let's get back can you see what I'm talking about it has changed that is one of it fine let's some kind of remove this and bring in what bring in our month here we have the month so the month now starts from April and it ends in September how do we fix this it's very simple we need to actually get that done I know most of you could do this but to those who cannot let's fix it so we open this place up we add one more column to support that on this column we type in month nor for month number so we close we do comma so instead of using format we going to use the month function so with the month function we call the date column we just close that is all we need to do so once we have done this we're going to see in some numbers right so those numbers are some kind of attributed to every single month so one for January 12 of December so click on this particular month here highlight it and go over to I think it's going to be column tools then you see over here where we have the sort by column you click on it and sort by month number so getting back right now is going to be from January down to December let's find out wow solved so what is left before we start proceeding on the creation of our report well let's go to the relationship area and make sure we have the right relationship so I'm going to close all of this and collapse this so now we don't have have it going to move and uh move this here so now we have the crime date I drag the crime dates to the DAT so we have established connection between the two tables and it's giving us many to one relationship that is beautiful so let's now get back to our main view here so what is the first thing we need to do beautiful okay let's see we want to create create and see how many crimes record we have so I always love to organize my uh mer just inside a table so I don't need this I'm going to remove it now so instead of me doing it the other way around let's go over and create a table so if I go to modeling I'm going to see new table here I click on new table I don't have to be on the table view to get this done because all I'm just going to do is to create a box where I can actually store all my measures right in so call this anything you like can call it calculations so equals the square bracket inside the square bracket you can just have one as a number if you want to have something like a text inside it you have to put it inside a quotes because Pia does not understand that but Pia does understand what number is so I close this and I hit my Enter key so now we should have a table of I think we have to place in double quotes is that what you're saying okay let's do double quotes here let's see the ENT key so so so what is wrong oh no I think this is not this this is not what we want we want a Col bracket like this I'm going to place one in it this as well and I hit my in key sorry for my wrong you know DX function okay whatever it works now if we go to this part here we have it we call it disconnected table it has no connection to any of the tables we're going to have here you know or we have here already so let's get back now inside here I right click I can create a new measure so with a new measure I've just created now I'm going to call it um total crime toal crimes so to for toal crimes you know what it is already we just do the count rows of our crime table which is our data table so I should have renamed that so let me call it controls and uh data this is confusing right I'm going to rename that table to Crime uh crimes table or whatever double click on it and just name it to crime crime data so don't worry about the function you have already written it's going to be updated automatically if you think is a d Li let's go ahead and take a look at it if I open it up right now updated can you see crimes data so quickly before we do any other thing let's use the comma separator here because it's number all right we have our first measure rating I'm going to have to bring it down to here so we have 2,500 crimes recorded right so what's next we do we need to create a chart first of all I'm going to bring in this one here as a table so I'm going to bring in my total crime but before then let's go to the date table and bring in the year so we have the three years of data so we have it splitted into the years so I'm going to create a chart and the chart I'm going to create right now was supposed to be this one but because of how we want our chart to read or values we not going to use that so instead we're going to use this particular taged column chart here so on the Ted column chart we just have one should I call it measure so not happens we're going to have something like this so where do we go from here so this is where the the whole thing get some kind of T and all of that so pay a very close attention for you to see how everything goes it's very simple and easy can you see it once again between 2021 and 2020 two we have increase of 745 crime between 2022 and 2023 here we have a decrease not an increase right now you can see we have an arrow down beautiful right so who shows that the crime went down by 205 so if you could actually analyze it on a percentage is all your choice so let's create a new measure this measure we're going to call it crime previous here crime previous year so all we just need to do is to change the filter context using the calculate function so we bring in our measure to Ty crimes and uh we can use dat that we use same period last year and we bring in our date column from what from date so here is it from a date table that we created we close and finally we close our calculate function so the functionality of this particular S period last year is to actually uh offset by one minus one so it's still the same if you use the date ad function so we have what we call dates um add so the date ad function you can do minus1 - 2 - 3 4 and 5 the difference between the date ad and the same period last year is that this one only offset by one so you can actually have control over how much or how many months years or quarters you want to go back when you use a date ad but this one automatically it offset by one so here we go so let us hit the enter key and look at what we're going to have right here so the next thing we do now is to look at the behavior of this I'm going to push this one let me push this one a little bit over here then let's bring in the ear then bring into T crime and uh the last here so we change this one to what we change it to a table so can you see what we have here right now so this is what we have so the next we're going to look at is actually looking at the difference the difference is what we going to be looking at here so we can call the different year-over-year change but instead of we creating lots of measures that is why we have variables why don't we combine everything inside a single variable although it's going to be some kind of a huge work you know so all you have to do is to pay attention right let's bring it down we can see what you're doing from here when we hit the enter key so I go to my previous here so instead of meing this one previous here I can just call this one label label so I'm going to use this one to be here okay so let's store this one inside what store it inside a variable so we call this variable underscore or previous year so equals that TOS this right so if you want to shift it at the same time highlight it and your tab key will definitely move it for you right so we now create another another variable here so this variable will be underscore year over year change so equals so for the year of year change now what we're going to do is just to actually simply use this total crime we subtract it from what from our variable onore previous here right so if I hit my Enter key right now let's see what we're going to have oh sorry for that so I need to return so create a return and return our year over year change so this is what we have for year-over year change right now let's just see it in detail so if you look at what we have here we have different values compared to what we have before when we actually you know return this particular part so this time around we the year over year change because there is no year before 20121 so is still returning the same 405 so for this between 2021 2022 we have you know uh 745 it's between 2023 to 2022 we have minus 205 right here this is beautiful you get it right now so we're going to use this to actually create something very meaningful that like what we have seen previously before we started creting this so I don't to return this I'm going to take it off so let me push my return over here so now let's get into the main detail so the next we're going to do right now is to use a function called switch through so the switch through function comma so what we're going to do right now is to actually say okay you know what if this particular underscore sorry underscore here previous year these our previous year if this previous year here is equals to zero is equals to zero so we want you to actually return it right return underscore previous year and uh we want to concatenate something here which is going to be inside of a quot want to put this you know um Dash in it I'm going to show you why I'm doing this in the gy but for now just watch so comma shift enter again underscore previous year so if the previous year this time around is greater than or equal to one which means it's not zero so what we want right now if you look at the previous one we've looked at before we began this particular class we have up and down arrow so we want to actually bring this up and down arrow so what do we do inside the book codes you want to actually have Arrow up we don't need to go to extra you know a website to get that done so your windows icon on your keyboard press it down and this full stop will give you this so once you have this now let's go over to here and now we scroll down so we can now see different what we can see different arrows here so we have this one if you scroll down you can see different icons that you can actually use so we have 10 arrows we have tick arrows it depends on which one you want so here the 10 ones here so I'm going to use the tick one over here I'm going to use this one so here we go so give a space and close this then we concatenate and bring in the value for the previous year so here is our previous year value so comma shift enter underscore previous here here is actually now less than one so if this less than one which means it's going to be minus right so what we going to do we have to say Okay if that is the case we want to get the down arrow so now we bring in the down arrow your Windows key and your full stop here gets you back to this place and uh you scroll down and you locate the downward Arrow this one here so nicely we have it space close and concatenate this to with okay we are done then shift enter let's close this and take a look at what this would look like okay so if you look at this now we now have those arrows here so what we are looking at here now let's see okay if you look at it there's a mistake here we have the two arrows going up so we are returning something weird so we are not supposed to return the previous you know uh year instead we are supposed to return our year-over-year change so what do we do how can we make this so what I'm going to do right now is to get this one changed to underscore year over year change and manually change this one all right we've changed all of them so let's go to the value now and uh now we can take a look at what we have here so if you look at this one we don't want to show this particular number here this 405 so we want to make it zero so how do we go about that let's come over here so I'm going to place my Cy here now and like let's copy I'm going to copy it so we use the if condition if so if this value here is blank so it's blank so if this value is equals it's not blank okay let's say it's not blank so it's not blank then go ahead and give us this otherwise give us just zero and uh we close and hit our ENT key let's going to verify if the one if this one does the work for us so that doesn't work what we're going to do right now is just to do something different so let me just control Z to take it back to the original version of it so this is how it were before so what we're going to do right now is our previous here we need to change so we're going to do if underscore previous year is not blank so if it is not blank so we want to go ahead and run this particular line of code otherwise so otherwise if it is blank we want to see zero give us zero then we close our if here so this one now should work absolutely fine let's take a look at it so now can you see it now we now have zero here so what we want is not actually zero we want to see blank so we don't want to display zero here let's go over here again so instead of us putting zero here we just tell it to give us blank so if we hit our Enter key all we are looking forward to seeing there is just a dash so let's get back again can you see it now we now have that Dash so you can see we have both up and down arrow here right now which is beautiful so it's time for we to use it over here but how do we go about it so let's go ahead and move this one away and now bring this one to focus this is our main focus point now so remember we are using this particular chart here which is talked column chart is very important in case if you don't do it right you're going to have a lot of problems getting it right okay now we have this we want to create extra measure before we go ahead and do anything so this measure we're going to call it blank measure okay so we call this blank measure so because I'm going to have two of it I'm going to put here to differentiate this one so I'm going to start just 200 here so later after all we're going to make Dynamic and I'm going to show you why we are making Dynamic but for now it's going to work exactly how we want it okay with this one created right now what I'm going to do is to push in this blank measure inside here right so want to push it inside our Y axis go to blank measure push it in so we've pushed in our blank measure but something weird happened this particular blank measure is above we want to be below so what do we do we take this one and we push it to the up now we now have a blank measure with this particular blue um color right that's beautiful so what we are looking forward to seeing is not this particular color here instead we want to see this particular value we're about to trick the system to get that done for us let's go over to this particular format and from format we go to the data label we turn data label on so you can now see we have 200 everywhere right right that was the number we dynamically or we just defaultly created right on our own so let's open it up over here scroll down so make sure you click on this and select your blank measure this one that has the ear go down until you see value open the value up and scroll down a bit you see custom label turn it on so once you turn the custom label on that blank measure value that is 200 disappeared so we can now locate where we have our label with ER right in and push it in here click over this then click here and uh this can you see it now this is how I brought this here beautiful we have something like this here right now I told you we are not actually interested in looking at the colors but just the number here we have the dash because there is not changes here so the next thing we are going to do right now is to look at how we can actually change the color of our up and down arrow with their values all right what we need to do now is to go back here we don't have to write the next measure from the scratch all we just have to do is to make like four changes if I'm not mistaken maybe four changes here okay let's go over to our label and uh we do copy so we create a new measure we paste it in and we just make changes so I'm pasting this one here so scroll all the way up and leave the year the way it is just type in C off for conditional formatting so conditional formatting for year and now we go down down down right here so for all of these here they are not actually important we move this off we come over here we move this off we come over here we move this off then they're gone so what we're going to do right now is that if it is equal to zero that means it's blank because blank and zero is actually the same thing what we want is to give it the color and the color should be gray then if this actually greater than or equals comma here what we want right now is to give it the green color that that depicts High comma again then if this is less than one which means it's on minus so we want to give with the red color where we have a decline right and that is all we need to do so we have this I told you we just need to make four changes and that is it one two three and the measure name that we changed so we are done we don't need to do any of that again all right with this one now we have to make sure we bring this to view we can now see this this is what we're going to make change to remember you have this one named CF for conditional formating so we click over here again we scroll all the way down to where we have the value right in we are on the value area here and the value needs to have its color change ordinarily if I change this to this red color can you see it now this is what I have but I want to make it Dynamic I don't have to change it you know manually to a particular color over here that is not what it is I click on this particular FX then I go over here I go to field value then I choose what I choose my conditional formatting I click on okay now can you see it now so something weird happened we can't really see the way it is what do we need to do we need to turn off the color for this one right so what do we do we scroll back to where we have column and this is the color so all we just have to do is to make it white that matches our background whatever background you're using is what you're going to be using as a color so now you can now say this appeared so well this is how we got this created so it is time for we to actually quickly you know touch this and U take off what is not important title is gone and the value is gone then we go to the X for the X we want to take off the um title then for the value we open it up and we just ball it to show very well beautiful huh this is all you need to do that is beautiful so so do you look at this now so I believe you can create this any time any day is very very simple very simple you can create it you can increase the size if you want to show it to be some kind of M than this it's your choice but now we're showing changes so which means now between 2021 to 2022 we have increase of [Music] 745 crimes and between 2022 to 2023 here we have decrease of two or five so which is good don't want crimes so now we can now bring this over here just keep it here for the time being so what is let's create for the month for the month yes for the month so on the month we click on the line chart here so we always know time series I'm going to drag it all over here and the next thing I do is to go over here and bring in our month then quickly I bring in my total crimes for every single month I have this trend so when I have this trend all I want to show is to show something exactly like this but this time around we are not looking at showing it as numbers we're looking forward to showing this as a percentage how do we do that all right very simple what do we first first of all do we just have to create the same kind of chart sorry I did create it somewhere wrongly so I'm going to click on this one and I click over here I have the same chart created stacked column chart okay with this chart created we want to create a blank you know measure so I can just duplicate this one to be lazy new measure I paste this one over here and I type in here [Music] month so I hit my Enter key then quickly I bring this one to the Y axis I have to move it above so here is our consistent blank measure so it's actually giving us 200 so let's quickly understand everything we're going to do before we bring it to this particular part here so I'm going to stretch this here and right now we move off this particular label away from here so for this one we change it to month so we go to month so we have it as month the next thing I'm going to do right now is to actually create a particular measure here and I'm going to name that measure crime previous month so I'm going to new measure so let us name it crime previous month so over here we're going to use the calculate function to change the filter context so we go with total crimes so comma so I told you when we do same period last year it goes offset by a year but right now we're looking forward to month right so what we need to do we use a function called dates ad so the date ad is just like your same period last year is looking for what it's looking for date so we go for the date table and push in date column and right now we have a comma here so what I want you to do for me is just pay very you know huge attention to this particular tence right here to know what you need to provide for a particular Dux function to work so right now we have given it the date so we need to actually give it the interval the interval is the offset we want to actually go by so offset could be minus1 min-2 - 3 - 4 it could be in months in days it could be in year and in quarter so we cannot offset by week so if you have to do that maybe you have to customize how that looks so you have to be very extensive in writing deck okay with my comma Now I want to do minus one and it's going to ask me how do I want to go backward okay let's do month all right we have this we close shift enter we close this and if I hit my Enter key we're going to have the crime Lament I can push this one now into this so let us look at that can you see it now so if you look at here this has given us exactly what it is that we're looking for so over here now can you see this is giving you the previous one so it returns for you the same thing we have over in January because there is no what there is no month before this particular you know month of January here so it's rning that that was what we handled when we actually created this particular chart here we tried to actually exclude it you understand so let's keep working now we have this we want to look at month U over month change right so we're going to add a new measure or we can decide to use this one but let us make it very easy for you to understand let us not make it complicated so I'm going to add a new measure right now this measure is going to create my label for me just like what we have done in the other one so how do label this one for the month identify that okay the very first thing I'm going to do right now is that I want to create um month of month change and it's going to be based on percentage not based on numbers right now so we create a variable here so variable underscore um month over month change so I want to make sure I identify it as percentage so if I go with this it wouldn't allow me to use this tle what I'll do is going to be PCT for percentage I can even put underscore here to do that so over here what we're going to do right now is to actually use the divide function divide so what am I dividing I'm dividing the total crimes so here I have a total crimes and total crime is actually the overall crimes right this one here so I'm going to subtract for my numerator here I'm going to subtract it from our months previous month crime previous month here so the denominator is going to be the same thing crime previous month and this gives us percentage so you might want to try to look at this and see what it to return before we proceed into doing this further so I will do return so my return returns this particular one for me and I hit my enter key quickly I convert it into a percentage so let's do this now let's push it inside our table take a look at that now we have the percentage change here so you can see the percentage change is giv us at some as we are having 0% 0.00% that was because there is not some kind of month before January so we don't have changes right so we have where it is down the crime went down in February and uh the crime went up again in March and down in April up in May and all of that beautiful so we have this the next thing we have to do right now is that we don't want this we want to have an arrow up and down to identify those particular percentages we have right here how do we put up and down arrow over this let's do it okay now let's get back to this here we are let's move this off first I'm going to type in that variable so this variable will store in a percentage so the percentage this variable is going to store is going to be called um negative p c so negative percentage I'm going to put equals and do minus 0.0 1 so that's the negative percentage we're going to set this at minus 0.01 but before I use this I'm not going to use this immediately before I decide to use this I want you to see something first what will happen if we decide not to use this what is going to happen or what is likely to happen then I can now return this now on the return over here so we're going to do the same thing we have done previously in the previous one so we create a switch function that switches through some couple of variables so the switch function now is going to actually look at the percentage of month change month of month change so underscore month over Mon change so if it is actually equals to zero equals to zero so I'm going to put a space here to make it look clean if it is equals to zero then we want to have zero right you want to have zero and uh that zero we want to concatenate the percentage as part of it do you get that now then what we do now is underscore before we do that we need to put a comma here then underscore now for if is true if what we want is true we want to bring in this particular percentage change over here you get it now so we want to concatenate a dash to it so inside here I use my ENT and uh D Dash so we go to that we're going to go with that I'm going to put my comma shift enter underscore percentage month of month change so this time around if it is actually greater than or equals to maybe uh let's say 1% for example so what are we going to do we want to return on the percentage which is this one here but right now it's not formatted as a percentage yet we have to format it but I'm going to let you see what the result would be before we decide to do any of this right then shift enter again let's do comma shift enter so underscore now percentage here is now less than one so we do so let's find out what this is going to look like so we have something like this which means we have an error somewhere let's find out what the error is going to be like where is this error coming from okay our error is from here the problem the problem of the error now is actually from here so we just have to remove this zero so we are given two different thing so now we hit the enter key that is all we should have it solved by now I'm sorry for that so let's go back and take a look at what we have so this is what we have here right now so we can now see it as a percentage not more as value the way it is right now so because this one is actually you know there is no change here it's given us zero so we want to see Dash here instead of we have in this zero what do we do and remember we have no arrows yet what do we do to bring that in so this is fine this is beautiful because it's some kind of still formatted as value but by the time we bring in those icons up and down arrow there will be problems right so let's go over to our label for the month and right now we need toing bring in what we need to bring in the arrow and the arrow is going to be coming over here so we want to add the arrow over here so what we're going to do right now is to put in here you know two double quotation and inside it the windows full stop so we go for the arrow that give us the up Arrow scroll down we use this upward Arrow so we step out give a space we close this and we concatenate this one to it you get it so you come over here now after the condition here we type double quotes your windows full step all the dots on your keyboard downward arrow that is this we remove and uh we close we close this so if I now go ahead and hit my Enter key so this is not going to be more formatted as percentage because we have added something that looks like a text now to it let's find out can you see this is what we have now we now having trouble reading this nicely so what do we do and it's not really it's some kind of weird how do we solve all of this the first thing we're going to do to solve this now it's some kind of going back and uh now we're about to use this so over here where I have greater than equals 1 I'm going to have over there underscore negative percentage over here I'm going to have underscore the same negative percentage so let's take a look at it this time around we should have the up and down arrow to be right so you can now see we have some of the arrow going up and have some of the Arrow coming down in casee you're not looking at it very detail look at it here we have some of the arrow going up and down before we added that particular variable the arrows were not pointing at the right direction but right now we have just fixed it with this but what is left is how can we convert this to a percentage very simple let's get it so we have to wrap up this particular percentage here with what with format function with the format function we go over to the end of that particular variable we put a comma inside of quotation we tell it how we want to format this you can format this with 0.0% right then you close so make sure you put it inside of quotes both of them then you close so we do the same thing to this particular one over here so we do what we do format so we format comma inside of quotes 0.0 and we put in a percentage then we do this we close let us hit our enter key and look at what is going on right now so do you look at that now this is beautiful it has worked so so well what if you don't want it in decimal places what do you do to remove it you don't want to to see minus 6.4 just like what we have here right now or 19.4 you don't want to say it so you go back here and take off zeros so you come here do the same thing to this one so hit your Enter key then let's come here can you see it now we have this okay on this one we want the zero to disappear we don't want to keep the zero what do we do to remove the Zero from here and just have the dash let's get back over to the month label and uh this time around all I'm going to do is do some kind of copy all of this CR C to copy so after I've copied I'll go above it shift enter I'll use the if condition here I'm going to say if this one here is not blank if it is not blank then go ahead and give us this the one I copy I paste it down so what will system do let's find out don't worry about the errors we have we'll fix it so let me show you what it is so the system is going to like okay you have just used the if condition here so I'm going to check if this particular part here when I run it and uh it's not actually blank there are some values inside it so I'm going to run it so it's check this one here creates check so after checking this and it's not blank it's going to go ahead and run it we now have it back here so this particular part here is what is asking this the question that is this particular measure that has this divide here blank if is not blank then we copy it and pasted it again and like okay go ahead and run all of this for us otherwise if it is blank we give what we give a comma here shift down just go ahead and return blank so we close what we close our if it might look a lot but with this now it makes it easy remember we use the blank twice the first blank checks if this particular part of our measure or our code is not blank if it is not blank the same code we copied it and pasted it over here then go ahead and run it otherwise if it is blank return blank we don't want to see Zero or any number at all so let's find out if I hit my key right now it should actually remove that zero we have for us so we go back here can you see it now we have blank we are having Dash right now because that is what we want to show that is exactly what we have actually specified when we wrote this can you see it now if it is equal to zero remember anything blank where we are supposed to have number defaults automatically to zero that was why we use zero here so so we want to see blank so this one now returns blank or zero and we are using Dash to show it so I'm quickly I'm going to quickly go ahead and copy so when I copy this I go over here and I create a new measure what is that are we trying to do we are trying to create the conditional formatting so I paste it over here and I quickly go back up so what do we name this CF for conditional formatting let's scroll down now as we scroll down here we going to go ahead and remove all of this remove this remove this so contr Z we're going to remove from here remove it from here we remove it so what will this place give to us this place is going to give to us gray so comma this place give to us green then comma and finally this one that is negative will give us red so I hit my Enter key so we are done let's get back can we actually format this and let us look at it over here before we even go to our chart yes of course we can we can format this over here what you need to do is to click over here and okay over the format go down and you see where it say cell element on that cell element you have to make sure you choose this particular label that you want to some kind of get so we're trying to do this formatting for this and we come over here instead of Ru we choose filled and we'll come over here conditional formatting for month so we click on okay do you see that now we have something like this so how do we represent this over our L chart very important okay it is time to step up from here we have this over here we need to do it over here it's something very easy it's not difficult at all so I'm going to go ahead and remove my Legend from it and uh for the y axis the title the value for the X just the title then I ball this beautiful okay now we scroll down we look at this we turn it on so we now click over here and scroll down a little bit now what do we do we go down to where we have value we have this value right here before we make use of the value let's go down or go up a little bit and click over here and select the blank so we have chose to use the blank right now so it's giv us 200 all through we scroll down and we turn on custom label on okay that disappears which means we want to use our on custom label instead of that so I'm going to click over here now and choose what choose my label for month that is month label this one here so you can now see this place would give us nothing because there is no changes between any other amount so we are seeing the same thing we have here if I click on this now I can see all of this okay this is what we have right then the next thing now is to go ahead to our color here so we can choose the color we want but we just want to use the conditional formatting we have written then we come over here we click on field value now we go over here we choose conditional formatting for month don't use the year or else it do not work so now we have this so if you look at this now something weird happened there is something we have to do so what it is we need to make this Dynamic yes this part very Dynamic so if I start like okay I want to make it this way it disappears right that is beautiful but first of all let us try to change this into a l chart if I go to my L chart I do this and it works but it looks weird what is weird about this everything about it is weird we have two lines we are not supposed to have two lines but that is not a problem we can fix it right let us first of all fix the extra line we have here for our custom label okay fine what I'm going to do right now is to first of all go over to the data label here I choose the total crime and I'll turn it off I don't want to see for that so it's getting clear okay we have line so I'm going to go to line if I scroll down I have colors so I have two colors here if I click on this one will that solve the problem changing it to White that wouldn't solve the problem what do I do you can see stroke width I will just oh everything got disappeared what do I do okay let us bring it back to three that is beautiful okay now we have to change that let's scroll up and uh we have it here it says all so we now change the one we want to remove we click on blank measure mon we scroll down we can now can you see it's gone okay what happens to the label here the label here is at a place where we don't want it we want the label to come below and we want to make this Dynamic I told you about dynamism right let's go ahead and do it now click over here let's click over this particular blank for month so instead of us having 200 here we're going to use a function called the max X so the max X function now is looking for a table so we want to remove filter from our table we use all function so with the all function now we are removing filter from the month column in the date table we type in month make sure it's in your date table this is the month here and after that we close we hit the comma we now go ahead and bring in what the expression the expression is our total crime so remember the total crime is what we use use inside our L chart you must bring exact the exact measure you are using in your L chart so I'm going to divide this by any number not any number you have to try some couple of numbers that actually matches what you want first of all let us do 10 divide by 10 and now we close if I hit my enter let's see the behavior of this over our chart okay let's wait for it is working on it okay A bit down I love it it has worked but not so well so I'm going to change it to 20 so let's see what 20 will give us so 20 moves sit down a little bit and this is what we're looking forward to seeing so we have pushed it down you can now see why I said we're going to make a dynamic so we'll do the same thing today uh ear chart as well but for now we are done with this and this is beautiful okay this is what it is that we want I don't want to see the title I hate seen title I want to customize and put my title at the place I really cherish all right everyone this is what you do to create this particular part of our dashboard if you look at it over here this is what we have just like what we have done is no difference very simple and easy can you see that all right everyone if we look at this particular chart here it looks some kind of ordinary yes of course it's not really complicated but we have to do something we have a column that actually recorded a time which every one of the crime with some kind of you know being recorded at so we want to group them maybe you can decide to group by three hours 2 hours 4 hours or maybe 6 hours is all your choice so over here now you can see between this particular 12 a.m. to 2:59 a.m. we have 514 crime recorded then if you come down here is between 6: p.m. to so 8 you know 59 p.m. so we have this scme recorded you can see it now so how do we create a group like this ordinarily if we don't create a group like this so this is what we're going to end up having let's take a look at how it's going to go if I come over here now we have this particular part that records the time of every single crime for us so going back here now I would prefer to add a new page so on the new page let's go over to our data and take the crime time uh for the crime time you can see it here so how can we make sense with this is what we're looking for to getting done so now you can see so if I try to put it on the table it's going to be a very long table can you see it now a very long table so how can we make it some kind of much more better than this so what we need to do right now is to create some kind of grouping and to do that we need to add extra column to our main data which is this table here so what do we do next we create a new column on this new column we're going to call it um time group so for the time group we're going to use a function called switch so we use switch through you know what it is switch through here so with the switch through we are going to use this particular m o function and the our function as well so first of all let us pay attention to the intelligence it's very important for we to actually know what is going on here first what we need to provide before this works is number and again we need to divide it this is this is the divisor so we're going to divide it by 24 hours right before we get that so right now over here is asking for the number the number is supposed to be the column where you have your time recorded but we're going to tell it that okay this is actually in hours we use the hour function so now for the r function now the r function is looking for what we're trying to actually convert that column into ours we actually bring in the what crime Crime Time okay here is our crime time so with our crime time here now we close it and if you pay attention to what we have here now this is the divisor so we need to actually divide it by 24 right so now we just place a comma so we add in here 24 so once we are done with that we can now go ahead and have it closed and I'll say okay if this we have here right now is equals to [Music] zero and so we are supposed to copy what we have written at the left hand corner and paste it here but because we are actually learning we shouldn't copy that all we have to do right now is to overdo it again like do it this mod function and it's asking for number we're going to provide the hour and the crime time over here the crime time so now once we have the crime time here what is next so the next thing is for we to close and we go by the divisor here we divide it by 2 and four hours you get it now so we can now close it so the first one we say if it is actually some kind of equals okay I think it should be greater than or equal to so greater than or equal to zero and the same thing is actually going to be less than three so what do we want to do we're going to group this particular time into what into 12 a.m. that is going to be inside of quotes we do 12 make sure you format it properly this is 12 12 sharp so we do am 12 a.m. so we put Dash between so we do what we do 259 p.m. that is almost three right that's sorry 2:59 a.m. as well so we do this 59 so we give it space we still do a again so we now go ahead and close this and place the cover so we're going to be doing this severally and this is what we're going to be repeating all we just have to change is where it is less than or equal to greater than whatever that is all we need to do so all we have to do right now is just to copy this exact number and uh duplicate it so now we paste it in so there is a shortcut for we to do this a very you know instead of you to do copy and paste you can just go ahead and use that particular shortcut to do this so the shortcut to do this now is actually shift alt down can you see now it's actually a quick way to actually have whatever you want to duplicate duplicate it I'm going to control Z again so if I contr Z I want to show you again how to do it so now place a CER anywhere around here shift Alt can you see it now anywhere your cursor is will definitely work for you you just have to learn that anywhere at all it could be at the end in the middle of it all you have to do is to make sure you actually use shift and the ALT key to actually get it duplicated so we want to duplicate into eight places so shift alt 2 3 4 5 6 7 and finally we have the eight okay once we have done this now so on this particular second one here we want to say here we are using you know zero so we're going to put it three here three and over here we're going to do six so here we also have though if it is less than or if is less than or equals to three here now that is what we do so over here now we come we have to put comma over this particular place comma so over here we come we do six so we do six and we do comma so when we do six now so we now say if this one is actually you know greater than or equal to three and this particular part of the code is actually less than or equal to 6 then what we want to do right now is going to be 3:00 a.m. so here is going to be 3:00 a.m. so 3 a.m. and we come over here so it's going to be 3:00 a.m. to 559 so we just change this 2 to 5 59 that's is cool right then we come over here again instead of us to have zero here now we're going to have six here so it's just that it's just that we carrying the six we have over here back to this particular place right now right then we come over to this particular part then we do nine so you get it now so we do nine so when we do nine this will now be six so this will now be 6 a.m. to what so we have to put comma here so 600 a.m. to 859 just change this one it's very simple so we do 859 I'm going to take it very slow for you to understand exactly what is going on right here it's very very simple so if I have done that now we did six there that is here we have zero we have three we have the three here we have six we have the six here now we have nine we're going to have the nine over here again you get it now so we have the nine over here so when we have the nine over there what do we do we're now going to come over here and say if this one is greater than or less than or equal to 12 right 12 the what we going to do right now we're going to do 900 a.m. so we do 9 a.m. let's still come here we do 9:00 a.m. to what so we're going to be doing 9 a.m. to 11:59 11:59 you get it that is beautiful so after that now we going to do 12 here we do 12 over here then when we do 12 over there we come over here and what we do here now is doing 15 when we do 15 that is going to be over here comma so comma so it's going to be 12:00 p.m. now instead of am we do PM so it's going to be 12 P.M to what to 2:59 p.m. okay sorry my cops lock is on so 2:59 p.m. so we're going to change this one as well 259 p.m. so over here we're going to do 15 so remember it's easy the 15 we have here we bring over here again so when we bring in 15 over there here we're going to do 18 so we do 18 and we put our comma right here so that's going to be three instead of 12 so we're going to do three: like three dots to what to 559 so we do over here we do 559 PM notm again we take off the air and put P for p right so after 15 now we're going to do 18 to carrying off this 18 over here we do 18 then from 18 now we come back right here so what we're going to do now we for we to do 21 and over here we do six so we need to put a come over here so we're doing 6 p.m. to what to 859 p.m. so we have e here so we need to format this and make sure we are using p p here so we correct this it's going to be p and finally we do PM for this one as well so we do PM for that then finally here the 21 oh sorry here was supposed to be 21 right let didn't do that 21 and we have over here we have the same 21 over here 21 and now this is going to be 24 so which is going to give us 900 p.m. to 11:59 p.m. so if I put my comma here now this is going to be 900 p.m. to 11:59 p.m. you get it now it's just to hit shift enter key and that we close for our switch function so before we finally go over I can hit my enter key right now to let's see if this is actually correct then I go further to explain exactly what it is that we have done right here for you guys okay it seems we have an error here and the error should be this one here we don't have something that with go is going to come later we not need to have a comma here so I've taken it off and I hit my enter key and let's see if this corrects it for us beautiful we have it rightly done so what have we done right here let's see what it is that we have done let's explain exactly what it is right here okay first of all we made use of the switch through function what is the functionality of the switch through function so the switch function or the switch statement evaluates conditions in order until it finds a true condition right so in this case it always evaluates to true and this particular first one needs to evaluate to what needs to evaluate to true so if that is true it's going to go ahead and like okay this is true it's going to actually Accord to e The Hour That is peculiar to this particular time we have right here that is how the switch function actually works okay the condition we have here are based on hours so part of our crime table right this here so I went ahead and use this particular M mod this particular function over here and we try to actually say okay you know what when you ask us for the number or value we want to convert it using this uh hours here which tells that okay this particular column is based on ours and so the mod is actually is a function to ensure the m o here is a function that ensure that the hour is written in 24 hours Circle that is why we actually use this particular hour right here so for each condition so this particular switch function checks if the hours Falls within a specific range that we have specified so if it is true is going to assign to it a corresponding time group label just like what we have have right here right now that is what it does so we are trying to do 3 hours interval you can do 2 hours interval you can do 4 hours interval it depends on what you really want to specify you know on it so once we have done this now let's go back to here and now I'm going to click over here okay let's duplicate this I'm going to move this duplicate over to this part let's find out what the difference is going to look like if I now get my um time and I bring in my grouped one instead of bringing that or have having that there grouped one here can you see it now the time is now grouped between 12 a.m. to 559 a.m. so we have 638 crimes so you can now see what it is all right let's get back to our main work over here so it is time for we to create it's a beautiful chart that is going to actually help us to some kind of visualize that particular you know column we just added and let's move this one up we don't need to keep this one so let's get a table oh instead of a table let's go ahead with a chart like this so this chart is not the ideal chart to be used but to those of you who are just coming up I want to show you why we shouldn't use this chat and why you should use the other chat okay now we go to a crime data we locate the time group then we go to here we bring in our total crimes so we have this so if I try to make this smaller to actually take this pie now there is no way it's going to work what do I do so before this can actually show up very well I have to stretch it can you see so it's going to take up a lot of space to avoid this what we need to do now is to flip it but do we have to flip this you call it flipping let's see if you're going to flip it now let us change the chart type to something like this we are going to use this very chart called close out BR chart it's an ideal chart to be used so we have something like this so when I even try to some kind of make it small can you see this particular part here the group The Time group is some kind of distorted that is not cool at all what do we do there is a solution for that so let me just first of all show everything so our time is not showing up we go over here now we click on x axis here we go over to this title we turn it off then totally we turn the value off now to the Y axis we turn off the title of the y- axis that make it a bit better but it's not even some kind of visible right okay what do we do do we have the turn of the value no that is not the ideal so what we are going to do right now you can see where it says Max maximum width the maximum width is something you have to work on this maximum weight so we have to push this slide to this part here to the end that is what we need to do so what am I going to do I'm going to have to some kind of remove it move it can you see it now it shows up very well so which means what I will try to some kind of make this smaller before it gets started you must have done it you know in a way you're not supposed to so why would you have to do that so let's do this all right this is nice I love this I can bring this over here now can you see it takes this space and I love it but I don't have to show my label of course so sorry my title I have to done it off we're going to create hours later after day so we have this s is taking shape we're about to create a very beautiful report or dashboard depends on what you want to call it all right I want you to cast your focus over this particular middle part here you get it so if if you cast Focus over there what do you see you see something very simple and easy but do you know how to do it if you don't know how to do it just wait and let me show you okay first of all what are we trying to portray over here so we trans crime over the uh weekday and as well the month so this chart above here this very one here here that has label on it is actually showing month so it's showing month while this particular one over here is actually showing the should I call it trend of what of the weekday that is what it is that is showing so this particular one in the middle this one here this boxes so we call them heat map or we call it heat map so it's cool so let's see clearly what is going on here if you look at this particular month here this month has the highest crime ever so we can see the week it falls to it falls to Sunday here it falls to Monday Monday has the highest one as well so if we come down here Friday has under record of high crime so if you come over here now you can see the week is Sunday you can see the number you can see this one you can see Friday here as well so not that other weekday did not contribute or other month not contribute but those are the very month and the Very week days that crime is at a very high rate you understand so what helps us to see and understand all of this is the way we actually presented the data so it's very easy for you to like use your eye to look at this box here so use your eye to look at this very box here this one here this this this this and make conclusion like okay this is the month so if we did not add something like this we added this particular part of it to actually support us reading what is going on out of that so you want to know how to create this let's find out quickly all right we are back here the very first thing we're going to do now is to actually add this particular Matrix I've done this in my previous class it's very simple and easy it's not something complicated so you don't think of like how am I going to do this so adjusted to your satisfaction so I'm going to go with this so once I have this the very first thing I'm going to put right now is actually adding my month and as well adding my wig day very very important how do we do that so let us open here so once we have here on the row what goes to the row is actually the weekday but excuse me do we have the weekday let's verify over here we do not have the weekday so which means we have to add a new column to our date table let's get it so get to the data View and quickly let's locate our date table so inside our dat table I'm going to add a comma here and uh I'm going to add here we there so put a comma so use the format function and we bring in the date so here is our date column comma and we do d d d for the short format then we close so we're going to add one more column this column is going to actually add week number for us for we to sort it if you look at the behavior for a month something crazy happened so it was not sorted the way it should be so we might end up having Friday to be the start of the week or even Monday but how crazy is that let's do it so we can do week n for week number we close and we use the weekd function here so we dat function so bring in our date and quickly we close and sorry just hit enter key all right we have both of them right now the next thing we have to do is to make sure we sort this particular column to sort it now we come over here make sure you are on column tools and come over here and click on sort by wig number so it has been sorted for us we have to go all the way back to data view here we are and now bring in to the row open up this and uh wig day beautiful we have it here the next one is to the column we bring in month month so you have something like this so to the value area what do we bring in we bring in the total crimes that we have this is it so quickly we have to do some formatting the first formatting we want to do is to remove move all the totals so we are left with this so we can go to the value now and increase the value or we can change the font type here to something like this more bold but we have to concern ourself about the font of this particular values because we're not even trying to show the values okay but one thing I love to do is just to increase this so increase this helps me to some kind of expand or stretch the boxes right so I can put it on 14 for example let's do four thing but mind you we are not going to use it okay the next thing we do is to go over and some kind of adjust the header so we have can see the column header so in the column header let's use this so on the road header here let's do the same thing to the r header so look at this would show very well now if you now look at it now we don't want to show our weekday how do we get off our weekday there is not a place here under the format to turn the weekday off what can we do very simple come over here and double click and just put a dot then hit the enter key that gets off the weekday for you that is the only trick you can do that doesn't change your main column name your column name remains what it is but over here we decide to get it up by putting dot right there you get it now so once we have gotten this done what is next it is time for we to affect the color we have right here what do we do okay before we affect the color there is something I want you to know I have been showing this over and over again to all my subscribers and I have to show it to you again that is you trying to understand the kind of of color you should use if you have problem with colors so all you need to do is to actually look for a site that can guide you through I'm going to use Adobe color adobe. color here so you can see here color. adobe.com so click on it so we can go over here and get any color of our choice that we call with but I have the color I want to use in mind I just want to show you how to get this done on your own personal project is very very important so once you are here click on the explore so under explore you going to see those colors here those are the default colors Adobe will just show you so the next thing you have to do right now is to come over here to type any color you have in mind let's say you want to use something like blue and uh put comma red so those two colors when you hit them up what the site is going to do for you is to actually mix other colors that correlate with it can you see it now so we might want to go with something like this or still when we go down we can look at different colors that are available that we can choose from so can you look at it let's go with this one here so do you look at this now all of those colors we have right here right now they all go together but you don't have to use all of them just pick the actual one you want and disappear so if I were you now I might choose this particular color go for this go for this three colors I'll run away I'm just kidding okay you have this right so just make use of this but I'm going to give you the color to use if you need that so we are back right here the next thing we're going to do right now is for you to come here and go to sell element then you click on it quickly you scroll down and you have this particular font color when you click on it this is what you have right all right we want to customize this based on our own way of you know whatever so I'm going to click on this particular effect here and once I've done that I'll come under the high value here I'll click and I'll click on more so I told you I have my color already so my color code is going to be something you can type in DF 47 47 and 6 e okay five let's do 5e so this is the kind of red color I want so once you have gotten this done the next thing you have to do right now is to actually get the color for the low PA of it so the low for the low value we come over here we can now do what we can now do six 7 A1 a e so we have this you can copy it in case you want to use it again not even in case we're going to use it I'm going to click on okay so this is what I have right now it's not beautiful what do we do we come down to what we come down to our font color so once we get to our font color what do we do we have to make sure we change our font color to the same color we have used for the background ground so we click on the FX right so once we click on the FX over here we just need to paste in the one we've copied before this is the same color so we come over here now we need to actually give it the actual color for the red so I'm going to use D what df4 75 then e make sure it's the actual one otherwise your figures will show up go ahead and click on the okay so do you look at it now we make it disappeared this is exactly what we want so let's go to the top so here we have the grd line so we can turn it on so that you know open up it again so leave it at one for this color let's leave it at this color for now we're going to change the color later after all then once we have done this so now we come to the value let's put the value at 13 this is actually nice so okay 14 still cool let's do 14 so once we have done this now we can now do this okay you can move it down for the time being we're going to make no adjustment after all but right now we call this so let us go for this particular you know close T bar chart here so we get it and quickly we bring in our months so our value here we go to the date table so we bring in the month so once we have the month over here we can make it this way so quickly let's go over here now and all we have to do is just to make sure we go over to the X and the value we want to turn it off we don't want it so we go over to the Y so we want to turn off the Y the typ on the on the Y X uh Y axis we turn out everything off we have this so we can make it a bit smaller and make sure you decided to actually match exactly what you have here so for the x axis we have not turned off the title or the yeah sure now it's turned off can you look at it now so you want to make sure you stretch it until you have something that looks just like what you're looking for the same so for this one here we bring it down a bit so let's go so if I hover over here I should be seeing October if I H over here should be seen January so make sure you set it that way you get it now so we need to add one more all I have to do is just get this one copy and paste so I'm going to change it to this particular close bar chart so we have this I'm going to stretch it so once I've stretched it I'm going to come over here and change from month to week day so we have this right so all I have to do is just to oh sorry I targeted the wrong place so we just make it this way so stretch it a little bit to Fifth and stretch this particular one to match this okay so we can just do this so once we are done with this now the next thing is for we to do the same thing we have done for our colors very very important so we need to change the colors we have right here to match the color we need to use right so it's going to match the men color for dashboard so what do we do we click over to the what to the bar and we choose the FX then quickly we go to the highest value so we click here we want to make sure we are using the same color don't forget it I have what I have the of what the f475 475 and E this is the color very important you should know that so we just want to give this one the actual color that is peculiar through with so we're going to use the same color we have used previously and that's is going to be 67 67 A1 AE this is what it is right then we close that gives you this right so all over here I might want to have you know middle color just to make sure things becomes cool right here so what I just have to do right now you don't have to remove it click back on it and uh add this middle color and for the middle color you choose the same thing again so we're going to go with what 67 67 A1 a e again same color now that reduces the for me okay once I've done this now the next thing I'm going to do is to go over over to Legend and T of the legend and I have it right so let's click on this one and quickly go over here now and we do the same thing choose the same color then we go over to this one and uh choose more color so we do six what or 67 A1 a e again then we have this done then if I click on this now so we're going to have something like this so if we're looking forward to Mark just to one aspect of it all we have to do is to actually make sure we use a middle point to do that and I want to use the middle point just like this one here so do this and come over here and uh do 67 A1 a e the same color then you go ahead and do it is so I'm just marking what I'm just marking the actual highest point this is the one here and over here I'm going to come and turn up the left legent and I have something as beautiful as this this is what we have done to have something like this created and you have just seen it can you just see that we have just replicated what we have here and beautiful it works so we are almost there we have one more chart to create and that is going to be this one and we get into this one and show you how to get this one done in case you're like how did you do this it's very simple and easy I'm going to show you how to get this particular paper you know a teered paper kind of this into PBI but it's very simple let's do it

Channel: Data with Decision

Views: 11,199

Keywords: data with decision, excel dashboard in nigeria, data analyst in nigeria, excel in nigeria, Power bi, power bi report, intro to power bi dashboard, dashboard design, microsoft excel and power bi, advanced power bi report and dashboard, create power bi report and dashboard, how to create Power bi report, dax, Power query, intro to dax language, data transformation in power bi

Id: c8S17REk-IA

Length: 114min 28sec (6868 seconds)

Published: Thu Dec 21 2023