 Hey guys welcome to SSunitech society side and this is continuation of Power BI projects. So today we are going to discuss about the customer enrollment dashboard. In the last video of this video series we have seen about the email campaign analysis. So if you haven't watched that video so I would strongly recommend to watch that video because this is the continuation of that video. So we are going to discuss about the email marketing. The first video we have released for the email campaign analysis and this is the second one for the customer enrollment dashboard. So in the customer enrollment dashboard for example if any member goes to the store and enroll himself on that store. So that is going to display in this dashboard. So customer enrollment dashboard is totally the information for the customers on which date he has enrolled and what is his category, what is the gender of that and what is the address of that customer we will see in this dashboard. So let's get started and try to understand about the visualizations which I am going to use in this dashboard. So as you can see we have two pages so we will create one by one so in the first page we can see we have three filters first is the enrollment date. Second we have the age group and third we have the state. So these three filters that will help us to filter the data on this dashboard. On below of this we can see gender wise customer total. So here we can see the male then female then not mentioned. So this is the gender of the members and the total count of the members for that particular store. So here we can see the male which is 6,887 so this is the male count and the female count which is 2,907 after that not mentioned 189. So this is the count for the members as per their gender. In below of this we can see count of customer by state. So here we are going to use this map so in this map we can see the customer count. So we can see this bubble which is very big. So this is for the state of Telangana and the count that we can see 4,331. So this is the count of the members on the state of Telangana. We can see the count of customer by age group. So here we can see the age group which will be have 30 to 35 then 25 to 30 then 35 to 40 and going forward we can see all these age groups. Now here the first thing that we can notice like the members between the age 30 to 35 has their count which is the highest one 2,147. Then 25 to 30 which will be 1,734 then 35 to 40 years which will have the 1,626. So this will have the count of customers by age group. Now below of this we can see the city and what is the marital status of the customers. So the member that we can see married and unmarried and their counts are going to display on this matrix. So the first column which is going to indicate the city. So as we can see Agra then Ahmedabad, Ahmedabad city then other cities that we have. And in the right side we can see M which will be going to indicate the married and U which will be going to indicate the unmarried. So the count that is going to display right here. So we can see all these counts as per the city. Now in the right side of this dashboard we can see the month year, week number then count of the customer. So basically here we can see the first column which will be going to indicate the April month 2018 and then the week like week 14, 15, 16, 17 and 18. And what was the enrollment count on that particular week. So as we can see in the week 14 it is having 9 then week 15 it is 13 then 16, 21, 17, 24 and 18 too. So if we will talk about the month, April 2018 the total count which is 69. So this is the matrix by which we can see the data as per the month year and week number. Now go to on the page 2 and we will see the which digestions that is going to have for the customer enrollment dashboard part 2. So here as we can see in the left top we have the logo of the company. After that we can see enrollment on which will be the enrollment date then gender then age group and then state. So these 4 filters that we have in this dashboard. After that we can see the right side of these filters the count of customers by age group. So in this pie chart we can see the total count of the customers as per their age groups. So 30 to 35 then 25 to 30 then 35 to 40 and going forward we can see all those values. Now in the right top we can see the states then what is the gender of the member and what is the total count. So here in the state of Telangana as we can see total count which is 4331 and what is the female count which is 1088 then male count which is 3150 and going forward we can see other values as well. Now in the bottom left we can see count of customers by year quarter and month. So basically this is very interesting like we can see the data as per the year. If we want to see the data as per the quarters and we need to select this which digestions after that we should on this drill down option. So once we on this then we can directly click any of these column then it will drill down to quarter and if we click on the quarter then we can see the data as per the month. So here as we can see July August September and if we go upside then we can see quarter 1, quarter 2, quarter 3, quarter 4 and year. So this option is also available in this dashboard so we can also see the data as per the drill down. After that we can see the living status wise customer count. So what it mean? So here as we can see couple with children then couple without children then single and living. So we have these three living status so the count of the members as per their living standard. So here couple with the children we can see the count which is as per others is high like 4825 then couple without children we can see 3519 after that single and living we can see only 1656. So this is living standard wise customer count. The last visualization in the right bottom we can see count of customer ID by states. So here we can see the state Telangana then Maharashtra then Andhra Pradesh so in this bar chart we are going to display the states in the access and the count in the values. So we will see the data on this. So now let's get started with the part 1 and try to design this first after that we will move to the second and try to design that visualization as well. So here before going to jump into the Power BI desktop for the training let's have a quick look of our data source. So our data source in Excel file so this is Excel file that we have the first column which is going to indicate the customer ID or member ID after that we can see the type so it should be business or individual after that the status it will be the active or inactive then here we can see the city state and postal code for the address purpose after that we can see the gender code so it will be the male or female then what is the birth date. So we can see the birth date column after that the marital status then what was the enrollment date of that particular customer or member then the living status the last one so these columns we have for this visualization. Now go to on the Power BI training one and try to get the data. Now let me have the data so we can click on the get data and our data source in Excel so we can click on the Excel let me select that file and click on open. So we need to wait few seconds. So here let me select this table and now we can click on load. So this table will be loaded in our Power BI desktop. Now go to on the existing one so here few things that we need to take care first we can see we are having this age group but in our actual training one we don't have this age group. So first we need to calculate this age group the second thing that you can see the month year for the enrollment so we don't have this column so we need to also calculate this column. Next one the week number this we don't have so we need to also calculate this. So these four columns that we need to create. Now go to on the training one and try to create all those columns first. Now go to on the data modeling and here first of all we can see we are having the customer ID then type status city all these data. Now we need to concentrate on this birth date first so by using this we need to calculate the age. So let me click on new column so it will be going to add a new column. So now we need to get this by using the DAX. So let me call this as age and here we have a formula which is called year frack. So I am going to use this formula so it is going to get the difference between the start date and end date. So here our start date that should be the birth date the first column the second parameter it is asking for end date so that should be the today's date. So we can select that now we can close the bracket and we can put the interval so we need to wait few seconds. So here we can see the age in terms of years but we don't want to see these numeric numbers so these decimal numbers shouldn't be here. So for that we can also use one more functions which is called as integer and we can close the bracket and put enter. So now it is going to get only integer part now we can see the age so it is indicating like the age which will be having the integer numbers like 38 years then 32 years and going forward now we need to have one more column on that column will see about the age group. So here let me call this as age group. Now here we need to check if the age which we have calculated is less than 20 comma and here we can say 0 to 20 years. Now we can put comma in the else part we need to again check the value. So let me copy this and go here and paste that value and this time instead of 20 we need to check for the 25 and it should be between 20 to 25 years. Now let me paste one more time and this time instead of 20 let me have this as 25. So we have already have 25 so it may have 30 this time and it should be from 25 to 30. Next it should be from 20 to 35 so it should be from 30 to 35 years now paste one more time and this time we can see we have done up to 35 so it should be 40 and it should be between 35 to 40. Next should be checked for 40 to 45 now next we need to check for 45 to 50. Next we need to check for 50 to 55. Next we need to check for 55 to 60 and others we should have 60 plus years. Now we can close all these brackets and we can put in there so it will add one more column that will be the age group and on that we can see all the values. So we have done about the age group next thing that I have already told you like we don't have the column which will be going to have the month and year so we need to extract the month and year from this enrolled on. Let me click on the new column and it should add a new column where we can write the DAX formula like here let me call this as month year first we need to extract the month from this enrolled on which is the enrolled date. So for that we can use the format then the value that is the enrollment date now we can put comma and here we need to specify the format so we can specify 3 small m so now it should have the month as we can see here it is having the month that looks good now we need to concatenate first thing that it should concatenate with minus then the second we want to calculate concatenate with the year so let me have one more concatenate and this time we need to have the year but here we need to close the bracket for the first concatenate for the second we have enrollment date now we can close the bracket and put enter so it should have the column which will be having the month and year as we can see here last thing that we need to take care for the week number because as of now we don't have any week number so let me add new column and let me call this as week number and here let me have this week number then the date date should be our enrollment date now we can close the bracket so here we can see we have the numbers only like 33, 33, 49 all those but we want to see the week column before this so we can use the concatenate and we can concatenate the text which will be the week column now we can put comma and last we can close the bracket so here we can see week column after that the week number like week 33 so we have done about all these columns now let's go to on the report view and here try to add the which license that we have so go to the existing one so here first of all I am going to create all these filters first enrollment date and second that will have the age group and state now go to the training one and here let me click on the slicer and here let me make it a little bit smaller like this let me try to drag and drop the enrollment date so here we can see this now go to on the format and try to format this first of all slicer header here we can see the background color go to on the custom color let me have this as orange double F double 6 double 0 so it should be the orange after that we can see the font color we can make it as white so here we can see this we can also increase the size but here it seems very small in the size so instead of doing right here let me off this slicer header and go to on the title and let me put this in the title and here go to on the background color it should be orange and the font color it should be white now it looks good next let me try to copy this and paste one more time put it on the right side and here make it a little bit bigger instead of enrollment date it should be age group so this is the age group that we have calculated go to on the format and on this slicer header and instead of this we want to see this in drop down now off this slicer header and go to on the title and let me call this as age group like this and we can also put it as this let me copy and paste one more time put it below of this age group and here go to on the fields let me delete this let me have the state over here go to on the format go to on the title instead of age group it should be state how we can see this state now we have done about this now go to on the existing one so below of this we can see gender wise customer totals so go to on the training one and let me have a donor chart let me try to make it a little bit bigger like this and like that now here we need to have this leasant then detailed then value so in the leasant we want to see the gender code so we can drag and drop in the leasant after that in the values we have party ID so we can drag and drop and it should be the count off so it is count of that so now we can see all these so it looks good after that go to on the format and try to do the formatting for this so first of all go to on the title instead of seeing this title I want to see the gender wise so we can rename this as gender wise customer total so this is the gender wise customer total after that here is called down and here we can see the font color so it should be white and here we can see the background color it might be this so now it looks good we don't want to see this leasant so now we can go here and off this leasant so now it looks good next thing that I want to have for this is the background color so go to again in the format and we should see the background color instead of the white I am going to have the background color like this and we can also make it a little bit transparent so now it looks good after that I am going to use the map so we can click on the map and try to make it bigger one thing that you take care for the map your system should be connected with the internet before going to use this map now let me try to drag and drop the state in the location and the customer ID in the size so now we can see this we can little bit adjust like that so now it looks good and we can also try to rename this count of party UID by state so we can go to on the format and go to on the title of this and instead of seeing the count of party UID it should be count of we can call this as member or we can also call this as customer in the font color it should be white in the background color it should be blue so now it looks good after that in the middle we should see about so now we can directly click on the tree and try to make this little bit bigger like this and here let me try to drag and drop the age group in the group and in the values we should see the party UID as their count so it should see the count of this so now it looks good and we can also do the modification in the title so now go to on the format go to on the title and we can also change the background color of this as like this and the font color that should be white now it looks good after that go to on the existing one and here as you can see the customer enrollment dashboard so we should see above this so now let me have the insert and get the text box put it on the top side make it little bit smaller from the bottom put it like that after that go to on the background color I want to see this background color as blue and here let me write as customer enrollment dashboard we can change this as aerial we can increase the size as 40 or we can also change this as 42 now we can make it bold we can decrease the size as I guess 36 put it like this now this is in the black color so let me change this as white color so now it looks good we can also little bit decrease this and we can increase this now it looks good after that below of this we have a table as per the city wise madeleine status so now let me have a matrix and put it on the below of this try to make it bigger and here as you can see we have the city column so let me try to drag and drop in the rows in the column we want to see about the metro status so we can drag and drop in the columns and in the values I want to see the count of the members so let me have the count so now we can see this as merit unmarried and total value of that so now go to on the format go to on the style and here let me add this bold headers that is good after that go to on the grid and try to increase the size of the text so we can increase this little bit like it has now it looks good after that we can also change this color from black to any another color so first of all let me try to increase this like this one and now go to on the column header first here we can see the black background so we don't want to see the black background we want to see this so it is going to change the font color that is good after that here go to on the row header and in the row header instead of those black we want to see this one so now we can see this color now it looks good after that we need to have one more visualization that is our matrix as well and in this matrix as we can see we have the month here then week number after that the count of party you ID which is the customer ID so now go to on the training one and let me have one more matrix and try to make it bigger like this and after that in the rows I want to see first the month and year so we have calculated this column in the rows so it has like month and year the second column that we want a week so directly we cannot use the week over here so we need to create the hierarchy for that so let me cancel this and go to on the month here and here let me find out the second that is the week column that we have created let me try to drag and drop the week in the month and year column so it should have hierarchy we can drag and drop that hierarchy in the rows so we should see like this one now here in the column we don't want to see anything in the values we just want to see the count of the members so we can drag and drop this party you ID in the values and it should be the count so we can see the count now here as we can see all these but one thing that we need to take care in the existing one this is the first one then the second which is in the next column not in the same so here try to go to on the format first of all go to on the style and make it as bold header that looks good now go to on the row header and here is step layout we can off this now we can see it has in the next row so that looks good the only thing that we need to take care like we can increase the size of this so go to on the grid and here let me increase the text size like 12 I guess now it looks good now we can check the values for all the month and years and after that the week number then count of party you ID so the first page that we have done now move to the second one so in the second one again we can see we have the logo of the company after that all these four filters so let me add all these filters and company logo first after that we'll see the visualizations now go to on the training one go to here in the below and add a new page and in this page first let me have the logo of the company so go to on the insert and here let me click on this image logo and try to get this logo first so we can see this logo let me put left top now we need to have four filters as I told you so let me click first slicer so it should be for the enrollment date so we can drag and drop the enrollment date go to on the format here we don't want to see the slicer header go to on the title let me call this as enrollment on and here we can see the font color that should be white the background color that should be orange so now it looks good let me try to copy and paste the second it should have the age group so we can directly drag and drop the age group in this and we can delete this go to on the format and here slicer header on this and try to make this as drop down and off this go to on the title let me call this as age group like this after that we can make little bit smaller from the bottom try to copy this and paste and put it below of this and this time it should be for the gender so we can go to here and try to drag and drop the gender in this field and delete this go to on the format and here go to on the title and let me call this as gender now the last filter that we have let me copy and paste it should be for the state so now let me try to drag and drop the state on the fields so go to on the fields first delete this gender code try to drag and drop the state now go to on the format go to on the title and here let me call this as state now we can see this so we have done about all the filters that we have the first thing that we need to insert a text box of this and it should be going to have the customer enrollment dashboard text on this now let me write the customer enrollment dashboard let me change the size font family aerial the size that should be 40 I guess make it bold and it should be white go to on the background color background color should be orange so now it looks good now go to on the existing one and try to see for other which leg essence so first we need to add the count of customers by age group so go to here and let me have the pie chart and put it right side of the slicer and make it little bit bigger like this and here way to drag and drop the age group in the agent party UID in the values and it should be the count so by default it has now it looks good the only thing that we need to take care here we can see the percentage and the value so we don't want to see the percentage so go to on the format go to on the detail levels and here we just want to see the category and data value so it is like the category that we can see and the value but the values are going to display in thousands so we don't want to see the thousands we can put it as none we can also change the font color as black so that looks good and one more thing we don't want to see this lesion so let me off this lesion so now it looks more good now we need to add a matrix on the right side of this that will have the state and genders in the columns and their counts so let me add a matrix and here in the rows we want to see the state so we can drag and drop the state on this and in the columns we want to see the gender so gender code should go in the columns and here in the values we want to see the count of customers so we can drag and drop instead of the first it should be the count so now it looks good we turn the format and try to change this style of this from this to bold header that looks good we can increase little bit on the right side like this or we can go to on the grid and try to increase the text size as 11 that looks good now go to on the existing one so here below we can see the count of customers by year quarter and month so what is this so go to on the training one let me have a column chart and try to make it bigger and put it on the right side like this now in the axis we want to see the enrollment on so we can drag and drop here we can see year quarter month so day the last one we don't want to see the day so we want to see only year quarter and month now here we can see the value so it should be the party UID as their count so this will have the data like that and left side we can see this count of party UID we don't want to see this in below this is here we don't want to see that so we can off this go to on the format go to on the x-axis here we can see we have the title we can off this title and go to on the y-axis here again we can see we have the title let me off that as well now we can see all these after that the header of this which will be the title we want to make the change on that so we can also do that go to on the title and here let me call this as count off customer ID instead of party UID so we can call this as customer by year and next will be the quarter comma quarter and month now we can see the font color this should be white the background color that should be blue so now it looks good now we can check like we can select and we can on the drill down option now we can click on this it will move to the quarter we can click and it will be going to move from quarter to month now we can see all these values after that the middle of this we can see we have a funnel chart and on that we have the living status so now let me have the funnel chart and make it little bit bigger and here let me try to drag and drop the living status on the group so here we have the living status we can drag and drop in the group and in the values we have the party UID so it should be count of that so now it looks good we can make the change as per the title so that is not a big deal I have explained a lot of time so you can make that change the last visualization that we have bar chart so we can click on this after that here we want to see the state so we can drag and drop the state on the axis and after that we can see the party UID as per their count so this is the status not states let me off and try to drag and drop the state now it looks good and we can adjust this little bit as per their requirement like this and here we don't want to see this state and count of party UID so go to on the format go to on the y-axis of the title go to here in the x-axis let me off the title so this should be gone now we can also change the background color we can also make the change on the title of this so we can make all those changes so this is very straight forward there is nothing very special on this like we did not have a lot of calculations on this but going forward you will see many more calculations in our upcoming videos where we'll introduce about the transactions as per email marketing so guys if you have any doubt or any question you can drop your questions in the comment box I will try to response onto questions thank you so much for watching this video if you really like this video please do subscribe our channel to get many more videos and don't forget to press the bell icon to get the notification of our newly uploaded videos see you in the next video