Power BI Project From Start to End Part 1 | Car Sales | Advanced DAX | End to End |2024 #powerbi

hey guys welcome back to my channel data tutorials so in today's video we are going to see a powerbi project or we can say a powerb dashboard from end to end from start to end and in this video we will completely learn how to build this interactive and dynamic dashboard which you can see right now in front of your screen all right so this dashboard is completely dynamic as said and all the elements which you can see right now on the screen those are very much Advanced okay so now let's just see the overview of the dashboard first so you can see this is a car sales dashboard and it is in powerb again so at the top here you can see we have kpi so this kpi is very much we can it gives us a highly brief we can say summary of our company or we can say a client company for which we are building this dashboard so we have shown here multiple measures in one single qpa card so if you can see here we are showing here today total sales okay so the total sales amount can be seen here then you can see I have shown here uh something in green color the amount is shown it is nothing but the difference between current year sales and last year sales okay so if it is positive you will get to see it in green color with positive value and this is nothing but the year- on-ear growth what was the year- on-ear growth with respect to last year how is the current year performing and what is the growth percentage if it is positive we are increasing if it is negative then we are going down okay and this is the month-to-date sales month-to dat sales is nothing but the latest month which we are right now in that will be the sales of that particular month so in one single kpi card we are we are you know trying to show or we are trying to give insights to the client of four particular measures okay and the same we are doing it for year-to dat average price we are doing it the same for for the year-to DAT car Souls so these are the three Dynamic and comprehensive kpas we can say uh which is giving a higher level of information uh to our client okay so we are going to see the calculations the design and how to build this uh kpis or we can say summary measures from start to end in this complete video all right the next what you can see we are showing your year toate weekly Trend okay and in this weekly Trend we are trying to highlight here which is the maximum value or what was the maximum sales done in which particular week okay so here you can see 15.5 million was the uh sales which was done in that particular week which was maximum out of all in that particular year and it is a dynamic value if it is moving from this particular week like like it is in 50th week if it is let's say in next year it is for 52nd week or 50 or we can says 30th week 31st week it will be moving dynamic all right then we are showing here two pie charts which are giving us the information of year to- dat total sales by body style and by its color right then what are the year-to-date sales which are done in or we can say sold by particular dealers okay so all the cars are sold by agency or we called as a dealer so how are they performing at that particular regions and then we have here company wise sales Trend okay so uh for different brands of different cars different makes how are the different companies performing for their respective cars all right so this is the first dashboard which you can see in front of your screen we have also designed the second dashboard but before that I will show you how what what we can say the filters or uh the filters which we are using in this particular uh we can say dashboard so first one is with respect to different body style you can go ahead and analyze this particular chart okay so whenever you click you can slice and dice the data you can deep dive in the data you can go at the highest level of granularity and the lowest level of granularity to see the results at different angles from which the insights can be generated so let's say if you want to see for a dealer uh who is from uh let's say a dealer name okay so let's say he's from Kia okay so how Kia cars are performing how how that particular dealer is performing then you can see for transmission so like if you want to see for manual transmission how are the cars if you want to see for aut transmission how are they performing likewise okay if you want to see for double engine or we can say Crosshair or overhead engine you can find this particular results for that as well all right so if you try to click here also you can see these are uh interactive filters which we have placed we call them as action filters also likewise if you want to see by color if you see on pale white you to click you can see the values are changing with respect to that all right so this dashboard is completely Dynamic and we are we have designed everything so when you click here as well well on Volkswagen if you want to see for Toyota you can see uh you can you can if you want to see for let's say forsche okay so for pors you can see that the total sales are 4.6 Millions okay and for mon to date it is 57 million for with respect to last year it has been increased by almost 1 million okay and 27% is the increase so likewise we we can analyze different things at different angles okay so this complete dashboard is giving a comprehensive idea to all the businesses of this particular Car Sales dashboard or we can say Car Sales company and for their stakeholders on their for dealer regions as well all right so this is the first dashboard then we have a navigation menu over here which is which is taking us to our second dashboard so when you click on this particular dashboard you can see we are getting a grid view okay and this grid view is giving us the row by row or we can say each row is uh been given as has a single car sold of that particular data right so let's say you want to see for each row by Row the we can say the data of that car sold so each row is giving a single car we can say uh information so let's say if you want to see for second January who was the customer name who was the dealer which was the company of that particular car then what was the colored what was the model and what was the total sales done by that particular car so in this way you can export this particular grid okay and you can also see with different filters okay so this is also filters are available over here as well so for different uh you let's say you want to see for uh you want to see for let's say CNM Motors so CNM Motors you can see all the dealer names are CNM Motors and if you want to export this only so you can export this in Excel and you can send this file to your cmn Motors dealers from there they can analyze more with their data right so it is a very comprehensive and it's a big project okay so we are trying to show multiple things in one or two dashboards over here and which is very important for our client at the top also you can see this is the date which we are showing here so this is the dynamic date which is giving us the information that the date in our data or we can say what is the date range in our data so the data is present from 2nd of uh January 2020 to uh 31st of December 2021 okay so this information also data is giving but for our analysis we are going to use the latest data that is we are going to use the 2020 and 2022 and 2023 data so you can ignore this I will show you how to uh do that in our dashboard while we are designing all right so this was all or we can say complete overall overview of this particular dashboard and stay tuned with our video uh I will be showing you how to prepare this entire dashboard in this complete video step by step everything will be mentioned okay so if you have came at this position of the video then I know that you are eager to learn what what's what will how we'll be designing you will be eager to learn different functionalities of powerbi which we are going to use here different data modeling data uh cleaning data quality checking we are also going to see how to check the quality of the data how to build calendar tables or we call them as date tables how to use int time intelligence functions and all those that is Dax functions so before moving in the video I would I I request you to please go ahead and subscribe the channel when people see the video they like the video they comment the video on video but they forgot to subscribe so please subscribe it will give me a motivation and it will help me elsewhere so also like the video if you like the video uh you just have to click once because uh one like will make this video reach to many peoples who are in need or who are trying to learn data analyst or powerp all right guys so go ahead and please subscribe subscribe my channel then you can just uh hit the like button and then we will start our video so uh before moving uh to uh building our dashboard we will see what CL or we can say what problem statement uh client has provided to us okay so just let's go from the uh start we will see everything so let's see the first the problem statement so the problem statement first is for our dashboard one that is overview and in that one we have discussed the kpis requirement or we discussed the kpis in overview when I shown you how what are the kpis which we are showing in the dashboard so first kpi which we are going to show is sales overview okay so we want to design a sales overview and in that sales overview we want to show four different measures in that first one is year-to dat sales we have to find out then we have to find out month-to dat total sales then year on-ear growth in total sales what are the year- on-ear growth uh from last year as compared to last year in in current year right then the difference between year to dat sales and previous year to- dat sales these four measures we are going to find out and these are very important to because in every interview you will be asked what is mean by year-to date month-to date how to find those what is time intelligence functions how to build a date table have you used in your original project so if you are seeing this video you will you will get to know all the answers of these particular questions okay then we have to find average price analysis what was the average price of each car sold or we can see overall average of of all the cars sold and the same four measures we have to design here also same car sold metrics also we have to find out the total number of car solds uh in numbers we have to find out year-to-date cars month-to date and year on-ear growth car solds right and same is the difference between year to date and previous year to date so this is overall the kpis requirement we called it as summary we called of the business which gives us the higher level idea of the business how it is performing all right so this is is a KBS requirement let's move ahead so before moving ahead in the video I would like to introduce you to my website so this website was opened with request of many subscriber and many followers like you where they wanted to reach out to me take can a guidance on uh mock interviews one-on-one in mentorship uh career idence in data analyst what road map you should follow what are your questions we can discuss over here so you can book a call from here also there are some digital products and uh you know uh some data analyst materials also you can see there is a power by material available some projects are also available SQL material the complete data analyst road map and material is available over here also the project which we are using as an example in this particular video that is based on financial domain so if you are interested you can go ahead and buy it from here in this the complete video from start to end is mentioned all the domain knowledge terminologies what functional requirements we are using brds and as well as problem statements from start to end everything is explained as in a realtime project in this particular package so uh you can go ahead and you can see more than 600 uh bookings are done in last 45 days okay and I was a to operated in data analyst field and it operated in last month as well so if you are interested you can go and follow and see the website and you can buy the things which are required for you so let's move ahead in the video second one is our charge requirement so in charts what we do is uh in KPS we see at higher level how business is performing why we design charts is charts is nothing but it gives us at deeper level at granular level how the business is performing if we want to see deep dive in the data if you want to analyze like how that particular uh set of or subset of data is only performing right so for that we are designing the charts and our client has given us six requirements of charts the first one is we have to find out year-to dat sales uh weekly Trend okay then second we have to find out year-to dat sales by bodyy and you can read the description what actually or why they are trying to analyze these particular charts then we want to find out year-to dat sales by color year-to dat sales by dealer region then company wise sales Trend and we have to find out the details grid okay that grid which we are going to design that is in dashboard okay so in dashboard to we are going to create a g view of dashboard displaying uh table of all the details in powerp and this will show us and we will be trying to display our all the grid and then we will trying to export it using different filters if you want to apply and if you don't we can completely export it and we can share it with our stakeholders are our different dealers right so now before moving ahead again we are very much close to 100K you have shown so much so much love in 2023 I I'm expecting the same love from you in 202 4 as well so I I request you to again please subscribe the channel and definitely we will reach 100K in one month and U hope so so thank you very much for this total love and support and motivation which you have shown here in last uh like last one and a half year so we will continue our journey and I will bring you the most important projects I will bring you most important uh uh I have planned of bringing a series of data analyst completely that is I will be launching powerbi course powerbi Advanced course powerbi Dax course then table table lods W Advanced calculations Excel MySQL course so everything is in plan just uh be patient I'm working on it so just go ahead and subscribe so whenever I do that you'll get a notification all right so let's move ahead so now what functionalities you are going to learn in this complete project so these are very few functionalities or important functionalities which I have mentioned in front of your screen but you will be learning more than this okay so you can read all of them whatever you are going to learn in this some important are connecting Excel file that everybody knows but most important are DXs time intelligence functions these functions we are going to use over here date functions custom charts filter conditional formattings we are going to use how to build the dashboards and all those things we are going to use in this particular video all right guys so the software which you are going to use in this particular video I am going to use uh this particular software and their versions which I which I will be using is for Excel I will be using 2021 version you can use any version above 2016 all right second I will be using powerbi December 2023 version I would recommend you to use the same or the latest one whichever is available with you all right latest one or the same December 2023 version all right so now let's move ahead and we will see uh What uh data we have I will give you the work through our data so now this is the data which we have by using which we are going to build our dashboard so I will just show you how many number of rows and how many number of fields we have in this particular data so this is a single table data so when I click here the total number of rows which we have is almost approximately 25,000 and when I click on here we have 16 number of fields or 16 column colums are there with different information by using that information we are going to build our particular dashboard all right so now let's go ahead and see each and every column of our data so first we have a car ID okay so when you check the car ID this is a unique uh we can say each row of this particular car ID will hold a unique value okay it will be a unique value which we called us for us as a primary key okay then we have a date so on which date that particular car has been sold so we have the data for two years that is 2023 and 2020 then the name of customer to whom the car has been sold the gender of that customer annual income of that particular customer then the dealer name so who was the dealer uh in that particular dealer region uh that particular or who is selling that particular car to that particular customer all right then we have company okay of which company that particular car has been sold we have different like we have Ford Audi is there BMW is there shevet is there and Honda so many cars are there right then what is the model of that particular car so for Toyota Let's say it the name of the car is Corolla Altis we can say Corolla then we have the engine of that particular car so what is the type of engine which we have in that particular car that is double A overhead cam shot or overhead Camp sh all right then we have a transmission okay so transmission if it is an auto transmission or it is a manual transmission then what is the color of that particular car right so in what color that particular car has been bought from that particular dealer by that particular customer all right then what was at what price that particular car has been sold okay so let's say 26,000 has been sold for uh for that of that or we can say price of that particular Ford Company expendition model was of 26,000 right so these are the different information which we have in the data and the dealer number is nothing but we can say the dealer phone number then what was the body style if it is a SUV type a car or a passenger or a hatch type car or or we can say a top type car right then the phone number of that particular customer and in what region this particular car has been sold that is Middle toown Aurora green vill different regions are there these are from us reasons all right so this data is from us data all right so this data we which we have is already cleaned data we have got it from kagle website it's already a clean data so many of rows were you know they were manipulated and modified as per our analysis all right so the data you can download it from our uh description box I will add a link over there you can download and you can use it for our analysis you can follow with the video and you can build the same dashboard in your system as well all right so now we will just go to our powerbi and we will take a new sheet over here so I will just click on file and I will take a new sheet so now let's go ahead and start building our uh we can say uh dashboard right so I will take a new worksheet or workbook for powerbi so this is a new workbook so we'll just close this okay guys so now we have a new book so first thing first what we have to do is we have to connect to our data the data which we have is a Excel file okay it's not a CSV F it's a flat file which is Excel SX file we call it as Excel file so we on our first page or our landing page itself on powerbi we have different default connectors in our screen Excel SQL server and all those things so you can directly click here or you can go in get data in home R you have to go in get data and here an option called Excel op so for now I will just click here so as soon as you click it is asking us to choose our data so I will just select my data so uh you have to navigate your data wherever you have uh located or uh placed in your system so I'll just select this data and I will just open it okay so it will take some time to load our data all right so you know this is the sheet one the sheet one is uh whatever we have the data okay okay so you can see this is the sheet one in which we have data I will just change the name of the sheet we can say carore data and I will just hit and I will just hit save and what I will do I will just cancel it and I will again load the data okay I just go here and I will load the data again so that we will have the name of the sheet in our data so you can see car data is there so this is the data which we are going to use so now we are getting a preview of the data first few 10 or 5050 rows we are having so we just have to check if everything has been populated and brought in powerbi and once you have checked you have to just click on load data okay so once you click on load data it will take some time to load and now the data has been loaded in our powerp so uh it will take some connection time and it will retrieve all the rows from our Excel sheet all right so you can see all the rows has been added in the sheets and now we are in the report report view okay so you can see this is a report view where we will be building our dashboard so to view the data you have to go to table view so when I click on table view you can see uh you will be seeing your data in this particular format over here so when you drop down this you can see these are the different fields which are available into our data so now what we are going to do is we are going to check the quality of the data and if any data cleaning is required in this particular data set okay so now to do some data cleaning and check the quality of the data we have a tool in powerp itself that is called as power query okay so I will just drop down this so you can see the full options of the whichever we have in our data so now you have to go in Home tab okay so to check the quality what we will do you have an option here called as transform data so you have your option I will just click on the transform data as soon as you do you will be having a new UI in front of you or a new dialog box will be opened which will give you the quality of the data so for now I will just down off this and now I will show you so I will just explain what we have here into our power query editor so first thing here you have at the left hand page you can see uh you have the name of the table whichever we have in our data so as we have only one single table so only one table will be appeared over here so if you are working on multiple tables all the table names will be appeared over here all right and at the right hand side in this particular pan you can see uh the applied steps so whatever changes which we are going to make in power query or whatever operations we are going to perform all those will be listed in this particular dialog box so let's say uh your colleague is working on this particular data set and he have made some changes made some data uh cleaning or data manipulation here so you will come to know by looking at this particular window that what operations have been performed here and you will come to know that yeah some data cleaning was done and what was the that particular data cleaning done so this is very helpful when there are two people or two different resources are actually working on a single project all right and it will be helpful for you also if you have work today and whenever you are opening it after 6 months or 7 months you will come to know that what you have made the changes in past all right so now let's move ahead so now how to check the data or we can say quality of the data all right so now to do that what we have to do is first will be in Home tab so now go in view tab okay in view tab you have to turn your two particular check boxes first is column distribution and second is column quality okay so by using make making using of these two particular things we are going to check our quality of the data so when you check this particular First Column you can say valid is 100% error is 0% and empty is 0% so this is very important always remember in any data whenever you are working valid should be always 100% means what you you are not having any error into that data or you do not have any empty rows or empty CS in that particular data empty can be null or empty can be blacks okay so both are not recommended to have in your data all right so in some cases they are uh they can be there I will tell you in which cases those should be there and in which cases those should be not there all right so now first this is a row ID okay so as I already told you when I was giving you the work through and the dat row ID is nothing but it is a unique key which we are having or a unique row or we can say unique value on each row is there for this particular field okay so it is a valid 100% And here you can see a distribution is there all right so this distribution is nothing but 100% distinct and 100 or 1,000 unique is there and 1,000 distinct value is there what does it mean that distinct means what and unique means what each and every row is having distinct and unique value similarly when you check for date column 100% valid but you can say 31 distinct and three units are there means what you can see here date has been repeated here you can see these two dates are repeated right so if it is having a repeative value then it's not a unique it is a unique value it's not a distinct value right so in this way uh we have to check the quality of the data and you will come to know which is the primary key which is the foreign key so these are required and these are the questions asked in interview as well right and here you can see valid is 100% so always remember date column is a field which should be 100% valid why because let's say you are working on this particular data and you have this particular date that is the third row in this particular the date is null or empty so it's not possible right so the car has been sold it has been purchased by a customer it has been sold by that particular dealer and that particular dealer region of that particular dealer but you don't have the date for that particular record it's not possible Right it must be sold on Some Day Some year or some month right so date should be always present let's say you don't have the customer information right it can be possible that you have not taken the customer information and it's blank that is okay if the customer information is blank if he is not having any customer name you don't have it in record you don't have it in uh uh we can say data uh you forgot to type you forgot to mention the name of that particular customer that is fine because that can be allowed but there are some fields which should be 100% valid all right similarly when you check for uh let's say uh we check it for this particular price okay so price should be there it is a measure or we can say it is a fact in our case because that if you are selling a car it should be sold at some amount right and if any any of the value or any of the row in this particular field is null it is or empty it is not accepted all right so let's say 12,000 of this 14,000 value is null or it is zero not possible you cannot sell a car at zero value or you cannot have a null value or a blank value in that particular sale so it should have a particular figure and it should be 100% valid all right so in this way you have to check in real time industry Al guys all right so now this is the how you check the data quality now next what we will do here you can see we have to do some data cleaning right so in data cleaning you can see there is a double A is there and at the small cap is there overhead sha so we don't want that we will change and we will uh just keep it as double overhead cap sha so to do that what we will do I will just first copy this okay copy this particular value and I will just write click over here and we will just have an option called replace okay let's find it replace values okay so we will just click on replace values I will just find we are going to find this and we are going to replace it by this just we have to uh delete this a button so I'll just click on okay so as soon as you do you can see all the values are been chained you can see in here also the double head double overhead camshot has been corrected the spelling mistes has been corrected for all the data which we have in our data sheet and at the right hand navigation page you can see steps applied and here you can see a value has been or population has been done which is called as replaced value so when you double click on this you can see what uh operation has been done in this particular step so this is very important and this is how we check the quality and the uh cleaning of the data so must not a complex level of data cleaning was not required in this data set because fortunately we had cleaned a data set and I did some basic cleaning in Excel as well so this is what we do in cleaning of this particular data set in this particular data all right so now once all the cleaning is done you have to go in Home tab and you have to go back to our powerbi whatever steps you have made here it should be visible in powerbi as well so for that what you have to do you have to click on close and apply so once you click on close and apply all the changes made over here will be automatically reflected in powerway so just you have to hit here click this once you do you can see the change changes are you know under process and you can see changes are made so if you want to cross check you can see an engine we have to made we have made here changes that is doublehead and overhead con so we are we have made some data manipulations and cleaning over here all right so now this was our data cleaning now we have to start our design okay or first I will show you as per our problem statement first we have to find out some sales data and we can say some kpis three types of kpis in which we are going to visualize four measures and here those are mostly related to year-to date month to date year on year previous year to dat so all these functions or all these measures which we are going to find out they are based on time intelligence functions like total MTD total YTD same period last year and all those functions so these functions are called as time intelligence function and what Microsoft recommend yes or what powerbi is recommend if you check the help document on internet also whenever you are working on year-to date or time intelligence function it is always recommended to create a calendar table or a date table okay so why to create a calendar table right so in many times what happens is in our data whichever we have seen here so you have data here and let's say the data we have and it is not possible that each and every day the car will be sold right so most of the times we have holidays we have some uh government holidays to that particular dealer or in that particular showroom so those days data will be not present over here but when you build a calendar table the calendar table will have all the dates from start to end whichever are there in our particular original data set that is from minimum to maximum whichever is there all the dates will be present into that particular calendar table and it will give us an ability to perform different operations and it will give us the ability to perform different date operations that is year to date previous year to date month to date all those operations can be easily performed when we are building a time intelligence or we can say a calendar table or a date table so it is always recommended and nowadays in every model every domain and in every uh project we generally use the uh we can say calendar table or a date table so now we have to build that so we are already in our table view so how to build it so you have to go in Home tab and here you have an option called new table so I will just click on new table so create a new table I will just increase your size and I will name it as calendar table you can name anything uh if you want you can mention as a date table also so I will be using here Dax function okay so Dax function itself is a calendar function okay so we are going to make use of calendar and it takes two arguments first is start date and end date so now you cannot hard code the start date and end date right because if you are typing manually and hard coding it every time you have to change it so how will you specify the start date and end date so we already have a date table into our car data set right so from there only we will pick our start date and from there only we will pick our end it so the minimum value which is there in our car data in that particular date table that will be our start date and the maximum value which is having for the date it will be our maximum date so whenever the values will be changing in our car data set automatically it will be taken into our calendar table right so now to that I will use minimum of date right date is nothing but we will pick it from car date right close the bracket for this minimum function and second is maximum second argument is maximum of car dat that is this one I will close this and I will again close this and I will just hit enter so as soon as you hit enter you will get the data right so the first date which we have or the minimum date in our date column here it is 9 uh we can say uh 9 of March 2020 right and the maximum date which we have in our data set is at8 December 2023 if you want to check in Excel also you can check over here and U just we will sort it by uh sort oldest to newest okay so when you sort it you can see the maximum or the minimum date in our date is or in our data is 9 of March okay and when you scroll down the maximum one is 8th of December the same data we are having here is the minimum maximum was 8 of December and the minimum is N9 of uh what we can say March so the minimum and maximum are captured from there and in between this minimum and maximum each row will be having unique value and it will be serially calculated right right after 9 it will be 10 11 12 13 14 irrespective of if the sale is done in this particular dates you will have all the dates over here right so this is the benefit of doing our intelligence uh we can say car calendar table and building or using intelligence functions for that right you can see each and every row will be having unique date over here unique date is that no date will be repeated right but in our this particular data set you can see dates are repeated over here but here it is not repeated and now by using this only we are going to build our data model also and our U that particular measures also so I hope you have understood why we are building a calendar table over here or a date table and how we have buil that also I have shown you right now what we will do we will build some more uh we can say columns into this particular table right so from this particular date what we will try we will try to extract month from here week from here and year from here so for that what we will do we will click on new column okay so first I will extract year okay so now to extract year what we will do we will use year function year of what this particular date okay that is calendar date okay so calendar table so this is the name of the table that is calendar table and in that in square bracket you can see date this is nothing but this date field so just select this close this and just hit enter so you can see here has been extracted so this is 2022 has been extracted when you scroll down you can see 2023 also for this particular rades all right now we want to extract month so to do that what I will do I just create one more column over here and I will name is as month okay so now to extract month we cannot use month function we can use but month function will give us the number of that particular month that is three 4 that is a number but we want name of that particular month so for that we have to use format function okay format the first argument is value value is nothing but calendar date okay so this from this value we are going to extract it comma what should be the format so I will just mention the format 4 * m close this hit end so you'll get full name okay one second okay we have to add this in double inverted Comm and just hit it so you can see it is giving us the month so four month fourth uh month is nothing but ail same we have to find out week because in one of the chart we have to find out year to- date sales by week so for that I will just click on one more new column over here and I will name it as we okay and here we will name it as weak function itself okay so I will just find it as weak number we want number or this particular day Falls in which particular week so we want it from cender date and we will just close this and just hit enter okay so you will get the numbers in this particular week right so you are having all the numbers that particular week or that particular uh this particular day Falls in which particularly all right so this in this way we have built our tables so now right now what we can see is these two tables this is the calendar table and this is the date table or we can say our main data set table these two tables are now right now they are independent from each other they are not connected to each other but we want to make it connected we want to make them dependent on each other uh while visualization we will need it so to do that we are going to make use of data modeling so you have here a third option called model view so I will just click here and here you can see these two tables which we have already created so this is our which comes our from data and this we have created inside our power right so now we want to make Connection in between these two tables right so if you can see this particular data what is the most common thing which we have here is the date right it is not that because we have the name of this particular two this date and this date is same not based on name but there is only one field which we can connect this data is on date field this here here it might the name change will be order date and here will be only date still we have to connect it on date only so what we will do I will just take this date and to make it relation we have to make it uh in relation in this table I will just release it over here so here you can see a relationship has been created and here you can see one and here you can see a star so one means what one and star means what many so this relationship is called one to many relationship so now why it is called one to many relationship I already told you in this particular table the date column which we have here right so this date column each it have unique value on each and every row right and in this particular car data set if you can check here so you can see the values are repeated on each row right 28 October 28 October it is repeated so these are many values on each row and here in this particular calendar table we have single value on each row that's the reason it is called one to many relationship so one date of this particular date table it is connected to multiple dates of this particular date table because we have multiple dates on each this is how you have to Define it or here you can also called this date is called as a foreign key not sorry for not foreign key it is a primary key because it have unique and non-null value on each and every row while here the date is called as foreign key because foreign key can have duplicate values on the respective rows which we have with the data right and this is the filter direction right so from this particular table all the value will be filtered in this particular table right I hope you have understood the data model so this is important if you don't do the data modeling if you don't build the relationship in between these two tables you won't be able to make or build the visualization based on these two particular tables right so now we will go back to our report View and in this report view now we will start building our visualizations so now before building our visuals what we will do we will set our background the canvas which you see in front of your screen this entire canvas we will try to set it up first and then we will start our building visuals all right so now we are going to add a background over here and I will show you we have already created some backgrounds over here so I will show you the pp over here so in this PP I have designed the background and how to design the backgr for your dashboard I have already created a video for that I will add the link in the description box you can click on that link and you can see how to design these types of dashboard backgrounds and you can use it for any dashboard for powerbi Excel or W any dashboard whichever you are using all right and so I have already converted this background in the form of images so and I will add those only into our power G so now to do that you have to click here on format your report p page then you have to click on canvas backgrounds and from here we will add image I will just click here and this images also and the pp also I will add in the description box from there you can go ahead and download it so I will just select the car sales background one I will click on open but still you cannot see the background over here so what's the reason behind that here you can see the transparency right now is 100% I will reduce you to 0% and you can see the background is now visible similarly you have to go to Canvas setting over here and you can see the vertical alignment is at top so instead of top I will select it as middle so this entire image will be at the center in vertically centered into this particular canvas all right so now this has been set up and now we will start building our visual so as per our problem statement if I show you so we have to find out the sales overview and first we have to find out the year to-day total sales okay so now to find out the year- to-day total sales I will just click on here and we can see all the values are here so to find out the year- to-day total sales as I already told you we are going to make use of time intelligence Dax function and for that we have already created your calendar table so by making use of both we are going to build a measure we are going to create a measure and by using that measure we will try to show our here today toal now we have to create a measure so I will just click on any of the field over here and make sure you are clicking it in car data right click over here okay and just click on new measure so I'll just increase the size of here and I will name it as year to date total sales okay so this is what our year to date total sales and here we are going to build our major so we are going to make use of tottal YTD function okay so this this total YTD functions TT four arguments okay first two arguments are compulsory for us and the second and third and fourth argument which you see in the rectangular brackets those are optional if you want to add it you can add it for more uh you know conditions if you don't want you can just mention the first two expressions so first is what expression so it is asking that uh for which particular value you have to find out the total yd so we have price over here so price is nothing but the sales done for each and every car so we have to find for that particular price but we have to take sum we have to take aggregation first I using sum of price okay so this will be a sum of price this is our first argument I will close the bracket comma for which which dates which date field you are going to make use to find out total YTD we are going to make use of this particular date field which we have created by using calendar table so I will add here calendar table I will type it and we are going to make use of calendar date over here and we are just going to specify two works I will just close this bracket so year to dat sales what it is giving us over here is that total YTD function what it what is it used for so total YTD is nothing but it will automatically detect which is the latest year for us in our data so if you check our Excel sheet I already showed you here if I see here you can see we have two years 2022 and 2023 so this is the latest here so what YTD will do total YTD automatically DET 2023 year and for which date this particular date that is this particular date column all the dates which we have in 2023 it will just bring it in a separate table and for all the price it will take the sum and it will retri it as a value so I'll just click here on okay so this is the first calculation which I created I will close this and now we want it to be shown in our visualization so now we have already created our calculation for year to- dat total sales now we will try to bring that into our visualization so in visualization we will go ahead and select here the card visual so this will be a card visual in which we will be creating our year toate total sales or we will try to show it over here so we have a year to- dat total sales I just try and bring it over here and this is the data which we are having here right so by using this particular data we are going to show our particular visualizations so for that what I will do uh we have to do some quick formattings on this so I will just go in format Visual and first go in general and in effects I will just turn off the background then go in visual go in call out value and in font I will choose it as semibold say UI semi bold I willo choose the color as 10% darker and here I will choose the font size as 25 okay and I will choose uh the category label I will turn it off okay and again go in general and the title I will turn it on and I will add the title as total or we can say year to date total sales okay these are year to-day total sales and the font I will choose at UI and I will choose it as 11 and color I will choose it as this okay so this is year to dat total sales and horizontal align bit we will choose it as sale so I'll just try to align it properly we'll take it and we'll try to place it over here okay so this is year to-day total sales but here also what we will do we will do some for we can say currency and decimal point changes over here so we want one decimal to be shown and we have to add a currency of dollar over here so to do that select this particular year today total sales and go to measure tools and here you have an option called just click on this particular symbol will be added and automatically two decimals are added but we want only one so just select here one and press enter okay so you can see if you check our uh the other dashboard you can see 371.50 to navigate over here so as for our problem statement second we have to find out the month toate total sales okay so now to do that what I will do I will just right click over here but before that what I will do we will find out year on year and year to date difference between year to date and previous year trate because those are dependent on this calculation itself so now to do find out year on-ear sales that is nothing but year on- year growth we have to first find out the previous year to dead sales right and to find out difference also we have to find out first previous year to dat sales so so to do that what I will do I will just take it a bit down and to do that I will just right click over here and I will create a new measure we have to write one more tax calculation over here and I will name it as previous year to dat toal cells okay and we will write a calculation over here we will be make using of calculate function over here it is taking multiple arguments one argument is compulsory that on what particular expression or what field or measure you have to calculate it and other are filters you can add multiple filters over here as much you want right so first we have to take sum okay because we have to find it for sum of price okay so for this particular value we have to find comma we are going to add a filter over here so we want to previous year to date sales that is right now let's say we are in uh 2024 don't consider 2024 because we are very early so let's say you are in 8th month that is you are in uh October end okay so for the same current year if you are current year consider we will consider it as 1 of October 2024 to 8th of O sorry 1 of January 2024 to uh we let's say 8th of October 2024 so this is current year span so for same current year span you have to go back in last year that is 1 of January 2023 to 1st of October 2023 what was the sales done that is called as previous year to dat and today that we are going to use make use of same period last year so it Returns the set of date in current year selection for previous year so what is the selection of current year same selection for last year retrive the value from there and date is nothing but we are pick it from calendar because it is a Time intelligence function so we'll be making use of calendar date close the bracket two times and I'll just hit enter okay so if you want to see what is the value of it I will just take a new measure over here or we can say card and I will show you so this is the previous year to dat Sals so it is obviously greater so we have to take difference of this two and we have to find out here on so I'll just remove it for now and to find out first we'll find out the difference so I'll just create a new measure and we'll name it as sales difference I name it as sales difference okay and I will name it as sales difference uh the calculation is nothing but year to-day total sales minus previous year to dat sales so year today total sales minus previous year we total sales this is nothing but difference between the current year and last year sales just enter okay so this will be created I will take a new card over here and we'll try to show it take the sales difference over here so 71 million was the sales difference that is nothing but 71 million and it is in Pro positive value it is nothing but positive means what we are in profit 71 million or we can say uh as compared to last year 71 million we are in uh more in current year right so we will do quickly formattings on this I will just select this I will click on format penter and I will just click here so all formattings will be applied here automatically select the sales difference and we will also give here currency okay and same we will give it for only one decimal point hit enter so this is for one decim point and we will do some formattings over here quickly so I will just go in format visual okay go in call out value and in call out value I will choose the uh font as for as 11 okay so this will be our value okay I'll just try to reduce it and I try to place it over here okay so this is the value just let's see where we have placed it original dashboard it's here all right so wherever you want you can place it all right so this is uh the sales difference now what we have to do is whenever sales are positive if difference is positive it should be in green color and if the difference is negative it should be in red color and this should be dynamic okay automatically it should be done we don't have to come here and hard code the color always so to do that we have to do give some conditional formattings so before doing that we will write a calculation to do that I will write a calculation a new measure over here and I will name it as sales sales difference color name it as sales difference color you can give any name and I will be using here if condition okay so uh we will use a if condition over here and I will name it as if okay so first logical test so it's The Logical test will be what if sales difference okay if this sales difference is greater than zero okay if it is greater than zero means what it is on positive side if it is less than zero means it is in negative side if it is if if this condition is satisfied if it is greater than zero what should be the result if it is true so if it is true we want the result as green color so what I will do I will mention here as green okay and if it is negative what should be uh what if it is false if this condition does not satisfy means what if it is negative then it should be red okay and I'll will just close this okay and hit enter all right so it says that okay so what we will do I will just add it here in double commas we are adding a text over here so we have to add it in double ined and just hit enter okay so this condition has been applied now what I will do I will just select this go to format visual then go to call out value and here you have have an option called FX just click on this FX and we will be applying here our rules so we are going to apply a rule over here so rule is for which field so we are going to apply it for which field that is for sales difference color so this field so if the value is green okay so if the value is green so this text and the text which we used in our calculation should be same okay so if the value is green we will use the green color so just choose the green color from here so we will use this particular green color all right and if the value is read I will just add a new rule over here if the value is red we will add it as in redor okay so we'll use this red color or you can use more colors and you can apply this particular okay guys and I will just hit on okay as soon as you do you can see the value is converted in green color because the value is positive right so in this way we have to apply the rules over here okay guys so same way now what we have to find out we have to find out the year on-ear growth percentage so to do that what I will do I will just add right click over here okay and I will create a new measure okay so to create a new measure that is to find out the year on-ear sales growth I find out the year on-ear sales growth to find out this this is nothing but the difference of year-to de Sales minus previous year-to de Sales total divided by previous sales this is the formula for year on-ear sales growth that is nothing but sales difference divided by previous year to- dat sales this is the formula for it so that is nothing but sales difference we have already calculated this is the sales difference and total divided by previous year Sals so this is the formula you can search it on Google as well what is the formula for year on-ear group all right so this is the formula I just click on okay and here what you you have to do you have to select this contrl C and control V use the same value okay why we are using same value because same rules will be applied okay if it is positive then this will be also positive because sales difference we have taken at the top itself right so we are going to use same uh functions over here right same logic and same conditional formatting so now select this close this just we are going to add here year on your gr so this is year on your gr but it should be in percentage so select this and click here on percentage so as soon as you do you'll get a percentage over here that is nothing but what inside we are getting here that as compared to previous year current year we have sales greater than 70 million and if you check the growth it is 23.5 percentage we are at higher side we are growing as compared to last year right and if it is green then we are in positive side okay we are in we can say we are increasing our Sals as compared to previous year I hope you're understanding right now as per our problem statement we have to find out month to date sales as well so now to do that I will just right click here again create a new major and I will name it as month to date total sales okay so to find out month to date total sales very simple just instead of YTD we will use Monto dat sales same expression is what sum of price okay comma calendar date okay that is this particular date we so what does this total MTD does is when whichever current month or latest month which we have in our data for that month it will give us the sales and that is what we want I will just hit okay and what I will do I will take a new card over here I will take this particular card and I will take the month to date total sales and I will add it over here so that is nothing but 54 million but but what we want is we want this text to be added to this particular month-to dat sales that is it should be having month toate total sales written over here we should have a semicolon and then this particular value should be there so to do that what we will do we will write one more calculation it is nothing but we have to concatenate it okay so this this calculations are important and this exact thing you have to do guys in your particular original or real time also so here I will give you as one to kpi I will name it as monate kpi and I will use a concatenate function okay so text whatever we are going to have here is we will write it in double noted comma month to date total sales okay and I will add one space string and again I will add the double inverted or I will close the double inverted comma X2 is nothing but we have to add this particular sales that is nothing but one today total sales which you already I will close this and I will hit enter okay now I will close this and instead of this what I will do I will just delete this and I will take Monto dat kpi so now you can see here all right so you are having Monto dat total sales has been added over here but you can see the value which we are getting it is getting converted in a text format so this is a text format value and we are getting it in complete figures but we want it as 54.2 million so how to do that again we have to modify our calculation so this is the calculation which we wrote okay so in this particular calculations what we will do we will use some format functions okay so instead of this what I will do we will use a format function first here okay which value we have to format that is one today total sales okay so this is the format value which we have to use comma in what format we want it right so what we have to do is first we have to divide this particular value by 1 million okay I will divide it by 1 million okay that is 6 * 0 6 * 0 what will happen this divide when you divide it by 1 million that is 10 to 6 we will get a decimal point over here right so that we have got now we have got 54.2 now what we want is that is it should show only a value up to two decimal points and a dollar symbol should be added to it so I will add in double inverted comma a dollar symbol should be added okay and we want two decimal points that is 0 0 only two decimal points and at the end M should be added that is nothing for million I will close this bracket and I will just hit enter so as soon as you do you can see this value is converted to 54.2 million and this is what we want so these Dax functions are important okay so learning of Dax functions getting the concept of those is not important applying those in real time is important that's why I already told you this dashboard will be bit comp complex but it will be good for your learning so here what we will do here we want one more space over here so what after this column what I will do I will add one more space over here and I will just hit enter you can see automatically a space will be added you can see right and I will just close this now we will do formattings on this so just come here go to call out value so uh in call out value again it will be at 10 okay and in this particular we will be using the font as s UI we don't want any category label because it's already there and we'll just try to reduce it likewise okay we'll bring it over here and uh as per our this particular uh dashboard we will just see what we have done okay now what we will do uh we will change the background of this so for here we'll just go in general go in effects and here we will add a background I will just go in more color and I will add a color code for this you can use any color if you want but for me the background color code I will be using 800 27 e okay 27 e okay 800 27e this will be the background color a little bit of purple color I will be using over here okay just reduce this and I will try to bring it over here and place it over here okay yes so this is our Monto date sales so just we reduce it like this okay and we'll try to take this a little bit left so we will have a roaming space this all right now just select this again we will come here in call out value we will change the font color for this and I will change the color and in more colors I will add a font color for this as C 4 99 CA okay so this will be a font color which you will be using so these are the month to date total sales all right guys and what we will do just go in general and in effects we will add a border not required okay so these are our month today total sales we will just make it a little bold for proper visualization okay perfect and let see how this looks or we'll choose this perect this looks okay so these are Monto day total sales and I just Place its position like this all right so this is for first kpi it is year to dat total sales difference here on your percentage and mon today total sales I hope you have understood and and if you have come at this particular point of the video you are doing great guys okay so because if you are learning this and if you are following the video if you are learning from this video and I I I bet you that you will be learning many more visuals from here right many more functionalities many more calculations and many more we can say power be Advanced Techniques also so here what we will do we'll just try to give a border over here so I'll just go in effect and as I said I will just turn on the visual border and we will be using the same color which we used that for that I just turn the corners with five pixel yes 5 pixel is enough okay and in properties will just increase the height of it by 35 perfect now this looks good right so this is our M to total sales all right guys so we have calculated the first kpi in the same way we have to find it for other two first is important to understand next we just have to change few of the calculations and it will be ready for us to use all right guys

Channel: Data Tutorials

Views: 24,318

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

Id: XnPo5Ft7RzQ

Length: 67min 10sec (4030 seconds)

Published: Mon Jan 15 2024