 Hey guys, welcome to SSU detect susan this side and this is continuation of Power BI projects. So this is part three of Power BI projects. So today I am going to discuss about the real-time project. So here I am going to discuss and analyze about the tickets. Some of the organization is having a portal buy with the customer cam, put their queries and open the cases. So this is something like that. So here I have created this beautiful visualization and in the left top you can see the before cases and after cases comparison. So here as you can see the access bank one which is our one of the customer. So the before cases as we can see 75 and after cases is 18. So what is this before cases and after cases? So for that let's use to our data source. Then you can better understand about this. So our data in the excel. So here we are having this SOC date. So here this is the you know between date go to on the data. This is our data. In actual we are having a lot of columns but these are the required columns. So I am using only these columns over here. So first is the case number which is the customer is opened after that the open date what was the open date of that case then the assignment group this ticket is assigned to which group after that the customer name this is the bank in our case in your case it may be different and in the lookup we can see the bank name. So these two are same. So what we need to do we want to check the values on this SOC date and we want to get the values before and after this and we want to do the comparison between those. So this is our project and we need to create the visualization as we have created. So go to on the Power BI and here first as I told you this is our line chart. So here the values is the before cases and after cases. So before cases as we can see the 75 and after cases is 18. So previous the customer was raising a lot of tickets but now we can see the after cases is less when moved to PNB our second customer. So before cases is 73 and after cases is 25. After that we can move forward. Here we can see for SGFC bank before cases is 25 and after cases is 11. Then ICICI we can see the after cases is 177 and before cases is 25. So this is the comparison between the after cases and before cases in the live chart. Here we can see the middle top the total before cases. So this is the total before cases and total after cases below this. In the right side we can see the column chart. So here again we are having the comparison between the before and after in the columns. So we can also do that after that move to the bottom. In the bottom this is very important table because here we are having the customer name which is our bank then the socket then total number of cases. After that we can see the after cases then before cases then what is the percentage of before cases and after cases. So we need to create this beautiful utilization by using our data source as you can see we are in excellent. So I'll put this file in the Google Drive and provide the link of this source in the description. So if you need then you can go there and download this file. So now go to on Power BI desktop and try to open one more Power BI desktop where we'll try to create it. So let me search for Power BI try to open it. We need to wait few seconds until it will not go to be open. Now this is open so cancel it and we need to wait few seconds and here we are. So what we need to do first we want to get the data from the Excel file. So how we can do that go to on the Excel source and get the data from this Excel and it will ask for file path. So this is our file path. So select that file and click on open. Now it's gonna establish the connection. Now here we can see we are having two seats. So yes we want all these two. So we can select these two and click on transform data. So once we click on transform data then it will directly move to query editor and it will not want to load inside the Power BI desktop. So here we'll try to modify this source data which is our raw data yet and we'll try to convert it into our useful format. So how we can do that. So first thing as you can see some of places we are having values in the capital and some of the cases we are having values in the small letters. Same I guess we are having in the lookup table as well. So what we need to do first I just want to modify this lookup table. So select this column go to on the add columns ribbon. So here what we need to do we need to convert these letters into capital letters in the board tables. After that we can apply the join and that join will help us to match the data because this is case sensitive. So here click on this custom column and here we can call this as bank underscore name and here what we need to do we want to use the upper and this is bank name and close the bracket. Now click on OK and it will add one more column. So this upper is not recognized. So go back and try to remove it. Try to use the U caps then PR. So that will be the text dot upper. Now open the bracket select this bank name and close the bracket. Now click on OK. Now it works. After that what next we need to do we need to convert the data type of this. So this would be the text so we can convert this as text after that we are not required this column anymore. So go to on the home ribbon and remove that column and next we just want to put this column on the left side. Now we have done this go to on the data and here we need to modify it. So first thing as I told you what we need to do we need to convert the text of this bank name in the upper character. So how we can do that again go to on the home ribbon and go to on the custom column after that we can call this as bank name. Then here we can call this as text dot upper then open the bracket then the bank name then close the bracket. Now click on OK. So it will add one more column. Now we are not required to use this bank name anymore. So we can remove that column go to on the home ribbon and here remove that column. So now we are good. Next thing what we need to do here we want to join this table data with the lookup table and get this socket in the data. So how we can do that. So for that we need to use the merge queries. So click on the merge queries and here we can select the lookup table. Let me select now we can map by using this bank names. So here we can see 699 and 699 so all rows are matching. If we are not converted this into upper character so it will not going to match all the rows. Now click on OK. After that we want to expand this lookup table and want the socket. So we can select this and uncheck this box and click on OK. So now this is our socket. So next thing what we want to do we want to create a custom column by which we will check the values. If this socket is more than the open date then we will put the before. If this socket is less than this open date then we will use after. So we will create two custom columns for that. So how we can do that. Again go to on the add columns and here after that go to on the conditional column. So in the conditional column here we can specify the conditions. So this is for the before and what we need to check. Here we need to check this open date. If this value is before or equals to then by which column we can select the column and socket. Then we want to put as one. Otherwise we want to put as zero. Now click on OK. So here we can see this is less than so we are getting one. This is more than that's why we are getting zero. So this is something like that. Next we want to create one more column for the after. So again go to on the conditional column and here we need to wait and this is for after. So let me call this as after. Now here the open date this should be after we need to select the column and socket. This is one otherwise zero. Now click on OK. Now we have done it. Next thing what we need to do here we have bank name socket before and after columns are required. Rest columns are not required to us. So what we want to do here we just want to apply the group by and try to sum up the before and afters. So how we can do that for that go to on the home ribbon and here you can see the option for group by. So click on the group by and go to on advance and grouping. So second we want to group on the socket. Next here we can see before value and click one more and it will have after value. Here we want to sum then again we want to sum from which column for before and for after. So it will return the total number of cases before and total number of cases after that socket. So that looks good and here we can check the values. So finally we are having these values. So that is good. Next thing what we want to do we want to add one more column that will contain the total number of cases. So again go to on the add columns and go to on the custom columns. Here we just want to add the before values and after values. So this is the total cases. So let me call this as total and here it should have the before value plus after value. Now click on okay. So it will add one more column that will contain all the values. So that looks good. After that what next we want to do we want to calculate the percentage before and percentage after. So how we can do that. So again go to on the custom column and try to create before percentage. So let me call this as before percentage. Here what we need to do we need to use the before value divided by total value and we need to multiply by 100. So that will be the before percentage. Now click on okay. Then we want to round this up to two decimal places. So go to on the rounding then round and two then click on okay. So it will add one more column on the right and it should be before percentage. So let me copy this and delete that column. So this is not required to us. So remove this column and this round we should remove this as before percentage. Now it looks good. After that do the same for after percentage. So custom columns and this would be the after percentage. So after percentage then the after value divided by total and multiplied by 100 and here we need to start a bracket. Now it looks good. Click on okay. So now we are having values like this. Again we need to do the round up to two decimal places. Now click on okay. Go to on the right side. Here let me copy this and try to delete this column because it's not required anymore. In the round just try to rename it. So now we have done this. Next thing we need to load this into power way desktop. So click on close and apply. It will take few seconds. So we need to wait and here finally we have done it. So next thing as we can see lookup table is not required as so go to on the data and try to hide this lookup. So right click on this and hide in report view. Now go to on report view and we will see only data table. That is good. After that we need to add a text box. So we can click on this text box and put it smaller and bigger on the right side. Go to on our existing one and we will try to see overall ticket analysis. So let me copy this text and go to our new one and try to paste it here. If you want then you can also write this. Go to on the background color. Let me try to put as gray. So now it's good. Next thing what we need to do here we are having a value which is our current date. So how we can do that? So for that we need to use a measure. So click on new measure and in the new measure will create a DAX formula for today's date. So we need to wait few seconds and here we can see today's date and here today. Now click on this commit. After that what next we need to do we need to use a card. So we can click on this card and try to drag and drop this to date over here. So this is not the correct format. We don't want time part for that. So click on this measure and here we can see the format. So we can scroll down. Here we are having all these formats. So we can choose what we want. So let me select this one. It will take few seconds. Now it looks good. We can put it smaller and from the bottom as well and go to on the format and try to opt the category. Now we can put it on the right side and we can also do one thing like for background of this we can change it. So background color this would be gray. Now it looks good. So we have done the header part. Next we need to use a line chart. So here we can see this line chart. So click on this line chart and make it smaller from the right side and make it bigger from the top again make it bigger from the bottom as well. So now what we need to do here as I told you we want to comparison for before and after. So let me try to before percentage in the values and after percentage in the y2. After that here we can see the bank name. So it should go on inside the axis. So now it looks good and we are having all these values as we are expecting. Next thing we want to get the total count before and total count after. So for that we can use it and let me try to smaller it and smaller from the bottom as well. Let me select it and here we want before value. So we can drag and drop the before value or here. So this is the before value. Next thing we can do the total value in the maximum value. So that is like this. So this is the count off. So we don't want the count off. Why we are getting this count off because for this we don't have the data type is integer. So again go to on the transform data and we will try to modify the data type of this. So make it bigger and this is the transform data. Click on this in the data as you can see the total. So this should be the decimal and rest is good. Now close and apply. So this time it will take few seconds apply the changes. So it is applying the changes and here we can see this is the count off total. So let me change it to some. Now it looks good. After that let me copy this and paste it here one more time this visualization and make it below of this and instead of before value we need to use the after value inside the value. So now it looks good. So we have done this. Next thing what we need to do go to our existing one. So here we can see the column values and this is the percentage. So I guess I made a mistake in the line chart we need to display the total values and in the column chart we need to display the percentages. So go back instead of the percentage we need to use the values. So after value we will go in the y2 and before value should go inside the values. Now that's it. Now it looks good. After that we need to use the column chart. So let me use that one and make it right side of this make it bigger. So here as I told you we need to display the bank name in the axis after that inside the values we need to display the after percentage and before percentage. So let me try to drag and drop in the values. So now we have done this next thing what we need to do we need to display a matrix report over here. So in the bottom as you can see we are having this so this is not a matrix this is a table. So simply we need to display table. So here let me select the table. So once I select the table try to make it bigger bigger from the bottom as well. Now what we need to do here we just want to use first is bank name in the values. Next we want to display this socket. So here we can see the year quarter month and day. So we don't want to use like this. We want to see the values. So click on this go to one socket. Now we will see the socket that looks good. After that the before value let me try to drag and drop it here. Then the after value drop it here. Then the total values that should be here. So finally we have done it. Next we need to display the before percentage and after percentage. So let me try to drop it here and after percentage as well. So now we have done this. Next thing we need to increase the font size of this. So go to on the format and here go to on the grade. So what we need to do go to on the below text size. So we can increase it as we want to see it. So let me make 17 so that is good. Go to on the top and go to on the style. So here we can change the style. So let me change at alternate rows. So this looks good. So finally we have made it and we can also do one thing like we can scroll it right side and try to make the bigger the column names. So it will sit on your pace. Let me try to do that. I guess that is good. Now we have done it. Now we can do the formatting like we can also change the title of this. So let me try to change the title. So what is the title of our existing one. So that is after and before percentage comparisons. So let me select this one. Go to on the format. Go to on the title. Scroll down. So before percentage and after percentage here we can see by bank name should be removed and it should have comparison. Now go to on the first one. So here we have the values. So change the title and values comparison. So this is the before value. So we can call this as total before value and next total after value. So total after value and we can do one more thing like we can change the background of this. So in the title go to below and here we can see the background color. So let me try to change it and make it orange and go back and in the font color let me change it to white and alignment is the center. So that looks good. Here we can go to the format printer and paste that in the first one. So these two have changed. Here we can see this. We also want to change the background color of the title. So how we can do that? We can go on the here and let me put it as this one and the font color is white and alignment is the center and we want to replicate this. So select it and go to on the format printer and paste it in the first one. So now I guess we have done it and go to our existing one. Here we can see the background color is little bit gray. So we also want to change the background color of these. So how we can do that? Go to our new one and here let me select the first one and try to change the background color of this. So go to the format again and here we can see we should have an option for background. So this is the background. Let me select the color as gray and try to transparency as 75. Now let me select the first one. Go to the format printer and paste that in the last one. So that is also changed. Go to on the middle top and in the background let me change the color with the little bit gray and transparency is the 75 and format printer and paste in the second one. So that is also good. So I guess we have made this beautiful vegetation and you can do the practice on your own and if you will have any question then please do comment. I will try to respond on your comment and if you like this video please subscribe my channel to get many more videos. Don't forget to press the bell icon to get the notification of our newly uploaded videos and I will try to post a project on every week. So do support us and share to others as much as you can and if you want the source then you can see the description of this video. You can find the link and download this source.