 Hey guys, welcome to SS Unitex, so we'll decide and this is continuation of Power BI project. So today we are going to discuss one more real-time project in the Power BI and it's totally related to the healthcare domain. So here we are having the data of the call center of the healthcare. So let's have a quick look of the source data before going to discuss about the dashboard. So here I am having the data in the excel file. But in reality, this data was present in the secosover table. For your convenience, I have copied that data from the secosover table and paste it right in the excel. Like if you want this data, then I will send that file to you. And here you can see the case ID. For example, if anyone call in the call center, then that will have the unique ID that will be case ID. Next will be the start date. This is the date and time by which any person call in the call center. Next date key, we can skip that column. After that, we can see the employed in healthcare. So this is like if anyone call in the call center and that person is working in the healthcare, so we'll have to specify right here. After that the call reason why that person has called. So this will be call reason. And we can check the values inside the call reason. That is the cancel appointment, then a general enquiries, schedule appointment after that text results, then a known walkthrough and blank. So these are the call reasons available. After that the first COVID-19 test. So it is basically like if we have to call in the call center nowadays. They want to know about the COVID-19 test. If you have already done that, then you have to inform to the call center of the healthcare. Next will be the language we can skip, then we can see the date of appointment. So this is for the schedule appointment in the call reason and we'll have to specify the date of appointment. Then the time of appointment and zip code like from which location we are going to take this inquiry. After that we have the second table that is you can see the zip code information. So here we have the pin code the first column next. We can see the other informations like city, then we can see the district, then we can see the state like that. So we are having all these informations on this table. So this is our two source tables by using this table will be going to create the dashboard. So now go to on the dashboard and we'll discuss what we want to do. So basically here this is our dashboard first, this is our company logo. After that this will have the text box. This will have the text box which is displaying like cases analysis dashboard. In the right side we are having this date filter. So we can filter the dashboard data according to the selected date in this slicer. Here we can see the column chart and in this column chart we are having the daily count. So here we can see the 87 then 84 like that we have the data. So it will have like August 7, August 4, August 3 like that. So we can see in the column chart now go to on the right side and we can see the pie chart. It is having the data for call regions like here we can see the 395 and we can check that is the text result and here we can see the 1053 that is the schedule appointment. So we can check the values how many schedule appointment we have how many text results general enquiries unknown cancel appointment. So we can see on this pie chart in the bottom this bar chart. So it is having the values on this. We can do one thing go to on the visualization and here go to on the format. Let me on the data level for that. So here we can see the exact count. So here we can see the 21st of July it has two counts 23rd of July 24 25th we are having the count values on the bar chart after that we are having this map. So in this map we can see the location wise cases count. So basically here we can see in the India New Delhi after that we can see the middle side and we can see all those values in this map. In the right side we can see this matrix. So it is showing the district wise count like if we can see the Almora so it is having 11 after that we can see all these like Basti that is also 11 go to below East Delhi that is 36 after that the Faridabad that is 216. So we are having all these values in this matrix. In the right side middle you can see the employed in healthcare. So it is showing the data like how many of persons are working in the healthcare we can see on this column chart. In the bottom donor chart we can see the first COVID test. So it is saying if you have already done with the test then that is in green if not then we can see it's no and other we can see it's unknown. So that is in the blank category. So this is that we need to do in this video. So go to on the next Power BI desktop and here we will create that beautiful with registration. So first of all let me grab the source. So we can click on this. Let me grab the COVID cases the first one and it will take few seconds. So we can wait go to on the seat one and let me grab this so we can click on the load. Now let me rename that as cases after that let me grab the second one that is the pin code information. So we can load that file as well and here go to on the seat one and let me click on load after that we can also rename that as pin code. Now go to on the relationship first and here we can see we don't have any relationship right now but we can make the relationship with the zip code and here we should have the pin code. So let me try to make the relationship with the zip code to pin code. So as we have already made the relationship. So first step we have done now go to on the report. So in the report as we have seen first we have the company logo. So now go to on the insert and try to get the logo. So click on the image. Let me grab this image so we can click on open. So this is the our company logo. So let me try to make it smaller. Next we should have the text box which will be having the cases analysis dashboard. So go to on the text box and click on that and here let me try to make it on top side and here let me call this as cases analysis dashboard. And let me make it bigger and a bit smaller from the bottom and here let's make as aerial and let me increase the size as 24 I guess or we can make it as now go to on the background color click on that and here instead of this we should have I guess this color and little bit transparency on this and here we don't want to see the font color as black. So let me make it as white. So this is the second step that we did after that as I told you we should have a slicer that will be having the values between the dates but here go to on the data view and here in the cases you can see we have this start date but it is having the date and time both we don't want to see the time part on this. So for that we can add one more column and we can call this as date new I guess. So let me call this as date new after that we want to have the date only. So it will asking for the year. So let me have the year from this start date now close this bracket then put comma then the month and here we should have the start date close the bracket then put the comma last will be the day so day then it should have the start date now we can close the bracket two times and push the enter after that we can see the date new which is having the only date part it is not having the time part like you can see the 12 12 12 only on this now go to on the report view and let me have this slicer and put it on the right top side and here let me try to make it bigger and smaller from the bottom now we can have this date new on that here you can see the values and here we are having the slider. So if we don't want to see the slider go to on the format we can off the slider and we can also off the slicer header in the date input we can increase the size like this after that go to on the border let me on the border so now we can see it is having the border on this and let me try to increase the radius size so this should be I guess 15 that might be good so we have done the second part after that go to on the existing one and here first we can see we are having this column chart so let me grab this column chart so go to on the new one and here let me have this column chart so now put it on the top side and increase the size so here what we want to see we want to see the values like the month and day in the axis and in the values it should have the count of the cases so here we should have a column that will have the month and day so go to on the training one and here go to on the data let me add one more column so we can click on that after that let me wait few second and here let me call this as daily I guess and it should have the month so we can extract the month from the start date and we can close the bracket so it will have the month but we want to concatenate with the dash so we should use the concatenate function for that so this is the concatenate and we can close the bracket next we want to have the day part as well so we can use one more concatenate so let me add concatenate one more time and this will be the our text one which include the month then dash let me put comma in the text two we want to see the day only so day after that we can have the start date now we can close the bracket for the day second for the concatenate let me put the enter on this so it will take few seconds we can wait on this so in this new column you can see we are having the month then day now go to on the report view and here let me have this daily on the axis after that we can grab this case ID as the values so we can add that so here now you can see the cases and in the axis you can see the month and day so this is the second thing but as we can see in the existing one we are having this color code on that and it should have the daily cases the minimum value is 9 and the maximum value is 215 and the middle value is 112 so this should be present in our dashboard so go to on our dashboard and here let me go to on the format and here go to on the data color and click on this effects after that here we can click on this diverging and here in the lowest value it should be red so we can use the red I guess and in the middle value it should be yellow so we can go and grab the yellow and in the highest value we want to see as green so let me have the green like double zero double B double zero now click on back and here we can click on okay so once we click on okay then we can see it is having the count of daily and here the values of this data color has been changed so that looks good after that go to on the after that here you can see the data labels if you want then we can on this so here in the title it is case ID by daily so we don't want to see the case ID by daily as title we want to see device case count so let me use the device case count right here device case counts so that will be our title and here the font color of this we can leave it in the background color we can make the change and little bit gray I guess and after that alignment that should be center here we can see the font family that should be aerial and the size it should be 15 I guess or 16 that might be good after that here we can see the count of daily so we don't want to see the count of daily we want to see the daily count so how we can make that change so for that go to on the lesion and here you can see the lesion name that is the count of daily we can have the daily count and we can make it as it has so here we can say the daily count the minimum value is 9 and it is in the color of red and the maximum value that is 112 and it is having the color as yellow and the green that is 215 so this is the device case counts so this is the first one the second go toward the existing one so here the call reason so we should have this pie chart and it is having the call reason so go to on the training one and here let me grab the pie chart let me put it on the right side of this column chart let me make it as a smaller and here we can see the lesion so in the lesion we want to see the call reason so we can click on that and here we can see the values so in the values we want to see the count of case items so it is having the values as per the call reasons but here we don't want to see the values and percentages on that so we should have the exact numbers so for that go to on the format and here in the data level we can have the data value so it is having the values in the thousands so we don't want to see the values in the thousands so we can put it as none so now we can see the exact numbers right here and here go to on the data color like in the schedule appointment we can make it as yellow I guess and here in the text results that might be good general inquiries might be good and go down here we have walked through we can make that change and put it as this color I guess let me use this as orange color and after that everything is good so this is the second that we have done the only thing that is remaining for the title of this so we don't want to see the title of this we should have the title like call reason only so go to on the format and here again open the title and we can remove the case id by call reason so it is having the call reason alignment should be center and here the background color that should be light gray and the font color that is good and here let me try to make it as aerial for the font family and the text size we can increase as 16 so now we can see this and we can make little bit space between these so we can so we have done with the twoizations what is the next so go to on the existing one so here we can see the device the dual appointment so for that how we can do that go to on the training one and here we can see this bar chart now let me try to make it little bit smaller and little bit bigger from the top side now here we want to see the appointment date with the counts so for that here we have the date of appointment we can click on that and after that in the values we should have the count of case IDs and here we can see the count of the case IDs that is perfect but here we can see the blank values because these might have in the different regions so go to on the filter and here let me grab the call reason and put the filter for the scheduled appointment only so this is the scheduled appointment we can click on that so these are the scheduled appointments that are available let me close this so these are not in the sorted as per the date so let me try to make it sort as per the date so go to on the sort and date of the appointment so now it's sorted and if we want to make it as ascending order then we can do that now here we can see like 21st, 7th, 2020 then 23rd, 7th, 2020 like that we can also make the change on the data color of this so go to on the format and here go to on the data color and let me try to make it as double zero double B, double one I guess and that might be good so here we can see all these the only thing that is remaining for the title of this so again go to on the format go to on the title and we don't want to see this title so it should be day wise schedule appointment so for that let me change the title of this so it should have the day wise schedule appointment and let me scroll down and here we can see the font color that is black that is good in the background color we can make the change and here scroll down alignment that should be center and text size let me try to make it a little bit 16 I guess and the font family that should be area so this is we have done for the day wise scheduled appointment and here we can see the date of appointment so if you don't want to see that then we can also hide that title so for that go to on the format and here we can see the y-axis and scroll down on the y-axis and here we have the title we can off that title so that will not be displayed but in my case I want to see that so we have done with the bar chart what next we want to do so go to on the existing one so here we are having this map and this matrix so how we can use these so go to on the training one and here let me grab the map so we can click on this map so first I would suggest before going to use the map your system should be connected with the internet and inside that go to on the second table that is our pin code and here we are having the division name so we can grab the division name in the location not in the leasant in the location should be and here we can see all these next in the size we should have the case id so we can grab the case id in the size so we can drag and drop right here so here now we can see it's a little bit bigger in the size and smaller in the size on that now go to on the format I want to make a little bit format on the color so instead of this color I want to use the green color and here let me scroll a little bit down here we can see the title so title should be off for that now try to make it a little bit smaller and we can adjust according to our requirement here we can a little bit smaller and smaller from the top side as well we can adjust as we want next we have the matrix so let me click on the matrix and let me put it on the right side of this map and try to make it smaller this one so here we should have the district name in the row in the first column and the count of the cases so here we can see the district name that is in the pin code table so we can drag and drop in the rows after that we can see the case id so case id should go in the values so now we can see all these values now go to on the format of this matrix go to on the style styles should be none I guess next go to on the values and here let me try to make it bigger in the size so we can increase the text size for that I guess 13 might be good after that go to on the grid and here scroll down in the text side we can increase this as 13 so it is in the same phase now here the background color of this case id is not the perfect as we are expecting so how we can make the change on this so again here we have the conditional formatting option so go to on that option and here let me select this case id and click on the background color so by default it is selected but go to on the advance lower value for the red and highest value for the green and diverging and it should be yellow and we can click on okay so now it is changed so we have done with the background color of this and here the font color that is the black so it doesn't seems good it should be white so here we can see the font color we can click on that and here let me click on the advance control and in the lowest value we can set it as white and in the highest value we can also set it as white now we can click on okay and it is having the white color on that so that looks good after that here we can see we should have a title for these two so for that let me add a text box so go to on the insert and here we can add the text box and this time let me put it on the upside of this map and matrix and let me try to make it bigger and what we want to see so it should be location wise cases count so let me add the location wise cases count and here it should be aerial and we can increase the size as 16 I guess and should be in the center and here go to on the background color background color of this that should be gray so now we can see the values and if we want make it bold black so we can make that change so it looks good now go to on the existing one and here we can see we are having these two visualizations here employed in the healthcare next is the first covid test so what we want to see employed in the healthcare we have unknown no and yes so now go to on the training one and here go to on the data view so in the data view we can see the employed in healthcare so here we can see it is having the blank no unknown and yes so instead of this blank we should have the unknown so how we can do that so cancel this and let me add one more column and here we need to check the values on that employed in healthcare so let me add employed new that will be our column name and here we want to check if the value in the employed in healthcare is not equals to yes and the value of the employed in healthcare is not equals to n then that should be unknown otherwise we want to see the value for the employee in the healthcare now we can click on enter now here we can see we are having one more column that is having the value for unknown for the blank otherwise unknown yes and no that looks good now go to on the report view and here let me try to add a column chart so we can click on this column chart and here let me try to make it little bit smaller I guess and here let me put it on the upside and here we can make it little bit bigger now here we want to see the cases count as per the employed in the healthcare so we can add the employed in the healthcare in the access and cases count in the values now here we can see the unknown then no then yes so that looks good now go to on the format of this and let me try to make it little bit format so go to on the data color first and click on the so all here if we have the unknown then that might be light yellow after that here we can see in the no that might be red and in case of yes that might be green so that looks good so we are good with this the only thing that we need to make the change on the title of this so go to on the title and here we don't want to see the case id by employed new we want to see the employed in healthcare so let me try to make that change like employed in and here in the background color let me make it as gray and alignment that should be center so we have done with that we can do one thing we can little bit increase the size of this for that we can decrease the size of the devised the dueled appointment and we can little bit adjust that and here we can switch it on the left side so we can select these three like that and here we can make it little bit bigger that makes sense I guess after that we should have a donor chart so click on the donor chart and what we have on that you know donor chart so here it is having the first covid test if we have already done that then yes if no then no otherwise unknown so again go to on the data and here in the first covid test we are having the blank values yes and no so instead of the bank blank values we want to see the unknown so let me add a new column for that and here let me call this as covid test and here we can call this if first covid test is not equals to yes and first covid test value is not equals to n then that should be unknown otherwise the value that we have in the first covid test column now click on the enter and here we can see the unknown yes and no on this column that makes sense now go to on the report view and in the donor chart in the lesion we want to see the covid test column which we have created after that we should have the case IDs on this so here we can see all these values but we don't want to see the values like that we should have the exact figures on this so go to on the format and here let me go on the data level instead of all these we should have the date value only and here display unit that should be none so here we can see unknown is 1453 and here yes and no now go to on the data color we don't want to see the values like that in the yes that should be in green in the no that should be in red in the unknown it should be I guess a little bit yellow color so we can do that now we can see that looks good the only thing that is remaining on this that is the title of this so select this and go to on the format and here we can see the title let me try to make the change on the title we can call this as covid test so this is the covid test now we can click on then on the font side that is perfect in the background color we can make the change alignment that should be center and here we can change the font family as aerial and we can increase the size if we want so now we have done with this report and in this dashboard we can see we have done the beautiful things like the column chart like the map and here we see in the matrix how we can do the conditional formatting and here we are having all these informations that is required so this is the real-time project and if you have any doubt then you can drop your questions on the comment box I will try to response on your questions thank you so much for watching this video if you need the source file then you can also drop the email ideas on the comment box so I will try to send the source file to you thank you so much for watching this video stay home stay safe and keep watching ssunitech and keep learning do subscribe if you really like this video and please share to others thank you so much see you in the next project