2024 Power BI Essentials: Crafting Cutting-Edge Transportation Reports

hi everyone welcome to my YouTube channel today I'm going to show you how to create this particular public or Transportation report from the scratch okay to get started let us look at different backgrounds we have right here so if I click here so we have the same thing we have just seen in a different color which is the brighter background and we have this very one here which you might be very much interested in yes I'm talking about this right here so we can take a look at everything we have right here and how we can actually get this built from the scratch for you to understand exactly how to create a report for public transportation in order for them to actually make accurate decision okay before we start the creation we need to know what are involv in building this let's get it you can go ahead and read about the data set we have used for this particular report and after that we can actually take a look at what it is that we are going to cover in in this particular project to actually create the report we are looking forward to creating okay we're going to look at the background we're going to be creating to actually make our work look much more beautiful you know that is very very important and this is very bright version of the previous background we have just looked at and we have this particular other one right here which we going to create from the scratch using PowerPoint to actually get everything very very nice and looking beautiful okay now this is the white version of what you have done seen previously so if you're ready I'm ready I'm going to show you how to create everything from the scratch so if you have not hit the Subscribe button yet go ahead and do that and let's do it all right everyone let's get started so when you click on get data those are data sources available so if you can see which one you're looking for click on more so it LS you here you can actually search from those particular groups we have here so I'm going to go to all and the one I'm looking for is actually Exel workbook or the CSV version of it the text file so if I have a text file data s and I mystically click on Excel workbook and I navigate myself straight up to where I have it right in so here is it on your folder as you can see it's blank so do we have to go back and correct that I guess no so what can you do look at this part here just go ahead and click on this drop down then click on all files there you have it you have all the CSV that you have but unfortunately we can't grab all those data all at the same time so we have to do it one after the other so here comes our first table then what we can do now is just to go ahead and load then when we load this it's going to load straight up to puia environment which we don't want for now so we just want to make a check even though if we are sure that our data is okay let's go on transform data all right here we go we have our first table right in you might not have the same view as I have right now so what is all about this like we have valid we have error we have empty this is just to check you know if the roles in these particular columns are okay so 100% valid means no empty no error so everything is intact so how do you turn that on or off so when you come here you can see you can turn it on so the column quality I have turned on column profile here right there is actually going to give you some kind of distribution of how many of the if you look at it we have two be the highest one and all of that then once we are very much cited with that we can actually uncheck it and now we need to go get more tables so you don't have to go back to powerbi again all you have to do is to click on new s then we know this Source already is text or CSV so we pick the second one click on okay all right we have all the data we want to use right here so the very first thing we have to do is to identify which one is our fact table and which one is our look up or dimensional table right so this particular first one we have right now is actually part of the lookup table or the dimensional table the question is this how do you know we have unique list of what of this particular data no repetition specifically when we actually look at this particular you know column we have right here it is actually straight up to from 1 to 10 there is no repetition of either 1 2 or three so for that this actually marks this particular table as a lookup or dimensional table then I'm going to actually have it renamed over here so have this removed and I'm going to use look up so I'm going to use underscore for that okay if I go to my writer ship right here now this seems to be what I'm talking about here is actually our um that is our fact table because there are some kind of repeative of some idas right in here so this is where our transaction is being recorded at you get it right now so we have more rows in the fact table than we have in that of the look up or dimensional table you get it so just go ahead and study what is the difference between fact table and the dimensional table for you to really understand how to deal with them okay now that we have known what can we do we can actually go ahead and remove this modify P of it here and we go straight up and actually use the name fact table so I love is underscore then I do the same thing to others so this one here is going to be my lookup table then this one the last one here with my lookup table all right we have all the tables being you know arranged based on their names and stuff like that and we have identified which one is our F table and our look up table the very first thing we have to do is to go to home and right here you see close and apply go ahead and click on it right guys we have all the data we need for this project right here under the data um view so we can actually take a look at our data right here and for relationship we have it done right here and this this particular one here is actually where we're going to have to actually create our chart that is the conference all right let's get it the very first thing we have to do after all is actually go to the model view then I'm going to actually have to arrange this nicely the way it should be so I'll let's leave the fact table below so if I actually click on this now you can see boss ID to boss ID the that is the writer ID to wrer ID and over here we have the route ID to Route ID this is exactly what it is that we are looking forward to seeing but we have one more table to actually bring in here and that table is not a table that we have in our our file it's a table we can create that is the date table and one other extra table is actually the uh helper table so I'm going to love to show you I've shown that how to use you know all ducks or do it without writing the code but I'm going to show you this one now inside par and how to go about it so what I'm going to do right now is to go over to new source and I'm going to click on blank query so with my blank query right here what I'm going to do right now I we'll just go ahead and actually just give it one and uh hit enter so I have just one here so this is not a table yet what can I do I'll just go ahead and say convert to table over here to table so it has been converted to table with one single row then what I need to do is to actually go ahead and call this calculate or calculation so this is where we gathered all our measures that we're going to use in this project so once we are done just go ahead and close and apply all right as you can see we have this particular table here it has no connection to any of those table so you can call it disconnected table all right as I said I told you we have one more table to bring in but we're going to create it ourself so just go to this particular table view this is where you take a look at all the rows and columns inside the tables you brought in so I'm going to click on new table so if you have not actually subscribed to my YouTube channel before now just go ahead and hit the Subscribe button leave a like and leave a comment let me know what you feel about my content all right I'm just going to maximize this I'm going to call it dat so for the dates I'm going to use calendar Auto to actually generate my date so with calendar Auto is going to actually scan our table and return this but I think it return something very huge how can we control this okay how do we know if that is what it is so I'm going to actually use the art columns so using the art columns I'm going to come over here with my comma then I'll go forward let me just minimize this a bit so add the ear and extract the Year date so this data I have right here with square bracket is actually this particular date column right here so this one here is a function while this one in with square is actually uh the main column we're looking for so go ahead and actually close then close this and hit your Enter key now if you look at it we have 1904 as the uh as part of our date so I don't know where this is coming from we have lots of this right here so if you you encounter the same problem in in in this particular project or any other project there is a solution to this so but let us go ahead and verify our date column from the fact table so we are here in the fact table and as you can see we have just two years of data so we have 2013 and uh sorry 2023 and 2024 so I don't know where that this is coming from so how can we control it quickly we can control it using a variable and tell it to actually go ahead and actually map this particular column right here called it inside fact table writer ship let's get it so we are here the very first thing we have to do is to actually go ahead and actually add a variable here so this variable I'm going to call it Max date so I love to use underscore when wri in my variable is very easy for me to reference so what I'm going to do right here I'm going to actually extract the year and the max Year from what from date inside my fax table here then we go ahead and close it and we do the same thing for the uh M date so mean date equals year so now mean and uh when I type in date I go ahead and grab this one here I closed it all right so the next thing right now we need to return this so we return once I have it return what I'm going to do next I'm going to go after my ADD column here I want to filter the calendar Auto that is what it is so let me make you understand something right here I'm going to take it very very slow so what I'm going to do right now is this over here I have the first variable that will go into this particular table right here here and extract the maxed the max here and that is going to be 2024 then this particular one goes into the same table and extract the minan year which is 2023 so we're going to use that to filter our calendar Auto our calendar Auto actually actually generated this particular one so for that we need to actually filter the calendar Auto so you go after your add column so we write our first you know code right here I weite a filter here I'll come below it and we actually tell it how we want to filter our calendar Auto let's get it so filter surround the filter around this calendar Auto so what we can do right now we can say okay the year so the year function so we bring in the date from where the date from our calendar Auto then once you bring in the date from your calendar Auto what you're going to do is for you to close it and say where it is greater and equal to the mean so use underscore so mean that so you use the ENT and so we type the here so we bring in the date the date is this one here so we close it so this time around this way it is less less than and equal to underscore Max the max did so we have it already the next thing is for we to close our filter and we hit our comma so if you look at it now this is not in any way readable to me all I'm going to do now is actually go ahead and move everything once you highlight it your top key will have it moved moved for you this is beautiful right so now if I go ahead and hit my Enter key let's see what we're going to have so can you see it now it has control what we have here we just have two years of data this is actually extracted from our fact table due to the um the variable we have used to control it and to point it to the exact place where we want to pick something from you get it right now okay we just need one or two columns so okay I think we need two columns the next column we need right now is column for the month so month inside here month name so we can do format we bring in the date then we add a comma here the format should be the shter form then we go ahead and close it it so we need to actually get a month number that will help us to actually you know suck this month the way it should be but we don't have much month I think we have just two months of data right here for the both years but we can still do that so month and the next thing now is to use the main month function here so the date and uh next is for we to actually get our weekday so for the weekday is not a different thing so we call it week day sorry for that so what we need to do right now is to use the format again for the format we use our date we put a comma we use a shorter form so now our weekday is not sorted we need to sort our weekday finally this is the last one we're going to write here so we do uh Weck num so comma so we use the wgd function and we'll bring in the date here so we close it so over here is month I'm going to change it to month number so month num for month number that is beautiful so this those are all the columns we want to add you know for now so we have all the columns right here okay this is what it is so let us go ahead and test this out and see how it's going to look right so the very first thing I'm going to do is to come over here and I'm going to bring in my dat so whenever you create a date table this is what you see once you drag it in here so it's still actually using the default dates inside pop to actually show you this grouping it for you but we don't actually want this what can we do we have one option the option is actually come telling Pia that you know what we have our the table right now can you go ahead and deactivate the one pia has for us because we don't want that then go over to the table View and click on this go over to this particular table tools you're going to see Mark as the table click on that and just go over here you need to validate this particular table so select column choose date and validated successfully then go ahead and click on the okay so let's quickly go back to Dev View and look at what we're going to have right now so there are changes this is what it is this is what we want exactly right all right the next thing we're going to do right now is I'm going to take this off if I bring in my weekday you can see we have it starting from Friday so for that we don't have to go back there you can just click on the weekday you have to some kind of sort right so I'm going to close this for you to look at what I'm doing so now go over to sort by and sort by the Weg number so quickly this is going to be changing from Friday and give you the right one now from Sunday to Saturday that is beautiful so the next thing is for you to click on your month and do the same thing to it so you sort by the month number so this is beautiful everything we need has been done the next thing is for we to go ahead and check our relationship if it is not linked for us automatically we need to work on it all right here is our dat table it has not been linked for the right table right here so what I'm going to do right now let me shift things around now it's for me to actually connect this one to my dates so we have just established the right connection which is called dat to dat beautiful so it is time for we to start writing our first duck and let's see where we're going to start from all right if you look at this particular dashboard right here there is something I want you to look at so if you are working in this company and you want to make decision on how to utilize the resources you have what do you do so over here we have the busiest route so we have the less least busy route right so how do you extract it out of this data it's very important you need to do that so we can do this with that and we can do this with normal filter but I prefer showing you how to use Dax to do this for we to learn it together otherwise we can just use you know my filter to do with so let's see how we can use Dax to actually extract this and this one from our data because it gives us that particular idea of like okay I think this route is more profitable let's put in some balles okay let's get it before we go ahead and do that we made a mistake while naming our tables so this one here is not look up modified instead we're going to name it to lookup route here is the route I think is spell as okay here we go nicely done so we have changed his name so the very first thing we're going to do right now is to create our first measure right here so new measure so this new measure I'm going to call it [Music] um so we call it busiest route so what we're going to do is to actually use variable to actually store in a virtual table we're going to use I'm going to display the table to show you what inside that table and show you how to use that table to filter and get the bigest route okay now we have our variable here I'm going to call this this variable to be some kind of total [Music] underscore so as you can see we have this particular thing here it might look some kind of weird so maybe your question will be like why are we using underscore in every aspect of it why don't we just use space is or we use Dash it wouldn't work so if you want to make it readable this is what you can do right so what happens if we use something like Dash over here instead of underscore you see that underline who tells you no I don't take this so for that if I use space let's see this is not really cool so for that what we can do is just strictly go ahead and use underscore or we actually write them together so for you we to make it readable we can do this all right right now we have gotten this done the next thing is for we to look at what we're going to store inside this variable so I'm going to use use the function called summarize so we're trying to summarize a table right so this table will be um the lookup route so comma inside this particular table so we have have the route name so type in the name here is the route name so I'm going to actually put a comma here so with this comma we want to actually create a column so this column will be called inside here tar writers or tar passengers anyone you want tar passengers here so for this two just we're going to use the sum function comma sum function the sum function is going to actually aggregate from our fact table for us we don't need any related you know function no this is going to strictly work with this let's do number so what have we done right here we try as much as we could to actually create and store in the total writers or we have or people who had actually you know um Transit with us inside this particular two top passengers right so what are we trying to do let's go ahead and actually close this for now I wouldn't return this because if I try to return this look at what it's going to say so I'm going to just type in return returning this now is for me to return underscore total passengers I told you the expression refers to multiple columns multiple Colum columns cannot be converted to a scalar value so but let's see what this look like in real life so I'm going to go over to table view we go here new table so if I paste this over here this is what we have and I hit my Enter key so this gives us this it helps us to summarize for every single r out how many passengers we have can you see down to the least one over here so now that we have gotten this now we're going to go back to our measure which is this one and instead of us returning this particular one right here we wouldn't return anything right now I'm going to take it off so what I'm going to do is to actually create a new variable so this one is going to be called top route underscore top routes so what I'm going to do is to use my top end function to extract the top from it so for the top end I'm going to be using top one and um don't forget our new column we added I'm going to show you that column after all so the new column is this particular one maybe you didn't take note when I actually show showed you in a table format but right now that is what it is so if I type in here total yeah that is the total passengers right here for the total passenger is going to be let me make you understand I want this to be some kind of very simple to you okay now when I put my comma let us look at what this is looking for so it's actually seeing top end the N value is actually one we are trying to extract one from the list now it's asking for a table the table is what I've shown you and that is the first variable we created underscore this variable here right then after that we put a comma again this comma is asking for how do you want us to group this so you are grouping this using the column that actually contains the value then we go down and we do total so doing this total passengers here is not actually a measure sitting outside of this particular variable instead is the one we created here that is this very one this line okay now that we have gotten this done we put a comma is asking us how do we want to actually descending or ascending going to do descending then we go ahead and close this so it is time for we to return so we return so what do we return right now what the turn we use the max function or the max X function inside the max X function we actually use for the max X function is asking for table our table would be our underscore to T out and we actually going to put a comma here and for the expression is going to be our lookup routes you get it now so look up route name that is the name we're trying to return name not number I'm going to show you how to return number if you want to return number but here we're trying to return name so if I type in name here I locate this and this is what we need so we go ahead and close this so this look very simple not complicated hit the enter key as you can see there is no more error so before we actually look at the result we have right now let's go back and take a look at what we have right here for we to understand understand exactly what it is that is the um what heck is it busiest busiest busiest route here we go so if you look at this particular part here is this is exactly what you're seeing over here where I have my csor hovering over so we try as much as we could to sum the total perer for each route and let us go ahead and take a look at the result we have let us display it on the card so the busiest route is this can you see it now very simple when I say very simple I mean very very simple so what next can we do next one we can do right now is to get the least busy route and that is not going to take us any time all we have to do is to actually recycle through the previous one so I'm going to contr a contr c to copy and we add a new measure so just paste this one here and make a little changes so over here list B route then instead of us to use descending here we use ascending so the question goes do we have to change this marks to mean it doesn't really mean doesn't matter if you change it to mean it works if you leave it at Max it works so what actually happens now is that if we are returning more than one that is when you can actually think of actually you know changing this from mean to Max but for now we can leave it do that way this particular part controls what we see you get it so right now let us hit the enter key and go ahead and look at the result we have and compare it to the previous one so we have a different result entirely so here is the result we have so the busiest route and the least busy route is this one right here but and just make sure let's put this on 20 oh we can use 25 that is beautiful so we just do this so for now those are going to be here all right if you look at this particular part of the class I'm talking about this one here we trying to look at you know our group of Time how many riders or passengers we actually have at that time for to identify you know the time we should actually concentrate more on utilizing our resources in our company okay how do we actually group this because we have a particular column that actually takes all the time that we have actually transacted you know uh or rendered our services okay let's take a look at that and see how we can actually have this done over here inside our fact writer ship we have this particular column that has to do with time right then what we can do right now is to actually add a new column so you can actually click here to add a new column or you can right click over here and go over to where it says new column and new column would automatically be added for you so with this new column here what we're going to do right now is to actually go ahead and create time group so for the time group we actually store something inside a variable so this variable we're going to call it [Music] um time okay let's just call it time so with my equals here so what we're going to do right now is to use this particular mod function and uh we use the our function as well I'm going to explain everything after all and we actually move in our time so if I type in time it's going to come from my fact wrers ship so I will go ahead and close by 24 hours so we divide it by 24 hours right then go ahead and close it so once we have done this what can we do if I have this return what will be the you know end result I'm going to have return this now return time so this gives us in numbers this is not actually what we want we don't want numbers to be returned instead what we want is actually the interval of time right so what we're going to do in this instance under where we have the return so we're going to move this away for now so we're going to use the switch through that is using true and false when this is true return this otherwise return that so switch true comma and the first we're going to do right now is to actually go ahead and reference our variable which is this one time so with that if is less than three so we put a comma here so which means if this particular number we have returned here is less than three what do you want so we want to actually create a group and say you know what this is going to be 12 a.m. to 2:59 a.m. that is going to be inside of B quot 12 this is going to be amm so we close oh sorry we actually give it Dash and we give it space and it's going to be to 59 a then what we do next we have this closed and we switch to the next one though go ahead and do this so what I can do now is to make it very simple I can just go ahead and copy this so once I copy this now you can actually have it pasted okay the underline you're seeing as an error is as a result of we not having comma all the places we need to have commas right in so now we have the commas right okay with this now what we can do is to actually say okay fine we want to actually change this this one now is going to be six so we turn this to six and this one will be three so instead of 12 we use three and this one is going to be 559 559 so this one right here will be nine so once we turn this to nine sorry that is going to be this one here will be nine and this is going to be six we gr this one from six what 6 to 859 so this one here will be [Music] 12 so remember the am and the PM have not touched it I'm going to do it I want to take it step after step right so this one here is 12 that is going to be our nine to 1159 so this one here is going to be 15 and this 15 here will start from 12 to what 12 to 259 don't worry I'm going to tell you what it is it's because we have am and p.m right we're going to put all that I want to make sure I finish the numbers first so now from 15 we're going to do 18 18 that is going to be from three to what it's going to be from three to 559 then finally we do 21 then from 21 that is going to be from 6 to 859 6 to 8 59 okay right now this one right here is going to be am this one is going to be am this one here is going to be am so from 11 uh 59 is going to be under am again then from this particular two uh 59 here is going to be P this one too is going to be P this one as well is going to be P do you look at that right now so finally we go down and we said otherwise inside of cords so we are going to do 9 p.m. to what to 11:59 p.m. we just put this P that we close and we come over here we close this so this is not in any way proper formatting what I'm going to do right now is to quickly uh rearrange them if you look at what we have done here before we actually hit the enter key to actually look at the result Let's us look at these numbers this is the number we have just some kind of used to actually you know create a bucket between the time that we have our services being rendered so if I go ahead and hit my Enter key right now oh sorry I'm going to have an error okay no no error already I've returned it so it's going to give me exactly what I'm looking forward to seeing so we can take a look at the result now it has been actually arranged the way we want it so can you see So based on the time we have here we have create a group of it so let's properly look at what we have here first of all we look at this particular line of code it's very important for we to understand it so that we can write it all by ourself without looking at it over and over again so this particular part here the variable time uh what we declar there is actually using the mod the hour right here and as well we actually push in the fact table that has the time column and we try to divide it by 24 hours so when we did that it terms numbers instead of what instead of time so we use that time and create a switch and switch between the numbers of the time to actually identify which particular time fall between this and this that is exactly all we have done you get it right now that is it so we have gotten this done let us look at how we can actually make use of it so we can go over to our data and now we we have our new um new column inside here and if you look at it here we go if I grab it so I'm going to have something like this so there is no column oh sorry there is no variable available to actually slice this so what can we do we need to create our toal passenger or wrers so I'm going to come over here right click and create a new Merion so in this new measure what I'm going to do is to actually say total wrers so inside bracket I want to identify what I'm talking about in case you don't know what it is so is the passengers so now it's going to be equals so what I'm going to do right now is that we have the numbers of people that actually you know uh appeared in our fact table so what I'm going to do is to actually use the sum function here the some function will go and locate the number here you have it so if you look at this this is from our fact table so I hit my enter key and with this now I can drag it in to see the numbers of people that actually appeared in that particular column based on the time group we have here so what if I go ahead and actually use the comma separator in case we have it you know in all right with this now I can go ahead and drag in this column oh sorry this measure into our time and there we go so this is what we have can you see it now with in time group this is beautiful I love it so quickly we have to do some formating so the first time or the first thing we do is to go over here and we bring in this and it gives us the numbers that is great okay if you look at it this is very cool but what if you try to actually make sure it looks a little bit not too much so this particular Y axis start going crazy so what can we do we can fix it so let me leave it this way like this the very first thing we have to do is to go to the Y axis here and uh we have to scroll down a little bit and turn of the title but still that doesn't fully solve the problem the next thing you have to do is to come to the maximum width and drag it to 50% thing we do now is to close the y axis and let's go to the X for the x-axis body title and the number that shows beneath it we don't actually want them so we turn them off go to what go to Value turn on the value turn off the value rather turn off the title and chart looks clean so because we're going to be creating our own title ourself so that we can have control over it so why don't we go and turn up the title as well so with this our chart looks much more better so we can put it right here this is beautiful right okay I love this so the next I'm going to do right now is that we want to show um time let me show what I'm talking about over here if you look at what we have at this particular part now is very informative so this line chart has all of this this and this so over here we're trying to extract you know the down hour of operation and over here we are trying to actually extract the pick hour of operation and we're trying to show how many passenger we have at the peck hour and how many passengers we have at the down hour and we're trying to show Bas on time without grouping time so we want to see when we actually filter this particular time between which time and which time so this is crazy right but very simple I can show you how I did it let's go all right we're back right here the next we're going to do right now is to actually bring in a line chart for now we're going to turn to an area chart after all so I'm going to make it very huge for now so what we can do is to go over to our fact table and we drag in our time so with our time right here the next thing I'm going to do right now is to bring in the total passenger I have for each time so we have something like this this is crazy so can just let's go ahead and I'm going to reduce this a little bit let's put it on 20 for now then we can create space okay can move this one away click on this now and here we go we have it opened so what happens if I actually is a filter so now it's giv us from 9 to 11:30 so what we specified when we actually wrote the code was actually uh telling the system to like okay you should stop it from 9 here to 1159 but we do not have our services out there till that of 11:59 so our services stopped at 11:30 and it stopped at 11:30 exactly and is a.m. and it's what we're see right here so let's filter this particular one right here now it's actually from 300 a.m. to five um okay I think we made a mistake right here we're going to go ahead and correct something over here there is a mistake somewhere let's quickly go ahead and rectify it and fix it inly so where we have this particular PM this ones as well should be pm so this one as well should be P this one should be P oh now this is crazy I think somebody must have S this I'm sorry for that so here is PM as well so if I hit my Enter key that should have corrected everything for me that is why it's always good to actually when working on data take a look at it over and over again if need be ask someone to actually evaluate what you have done all right with that let's get back what happened there's an arrow somewhere let's fix it okay this here this this okay now fixed so you can see it's now p.m. to p.m. not a.m. to p.m. this is cool okay now we have from 3:30 p.m. to 5:30 p.m. is not up to 5:59 that doesn't mean this is not correct that was because we do not have our services extended to that time you can see it right now but what if I tell you this is not really cool I want to change this to an area chart so go over here and choose this one later after all we can control how we actually show this specifically when you look at what I've done right here in times of design look at this how I want it to look like but for now let's go with this so the very first I'm going to do for my y AIS or for my xaxis this I want to turn off the title and I can bold go into the value area so bold the text I have it on it so for the y axis right I want to leave it but the title should be gone but leave this one the way it is so I told you want to have control over my title itself I'm going to turn it off it's gone okay once I have this the next thing I'm going to do right now don't concern yourself much about the design yet let us go with everything we need to actually have right here before we go strictly for the design so the next thing I'm going to do right now is to make sure I provide the pick R so for the pick R this is not something in any way difficult at all so what are we going to do it's the same thing we have done in the first part of our decks so I'm just going to click on this one to identify which particular measure actually produced it and this is it right here so I'm going to actually go ahead and control a contr c to copy recycle through it and go ahead and create a new meure so we can go ahead and paste this right here all right this particular part needs to be changed to what to pick hour of operation that is it okay instead of us to actually return this we're not returning this instead of us to be looking at summarizing at this level of routes we're not actually doing that instead we are going to be summarizing this at the level of what of our fact table I'm going to show you what it has once we have done this so I'm going to actually come here the fact table wrer chip here and over here I'm going to actually have to show the time time that's crazy right time from what not the grouped one the main time itself so for this one we live it the way it is but right now what we're trying to display is nothing but the max time so we change this to Max time so we change this one to here to time so this to top route here we can leave it that way doesn't really matter because just a name but what if I decide to actually say um pick time so that would affect this particular one right here but can we not do it at once what can we do just hold your control shift L that highlights everywhere we have it so we can type it all at the same time can you see it now doesn't affect anything okay if I hit my Enter key this would have done the work for me but what if I want to show this and show you exactly what it is that we have right here I can just copy this particular part so now copy this so already we go here I'm going to replace this so what can I do so going TR to replace this def fact [Music] table so what we need to do right now is to go ahead and actually return and we return what return the variable so when we return the variable look at what we have right here right now it's going to be different instead we're going to be actually grouping this or summarizing this by time so let me hit my enter key and look at what it is that we have so can you see it now so we have the to top passengers right here and we have time this is what it is so if I keep scrolling down you can see what we have over and over again and that is what it is that we have done so we can actually go ahead and bring in our busiest um did I use the list okay the pick R right there I'm bringing in my pick R so it's going to show in date format and time so what you can do right now is to go ahead and change the format you have if you don't like it and I don't like it nobody would like this it's giving us $18.99 how is crazy so click on it over here not on the chart or on the card you have then on the measure itself go over to where you have format then go to short time or long time whatever I want I'm going to go with the long one and that actually formats my time for me the way I want it okay this is exactly what we're looking for this is the pick R so can just make it a bit smaller so let me do 20 as well I'm going to put it over here so copy this oh no I don't want quick measure actually I want to use in new measure so if I paste this over here now all I need to do is to actually do the sending or ascending rather ascending and I told you don't tou this you can change it to mean or whatever it's still going to retrieve the same thing you're going to have so over here instead of us doing this by pick R so this is be this going to be down hour of operation that is all we need to do so let's just go ahead and return this so we can now go ahead ahead and just copy and paste this one right here so let us change this one to the new measure we have created so quickly we need to go ahead and format this click on the measure itself over here and we use the long time that is all we need to do okay we have one more thing to do right now we have gotten this done and it's working for us nicely so the next thing we're going to do right now is that we want to extract how many passengers we have for each of the picked hour or the down hour we want to see that is not in any way complicated so what I'm going to do right now is to do the same thing I've done before new measure and paste in that particular measure so we call this recycle recycle through the previous measure without you writing it from the scratch so if I paste this over here there so we have it pisted instead of for this is the down hour so for this particular down hour we can actually change the name to match what we are about to actually get into so what do we do we can identify it using V for Value so I'm going to put V in cap to later here for Value so now instead of us to return this particular one here what we can return right now is to return number but I doubt if total passenger will do that so let's do total passenger right here and if I hit my Enter key it should do it if it doesn't do it we would have to use the variable we have created for our two top passengers but let's just take a look at what this is going to do like return so um I'm going to click on this one so we have 15 which means it works so if you want to change it you can come over here and take it off remember the one we have used is this particular one right here so we can just go over to here and do this particular two TI writers and hit my Enter key if it gives me the same thing which means we can use either of them so let's quickly go over so we still have the same 15 so what am I doing this this now this particular one here is actually the same thing we have created for this one but a different measure this is an embedded measure inside the same measure right here but this one is measure sitting outside if you reference it it's fine if you want to reference this one all good so we can just go ahead and quickly copy the same thing over from here so remember when changing this for pick R so we want to use the V for Value which is the number and I told you over here you just need to change this one we can decide to use our main variable sitting outside to calculate the total passengers that we have and once you hit the enter key what you have is just the numbers of people at the pck R so let's bring in the one we've just created newly so you can see we have 80 people right here this is exactly what you need so what are we doing we're trying to create all the charts we need before our design so we have just actually you know have this created and this looks beautiful so don't worry the design is going to be some kind of cool the very next part we need to focus on right now is the boss utilization category so I'm talking about this very part right here so we want to see how our bosses are being utilized and what determines this is something you need to know so this kind of analysis helps us to understand how our trans uh transportation business is doing so we need to actually know that let's get it over here in the fact table we have the numbers of writers which is how many people on a particular date and time like on this particular first row here we have 34 people here we have 40 40 18 48th and all of that so we have another table inside this particular table called look up buses so we have capacity which means this particular bus one has 30 seat capacity this one has 60 this one has 40 and stuff like that so we want to see which among them are over utilized properly utilized and underutilized so that is exactly what we're about to create right now so to do that let us go back to our fact writers ship here so we add a new column to this particular table so new column so inside this particular new column we're going to type in Boss utilization so under here what what I'm going to do right now is to create a variable so inside this variable we're going to just call it utilization so all I'm going to do is to divide so divide what I'm dividing my to to passenges so we have not actually created a meure for our capacity our to BU capacity so we can actually embed the measure inside here so the first thing we're going to do right now the measure we have existing is actually the total riders or passengers right here so we divide that by what by the sum of our capacity which is this particular boss capacity right here so once we have done that we can just close and let us just go ahead and hit enter key oh an error will actually come up so let's go ahead and return it um okay this closes this so to have this one close this one then we can go ahead and return this so I always love to use uncore that would be very easy for me if I have multiple measures or multiple variables it's very easy for me to actually reference them okay now we're going to have this so here we have this particular percentage right here in decimal places this is what we want so we don't need to convert to percentage if we need to actually look at it in a percentage level we can go ahead and do that so here we go let us take a look at this in detail before we actually move further from here so right here as you can see based on what we have done so we have divided this particular total number from this one we have over here like oh sorry I'm talking about this capacity so this particular BS here has 30 capacity so we divide it by by if this particular boss carried more than 30 that is going to be over utilized so if this BS Carri below 30 that is underutilized you can see it now so proper utilized is when we take exactly 30 that is what it is 30 passengers so let's go back to here and take a look at the percentage we have right here so right now we can determine what that is right so we want to actually create a category C column so what we can do now is to go back instead of f returning it as a percentage what we can do is to use the switch function around this so I'm going to just use a switch over here true so using the true and first now so I'm going to say you know what this particular variable called utilization here if it is less than 0.5 that is approximately 50% so what I want to categorize this at is going to be uh underutilize so I can give it space or I can actually write it together under utilize so comma so we actually bring forward the same variable so this time around we are looking forward to 90% so if it is actually greater than 0.9 then what do we want to actually call this we call this over utilized so we need to to surround this with double quotes is very important so comma now otherwise so we're going to say proper l utiliz so we close this we close this so you can see if the utilization of our bosses are within 50 % so we going to actually turn that as underutilized right then if the utilization of it is actually uh less than oh s sorry that is greater than 90% right here that means it's actually over utilized then if it is between this 50 and 90 that means it's actually properly utilized so I'm going to actually hit my Enter key right now and forther take you back back to where we have our bses and the utilization and take a look at exactly what I'm talking about so let's go ahead and collapse this so over here we have 34 and it's giving us underutilize if presumably we can identify the boss from here would understand when we go back here what exactly I'm talking about so we have 30 capacity 60 40 and stuff like that this exactly what it is I believe you must have known exactly what we're trying to achieve now so the next question is how do we visualize this the thing is this we want to know the numbers of bosses that are underutilized and over utilized crazy all right go over here we can definitely copy this because already this one has formating and copy paste it and shift it down so what we're going to do right now is to remove all the axes we have over here so nothing is there so I'm going to click and go to my fact table so inside the fact table here I'm going to go to this particular boss utilization so click on it and uh we go ahead and look at our total Rider or the passengers no not that we don't have it yet we're not doing that when we do that we're going to do that under the tool tip so instead what we're going to do right now we need to create a new measure that counts how many bosses do we have on the road so go ahead and create a new measure so remember I talked about the fact table or the transactional table or you can call it the sales table if you dealing with cells so and we have the lookup table or dimensional table the look up table or dimensional table they all have unique value which means no duplicate values right so what we can do right now is to actually say total bosses here so for the total bosses what we can do now is to actually use the count row so instead of me using the count now because we might have some bses that does not actually uh have any transaction so if we do that that means we're going to have correct or incorrect values rather so what can we do we only count those one that were out there for business so what we can do is to actually go ahead and go into our fact table so we can just type in fact table right here it's not possible so what we can do now is to use distant count so the distant count is going to count the IDS of the bosses we have inside our F table so we go into the F table and this is the boss ID right so we close and hit the enter key this helps us to do the counting and this is something you need to understand better very important okay now we can now bring it into our chart so let's grab it h utilize why do we have it alone so which means something is wrong with our calculations right here let's go to the fact table so everything here falls under utilized so there is something wrong with this let's quickly look at this all right to actually troubleshoot the error right now what I'm going to do is to actually highlight this area my control and under the question mark you actually can comment at the same time that is the this slash the slash control and slash helps you to comment it all at the same time so I want to test something something here I want to return the utilization rate we have here alone to see what it is so we should have something beyond the percentage we have previously so I'm going to convert it quickly to percentage so if you look at this now this is what we have so what we can do right now is to actually go ahead and create a different you know instead of us having this here as summ so we don't want some kind of context transition to happen so what we can do is to create to T bus capacity which is this particular one right here what do we do we just sum the BS capacity and I'm talking about this one scroll here so you have to sum this one in a separate measure to have this done so once you have done that let's come back over to here right now and replace it with this particular one here so we bring our total boss capacity here so once we we hit our Enter key so we should now have different variation of percentages so now let's look at what we have here so now you can see we have 56% 100% 66 120 and all of that so with this we can actually grab exactly what we're looking for wherever we have it above 100% or above 90% per say that means it is some kind of over utilized you get it right now okay now that we have gotten this let's quickly go ahead and release this so first of all have this removed so highlight this your control and slash we have it back to life so if I hit my inter right now this should actually give us what we want so here we go so over here we have it at what at 56% so at 56% if we go over here here we say properly utilized is between 50 to 90 so it's going to give us that then while the second one here is given us 100% so it's actually over utilized above 100 above 90 is over utilized let's go back to our Visual and that corrects this for us so if you look at what we have right here right now you can see that we have um 36 of our bosses properly utilized and 27 of our bosses overly utilized and 19 of our BS is underutilized that is crazy so this exactly what you need to do to create something insightful and we have just done justification to that so you can now go ahead and add more information to this particular chat so all you have to do is to open up this and scroll down you see the tool tape click on the tool tape and then you click on the calculations right here then we can decide to actually show uh little to the passengers and uh if there are other ones you want to show you can go ahead and put it under but for now we go with that so if I overover right now what we see is 3 six you know bosses were properly utilized and we have 2009 72 and this one here 2008 to7 while the last one here is giving us 788 where we have the boss underutilized beautiful right all right next we're going to look at the distribution of riders or passengers by this particular weekday right here very important so next we look at the month we look at the year and we look at the year over year change that we have right here very important to look at that so with this we are done with all the measures we need to write it is time for we to actually go ahead and set it up and create it and make it as beautiful as this so let us quickly go ahead and do all of this before we talk about the design don't forget to hit the Subscribe button if you have not done that leave a comment let me know what you feel about this and as well you can actually hit the thumbs up for me to get more encouragement to create more content for you guys all right we are right here we don't need to write any measure for the weekday so all we have to do now is to grab the chart we're going to use to actually visualize that and that is going to be this one here so stretch a little bit here so the next I'm going to do right now is to go to my calendar so first of all I can actually plug in my total passengers right here then we go to the date table and we bring in the weekday into it so we have the weekday distribution right here okay this week day distribution is fine there is something I want us to do this chart is going to be very very much more informative and how do we go about that so if you look at this chart it's telling us okay fine Sunday is the highest but we want to see which among those particular days went above average and how do we add the average line is very important so there was a particular video I made that I actually uh added this but people felt like I don't know how to do this how can I go about it so remember the chart you choose while doing this matters so look at the chart I'm using I'm using closed hard column chart to have this done so once you are using the same chart let's quickly go ahead and actually do this little formatting first before we go ahead and do the other part of it so I take off the title and I bold this quickly close this and let's go over to here title is gone the value is gone all right so scroll all the way down you're going to see the reference line open it up so under the reference line you see add line click on add line and let's go ahead and actually choose the type of line we want to add so clicking over here we want to add our average line so once you have added the average Line This is what it is so we want to use color to tell a story right here and what is that we want to see which of this particular week days we went above average and which of these week days we went below average so colors would talk about it now we okay the next thing we're going to do right now is that we can decide to actually change uh go to the data label and add the let me show you what I'm talking about so we can add the average so the average is 941 right if it is important to you you can add it but this is not why we are here what we're about to do right now is to actually create some measures that will help us to conditionally format this chart let's do it so go over here and create a new measure under calculation all right all right what we're going to do first is to actually create a measure called CF this the CF means conditional formatting right so for week day so with my equal sign here so we're going to create a variable so under this variable here what we can do is to actually store in over all average oh sorry mistake I hit the end key that is going to give me an error don't worry we can fix all right what we can do here now is to actually say okay fine we want to take the average of our passengers irrespective of the week day we are want to see the same number all through so that we can have this Flatline and the number this average is going to return should be 941 941 so it should actually return this for Sunday Monday Tuesday Wednesday Thursday Friday and Saturday without having to change with okay this is what it is we don't want to average for a particular week day we want to average for all weekday to do that now what we can do is to use the calculator function fun here to affect the filter context so with the calculate function we go ahead and use our toal passengers so what do we do we divide it by what go ahead and close um go ahead and divide using the divide function so we divide so we divide it by the count of our week days is seven right so we're not hand coding seven because things can change over time when we actually place the filter so it might be like we don't have Sunday so we only having six weeks or we don't have Sunday and Monday we're having just five weeks so for that we use distinct count so this is the distinct count we have right here this distin count will count our what our weekday so once we have done that we'll play The Comma here then now for the filter for the expression we have done that then for the filter what we need to do we use the all function and uh we say remove from what from the DAT table okay so I want to see what this would actually give us close your calculate so let us return this and let's see what it will give us so overall average so if I hit my ENT key let's see what this is going to give us so fixed so if I drag this in so let us okay look at it now we have it on the same line pointing to the same average line so if I hover on it now you have 94 or 941 941 the same thing this one gives us the same thing this one gives us the same thing this one gives us right here so let me just right click and say show as a table so can you see it now if you look at the average we have here under the line the same thing we have over here and I said it's going to return for every single every single weekday we have right here irrespective of the filter we are actually looking at it so we're not going to try to do for a particular weekday so it Returns the same thing so with this now we are good to go we can go ahead and use this to actually uh make a condition on how we should actually color this particular chart so under the return so we're going to use the IF function if so we are taking off this average now we don't want it yet so we're going to say okay if the total passengers right here or wrers is some kind of greater than our overall average then comma if that is true we want to see one otherwise we want to see Zero so we can close our if and hit our Enter key so let's see what we have here so let's go ahead and take a look at this so we can remove it can you see it now so over here is not returning one on this particular part for us is not in anywh returning that for us but right here it returns that number here we have one we have one we have one here we have zero we have zero we have zero that is why you're not seeing any brows right here you get it so let's quickly go ahead and take a look at what this actually return so we can look at that when we bring in this on a card so this is this is it it returns one for you which is the overall so we don't want the measure inside here please remember to remove it and have this back so I'm going to put it at this level for now we use any color we just see to make sure we check if this is working so what we can do is to go to the format and from format you scroll all the way down yes okay format this scroll down then you scroll down to where you have column you click on this particular conditional formatting the FX there and we go to the rule we can go ahead and there are two ways to do this but I'm going to show you this one add a new rule so this one turn to equals turn this one to equals here so this one should be equals to zero this one should be equals to one so change this one to number very important this two number so when it is equal to zero that means it is below what it is below average so for now let us just pick any color as I've said earlier so I'm just going to pick random color from here so above can be this or let's do something like this for the time being we change it right okay we have this and not go that color let's go with this color so if I click on okay let's see what I have oh sorry it's done working because we have not chose the column we have just created we leave it at the default column that was there let me show you so remove it and go over to FX again so now if you change it to Ru you have to come over here and your CF conditional formatting should be what you have under what field should we base this on right so let's do this all over again so we can go ahead and make sure we using equals to zero and we change it to number so we now do this equals to one and we change this to what to number and we tell it if it is equals to zero we want to say this color otherwise give us this color now when we click on okay it should give us this is what I'm talking about so do you look at it now so whenever you know it's actually below average you have this color above average is actually on this color this is what it is we're going to change the color after all but for now let us just know that this is exactly what we want you get it all right the next thing we're going to do right now is to actually move further to actually look at what we need to look at the the monthly Trend so actually we have just two months of data so I want to use LINE because we would have more in the future so we go over here we pick up our total you know uh riders or bosses or your passengers rather then we go to our calendar here or our date table we pick the month right in so it gives us something like this so we need to actually format it quickly we click this and we go with the same format we give it this one this is what it is that we have right here importantly so once we have this we good to go so another one is just for we to visualize the ear that we have in this data set so we click and I choose to actually use this particular chart this chart should be used with care so if you know you're going to have growth of your dimension or your lookup or the categorical value we have right here don't use it other otherwise it's going to look very much Clos dead but for now we call with this so what I'm going to do is to pull in my ear and go for my total passengers right we have it so if you look at it this is what we have right here right so I'm going to just do some kind of basic formatting right here for it so the title should be turned off because I'm going to use my own person title for it so I go for the Legend and I love to push my Legend to what to the bottom so for that turn up the title and click here and we use the bottom center this is crazy I love it so now we have something like this for the time being all right so what we need to do right now is to actually calculate the year over ear change if it is up or down so we want to see that be on the two years of data that we have which is something very very simple you don't need to think think twice to do this and I believe anybody can actually do this all right let us quickly go ahead and add a new measure to calculate that all right on this new meure what we can do right here is to actually type in year over year change okay for the year-over-year change we create a variable here so this variable will carry the current the current here right so with my equals here I use the calculate function so with the calculate function we want to calculate the total rides which is this measure we have already here so with the comma we bring in the year so the ear function so with ear function we want to go to the fact table we have a date column right there we bring it in date or we can type in fact table so we locate the dates part of it here we have it we have this so now that we have this we can close it and use equals so where it is equal to the ear so we extract the main Year from it oh I think we can use now so this particular functionality we're going to use is actually working Bas on if you have if you have some kind of um live data right so we can use now right here then we close this and finally close our what close our calculator so what we can do is to properly format this what if we do this so and have this done so this calculates the current year for you so we need to calculate the previous year very important so for the previous year we create a new [Music] variable previous year ride so for the previous year ride what I'm going to do is to use a calculate function as well so with the calculate function we do the same thing total passengers or rides so we give a comma and here date of f table we extract the date part of it and we locate where it is equals to what to year now so here we go all right we have done this so we need to do minus one on this one here so we need to return this so we have an error somewhere let's just fix that [Music] no so closes that okay nicely done so we can now return this so under the return now what we're going to do is to say if not is blank so what are we trying to check if this blank we check for the current rides current right is not blank we close and we check for another one which is is blank so we check for the previous the previous here right over here if it is blank as well so once that is the case if this not not blank what do we do so we go ahead and what and actually say Okay um inside bracket the current year ride we subtracted from the previous previous year ride and we further go ahead close the bracket we have here so we now go ahead and divide it by the previous ear right then with this that create our year over-ear change for us I think this might seems a little bit complicated but if you learn this believe me honestly your life will become very much easier so we have this done what we can do right now is to convert this to percentage before we do any other thing so I'm going to go ahead and do this all right so let us bring it bring it on a card you your change on a card oh it's giving us blank which means something is wrong somewhere let us quickly fix that um I think our issue is from here so remove this particular last one here so remove it and close this particular year so we have not closed yet we close it now so when we hit our Enter key this should actually fix the error we have that is given us blank let's go ahead and check it out and see if this has worked so our check here is a problem so we need to use not here so we use and then not over here so we need to use not let us make sure it's in capulator not and we do this same one as well not as well so with this now we should have all the problems solved there should be no issues with our year over year change so let's check it out and see if that is really okay so as I've said earlier we have just fixed this and there is no ises so we have 85% down and why does that happen if you look at it right now the previous year has more passengers than the current year 2024 so because the just begin it's going to pick up in no time for that we have this okay I think I love this already so one thing I'm going to do right now is that is like okay I want to show if there is some kind of uh change like this I want to see an arrow that points up or down in different colors that tells me exactly um what is happening so what I'm going to do right now is to create a particular measure for that and this measure would first of all help me to do the check and if this is below or above I want to see either Arrow up and down so let us quickly go ahead and create that so over this one here I'm going to type in ear over ear check so I create a check here so I'm going to put equals so what I'm going to do right here is to say if the year over year is less than zero so what do I want if there less than zero inside quotes comma and double quotes here so inside the first double quotes I want to have arrow down so your Windows key and the full stop would actually uh help you to use uh some icons available in Windows just like this one I have right here so enjoy this icon when you press your Windows key and as well the hold your Windows key down rather hold it down and the foot stop then you have this so you can move it anywhere you like so let us go over here and scroll down so this is where I am and this is the arrow that is actually this particular geometric symbol click on it and you're going to be here so I have my up Arrow so I should be looking for my down arrow still being here I'll go ahead and look for the one that is pointing down pointing down should be here look at it here we go so we have both of them right now we can remove this so I'm going to just cut this one away from here CR X and have it pasted right in here like this you see that so you can just go ahead and do this and I hit my Enter key and that gives me the condition I'm looking for so I should have up and down arrow and the one I'm going to have right now because this is actually minus 83.5% approximately 84% then it's going to actually Point down so let us try to see if this gives us what we're trying to get oh this is pointing up why I'm pointing up it's not a growth let's check it out oh I think we messed it up so now we are saying if it is less than zero we are actually giving it point up so instead of that we just have to change this cut this off and paste it over here this should correct what we want for us now should be seeing arrow down because it's going down so this is what I'm talking about so we now have arrow down what we can do right now is to actually copy the same thing we have from here crl C CR A and contr C to copy now we create a conditional formatting that will format it it and actually change the color based on up and down arrow that we have so now new measure okay we have it down here so I'm going to just do um C ER for conditional formating year over-ear check then over here where we have it to be down what I'm going to have here if it is down we're want to see uh red so when it is up which means there is growth so we want to see something like green so let us be consistent with all this thing okay fine we have it right here so right now it wouldn't reflect what do we do we have to use it as a conditional formatting to change the color so at default when we come over here turn off this particular category label so when we come over over here and change this color to this this is what we have if we change it to something like this this is what we have so instead of that we go to the conditional formatting and we base it on the field value so we go over here we search for CF and we use this one here and if I click on okay this should give us this you get it right now okay everyone I'm going to tell you first hand that we are done with everything we need to do in terms of writing measures for this particular first part of our dashboard so we have just created exactly what we have right here right now so the next thing we're going to do right now is to actually look at how we can actually transform the whole charts and cards we have right there into something like this but wait a minute are you really sure of that yes of course so we about to actually create our design and you know what it is we're going to use PowerPoint there are so many programs out there that you can use for your designs or for your background creation so feel free to use any one you feel like but PowerPoint is one of the simplest and easiest and even almost free to be used don't forget to hit the Subscribe button and let's get on the other part of it

Channel: Data with Decision

Views: 7,940

Keywords: data with decision, excel dashboard in nigeria, data analyst in nigeria, excel in nigeria, Power BI, Power BI dashboard, Power BI report, Power BI tutorial, Data analytics, Power BI DAX, Power Query, Data visualization, Dashboard design, Power BI Best Practices, Data Modeling Power BI, Power BI Desktop, Power BI Dashboards, Advanced Analytics, Data Analysis Techniques, Business Intelligence, Data Analytics Dashboard Creation, datawithdecision

Id: ikIKfy0RrlY

Length: 102min 11sec (6131 seconds)

Published: Wed Jan 17 2024