 Hey guys welcome to SSUnitex to see this side and this is continuation of Power BI projects. So today we are going to discuss one more project in the Power BI and this is based on the Olympic Games. So here we are having the data set from last 120 years and by using that data set we have designed this dashboard. So let's get started and try to understand about this dashboard. So first of all here we are having four filters as you can see first is the country, second is the year, then here we are having the gender which is male and female, below of that we are having the seasons. So we have two seasons as you have already know I guess for the Olympic Games. So first is summer and second is the winter. So we have these four filters by which we can filter the dashboard. After that in the right top you can see we are using three cards. So first card is going to show the value for the count of gold medals and then silver medals then bronze medals. So we are having these three cards by which we can check the value. After that here go to on the left side and below of the filter we can see the youngest medallist. So here we can see the name Dimitrios. So he is the youngest medallist who got the medal in the age of 10 and he got only one medal. So that is going to display on these three cards. So as of now you have seen we are using six cards, three cards for the gold silver and bronze total count and three cards for the youngest medallist name then total medal then age. So by using DAX formula we create all these cards. After that go to on the tree map chart. So here we are going to display the value for the top five countries by medals and after that go to on the bottom left side we can see gender and country wise medal count. So United States is the highest count so it is in the green color and then yellow and if we go on the right side then we should see the red color. So the count is very less that's why we cannot see this properly. After that in the middle bottom we can see sport wise medal count. So here we can see the sports that is first of all we can see the athletic and the bronze, gold and silver. So we can see all these counts and if we scroll on the right side we can also see the total value of the count. In the right side we can see player wise medal count. So here we can see the list of the players and how many medals he got. So first of all as we can see we are having the Michael Fred. So he got the medal 28 so highest count in the history of the Olympic games. So he is in the top one and total 23 gold medals, three silver medals and two bronze medals we can see and if we filter this data up to 2016 only. So here we can see we have only one session that is the summer and youngest medalist that we can see Ren and Ace that is 15 and total medal two medals he got. And in the right side we can see again Michael Fred so five gold medals in the 2016 only and one silver medal total six medals he got. And here if we see first we are having the athletic second we have swimming then rowing then football honky all these sports and the total count of the medals like 192 then 191 144 then 106 and all. So this we need to create by using the source that we have. So before going to design this dashboard let's have a quick look of the source. So our source is Excel file so this is the Excel file and here we are having the data for 120 years of the Olympic history so first column which is indicating the IV then the name then year then season which is a summer and winter only then we can see the city then sports so in the sports we can see the basketball judo then we can see the football and other games then events we can see this in the right side we can see the medal so in the middle we need to concentrate only the gold medal bronze medal and silver medal if we are having any NA which is not available so we need to remove those in the data set and if we go on the left side on this then we can also see the year as we have already seen then the games which is the concatenate between year and season then NOC then teams so this is the country of that player go to on the left side we can see the weight of that player then the height of that player then the age on that particular year like if we see 1992 then age of the player on that year so it is 24 then the gender which is the male or female then name and ID so these are the total columns that we have so by using this data set we need to create the beautiful dashboard that we have already seen like this one so go to on the training one and try to get this data into the power bank desktop and after that try to design this dashboard so go to on the power bank desktop with the blank one so this is the training one first of all here we don't have anything so now we need to get the data so we directly click on the get data or we can directly click on this import data from Microsoft Excel file here we need to select the file and we can click on the open so it will take few seconds so we need to wait so here as you can see we are having four tables first is athlete events after that the country definitions and the others two are the same so I am very happy to use the athlete events so we can select that and after that we need to click on load so it will be loaded into power bank desktop so we need to wait few seconds that data set will be loaded so now data has been loaded successfully first of all if we see in the existing one so here we are having this image which is the Olympic image so you can directly download this from the Google and you can use this in your dashboard so go to on the training one and try to get this image so here go to on the insert and try to click on the image so we need to select the image and we directly click on the open so this image should be loaded in your power bank desktop now we can make it little bit smaller like this so this is the image after that we need to get the slicers that we have so first we have the country slicer so let me click on the slicer second we have the ear slicer so we can click on the ear third we have the gender slicer and the last we have the season slicer so we have total four slices let me put it on the top side and make it a bit smaller and we need to drag and drop the team over here so we can see the team so this will have the country name go to on this and in the right side we can see this down arrow we can click on that and make it as drop down instead of list so we have this in the drop down now we can check the values of the countries so these are the countries as we have now go to on the format of this and try to do the formatting so first of all go to on the background color of this and try to use the background color as gray now go to on the title of this and try to use the title as country so we can call this as country after that go to below of this the background color that we want to use this dark blue the font color that should be white that looks good after that go to on the slicer header so we don't want to see the slicer header now go to on the items so in the items we want to see this as font color should be black so we can use the black and the background color that should be white so we can select white now it looks good we can make it a little bit smaller and smaller from the right side as well now go to on the second slicer and try to configure that so make it smaller from the bottom and make it bigger now we need to have the ear so here we have the column here so we can directly drag and drop in the fields so it should have the ear like this now go to on this country and try to use the format of this in the ear slicer so here we can see this go to on the format and try to do the formatting go to on the title so in the title this would be ear so we can call this as ear after that go to on the numeric inputs and font color it should be black and the background color that should be white so we can set that so now it looks good and we can also make it a little bit smaller from the right side now we need to configure the third one that is the gender so we can directly put it here and make it a little bit smaller and like this now here we need to drag and drop the gender so we can directly drag and drop it here go to on the format and here we can see the slicer header so we don't want to see that now go to on the general in the general we can see the orientation so it should be horizontal instead of vertical so it should be good now we can little bit smaller and here go to on the items and in the items the font color that should be white and the background color that should be dark blue i guess and we can also increase the text size now go to on the general and here we are having an option for responsive so we can off that so now we can see the male and female the last that we need to configure for the season so we can put it below off that and try to make it a smaller from the right side and here we need to drag and drop the season first of all go to on the field and here we can drag and drop the season now we can go to on the first one and try to use the same formatting for the second one so now it looks good like we are having the male and female then we have the summer and winter so now we have configured about the filters next thing that we need to configure for three cards so that will have the gold silver and bronze so how we can do that so first of all go to on the insert and here go to on the shapes and try to add a oval now we can put it on the right side of this slicer like this and we can also make it a little bit smaller from the bottom side now let me copy this and paste one more time so second that should be for the silver and third that will be for the bronze so we can try to copy it and paste one more time put it on the right side so now we have three ovals first will be gold then silver then bronze so let me click on the first and here go to on the fill in the fill we want to see as gold color second it is already in the silver go to on the last one and here let me try to use the brown color that will be for the bronze now let me add three cards click on the card so this is the first one and try to put above of that now here let me try to drag and drop the medal so as you can see the medal now we can use the count so this will have the total count now go to on the format and go to on the data level and here we need to decrease the size of this so it should be 25 instead of 45 now here display unit it should be none and one more thing that we need to take here go to on the filter try to drag and drop the medal on the filter on this page so it will be going to filter on this page we need to select everything except this not available now here count of the medal so we need to count the medal for the gold only so we can drag and drop it here and let me select only gold so now we can see the count of the gold now here go to on the background color we can off that and here we can also go to on the data level and here let me try to change the font family that should be aerial and go to on the category so here in the category we can see the color it should be black after that go to on the fields instead of pound of the medal we can rename this as gold so now we can see the count of the gold let me try to copy this and paste one more time and put it on the right side of this for the silver try to copy this and paste one more time for the bronze go to on the silver first of all try to rename this as silver now go to on the format and here we don't need to change anything just go to on the filter and here we need to select silver instead of the gold now in the third one we need to select the bronze instead of the silver so here we have selected all these so not on this page on this card only so here let me select bronze instead of the good go to on the fields and here try to rename this as bronze now we can off this and go to on the format just try to change this as white of this font color and also in the category so now it looks good we can also you know increase the size of this or and we can also make it bold so that you can do on your own so here we have done about these three cards next thing that we need to take care go to on the existing one and here we are having this tree map chart so in this tree map chart we are going to show top five countries by medals so now go to on the training one and try to have a tree map and put it over here try to make it bigger like this and like that now here in the group we need to show about the teams that will be the country so we can directly drag and drop the teams in the group second in the values we want to see the count of the middle so we can drag and drop the middle and here we can see the count of the middle now go to on the filter now here we need to see the data for top five country only so how we can do that in the filter we need to drag and drop the country so that will be the team so we can drag and drop it here and instead of basic filtering we want to use the top n and we want to see the top five country only so what is the value so that will be the count of the middle now here instead of first we need to use the count and put apply filter now this will be filtered and now we can see the value that is the united states then other countries now we need to off this category and go to on the title so we want to see the title that should be top five countries by medals so we can rename this as top five countries by medals like this and we need to also make the change on the background color of this so we can make it as gray like this last thing that we need to make the change on this like the background part of this so the background color that should be gray as well so now it looks good and we are having all these values but one thing that you can notice we are not going to see the country and we are also not going to see the values so go to on the data label and try to on that go to on the data label so we want to on the data label and here in the display unit that should be none so we will see exact value now in the category we need to on that so now we can see like united states and what is the value of that and other things last thing that we can notice here we need to make the change about the color so go to on the format again and here go to the data color so here for the Germany for this we have all these so go to on the advanced control we don't want to see the values like this so in the advanced control we need to wait few seconds so here here we can see the lowest value so in the lowest value we want to see that is the red in the highest value we want to see that as green 0080 00 so this is for the green and we are having the diverzing color so that should be yellow i guess like that or we can click on okay so these colors will be changed like this so that looks good after that we have done about this next thing that go to on the existing one and here we can see gender and country wise metal count like that and sport wise metal count so let me use these two variations so go to on the training one so first of all let me try to use the column chart so we can use the column chart this one put it below of that and make it a little bit bigger and try to increase the size from the right side as well like this and here let me try to increase the size of this as well in the axis we want to see the country names so that should be team so we can drag and drop the dream here and in the values we want to see the count of the middle so try to drag and drop the middle and this is the count of the middle so here we can see as we have selected for France so it is highlighted now we need to unselect this so now we can see the values for United States and other countries now go to on the format of this and try to do the formatting so first thing that you can see below we are having the team then here count of the middle so we don't want to see these two so how we can make the change go to on the x-axis and here we have the option for the title so we need to off that after that go to on the y-axis we don't want to see the title in the y-axis as well so we can off this title so like this so we are having the value and the last thing we can change the data color so go to on the data color here again we can see the default color so we can see this effects so we can click on this effects again here go to on the lowest value so in the lowest value we want to see as red in the highest value we want to see as green diverging color that should be yellow i guess so we can select yellow and click on okay so it will be changed according to that here we can see the count of the teams and we are having this so we don't want to see this in the visualization so how we can off that so here we can see we are having the legend so we can off that legend so that will be gone now the last thing that is remaining in this the title and the background color so go to on the title of this and instead of this we want to see country wise medal count so we can call this as country wise medal count and in the font color that should be black that is perfect in the background color that should be gray color and go to on the background color of this visualization that should be gray color and let me use the transparency around 80 percent now this looks good next thing that we need to use a matrix so let me have a matrix and on the matrix that should be on the right side of the column chart and let me increase the size like this and like that so in the rows we want to see the sports so we can directly drag and drop the sport on the rows and in the columns we want to see the medals like gold silver or bronze so we can see the bronze gold NN silver so we are seeing all these but I guess we have applied the filter for the NA only so that is not available over here so let me try to add the filter on this page and here we need to select everything except NA so NA should not be here as we can see NA is not available now now in the values we want to see the count so we can use the id and try to drag and drop in the values and let me use as count as it already has now it looks good next thing that we need to take care on this go to on the format go to on this style let me use the bold header so now we can see this after that we need to use the title of this matrix go to on the title and on the title and here we want to see the sports wise medal count so let me call this as sport wise medal count now here on the font color that should be black in the background color that should be gray color I guess that looks good and here go to on the background color of this visualization that will be gray color now it looks good and we can also a little bit adjust this we can make it a little bit smaller and here we can adjust like that now it looks good now the last thing of the visualization which is remaining this table which is the player wise medal count so let me use that as well let me use the matrix and here let me increase this like that after that here in the rows we want to see the player so we should see the name so we can directly drag and drop the name in the rows and in the columns we want to see the middle so we can drag and drop that over here in the values again we can drag and drop the IDs and it should be the count so we can see its count that looks good now we need to do the formatting of this so what on the format and here we can see we are having this style we can make it as bold header so now here as we can see if anyone is not having any medals for any particular category for the bronze gold or silver then we can see we are having the blanks so we don't want to see the blank instead of the blank we want to see the zero so how we can do that so for that we need to create three more columns in this table for the gold silver and bronze and on that we can check the values if gold then one otherwise zero so how we can do that go to on the data and here let me select the table now let me add a column so we can click click on new column so in this column let me call this as gold and here we need to check if the value of the medal that is gold then one otherwise zero now we can close the bracket and put the enter so this new column will be going to indicate zero and one if gold then one otherwise zero now let me copy this and try to add one more column and this time it should be the second one that will be the silver now we can paste this let me rename this and it should be silver and here we need to check if the value is silver then one otherwise zero so now we can put enter now let me add third column so we can click on the new column and paste it and this should be for the bronze so we can rename this as bronze and here we need to check install the silver it should be bronze if this value then one otherwise zero now we need to wait few seconds so here we have added that column go to on the report view and here instead of using this now we need to remove all these like id and columns so we can see this now we should have three columns in the values so first of all let me have the gold second we need to drag and drop the silver and third that should be the bronze here we can see it should be some so it is some that is good second some then again some so we can see all these but here we are not going to see the total what will be the total like gold plus silver plus bronze so how we can do that go to on the data again and here let me add one more column and this time we need to add gold silver and bronze values on that column and that will be the total medals so let me call this as total and it will be going to add gold silver and bronze we can put enter so that column will be here now go to on the report view and try to drag and drop that total column in the values so now we can see we are having this now it looks good the only thing that we need to do the formatting of that so how we can do that so go to on the format and here go to on the row headers instead of black we need to have the orange so double x double six double zero the font color that is white that looks good now go to on the column header in the column header instead of this let me have the dark blue I guess so that will be good now we need to increase the size of this little bit so go to on the values and here let me try to increase the size of this text so we can use as 11 and we can also change the font family as a real now in the header we can also make the change so go to on the grid and in the grid let me increase the size of this as 11 so now we can see it looks good so now we have done all these variations except the three cards for the youngest med list so go to on the existing one and here we can see the youngest med list and the total middle and the ace so these three cards that we need to use so we need to have three measures for calculating this so how we can do that now go to on the training one let me add a new measure so we can click on that and this will be the youngest med list name so we need to wait few seconds now let me try to rename this as young player now here let me use that calculate after that either you can use the min or you can use the max function and here we need to use the name and let me close the bracket for the max let me put comma and here we need to do the filter and we need to specify the table name so this is the table name put comma then the filter expression so the filter expression the ace that should be equals to minimum ace so we can use this formula to calculate the young player name so we need to wait few seconds now we need to put it little bit on the top side to make the space and little bit this season as well now let me add a card over here so we can click on the card and make it smaller like this and put below of the season slicer like that after that in the fields we need to drag and drop the name the measure that we have created so here we can see the young player so we can drag and drop it here so we can see this now let me try to do the formatting of this first of all go to on the data label and the size that should be 25 it is bigger so let me use this as 20 that looks good or we can also make it as 15 now in the font family that should be aerial next go to on the category we don't want to see the category we want to see the title so in the title we can call this as youngest medallist so we can use this as youngest medallist now in the font color that is white I guess and in the background color that should be orange now it looks good and we need to change the background color of this so how we can do that so for that go to on the background color and here we can use any color as per our requirement like this we can also change the font color that should be white now it looks good and we can decrease the size like that now this is the youngest medallist next we need to calculate two more measures first will be the total number of medals for this and second will be the is so how we can do that so for that go to on the new measure and here we need to calculate the total number of measures first and then the is so we need to wait a few seconds here let me we call this as total medal and put it as youngest let me use the calculate function again and here let me use the count and here we can count the id we need to close the bracket then put comma here we can use the filter expression filter then the table so we can specify the table name after that we need to specify the filter expression that is always as should be minimum of is and we can close the brackets put enter so this will have the total medal for the youngest player now let me add one more card here and make it smaller like that and put it on the left side and make it a smaller like that now here we can try to drag and drop this total medal this is one that is good after that go here we don't want to see the category in the data level it should be 25 that looks good after that go to on the title so in the title we need to call this as total medal now font color that should be white I guess and the background color that should be gray so this is very light let me so in the title the font color that should be white and in the background color let me have the this color and we can copy and paste one more time so this will be the is so again we need to create one more measure so first of all go to on the total medal for the youngest player let me copy this formula and add a new measure let me try to paste what we have copied and here instead of the total medal it should be the is now everything will be same except this count here we can use the max then is we can close the bracket and put enter so here everything is perfect now we need to wait few seconds now here we should have the as young so we can drag and drop that measure in this card so we can try to drag and drop this as young over here and the is that should be 10 so as we can see this 10 so thank you so much for watching this video if you have any doubt so you can drop your questions or your doubts in the comment box so I will try to response and if you have any projects that you want to share with us so you can also share that and the source of this dashboard will be available on the description of this video so I'll provide the google drive link so you can directly go and download the source and you can start the practice so thank you so much for watching this video again and please do subscribe our channel and press the bell icon so you will never miss any newly uploaded videos