Secrets to Building Excel Dashboards based on Non-financial Data

i'm well known for my dashboard tutorials many of which are based on financial data and as a result one of the most common questions i get is how do i build a dashboard using non-financial data and the answer is the same way dashboards give a visual snapshot of your business performance they typically show data spread over time and grouped by category in these visuals we're either summing counting or averaging the data and it doesn't matter if the values we some average or count are sales figures or head count or frequencies or some other non-financial metric the approach and charts we use are the same so in this tutorial we're going to build a dashboard with some non-financial data that i got from nasa on the past 59 years of humans in space and i promise there are no sales figures in sight and i'll be including some techniques i haven't demoed in any of my other dashboard tutorials so stay tuned before we get started i want to point out that you can alter the video playback speed using the cog icon in the bottom right slowing it down or speeding it up also this video includes some advanced features but if you're new to dashboards don't be put off because there are much easier ways to build dashboards examples of which you'll find in my dashboard video playlist linked to in the video description now i must confess that i know very little about space travel so please forgive me if i've misinterpreted any of the data in my analysis don't worry nasa won't be using this dashboard to make any business decisions so it's all okay before we start i just want to give you a tour of the dashboard i'm going to be building in this tutorial and highlight some of the features at the top we have the headline figures these give an overview of the key points now all the graphics you see here are created in excel the icons are available in excel 2019 onward and the stock image in the background is available in 365. the special font is created using wordart which is available in all versions of excel currently supported the access labels in the crew count by destination chart allow you to filter the dashboard by clicking on the items much like you can cross filter and highlight in power bi now when i click on an item you can see the selected bar is highlighted in the chart and the crude missions people in space and human days in space charts are all filtered accordingly notice that the people in space chart shows the male and female values on either side of the horizontal axis there's a sneaky way to create this effect which i'll show you and lastly the map chart shows the number of space travelers since 1961 by country and then we have flights by vehicle class okay let's get started now the data is stored in these csv files there's a huge amount of information here and i've only summarized a fraction of it in my dashboard but you can download these files from the link in the video description and use it for your own practice either recreating my dashboard or creating your own original i'm going to import the data using power query and then i'm going to load it into the powerpivot data model so i'm going to copy the address of the first file just copy path and then in my new excel workbook here on the data tab i'm going to get data from text csv in here i'm just going to paste in the file path i need to delete the double quotes at either end and click ok gives me a preview of the data tells me the file origin that it's chosen i can choose a different one it's also selected the comma delimiter but again i can choose a different one although comma is correct and we can see that by the preview and it's going to base the data type detection on the first 200 rows i can change that to the entire data set or not detect them at all i'm going to leave it at 200 rows that's fine and i'm going to click transform data because i want to just check that the data types set are correct before i import it into powerpivot so let's bring the window over here so you can see here i've got 10 columns and 329 rows in this particular table i need to change the data types here i don't want the time as well as the date so i'm just going to change the data type to date and i'm going to add a new step rather than replacing the current i'll just scroll across to the right and i'll check the data types we can see the data type in the column header this one's text that's denoted by the abc if i click on it you can see i've got the list of data types and i can change it if i need to but looking at this all my data types look correct so i'm happy with that so without closing the power query editor i'm going to go up new source file text csv it's found the folder based on the last file so i'm just going to select the next one in the list and import it again it gives me a preview i'm happy with all that so we're just going to click ok have a quick look at the data types and they all look fine so let's rinse and repeat that for the next files so we need space travelers and i know that that date is correct so i'm not going to check that space vehicles and again okay last one spacewalks so there we have my first five files they contain all my fact data so these are my fat tables if you like although if we look at the space travelers table this is actually a list of all the astronauts gender country continent and birthday so this is really a dimension table and i'm going to change the name of this table accordingly so call this dim crew now i need to create a calendar table because i want to be able to control the years displayed in the charts plus my data only contains the years 1961 through to 2020 and i want my chart axes to show 1960 through to 2020. so i'm going to right click new query other sources blank query this is just another way of creating a new query from within the power query editor so i'm going to create a blank query and i'm going to generate a list of dates using some power query functions so it's a list i open with curly braces i'm using the number from function and the date function to generate a list of dates from 1960 01 for january 01 for the 1st of january and then i'm going to use the dot dot operator which is going to generate a list based on this date through to the next date that i put in so again we're going to use number from and the date function and this time it's 20 20 12 for december 31 so i want full calendar year close my parentheses and press enter so what we see here is a list of the date serial numbers i need to convert this to a table so i can then convert those date serial numbers into proper dates in this dialog i just need to click ok now i can change the data type here to date and magically they now look like the dates that i wanted let's give this column a name date and we'll call this query calendar one more thing i need on my calendar table is the year field so i'm just going to add a column for the year it's defaulted to the whole number data type so that's correct and that table's ready to go now i need another table for my destinations and this is also going to be a dimension table and i want this sorted based on the count of crew for each destination now i have that information here in the missions table so i'm going to right click and duplicate this query i want to keep the crew name column holding down control i'm going to select the destination column and then right click remove other columns that leaves me with just the list of destinations and the crew name now i want to sort destinations based on the number of crew so i can add in a group by rule that groups the destinations based on the count of the rows so there we have the count of crew names per destination let's sort this in descending order so earth orbit is 1 iss is 2 and so on i can add a column that gives me an index from 1 3 to 6 and that's effectively a rank because it's numbering them based on the sort order which is based on the count now strictly speaking i could use the count results to specify the sort order of the destinations but to avoid confusion i'm going to remove this counts column because i don't want to use the count field from this table in any of my pivot tables the counts should come from the original missions table so i'm just going to select it and press delete it's gone let's give this table a name so it's dimension table for the destination okay i'm ready to close and load the data to the power pivot data model so in the home tab close and load close and load two and here i just want to create a connection so i don't want to load the data into any tables in this excel file i just want them to be put into the data model so click ok and on the right we see the query pane you can see it's loading the data might take a minute or two depends on how much data you're loading there's not a huge amount here so it's pretty quick you can see it's now done i'm going to close that pane i don't need it open it anymore power query has done its job let's open the data model now you can either do this from the data tab or we have a powerpivot tab here and then manage so the next thing i need to do is set up the relationships between the different tables and these relationships enable me to create pivot tables that contain data from related tables in the model and we can do that by the diagram view i find this the easiest way so i'm just going to rearrange these tables just to make them a bit easier to work with you can just left click and drag them around let's make these ones a bit bigger these are my fact tables so crude missions missions let's bring that one down there put this one up here this is a dimension table as is the crew spacewalks can pop over here this is also a fact table as is spaced vehicles to create the relationships between the tables you simply left click and drag when you release it on the field that you want the relationship between powerpivot goes ahead and creates that relationship we can see the direction of the relationship based on the arrow here we can see that this is the many side with the asterisk and this is the one side of the relationship so let's do that for the rest of the tables we need a relationship for the name between spacewalks and crew there we need a relationship between the destination fields between these two tables and this one now missions also has destination i don't want to create a relationship here at the moment i'm going to come back to that because there's something tricky with this relationship i'm going to show you when the time comes we also need a relationship between the calendar and our facts tables just left clicking and dragging and this one now i'm pretty sure i've set them all up we'll soon find out now before we start building the dashboard there's one more thing i need to do i'm going to go to the calendar table and here on the design tab i want to mark it as the date table and it's asking me which column is to be used as the unique identifier for the date table and it's the date column this first one here click ok and that's all done now strictly speaking in this particular dashboard i don't need it marked as the date table but it's a good habit to get into okay so i'm ready to go into excel i'm going to close down my powerpivot window enable the content and start building the dashboard the first thing i'm going to do is create the headline figures so let's just have a look at the top you can see i've got a header i've got some key information that summarizes the data that we're analyzing i've got this nice image in the background and some icons that create some interest as well as making it quicker and easier to interpret the data so let's set all of that up we'll go back to the file firstly i'm going to increase the row height to 87 it's going to allow me the space for my header data now i used a stock image and these are available by the insert tab and then pictures and then stock images you need a 365 license for this so if you don't have one you can get stock images yourself from the internet as long as they're free to use or you purchase them yourself these are come free with your 365 license so i'm going to filter based on space and this is the image i want down here you can see there are tons of images anyhow let's insert that one now it's already a slightly blurry picture so i'm going to stretch it out to column z and then i want to crop it so that it only goes to row one so on the picture format tab i'm going to crop i get these little pull handles here that allow me to crop it i want it to finish just above the bottom of row one because i want to select row one you can see it's selected behind the image ctrl 1 to open the formatting pane and on the border tab i'm going to insert a thick bottom border in this blue color click ok and there's our header background next i need to put in the heading so let's insert a text box i'm just going to draw up here and the heading is 59 years of humans in space let's format that font so first of all i'm going to make it 44 point which means i need my text box a bit bigger and then let's go up here i'm going to set the text color to the same blue as the border and then in the effects i want a reflection i'm going to use this first one type reflection touching the actual font then let's get rid of the fill on the text box and the outline so that the text just appears to be floating in space that functionality formatting the font like that is available in all currently supported versions of excel so everyone has access to that now the next thing i want to do is insert some icons these are available in excel 2019 onward you need to be logged into the internet because they're downloaded from the internet again i want space themed icons so i'll go with this astronaut here and the rocket we'll grab some planets and the moon then i also want a calendar i'll grab this one and i want a world icon so insert the eight icons there they are there i'm going to resize them and i'll make them all white for now these ones that's the moon these ones here are just sort of some decorations so they're going to be slightly different i'm going to make them a little smaller and these ones will be blue in keeping with my color theme all right i'm just going to spend a few seconds lining these icons up roughly to where i want them when i insert my values so the rocket goes there astronauts over there calendar is roughly there and the world is under column v okay so we've got the icons roughly in place now i need some text boxes so start with this one is for gender if it's selected i'm just going to make the font a bit bigger i want the font to be white which means i can't see it now so let's get rid of the text box i want no fill and no outline okay we'll roughly leave it there i'm going to hold down shift and control and drag it across just to copy it let's change that to days in space and we'll resize that okay and again holding down shift and control this one's going to be rockets launched and this text i want to be right aligned so that i can squish it up against the rocket and then holding down shift and control again this one's going to be moonwalks we need to make this one a bit wider and finally this one is astronaut nationalities and a big s there okay let's center that right so we have our labels in we'll tidy up the alignment of them and what have you a little later on once we've got all the other data in now we're ready to do our first pivot table that's going to feed the numbers into our headline area so let's give this sheet a name i'll call it dashboard and we'll insert another sheet that's going to contain our analysis now normally when building reports you should create a new sheet for each pivot table so that they don't overlap one another when you refresh the report however because this data is static i don't need to worry about that so i'm going to store them all on one sheet just to save some time the first pivot table i need is going to populate the gender values so let's go and build that i'm going to insert a pivot table from the data model that's where my data is it's in powerpivot i'm going to put it on an existing worksheet let's bring the fields over here so that we don't have to zoom and pan so much we'll just resize this okay so this data comes from the missions table and i want to count the crew name it's going to default to account because it's text and i want to split this out by gender across the columns so there are my values i might just insert a row above that pivot table control plus to insert the row now if you remember let's look at the dashboard again the values for the female have an f at the end and the values for male have an m at the end and you can see that this text box is linked to the analysis sheet cell d2 so let's have a look at that cell d2 is up here so i've created that value with the f appended to the end using a formula so let's do that so equals text the text function allows me to take this value now because it's in a pivot table the get pivot data function automatically gets populated and that's fine i'm not going to mess around with that we'll leave it if you don't like get pivot data then you can put the cell reference in in this case it would be c5 but i like get pivot data so we'll leave it there and the format is going to be comma separated so that's my text function that's going to return the value but now i want to also append f to the end so there's the value that's going to populate the text box on the dashboard let's copy that and paste it for the male value and all i need to do is change this to reference the male value in the pivot table and this becomes m so there's our two values that we're going to link to a text box so on the insert tab i'm going to insert the text box draw it in up here it's a bit hard to see once it turns white it's much easier so with the text box selected in the formula bar type in an equals and then come to the analysis tab and select the cell so the female values are in c2 and the male are in d2 now i can go about formatting this so we want the font to be white let's go and get rid of the fill and the outline and then i can make it a bit bigger we'll go right up to 24 point which means my text box needs to be bigger all right that one's done let's ctrl shift to copy that and then i'm going to change this to d2 now unfortunately when you do that it loses all the formatting so let's format it white again and 24 point need to make the box a bit bigger and let's then line them up so let's align that to the right make that one aligned right as well okay so there's our gender information i could probably make this font a bit bigger there we go so that's the first one done let's go ahead and create the pivot table for days in space this is just the count of the number of days in space so i'm going to insert another pivot table from the data model and from the missions table i just want to sum the duration i'm going to go in and format that number so that when i feed it through to my text box it automatically picks up the formatting i don't need to faff about with the text function so i'm going to copy this text box and let's just check what cell it's in b10 let's just change the reference to b10 now i tell you why i'm doing it like this let's just format it first this one's going to be 32 point all right now the reason that i've typed in the cell reference rather than clicking on the cell in the pivot table is because remember when we select the cell in the pivot table it puts the get pivot data function in where you can't have a formula up here linked to a text box you can only reference a cell so let me demonstrate with the rockets launched let's copy that across let's go and create the pivot table so here i'm going to insert a pivot table from the data model press enter to say okay so the rockets launched is from the space vehicles table and i want to just sum the total flights again we'll format the number and this time i'm going to select the text box type in equals and then select the cell in the pivot table you notice it puts the formula in when you try and press enter it gives you this obscure error message essentially the problem is you can't have a reference to a formula connected to a text box it will only take a cell reference so the work around is to maybe just click below it and then change it instead of b16 choose b15 let's go and format it and make it size 36. so there's our rockets launched all of these could come over here a little bit let's make that a bit bigger so we can see the comma all right so i'll copy this one for moonwalks let's go and create the pivot table insert from data model click ok or you can press enter there now this one is from the space walks table and i need a filter here so i need the moonwalk in the filters and here i want to filter for moonwalks that are true and then i just count the names it's in cell b21 so let's change this to 21 and then go through the formatting again which is super annoying okay that text box is a bit bigger than we need but it does the job all right moonwalks is done last one is the astronaut nationality so just copying that text box ready insert a new pivot table from the data model press enter this one is from the dimension table for crew and what i want to do is count the number of countries what we see there is it's counting the duplicate countries so i want a distinct count so right click value field settings scroll down to the bottom and distinct count now this distinct count option is only available with power pivot data model pivot tables you can't get a distinct count this way using regular pivot tables so click ok and that's more like it it's 43 different countries represented in this space tarot data so that's in cell b25 let's change that to 25 format it and our headline figures are done now we can just do a little bit of tidying up let's make this glow bit smaller and to separate the data i might insert some vertical lines just to give each section some separation so i'll insert a vertical line just holding down shift draw it keeps it vertical even if you move your mouse left or right slightly and i want it to be white now with it selected ctrl and shift and left click and drag to copy it and again to roughly insert it in between each section okay we can move these over a little bit our headline figures are all done and we're ready to start building the rest of the chart so let's go back and have a look at the dashboard the next chart to create is the crew count by destination bar chart now if we take a look at how it works remember if i click on one of the items it filters not just this chart it highlights what's selected but it's also filtering other charts and the way we do that is this is actually a slicer you can see as i move it away there are the actual vertical axis labels behind and the slicer sits on top you can see it's a slicer it's activated the slicer tab on the ribbon i've just formatted it so that it looks like it is the chart axis now i think what's happened is it actually chopped off some of those so there we go that's better and if we look at the analysis tab this is the pivot table here that supports that chart and slicer if i select one of the items you can see in the pivot table the first column contains the count for each destination the second column only contains the count of the destination selected in the slicer and if we look at the chart and go into the formatting let's bring that over here i just press ctrl 1 to open it if we look at the fill you can see for this series it's gray but for the selected series it's blue and what i've done is align them to overlap 100 so the selected destinations series is sitting on top of the count of crew names series in the chart so it's just a little bit of smoke and mirrors to make it look like we have that effect in power bi of cross filtering and highlighting but it's all done in excel and to do that we need to create a disconnected table and a dax measure so let's go back to the file that we're working on and build that now don't worry too much if you don't follow along completely because there's a video on this technique that i'll share in the video description so let's start by inserting a pivot table on the missions table i want to count the crew name by destination now it's very important here that the destination field is the one from the missions table and this might be counter-intuitive if you've used powerpivot before because normally the row and column labels fields come from the dimension table so you'd expect me to use this destination field rather than this one in the fact table but stick with me here next i'm going to insert a slicer for this destination field and again it's important that it's the one from the dimension table there is there behind the field list when i select items in this slicer it doesn't filter this pivot table at all and that's exactly what i want now the next thing i need to do is set up the inactive relationship so back in the powerpivot model on the diagram view i want to set up an inactive relationship between the missions table and the destination table so left clicking and dragging it sets up an active relationship by default it's this on here i'm going to double click and deselect active now you can see it's a dashed line indicating it's an inactive relationship so that's done let's close the data model nothing's changed here it still doesn't filter my pivot table that's fine what i need to do next is write a measure that does listen to the slicer and only counts the item selected so let's create a new measure i'm going to assign it to the missions table and i'll call it selected destinations so in here i want to calculate the count of the crew name and i need to activate the relationship so i'm going to use relationships between the dim destination table and the missions destination close use relationship let me just control and scroll on my mouse to make that a little bit easier to read and then close parentheses on calculate we can check the formula is correct it is and let's set some number formatting we'll make it a whole number click ok so now we can see that the selected destination is only calculating for the destination selected in the slicer let's insert the pivot chart so this is going to be a bar chart bar charts are good when you've got long access labels so let's click ok right click to hide all the field buttons i don't need the legend or the gridlines i'm going to add data labels instead but i'll do that afterwards the other thing i want to do is sort this in ascending order and you'll notice in the bar chart it sorts it in descending order and that's exactly what i want let's format these series to overlap so ctrl 1 to open the format series 100 overlap and 30 gap width just makes the bars a bit wider now you notice that the labels in the slicer are not quite in the same order as the chart and if you remember back in power query we created the summary table that counted the crew based on the destination and then we added the ranking or the index column so what we can do in powerpivot is specify the sort order using that index column so let's go in and do that we'll go back into the powerpivot model and then on the dim destination table i want to specify the sort order for the destination column so on the home tab sort by sort by column that's going to be sorted based on the index column this is going to force the sort order in the slicer to respect that setting so now they're sorted in the correct order i can right click and go into the slicer settings and turn off the display header and then we can spend a little bit of time formatting them but i'm going to do that when i move it to the dashboard first of all i'm going to format the bar colors so here i want this to be a gray and this one is going to be the blue i'll select both of them holding down shift ctrl x to cut let's put it in the dashboard so first of all let's format the slicer a little bit a rather dark theme that's then in keeping with the colors in my chart but i don't want the border on the slicer so selecting it i can right click and duplicate this style and then modify it to suit my needs so i'm going to format the whole slicer and on the border i want none click ok and ok now i just need to go and apply this style which is up here in the custom group to my slicer and there it is next thing we can do is give the chart a bit more space for the slicer and let's bring it to the front so at the moment it's sitting behind the chart so we'll put it on top let's move them over here and we'll make the buttons a bit bigger all right we can use the arrows on the slicer settings and that's not too bad let's make this okay let's add some data labels now so i just want labels on this series because i want them always to be displayed not just for the one that's selected i don't need the horizontal axis because now i have my data labels so let's turn that off we can make the chart a bit smaller and bring that back down all right i forgot to put the chart title in let's quickly go and do that that means i need to make this smaller and my slicer all right that wasn't too painful move this over here and this is my crew count by destination all right ready for the next chart so let's go and have a look at the dashboard let's do these two next the map chart and the flights by vehicle class so back in now file on the analysis tab i'm going to insert pivot table from data model click ok so the map chart is looking at at the data by country and the number of missions based on the count of the crew name now if we try to insert a map chart from this pivot table we select map from the list you see we get a message that says you can't create this chart type with the data inside a pivot table so what we need to do is trick excel into creating a map chart and then referencing the pivot table as its source data so copy the data i'm just going to paste it as values and then from there i can insert a match chart and all i need to do is reference the match up back to the pivot table so right click select data i'm going to edit this so instead of column o we want column l so replace o with l and then again for the country names they're currently in n we want it to reference column k so replace n with k and now you can see the map chart is referencing the pivot table this data has done its job so i'll just delete it with the map chart i don't want the chart title so let's deselect that i'm going to put the legend at the top it's going to act as a kind of title so let's make the font a bit bigger and we'll cut ctrl x and paste it in here so it's a little bit big i'm just going to hold down alt to snap it to the grid behind we'll resize them a bit better later on so there's my map chart now one thing i want to do is actually remove the outline on all of these charts so no outline let's do the same for this one no outline and i might just bring this one down a smidgen along with the slicer because it's slightly overlapping the border on the cell at the top now we also don't need these grid lines so let's turn them off and it's looking more like a dashboard all the time okay the next chart i want to build is this one here that's the flights by vehicle class so let's go ahead and insert the pivot table for that i'm going to pop it here below this one remember you should create your pivot tables on a new sheet don't do what i'm doing here so this one's using the data in the space vehicles table and it sums the total flights by class pretty easy let's sort it in largest to smallest descending order i'll insert a column chart i'm going to right click and hide all the filled buttons now i don't need the legend and i'm going to get rid of the grid lines we'll put in data labels which means i can turn off the vertical axis now let's just format this number so that it has comma separators that's going to feed through to the chart this looks a bit nicer we'll give it a title flights by vehicle class since 1961. now let's control x to cut that out and control v to paste it in i'll just resize it and we'll do the same vertically let's make these columns a bit wider so in the formatting i'm going to go in here and change the gap width to 30. okay now one thing i might do is change the name of this label here and the way i do that is in the chart data it's picking up this name here so i'm just going to change that to say space travelers since 1961 and if we go back and look at the chart you can see it's picked up the new label that looks better okay let's have another look at the dashboard that we're building so the next chart we want to build is this one here for crude missions you can see it's a stacked column chart now it responds to selections in the slicer and that means we need a special measure that references that inactive relationship between the destination table and the missions table so let's go back to our file and create that pivot table and insert pivot table from data model so this pivot table let's just dock that is going to look at the years down the rows and then i want the destination across the columns and i want to count the spacecraft from the crude missions so there's our data let's insert the stacked column pivot chart i'll make it bigger get rid of these field buttons and we'll give it a chart title and remove the grid lines and add the legend to the top so just move those up there like that let's give the chart a title crude missions and i want to change this format so that the color is shades of blue and gray let's cut this out ctrl x and we'll pop it on the dashboard i'll just resize it a little bit okay so now we need to link this chart to the slicer i'm going to right click the slicer report connections and i want the last pivot table i inserted which is this one here pivot table 9 and you can see now it's filtered to show just the data for earth orbit you can see the years go from 1961 through to 2009 if i pick iss we get 98 through to 2020 and as i keep changing the destination i get different years shown on the horizontal axis and that's not what i want i want all the years displayed all the time and the way we do that is with a custom measure so let's go back to the pivot table and i'm going to go to powerpivot tab and write a new measure i'm going to call this spacecraft missions and the formula is if let me control and scroll my mouse wheel to make it a bit bigger is blank count spacecraft so on the crude missions table i want to count the spacecraft and if that count is blank then i want it to return zero so that's going to ensure that if you look at the pivot table behind instead of these blanks i'm going to have zeros there that's going to force every year to always be present in the pivot table if it's not blank i just want to count the spacecraft close parentheses on count close them on if let's check my formula oh i've missed something out oh i didn't do close my is blank there we go all right so that's my formula let's format the number like so now it's showing both measures in the pivot table so let's go to the field list i'm going to remove my original count and now i just have my measure spacecraft missions you can see there are zeros where i don't have any counts and that's exactly what i want because when we go back here now when i select an item in the slicer you can see all the years remain now we have this blank here and they look a bit cluttered so let's go and filter out the blank and then back on the dashboard and control one and i understand the labels to have an interval of every 10 which is every 10 years and instantly it looks less cluttered let's get rid of the chart border and i can probably bring it over here now one other thing i want to do is bring the legend over here that way it's closer to the chart title and it all updates nicely so that one's done now the next two charts are very similar let's just have a look they're similar in that they both respond to selections in the slicer the years are always static on the horizontal axis but otherwise the data is different particularly this people in space chart because it shows the female values on the negative side of the horizontal axis and there's a little trick to achieve that so let's go back to the file and we'll create the pivot table for this next chart so over here insert pivot table from data model click ok so this one's going to use the gender in the columns we want the year down the rows and then again i need to create a custom measure so powerpivot measures new measure it's going to be called people in space and we'll scroll in a little to make the formula bigger so again if is blank now here instead of just counting the crew names we need to activate that relationship between the destination table and the missions table because the count that we want to perform is based on the crew name in the missions table so i'm just going to alt and enter to go down onto the next line so we want to use the calculate function to count the crew name and i want to activate the relationship so use relationship between the dim destination and the mission's destination we'll close parentheses on use relationship close it on calculate and close it on is blank if that count is blank then we want it to return zero otherwise let's just make this dialog box a bit bigger otherwise we want to calculate the crew name count and use relationship between the destination dimension table and the destination on the missions table close use relationship and close calculate let's check the formula oh i've missed off a parenthesis there we go number format to a whole number click ok now i don't need the grand total we also need to filter out the blank year now remember that the female values are on the negative side of the axis therefore i need to make them negative values now i could write a really complicated measure to do this but my data is static and i don't want to overwhelm you any more than i probably am so what i'm going to do is just copy the years and i'll paste them as values so this is the year field then we want female and male and here i want to minus cell x5 and equals cell y5 so it's just going to bring in the figures copy that down so this is going to be the data for my chart it's linked to the pivot table so when the pivot table is filtered by the slicer this data is going to update now i can just insert a regular column chart now what it's done here is it's interpreted the year as a series so let's right click and get rid of that we'll remove it from the series and then edit the category labels to pick up the year field which is what it should have done in the first place okay that looks better let's move the chart title over here we'll put the legend at the top we need to do some work on the axis label so ctrl 1 to open the formatting so here i want the labels positioned to be low so it's out of the way let's set them to every 10 years now the other thing that you probably can't see that well if i change the gap width to 30 you can see that the male and female columns don't quite line up so we're going to set the series overlap to 100 and now they're aligned correctly let's format the color so we'll leave the male series as gray that's in keeping with our theme and the female series is navy blue we'll give the chart a title going to be called people in space and we'll move a legend up to the top which gives us more room for the plot area now i think i'll turn off the grid lines and the last thing i need to do is change the vertical axis labels so that the negative values don't show the minus sign so let's go into the number formatting and down here i can create a custom number format now it's detected the one i did earlier i'll zoom in on this and you can see the positive format which is the first one is exactly the same as the negative format and what that essentially does is just hides the minus sign you can see in the chart now they look the same so let me ctrl x to cut that out and pop it on the dashboard and we'll resize it holding down alt just going to snap it to the grid i can probably bring it up a little bit let's get rid of the border okay there we go oh this one needs the border gone as well let's do that while we're here all right so the last chart we want to do let's go back to the dashboard is human days in space it's a stacked area chart let's go ahead and build the pivot table so i'll insert a pivot table from the data model now there's nothing particularly tricky about this chart but like the previous two it's going to be filtered by the slicer and i want the years to remain on the horizontal axis even if there's no data so that means another measure again we have the years in the rows and the gender in the columns and then i need to create a new measure we'll call this human days in space and the formula is very similar to the last one if is blank then calculate the sum of the mission duration and use the relationship between the dim destination and and the destination on the missions table close parentheses there and close it on calculate if that's blank return zero otherwise we're just going to do the same thing so this time i'm just going to copy formula paste it in one more closed parenthesis on if that one needs another parenthesis there we go all right set that as a whole number let's filter out that blank year and now i can insert a stacked area chart now i want the female values to sit at the top so let's change the order of these and select clicking and dragging we'll get rid of the fill buttons and make it bigger let's set the colors in keeping with the other charts so control one and in here i want the male series to be this gray color and the female series to be navy blue we'll put a title in and we'll put the legend at the top this chart is going to be called human days in space and we'll move the legend up here which gives us more room for the chart all right control x control v let's go ahead and resize it just holding down alt i need to fix the axis so let's do that we'll make the labels every 10 and we'll just try and line everything up so that 1960 is in the same place on all of the charts and 2020 is also in the same place now let's just make these columns a little wider i notice they're a little puny so we'll make that 30 that's better now the columns on these two charts pretty much line up as well so the last thing we need to do is make sure that the slicer is connected to these charts and if i click on it you can see it's only filtering this one right now so i'm going to right click go into report connections and set it up on 10 and 11 pivot tables 10 and 11 which are the ones people in space and human days you can see it's now filtered let's get rid of the outline on that chart and we're looking pretty good we could probably make these two charts a bit smaller so there's more room for this one on the one page i can just turn off the formula bar to try and squeeze it in there we go i hope you found these techniques useful you can download the file for this lesson from the link here and if you like this video please give it the thumbs up and subscribe to our channel for more and why not share it with your friends who might also find it useful thanks for watching you

Channel: MyOnlineTrainingHub

Views: 45,271

Rating: 4.9637561 out of 5

Keywords: excel dashboards, dashboards

Id: PvlH03kvG7c

Length: 52min 33sec (3153 seconds)

Published: Thu May 20 2021