Power BI Project End to End Dashboard Development | Beginners | Power BI Tutorial 2024

a typical power bi dashboard creation process consists of nine different steps requirement Gathering data collection data transformation and modeling data visualization blueprint dashboard layout and Design adding interactivity and navigation testing sharing and then finally maintenance and routine refresh now for a beginner it is very difficult to go through all these steps without any proper structure so in this video we're gonna develop a complete power dashboard from scratch following all these steps in detail if you're interested keep watching thank you hey everyone welcome back now before we begin let me quickly show you the data set that we're going to use for this exercise this is a publicly available Healthcare data about patient waiting list and this data set contains two types of data categories first one is an inpatient and second one is an outpatient now for those of you do not understand the difference between two let me just quickly give you a brief explanation when a patient goes to a hospital and gets admitted for a treatment that patient is considered as an inpatient and when a patient goes for a treatment and get treated on the same day and then leaves the hospital without Ever Getting admitted that patient is called an outpatient now what exactly are we going to achieve with this is something that we will understand in the requirement Gathering phase so we'll jump right into that now the link will be in the description in case you want to download the data and follow along if you want all right it's time to get into the first step of the dashboard development process which is the requirement Gathering phase within this we have couple of sub steps which are very important to understand and usually you would have to follow them in the same order the first one is identify stakeholder now ironically enough most of the beginners make a mistake in this same step itself because in this step you would have to determine your primary stakeholder and establish a point of contact who might be the domain expert or leaders who will eventually use the dashboard so unless you set a point of contact at the very beginning it will be very difficult for you to ask for explanations or clarify your doubts whenever required the second step is to understand business objective now through meetings and calls with stakeholders you should get an outline of goals from the entire Endeavor asking open-ended questions will help you gain more insight to understand the data and how this dashboard will help achieve a specific business goal next comes a high level data study now remember this is just an high level overview of the data you are not required to dig deep into the data in this phase because that will come later on in this phase it is expected to cover few specific topics like the data source of the data column descriptions data type volume or frequency and finally data quality that is any missing values or anomalies once you have acquired a high level data understanding it's time to Define scope now this is the stage where you would discuss key metrics kpis or deployment timelines with the stakeholder now remember to document everything that you discuss because that will prove to be a boon at the later stage and in this stage you will document calculations time frames and scope which will help you set the expectation and avoid any future disagreements and also as a best practice I follow a 80 20 rule that is always keep a 20 buffer while finalizing the deadline because it's always better to over deliver after a standard commitment than to under deliver after an extraordinary delivery pitch now let's assume you've already established your primary point of contact which was my first sub step of the requirement Gathering phase and then you've had a discussion with this point of contact or your stakeholder to understand the business objective so these are the business objectives or the problem statement that you would have for this particular exercise overall we would have to solve three problems first one is to track the current status of patients waiting list second we would have to analyze historical monthly trends of waiting list in inpatient and outpatient categories and then we would need to do a detailed level specialty level analysis and age profile analysis the data scope for this entire exercise would be from 2018 till 2021 and the metrics that the stakeholders are interested to see is average waiting list and total wait list count now we are free to add any other metric that we would feel is important to this analysis we will see later on if anything else is required or not and in terms of the overall view of the dashboard we would need two pages first one for a summary View and second one for a detailed granular level analysis okay so if this is clear let's just move into the second phase of dashboard development which is to collect your data within power bi okay so now I have opened up power bi desktop on my system and in case you want to learn how to install powerbia desktop then I have already created a detailed video about that I'll place the link in the description in case you want to check that out now this is the data collection step and when I mean collection it simply means that I am collecting data from multiple sources and then importing that into my power bi environment now there are over 200 different data connectors available in power bi now here are few widely used connectors now this is a very crucial step because this is the stage where you decide the source of your data and this will Define how we are going to refresh the dashboard after deploying the solution for today's example we are going to use a central folder which will host all the files required for the dashboard refresh process now it will not be possible for me to cover all the data connectors within this video let me know in the comment section if you want me to cover any other data source like SQL server or power bi Services I'll be happy to do that but for now to keep things simple I would use the folder connector and show you how we can first of all use multiple Excel file collate them together and then import it into Power bi and again the data set that we're going to use is there in the link in the description please feel free to download the same in case you want to follow along now once you have downloaded the data and unzipped the files you will see something like this one folder for inpatient and one folder for outpatient and each folder will contain four different files representing each year now before importing any data set into my power bi one thing that I always check especially in the case where we have multiple files available is that all the files have the same number of columns and the column headers are the same so I'll just quickly check all the files foreign all the files within both the folders and everything seems good to import all the data into Power bi now it's very simple I just have to click on this get data button in all you will find a option which is called folder because this is the connector that we're gonna use and then click on connect in the next prompt you need to browse the folder path where you have stored your data set right now I'm importing the inpatient data press OK in the next prompt it will show you certain attributes about your files that you're loading and give you an option to load or combine okay I'm not going to click on load instead I'm going to click on this combine button and within this I'm going to click on combine and load okay I can also click on combine and transform but that is something that we will do in the next step for now let's just simply load the data so I'm going to click on combine and load evaluating and now it's loading okay in the next prompt it will give you a preview of the data set that you are loading so this seems fine to me I'm gonna click on OK and now my inpatient data has been loaded here I'm gonna follow the same process to load the outpatient data as well all right so now that we have loaded our inpatient and outpatient data in power bi which you can see here it's time to go to the transform step and apply some transformation to our data so to transform your data you need to click on this small icon here there's a drop down click here and it says transform data you can click on this and it will take you to the power query editor if you look at the left it has the tables that we have loaded it into our power bi and there are some additional queries which you can see here this is because we have used the folder connector it by default create some additional queries you don't have to worry about that the main thing that we need to see is that both the tables that we loaded are present here and when you click on any one of them it will show you the data preview in the middle section and on the right hand side it's showing you few other properties first one is the name of your table that you have selected and then at the bottom right you can see a applied step section this is very interesting because whatever transformation step that you apply on your data all the steps will be captured in this in the same order so it's easier for you to backtrack the steps that you've taken and make changes in case you have done any mistakes let me just quickly go through the data and check whether any transformation is needed or not okay so this is my inpatient data and I'm just gonna quickly scroll through the first thing that I would check is the data type I want to check if the columns that are loaded are in the correct data type or not so the way to check the data type is this small section here it says ABC and if I click here it will tell me what ABC means so ABC is a text and this is perfectly fine for me and there are other options as well which you can select and change the data type so if you see 1.2 means decimal numbers dollar sign means fixed decimal number and so on and so forth okay so Source name this is fine for me archive date this is in a date format this is perfect for me most of the times you would want to check the date column because sometime date gets captured into a text format and you would not want that so obviously you would have to make some changes and the way you change it is by clicking on this and selecting the appropriate option sometimes date column is captured as a text and you would have to make some transformation for you to convert that into a date format there is a detailed video on this topic on this channel itself I'll paste a link in the description which will have the entire playlist of power bear tutorials if you want you can check that out then let's just go ahead and check all the other columns all right everything seems fine to me let's just go to the second data set and check the same all right all good the second thing that I normally check after loading my data is the total number of rows that I've got in my loading process so to quickly check the number of rows that you have in this entire data go to transform and click on this count row here it will show you the row count of the entire data here and if you notice at the right side the moment I applied that transform step it's recorded my step here so now that I've checked my row count here I can go and validate this row count from my CSV file so this row count is fine I can just remove this step by clicking on this cross icon and that step will be removed secondly again I'm gonna check the same thing for outpatients transform count rows I'm gonna check the row count I'm gonna tally this with my CSV file and if it's good to go then I'm gonna just simply remove this tab and in case you found any problems then you would have to repeat your load process and while repeating you would have to check if you are receiving any errors or anything else so that you can Rectify your load process now once this is done the next step that I would like to do is I would like to Club both inpatient and outpatient data set together okay so that I have a single data which I will be using for my visualization purposes now when I was reviewing both the data I noticed two differences first one is the specialty the caller header for this is specialty underscore name however in outpatient it is specialty okay so I would want both the headers to be matching just so that the append process that I'm gonna do just after this is much simpler and easier so to rename it's very simple I can just right click and I'm gonna click on this rename section here and I'm gonna say specialty underscore name okay the second difference that I notice here is in inpatient I have an additional column which says case type I need this column in outpatient as well otherwise the append process will give me an error because the number of columns will not match and this case type column has two values in it inpatient and day case in patient simply means anyone who got admitted in the hospital for more than one day and day case is something when someone got admitted and then got discharged on the same day okay so what I'm gonna do is I'm gonna create a case type column in outpatient and I'm gonna put it in the same position as it was there in impatient which is right after specialty name and the value in that outpatient would be simply outpatient come to this add column section then click on this custom column name this as case type and in the custom column formula there is no complication here I'm just gonna say outpatient because that's the only value that I want to display just so that you know we can enter complicated formulas as well in this section like if formulas or any other aggregation formulas to derive a certain column now after this I'm gonna just press ok so it will create a new column and I'm gonna just drag it in the same position as it was there in inpatient so the next step in transformation is for me to append both of these data together and create one single table to do that I'm going to just come to home and in this you will see a a point query option here click here and click on append queries are new if you click on append queries here it will append the outpatient data into the inpatient data I do not want that I want to keep the raw data separate and then create a new table instead so I'm going to click on append query as new so it's asking what will be your first table my first step will be inpatient my second table would be outpatient and then press OK if you have more than two tables you can click on this three or more tables and then follow the same process so now it has successfully appended both the tables together I'm gonna rename this table by right clicking on it and clicking on rename and we can rename this as all data now we have just crashed the surface of data transformation there are many options available here but for now I think these steps are enough to formalize the structure and then move ahead with visualization go to home and now click on close and apply so remember close and apply is important otherwise it will not apply all the changes that you have done to your data the next step in this is modeling your data and to go to the modeling tab you click on this model view okay but when you click here you see you have three tables inpatient outpatient and all data which we created since we have already appended our inpatient and outpatient data in all data I would not want to see inpatient and outpatient in my report view just so that I can avoid any confusion okay so these two tables that you can see here I don't want to see them in my report view which is this particular view so I'm going to do is I'm going to go to modeling Tab and I'm going to click on this three dots icon here and I'm gonna say hide in report View I can also go ahead and delete it from my model I'm not gonna do that right now in case you require you can do that as well the next thing that I'm gonna do is while I was studying the data I noticed that I have specialty name which is a humongous number of Specialties and I would want to categorize them in some bucket so that it's easier for me to analyze it okay so what I've done is I have created an additional mapping table which maps all the Specialties to a certain Specialty Group I will include this mapping table as well in the data set so this was a manual effort that I did just so that while analyzing specialty in The Next Step I'm able to create buckets and visualize it in a better manner so let's first import this mapping table into Power bi and I'll show you how we will be using this mapping table to our advantage so to import again I'm going to follow the same process get data and then browse the file but this time since this is a CSV file and we do not have multiple files to import I'm gonna just click on text and CSV and click on connect now let's browse that file which is mapping under Source specialty now in this prompt if you see there is a small problem it's showing me the preview of the data but it's not considering my top row as the column header instead it has created its own column header as column one and column two I would want to change that so instead of clicking on load I'm going to click on transform data and with this there's a small drop down at the top left click there the second option which says use first row as header click there and it will automatically promote my first row as the header now everything seems okay I'm gonna just click close And apply now let's go back to the modeling Tab and see how we can model these two tables together now if you see Power bias automatically connected my tables together so this is my all data and if I click on this connection it is showing me that specialty name here is connected automatically with my specialty which is exactly what we wanted but it has also connected my mapping specialty with the inpatient data as well which we do not want so what we can do is we can just right click and delete and with this we have successfully modeled our data by creating a relationship between mapping specialty and all data there are much more intricate way of creating relationships following star schema or snowflake schema but since our data set it's not that complex we only need it once more all connection so we did that but the possibilities are endless in this modeling tab now sometime you would realize that power bear does not automatically recognize the connection between two tables and you would have to create them manually so let me show you how to create that first let me just delete this connection now I know in both the tables specialty name is the common attribute so I would want to connect the specialty name with the specialty in the mapping table so all I have to do is just click on the specialty name drag it and drop it on top of the specialty of the other table so this will automatically create a connection between it and if you notice it has created a connection and showing an arrow which goes from mapping table to the all data not the other way around this simply means that the filter context is Flowing from mapping table to my all data so when I use the specialty column in my filters then I should use the mapping table specialty instead of the all data specialty column okay if I use all data then it might create a problem so we will see that in the following steps now the modeling step is also done now it's time to move on to the next step which is data visualization blueprint where you will be discussing the blueprint of the entire dashboard with your team and finalizing a structure so let's go all right so at this stage it is expected that you might have already gained a detailed level knowledge about your data because you might have gone through your data a couple of times while you're transforming your data and also while you're modeling now don't misunderstand me transformation and modeling is something that we would have to revisit because at times it happens that while you're designing a dashboard you realize that there might be some supporting data or some supporting tables you might require in order to improve the dashboard performance or visualization so obviously we will revisit them later on however at this stage you should have a complete understanding of your data and how each attribute contribute to your overall business objective now with this knowledge you go into a meeting with your teammates to conduct a brainstorming session where you will finalize the dashboard design blueprint now this is just a high level idea of how the visuals will look like and this might change slightly over the period of development process so as you know we require two pages first one is the summary page so the summary page at the top left we would want count of the total waiting list for the current month and since we are showing current month I would also want to see the waiting list which was there in the last year for the same month so that we can have a reference point as in how we are performing in terms of last year then I would need a section in the middle which will highlight average waiting list for multiple attributes for example I would need a pie chart showing a bifurcation between outpatient inpatient and day cases waiting list also I would need a column chart of some kind which will showcase the relationship between the age profile and The Time band and lastly in the middle section I would need a grid of some sort which will show me top 5 average wait list by specialty because if you remember specialty analysis was one of the key requirements from the stakeholder so while I was studying the data I realized that in few specialties data had few outliers okay so if we just rely on average this outliers might create a problem in our analysis so I want the user to have an opportunity to see one more metric which might be useful when there are outliers so in this case I would like to add a median as well so apart from average wait list for all these three charts in the middle section I would also want to see the median numbers for all three charts in the middle section so we would have to add some kind of mechanism to incorporate that metric as well now coming to the last section the bottom one we would need a monthly Trend analysis on the basis of inpatient day cases and outpatient this will showcase the total wait list for a particular category at the top we would want few filters I would like to add a month filter a case type filter and a specialty filter now the next step is to create the second page which is the detailed granular level page and we would want to add some kind of navigation so that the user can jump from the summary page to the detail page and now in the detailed view we would want to design some kind of grid which can show the data in the most granular form and on the left side we will have few filters so that user can interact with the data also we need to make sure that the grid is not a simple table output from the raw data instead we can use some Advanced visualization features within power bi which can be applied on a grid to make the user derive more valuable inside of the data and get a better user experience now obviously when we are actually designing the dashboard we will make more enhancements which enhances the user experience further but for the time being this blueprint is more than enough to get us going in the right direction so with that the blueprint face is also over let's get into the next phase which is actually designing the dashboard so now in the design phase let's start building the charts that we decided in the previous step however before I start with the design process I usually enable two options and both of those options are in the view tab so come to the view Tab and enable grid lines and enable snap to grid what this will do is this will help you align your objects or charts in the entire canvas more easily okay now let's start with the design and to begin with let's start with the top left section here we needed two numbers which will be showing current month wait list versus same month previous year wait list for this we can use a card visual however for the numbers we need the total wait list column and we would also want it to be dynamic so that it can always display the latest month even if the data gets updated so how do we do that so first of all let's quickly insert a card here you can click on this button here which says card now for the numbers we cannot directly use the total wait list here because that would not be dynamic to create a dynamic field this is where we will use Dax in power bi for the first time and create a measure in order to create a new measure what you have to do is come here at the table where you are creating a measure and there's a three dot icon here click that and click on new measure the moment you click that you will see a formula bar at the top give a name to your measure so for now I'm gonna name it as latest month wait list and for the formula itself the logic that I'm trying to build here is I'm going to sum the total wait list column here and apply a filter on top of that and that filter will be based on the archive date column and that will retrieve the maximum date that I have in my data so how do we do that we're gonna use a Dax called calculate and within calculate I'm going to use sum and I'm going to sum the total column in my all data and then I'm going to apply a filter on archive date column equal to Max of archive date because remember I want to find the maximum value that I have in the archive date column close the bracket for calculate and press enter once you've created this you can drag this new measure into the Field section of the card and this will display the current month wait list similarly I'm gonna just copy paste this View and create a new measure for same month previous year so I'm gonna click on the three dot icon new measure and I'm going to name this measure as p y latest month wait list and the formula remains almost the same I'm going to use this calculate function sum my total column in the filter section again I'm going to use the archive date column however this time I'm gonna definitely get the maximum value using the archive date but outside of the max I'm gonna apply one more function called e-date edit enables you to move a particular number of months related to the current month that you have provided in the function so because I have used Max it will use the latest month and when I use edit we can navigate from that particular month to the same month previous year and to do that I'm going to enter the second parameter in this e-date function which is months and I'm going to say minus 12 because I want to go 12 months before the current month I'm going to close the bracket press enter now just drag this new measure into the Field section of the new card and as you see it is now displaying number or the count of waiting list for the previous year of the same month now in the middle section we discussed that we required three different charts first one would be a pie chart and I think we'll go with a donut chart because that looks bit better then we would need a column chart for displaying the relationship between Time band and age profile and I think for that a stacked column chart would be a better choice and finally we would need a grid which showcases top 5 Specialties and all these three charts will showcase average wait list values but if you remember while we were during the data we realized that there are a couple of outliers and only having average in the dashboard will not be sufficient so we would like to add median waiting list as well but in order to add two metrics to the same chart we wanted to create some kind of toggle mechanism using which user can easily switch between any metric they want there are many ways to do this but I'm gonna stick to a simple method which will be easier to grasp and you will be able to implement this very quickly so what you need to do is before we create that toggle button we need to create a blank table which will host the value of the metric that we are trying to toggle which is average and media let me quickly show this to you so in your home tab itself you will see a small icon here which says create new table click on that and within this you will see a small column rename this column by double clicking and let's rename it as calc method and in this you will see an option to enter a value into your row just double click there and enter average and then add another row by clicking on this plus icon and in this double click and write median name this table as calculation method and then click on load what this will do is this will create a new table in your report itself as you see but this is a dummy table and this does not host any value apart from those row values that we entered which was average and median now use this particular calc method column that we created to create a slicer okay so I'm going to click here and instead of this visual I'm gonna just click on this slicer option I would rather have a tile kind of a visual for my toggle button so what I'll do is I just click here and go to the format visual option slicer settings and from this style drop down select time arrange it so that it comes in an horizontal format and then I'll turn the slicer header off so that it looks more like a button to me also in the selection section I will turn on this single select option because at a particular moment of time I want any one of the buttons to be selected so right now average is selected now let's create two measure which will be our average waiting list and median waiting list so now I have created two measures first one is average wait list which contains the average of the total column and then median wait list which contains the median of the total weightless column now I need to create one more measure which will enable us to interact with this button and switch between these two metrics let's name this as AVG median wait list and we're gonna use a function called switch to switch between our two measures that is average waitlist and median wait list okay and the expression would be values and within values use the new table column that we created which was calc method so now what will happen is it will analyze the values that you have in your calculation method table and when the value is average which is this button value then I want to enable the measure average wait list and when it's median which is the second option here I want to enable the measure median wait list close the bracket and press enter now our measure is also ready now we can quickly drag visuals into a middle layer so for the first chart we'll drag a donor chart because this donut chart was supposed to host my case type I'm going to use case type here and also the new measure that I created which enable us to toggle the values which is average median wait list and now if you see if I select any one of them so press Ctrl and click on the median value the value will change again when I press average the value will change so now this is dynamic when the user select any one of the options the measures or metric used within the chart will also change dynamically second one is a stat column so let's use this one and within this this was something which will give us a relationship between Time band and each profile so I'm gonna use average median wait list in my Y axis The Time band column in my x-axis and finally the h Profile in the legend section now instantly I see a problem here I have few values which are duplicates like for example 18 months plus and 18 plus months so we need to check what's going wrong and make some changes to our data in the power query editor and this is why I said that transformation step is not a one-time activity you might have to revisit it after you've started your visualization depending on your current need so let's go to the transformation Tab and see what we can do so let's get to the power query editor by clicking on this transform data button and let's go to all data because that's what we are working with right now and within this I have two columns h Profile and time bands which I am using for that stacked column chart now if I open up this drop down here I have 18 plus months here and 18 months plus here both are same things only but depicted in a different form maybe because of human error or the way data was captured we need to do something to change this and make this as one also I can see that 15 to 18 months or 12 to 15 months all these months are getting captured twice in my filter although the values that are there are pretty much the same the only reason that I can think of is it contains some kind of spaces in between so we need to remove that and also I can see a blank value here which means couple of rows are blank so we need to change that as well so let's see one by one the first one we're gonna change this 18 months plus to 18 plus months okay so how do we do that select the column go to transform click on this replace values and find 18 months plus and replace it with 18 plus months okay now if I open up the filter and see I only see one value for 18 plus months and there is no other value for that particular category now let's look at all the other values to remove any unwanted spaces from your rotator what you can do is you can use the function of trim and to do that again in the transform tab there's a small drop down here which says format click there and click on trim this will trim all the values and remove any unwanted spaces now if I open up my drop down once again the number of items in my drop down has drastically reduced and all the duplicate values have now been clubbed together next is the blank values which we have in both time bands and also in the age profile column if you see so we're going to remove both of them together with one step to select both the column just click control after selecting one column and select the another column now click on replace values I want to replace blank with no input now press OK the blank value has been converted to no inputs same goes for each profile as well now once you apply those changes you're gonna see something like this but in this chart I don't see a importance of showing no input so I'm gonna filter it out and filter it out Simply select this filter section here and within this select time branch and press on select all and remove this no input this will automatically remove my no input section from the chart do the same for the age profile as well so that we can remove no inputs from the Legends as well so I'm going to select all from each profile remove this and that's it finally we would need a top 5 specialty Grid in our middle section for this we can use either a normal grid like this one this table or a matrix but I want to display it in a different style so I'm going to use this multi row card and within this I'm going to use specialty and average median weightless Matrix after applying this I'm going to apply a filter to this particular view in the specialty section now from the filtering type drop down select top n and in this space provided write five because you need top five items and the value on the basis of which you will find out the top five is obviously the average wait list measure that we created so drag that measure in and then click on apply filter and that's it now let's check our average median button this should work on all these three views so as you see it is working perfectly fine now we're gonna come back on the views again to make it more aesthetically pleasing but for now let's continue and create our remaining two charts for the bottom section which is a line chart for inpatient decades and outpatient so for the first line chart I'm gonna enter a line chart here use my archive date in the x-axis I don't want this hierarchy here so just click on this drop down and just click on archive date now because this line chart will showcase the total wait list I'm gonna use this total column directly in my y-axis case type column in my Legends and in the filter section I'm gonna say case type only day case and impatient I'm gonna copy paste this same chart create a same chart for outpatient and now only thing that I have to do is Select outputation from my filter so as you see now all the charts which are required have been created finally at the top we need to create few filters for the overall dashboard functioning which will be month case type and Specialty so let's enter few slices so create one slicer here now in this slicer let's enter first archive date column the moment you use a date column inside your slicer automatically give you this kind of a view where you have a scroll bar and a date selection calendar so I like this view I'm gonna keep it as it is enter another slicer and this time it would be a case type slicer so use the case type field I want to create this as a drop down so go to formative visuals slicer settings drop down adjust the view little bit and then copy paste and in the second one use specialty all right so if you see now our overall summary page has been created I'm going to rename this page as summary and let's insert a text box which will highlight the page name at the top select any font you want and the size of the font and that's it we're done now let's quickly jump on to the second page and create the detail page Now to create my second page all I have to do is click on this plus icon that you see here and it will insert a new page I'm going to rename this to detail and I can copy paste few of the elements from my summary page so that our process of creation is accelerated so I'm going to copy this text box these filters and paste it here while you paste it will ask whether you want to sync your views or not and I will say yes sync now this simply means whatever filters that I apply on my first page will automatically get applied on my second page okay now this one I'm gonna rename to detail and these filters I can just place it on the left so that I have some space for placing my detailed grid view now I'm going to need two more filters first one would be h Profile so I'm gonna change the values from here and second will be Time band so again I'm going to drag Time band from here now let's insert our grid or a table now instead of using this simple table here let's add some value to our data and use a matrix View this will give us an option to summarize our data and maybe create some other type of transformation within this View which will be beneficial for the end user for granular level analysis I'm gonna extend the view here and in this I'm going to use all relevant columns now coming to the case type column I would like to see case types in columns and then finally in the value section I would use the total column now just right click anywhere on this plus icon here and click on expand all and maybe you can reformat the column width as well so that it's much more easier to read with this we have wrapped up all the visuals which were required in our dashboard however at this current stage user will not be very happy to see your dashboard you should make your dashboard beautiful to view and interact with so we will now move into the section where we will be beautifying this entire dashboard and apply some very critical formatting properties to improve the overall user experience so let's go now the beautification part of the design process is very subjective and it depends on person to person but what I'm gonna do is I'm gonna show you my workflow which I normally follow okay so the first thing is to draw inspiration for your design and what better way to draw inspiration than to go to Google and search for it so obviously the first step that I do is go to Google and search for a keyboard let's say dashboard designs and go to images this will show me so many options that I can Implement in terms of themes and colors and I can choose any one of them now just in case if I do not find any inspiration on Google then I go to another website called stock.adobe.com and again search for a keyword let's say dashboard [Music] and this will also show me a lot of options okay now let's scroll down and choose one of the options let's say this one okay I like the design very much so I'm gonna just simply right click and save the image now the next step after you have saved the image is to extract the colors used in that particular image now the easiest way to do that is to come to another website again from Adobe it's called color.adobe.com and once you're here click on this extract theme and then drag and drop your image onto this panel so I'm gonna quickly do that once you drag and drop the image it automatically extract all the colors that are present in that particular image now if you see at the bottom it is showing couple of color hex codes for each color used in this particular image I'm gonna copy these hex codes for my reference and use them later on when I'm designing the dashboard now come back to your power bi dashboard and take a snippet of your summary page first so I'm gonna just copy this snipped image and then open up a PowerPoint file paste that image there and then adjust the page size as per my slide size similarly take another snippet of your detailed View and paste it on a new slide of your PowerPoint again I'm gonna adjust the size so that it matches the slide size now let's start inserting shapes so that you can design your dashboard accordingly and I like the rounded rectangle shape so I'm going to use that and the first shape I'm going to use is here try to align it properly at this stage itself because you would not get any chance to align it later on because you are now designing the background of your dashboard foreign to assign colors to these objects and the background so for colors I decided to go with these three colors the last three and let's see how we can use them so I'm gonna just copy this last one go back to my object I'm going to delete the snippet that we inserted earlier I'm gonna do the same for this one as well now for the objects I would want to use the second last color this one and for this object I'm going to use a gradient color but to create that gradient I'm going to use this blue color and let's just first apply the gradient thank you now I think we have designed the background of our Barbie dashboard now what I'm gonna do is I'm gonna extract these two slides as a image to do that I'm gonna Simply Save it as a PNG file so now I have successfully saved these images now I'm going to use these PNG files and apply it as a background on my dashboard to do that I'm gonna just come back to my PowerPoint dashboard click anywhere on the blank space here click on this format your report page and here you'll see an option called canvas background I'm gonna browse the image that we just created and then I'm going to do the same for detail page remember to turn the transparency of the page to zero percent otherwise you will not be able to see the background now it's time to format a chart so that can display the background properly I'm going to click the donor chart go to my format tab here and in general you will find first of all let's go to effect and in the background I'm going to turn this background off now I'm going to turn this title also off go to visuals and in the legend section text and let's turn this to White now in the detail label section let's go to values turn this to white as well so now as you see my donut chart has been formatted and it is displaying the values properly with the new background now let me make these changes on the other charts quickly and we'll come back and see how we can add some more feature to our views to make it more user friendly and Interactive okay so now I have applied all those changes to all the charts in both the pages but I feel that there is still a scope of improvement so let's just do some formatting in terms of numbers that we see here okay so I have added a data label on this chart but I see there are two decimal numbers here which I do not need so what we can do is to format a particular number I'm going to select the measure which was used in this particular chart and by the way all three charts uses the same measure so if we format this everything will be formatted accordingly now I want to reduce the number of decimals to zero so just select the measure here and at the top you'll see measure tools and within this you can just reduce the decimal to zero now one more thing that I want to add is this donut seems to be a bit blank so let's add a number in the middle which will show the overall average or median for the selected filter criteria at this point of time I think we can turn off the snap to grid option in grid line now I see these two numbers and I would need a divider in between this could have been done in the background design phase but let's do it here since we did not do it in the PowerPoint so what you do is just click on insert click on this shape icon and let's select a line go to the rotation of this line and rotate it by 90 degrees now in the style section turn off the fill and in the Border section let's have this color reduce the size little bit so that it can be adjusted okay looks much better now finally let's add some titles to our charts okay so now I have added all the titles in both the pages however when I look at the dashboard this one particular feature that we added which was this average and median selection there is no indication to the user that which particular metrics is currently being selected so we obviously know from the button that average is selected but I want an explicit title which will show that whether average is selected or median is selected so to do that we're gonna create a dynamic measure similar to what we created for this average median waiting list so I'm gonna copy paste this measure and create a new measure here let's name this as Dynamic title and in the switch values section what we're going to do is we're sticking if the value is average then give me a title instead of this measure key indicators patient wait list average I'm going to close this double quotes now and of its median then I can copy the same thing but this time it should be median and that's it now what I'm going to do is I'm going to use this Dynamic title inside a card we can use an already existing card which is this and copy at the top and instead of this overall metric I'm going to use the dynamic title from the formatting tab let's just remove the category label I'm gonna reduce the size of the values let's say 12. adjust the card little bit so that it aligns properly now if I click on median the value changes to median here and if I click to average the value changes to average so now user clearly knows what metric is selected for these three charts so with this we have completed the design phase now we will move into the face where we will build interactivity and navigation into this dashboard so let's go okay so now we're gonna try to build some interesting interactivity and navigation into this dashboard starting with let's check out couple of filters here so if I change this filter here I see that while selecting a earlier date my previous year wait list goes blank now this is very obvious because we do not have any data earlier than 2018 but from a user perspective this does not look very good in a dashboard so what we're going to do is we're gonna change this and change it in a way that this shows 0 instead of blank the solution is very simple just select the measure that this particular card is using so in this case it's using this py latest month just add 0 to this measure like this press enter now it will show zero do the same for the current month as well let's move on now if you see whenever I interact with this case type filter let's say we select day case every chart gets filtered accordingly but I do not want the bottom charge to be filtered because bottom charts are already filtered on case type the left chart shows me impatient and day cases and the right chart shows me out patience so I do not want this filter to impact this bottom view so to do that just click on this filter click on format and click on edit interactions you will see pop-up on top of each element which is placed on this page and that pop-up enables you to turn off any interactions that you have for the selected visual so in this case I want to turn off the interaction of this drop down with these charts so press on this none button here and I'll do the same for this chart as well so now no matter how many times I change the filter values here the bottom chart will not get impacted now let's check another filter specialty now when I select this specialty I can see that this chart goes blank but from a dashboard perspective this does not look that good so I need some kind of text at the background which can display an information that there is no data for this particular selection we're going to create a new measure I'm going to call it no data left and what I'm going to do is I'm going to write a formula so I'll start with calculate then apply a sum on the total column and the filter would be case type is not equal to outpatient let's see what this particular measure will give us I'm gonna use this measure inside a card this is giving me blank let me change the number so it's giving me some kind of numbers okay which is perfectly fine now I'm gonna select this measure once more and I'm gonna say if this particular measure is blank there's a function called is blank we'll use that then give me no data for selected criteria else give me blank I'm gonna turn off the category label here reduce the size accordingly and do some basic formatting now I'm gonna just copy paste this card and place it here as well but for this I'm going to use a different measure so let's copy this measure here and create a new one this one would be no data right but in the criteria instead of not equal to outpatient I'm going to say equal to outpatient and instead of this measure in the card I'm going to use no data right now ensure these cards are at the background not on top of the chart otherwise while user is interacting with the chart this will create a problem so go to view click on selection this is the card that we are talking about and the chart is here so all you have to do is just move your card below the chart also this is the chart here and the card we are talking about is this so I'm gonna move this below that chart so now everything is working fine now while I was looking at the filters I observed that although we have an option to select multiple items here by clicking on control and then selecting the items there is no option to select all items at once so how do we do that click on the filter go to slicer setting selection and turn on this option which says select all now when I open up it will show a select all option do the same for the second one as well and I think these changes needs to be done in the detail page as well so I'll quickly do that now talking about the detail page there is no specific option on the interface itself to navigate to that page unless someone clicks on this tab and you will soon realize that this might be a problem or there might be situations where you do not want the user to see the tabs at the bottom and instead navigate to that page using a link of some sort so how do we do that the easiest way is to insert a small button so I'm gonna say insert add a button and let's say information okay I'm gonna use this icon for now format this a little bit so I'm going to change the color of this icon and now I can add an action to the cycle so click on the button and the button tab here turn on the action and the action that I want to do is Page navigation and the destination would be detailed also when a user hovers over this icon I want a text to appear which tell them what this button does so now if you see when I hover over this it says click to view detail page and when I click on it it will take me to the detail page now because I'm in the power bi desktop version right now I need to press Ctrl before clicking but this is not the case when you actually share your dashboard in The Next Step so stay tuned for that but for now we are in the detailed view now we need a way to go back to the original page which is the summary page so we're gonna add a button here as well and this one would be a back button and for this you do not need to add an action because you already chosen a back button power bi automatically added that now if I click here it will take me back to the main page now before we move on to the next step I want to add one more feature into this dashboard which will enhance the interactivity a lot for now if you see when I hover over this chart it's showing me a standard tool tip with the day case number and inpatient number instead I want a pop-up window of some sort which will show me a split of specialty for that particular month and specifically I want to use Specialty Group if you remember in the initial step I showed you how we added a Specialty Group in our data transformation so we will use that column now and add a new pop-up window which will show a different kind of a view without wasting any of the real estate within this dashboard so let's see how to do that let's add one more page here and rename it as drill down and in this I'm gonna add a chart using the specialty mapping now if you remember we have already modeled this so we can directly use the total from this all data table and the numbers will fetch out perfectly fine and I'm gonna use a chart here so let's use this one also I would like to see the total number of wait list for that particular month in a cart kind of a view so I'm going to use this card now once you've done that go to the format your report page in the Page information setting click on allow use as tooltip go back to summary page click on this chart in the general section go to Tool here keep the type as report page itself and in the page use drill down as your tooltip now let's see what happens I'm gonna hover this and it's going to show me an entire page with that particular View and also if you notice when I move my cursor the values are also getting filtered accordingly however when I'm hovering it's showing me the entire page like this so I would like this page to be a bit more concise so go back to drill down move this card to the extreme left and this chart as well and then in the format your report page section go to Canvas setting change this type drop down to tooltip now if you feel that your chart is not getting displayed properly like this you can change the setting here instead of tooltip change to custom change the height and width as per your need and then go back to the summary and check if it's displaying properly or not so for me I think the pop-up window size is perfect I'm gonna resize my chart and the title here so that it looks good on that pop-up window also I'm gonna add a background to this pop-up window similar to the one that we have already added for detail and summary page using the same method that we saw earlier using the PowerPoint and let me just quickly do that and then come back all right so now I'm done with formatting of my tooltip and this is how it looks if you see the background is now matching with what we were using in the other pages and also the numbers are getting filtered as per the data point that you have currently hovering upon I've applied this tool trip to both the charts at the bottom now in case you want to see the settings that I've used for this canva setting as letter and the canvas background I've changed this image fit section to fit and the transparency is down to zero percent which is same as the other pages so with this we are completely done with the designing of your dashboard now it's time to study your data use the dashboard to find out insights about your data and then share your dashboard with the stakeholder using power bi services so next we will see how you can share this dashboard online now that we are ready to share our dashboard on power bi Services one important concept comes into our mind which is data security now in today's world data security is a huge topic and every organization will be very critical about maintaining the Integrity of their data so this is where we get introduced to the topic of row level security and I've created a detailed video on how to implement static rollable security and dynamic rollable Security on this very channel so I'll leave a link in the description if you want to check that out first now once you have implemented your RLS it's ready to be shared and with your stakeholders to share you need to First login into Power bi using your official email ID or your Microsoft developer account you would not be able to log in with your personal email ID in case you do not have either of them then it's very easy to register for a developer account there are multiple videos on YouTube on how to use developer account to login into a power bi you can check that out once you have logged in click on this publish button and then it will show you a prompt where your workspaces will be listed now since I only have one workspace which is my workspace it's showing me only one but when you're working in a team environment you might want to have multiple workspaces which will host multiple different dashboards and each workspaces can be shared with multiple users but for now I only have one so I just select my workspace and press on select this will publish my dashboard onto power bi services and once it is published you will see a prompt like this now let's go go to Power bi services and see how we can share that dashboard with our end stakeholder you need to come to app.power.com and log in with your email ID go to this tab which says workspaces and you'll be able to see the dashboard that you just published whenever you publish a dashboard it gets published in two layers first one is your report layer and second one is your data layer now because we are using the free version of power bi the sharing options are pretty limited so what you can do is you can just click in this dashboard here and this will open up the report and from here you can do is you can just click on the share button open this small option here and select specific people apply and then add email IDs with whom you want to share your dashboard in the Pro license and the premium license of power bi you will be able to create something known as an app that is much more versatile however unfortunately since we do not have that I would not be able to show you this but you can check its features and how to implement that on either Microsoft website or multiple YouTube videos which are available so that you can also Implement that in case you already have a pro license or a premium license one small change that I would like to do here is as you see in the left panel I can see this drill down tab which I do not want so I'll go back to my desktop version here and on this drill down page I'm going to right click and I'm going to say hide page go back to the summary page save your dashboard and then publish again now this time it will give me a prompt saying whether you want to replace your dashboard or not because I have that dashboard already published so I'm gonna say replace and it's going to publish that dashboard the reason I went to the summary page is it saves the instance of the dashboard which was published so if I keep my dashboard on drill down page and then publish on power bi Services also when I open up the dashboard it will open up on the drill down page okay now let's go to Power bi Services now here let's open this dashboard once more and if you see the drill down page is not visible and the page is opening on the summary page which is exactly what we want let's use the full screen mode here and see everything in action now I can click my average and read median button and this time I do not need to press the control button I can hover over these charts and it will show me the tooltip that we added also every aspect of my dashboard is interactive so now we have successfully shared the dashboard with our stakeholder and the final step in your dashboard preparation process is to first of all document your BAU process so that anyone in future can come in and start refreshing the dashboard and also if you want you can set up a refresh schedule if you are using a pro or a premium license but in this case since we are using a folder connector we will store the latest data in a folder and the person who's refreshing the dashboard can simply comes on the power desktop software and click on this refresh button here it will automatically connect to the folder refresh the data set and finally click on the publish button so that the latest data can be refreshed on power bi Services as well so with this we have wrapped up the dashboard preparation process it took a lot of efforts for me to prepare this entire video for you so if you find Value in this video then please consider subscribing to the channel and hit that notification Bell icon so that you do not miss any content that I upload and as always thank you so much for watching I'll see you in the next one

Channel: Pivotalstats

Views: 207,818

Keywords:

Id: G8ikAJele_s

Length: 66min 26sec (3986 seconds)

Published: Thu Sep 21 2023