End to End Retail Store Analysis in Power BI | Power BI Dashboard | Learn basic Power BI in 30 MINS

Hello friends welcome to this new video so in this video we are going to create this kind of dashboard okay so basically this dashboard is for one retailer which is present in USA okay so I got this data from kaggle so basically uh there are so many features of this dashboard so the first thing is or the main important point which we are going to learn in this particular video is what is the use of slicer how it act as a filter and how to create it okay so basically this one for quarter month retailer region and beverage okay so these are called as slices right it will act as a filter now I'll tell you how to create this in our video okay so basically based on your selection the data will change see if I select quarter one so data will change according to the quarter one if I want to select quarter one and three so accordingly data will change C okay now let's say uh from month we want only the data for February then August and then September so according to the selection the data will get changed now we can like select a retailer as well region as well okay let's select Midwest invest as well okay and based on the beverage brand we can get the data like this okay so I'll tell you how to create this kind of dashboard or like based on our customization okay in our video now uh what I'll do I'll give the data set in the description box I'll give the data set in the description box you can also download the data set and you can follow the video for creating this kind of dashboard okay so we know the very first thing while creating the dashboard is to load the data okay the very first thing we'll create the dashboard is to load the data so basically there are multiple sources for loading the data so today our data set is present in Excel workbook format okay so we will use Excel workbook as an over data set so I'll tell you how to load the particular data and what are the next steps after loading the data okay so we will create this kind of dashboard step by step so the very first step is to load the data for loading the data as we know our data source is a Excel workbook right so you can directly click here on Excel workbook for loading the data okay so now my data is present in download folder in this data folder so this is the data set okay I'll just open it so it will hardly take 10 to 15 seconds to get loaded okay so after low like when the data is loaded now you will get a window like this so basically it means that in this Excel workbook only one sub sheet is there which is sheet1 so I'll take Market it will show the preview after checking the preview you can click on load button okay so now see our data is loaded so when the data is completely loaded on the right hand side you will get that particular sheet name and on the column names whatever which are present in that particular tab okay so we know in our Excel workbook there was only one tab okay which is sheet1 and under sheet 1 there will be different columns I will show you so basically now see the data is loaded already loaded so the tab name is sheet1 so in this sheet one these are the different columns okay so for uh checking the data okay what we have you can click on this one data so it will show the preview of our data so basically uh multiple columns are there in the sheet one First Column is for retailer second column is for retailer ID the third column is for invoice date the fourth column is for region fifth column is for State sixth column is for City then beverage brand then price per unit then unit sold we have total sales as well operating profit operating margin okay so as these things okay uh total sales operating profit and price per unit right so these uh Things Are showing the amount right so we should have a dollar symbol or currency symbol before it right so I'll tell you how to do this as well basically as we have only one uh table right in our data set so there is no need of checking the model so basically we have to check model when we have multiple tables for showing the connection between the tables we have to check it here but as today we have only one table so there is no need to build any relationship with other tables right so we will directly go here on our report page so basically the area where we are going to create dashboard is called as canvas so this blank area where we are going to create a visuals different visuals is called as canvas okay so what I will do um before proceeding okay the main thing is trans uh see this total sales so this should be in currency right so I'll click on here currency symbol okay then for price per unit as well I'll click on this currency symbol okay so now everything will be in dollar okay so let's create our dashboard now so the very first thing is to select our desktop background now how to apply the background on canvas okay I'll tell you how to do this okay now we have to apply background so while applying the background on canvas you have to go to format your report page okay under this you will get an option of canvas background so select canvas background now here we have an option to select color if you want to give the background as plain color you can directly select color from here otherwise you can set as an image Okay so now in this image option we have to provide the location of that particular image so I'll go to desktop then in this particular folder I have my desktop background I'll select this one so select fit as instead of normal fit you can select fit so that the image will take entire area of your canvas and now we have given the image location it has already been loaded but it is not visible on a canvas because of this transparency so we have to reduce the transparency level to zero now your background image is clearly visible so basically this image we will use as a background of our report page or we can say dashboard page okay so this was the very first thing now the next thing is we have to create slices so first go to format your report page okay now the slices for selection right which will act as a filter so I'll click on this one this icon so this is for slicer if I click on one uh like visual will open so inside this we have to give the field value so as we want to create slicer for month what I'll do I'll go to invoice date date hierarchy and I will drag month to this field okay but now the main important thing here is I don't want the Slicer in the form of check box right I want Slicer in the form of buttons right so for that what we will do I will go to customize our visual I'll go to slicer settings so instead of vertical list I'll select tile okay so formatting we will do later on okay I'll just give the position correct position yeah so one two three four five six yeah this size of the slicer will be fine now the next thing the next slizer for our dashboard will okay next slider of our dashboard will be the quarter okay so I'll select this one I'll just paste it so what we can do now see uh we can just okay I'll just copy it I'll just paste it I'll drag it here so ins in this particular slicer instead of month so what I'll do I'll uncheck the month we want quarter right so I'll drag this quarter to this field so four quarters are there okay so based on our selection it will show the figures okay so these are our two slices now we want to create more slicer for retailer okay for region so what I'll do I'll just copy this slicer I'll paste it like this I'll just align it at one position okay so in this instead of quarter I'll check on I'll uncheck the quarter okay we want region so I'll drag the region to the field okay so we have these five region basically in our data set okay now the next thing which we will do is I'll again copy the slicer I'll paste it okay now instead of region we want retailer so I'll just uncheck region and I'll drag retailer here so we have this 5 retailers in our data set now basically we have to create one more slicer here so the slicer will be for beverage brand okay the slicer will be for beverage brand so what I'll do I'll just copy this one I'll paste this I'll just resize it okay so in this instead of retailer we want beverage brand okay so I'll just drag beverage brand to fill okay now for this particular slicer what we can do we can give a drop down option right so I'll go to format your visual slicer setting I'll click on drop down I'll just align it yeah now we have each and every slices now what we will do we will just format this okay so the very first formatting is to disable the background so I'll go to format your visual I'll go to general setting go to effect and here the background is enabled I'll disable it okay the next thing will be visual border so I'll enable visual water the color of the Border I'll give select as white okay and roundness Corner also we can give to the border so I'll select 20 here now see our slicer will look like this now basically in this particular slicer I want to do one more thing okay before that one to four yeah so I want to do one more thing basically uh to give the background color for this button okay so what I'll do I'll again select the slicer I'll select this format your visual option so inside the values I have one option for background okay so I can select the background color so the background color which I'll select will be this one yeah this looks good okay and all other thing for values and all I'll select bold and black color so that everything will be clearly visible yes now the next thing will be the this one this heading for quarter so for that you have to select slicer header so inside the slicer header you can select the color I'll leave black okay I'll give this one and I have already made it as bold okay so this will be clearly visible now see you don't have to do this for each and every slicer what you can do you can directly copy the format from this slicer and you can paste on on the other one so for doing that just select the formatted slicer click on format painter and click on the next slizer so accordingly the slicer will get formatted again click on format printer click on this so see we have formatted the slices very quickly okay now this is done the next thing in our dashboard is to create the visuals okay now before creating the visual there was 10 particular numbers the numbers like total sales total units sold okay operating profit what is the operating margin okay so that important number we have to show in the form of important numbers right so for that particular thing we have to use card visual so I'll click on this one okay this is the card visual so when you click on this one you have to enter only one thing which is field what number you want to show so let's say I want to show total sales I'll select this one so this is the total seal okay so you can just resize this one like this okay now we will format this one as well so basically again you have to go to format your visual so this number 8.68 this is called as call out value okay so I'll go to call out value for making it bold I'll select bold color I'll select again yellow one so that it will match with the background the next thing which is this one sum of total sales okay this is called as category label so I'll again make it bold and I'll select this color okay the next thing is we have to disable the background so I'll go to General go to effect I'll disable the background okay the sum of total sales is not visible so I'll go to visual I'll select some light color like this okay now it is visible so for background we have already disabled we will enable the visual border the color of the Border will select as white and roundness corner will select 20. okay now so we want to show four numbers so what I'll do I'll just copy this one I'll just copy these two at a time so as we have four important numbers I'll paste it here okay so basically the first number is showing sum of total sales the second number will show total unit sold so I'll untick this total sales and I'll track total unit to this field okay the next thing will be this one so I'll again untick total sales and I'll select operating profit so it will show the total profit and At Last I want to show operating margin as well so I'll drag this one to here so instead of sum we have to show average operating margin always in terms of percentage so the average operating margin for our given data set is 36.31 percent right so this is also done the next thing which we'll do is to align it properly okay so what I'll do I'll select these four numbers okay I'll go to format click on align and click on distribute vertically it is already distributed and you have to distribute it horizontally so I'll click on distribute horizontally okay now see the space between two card visual is uniform okay now the next thing is we have to create the visuals okay how to show this particular data which is in the form of table in terms of graph we'll check okay so first thing we will show total monthly sales okay total monthly sales or total sales with the help of month so for that particular thing I'll select stat column chart okay so under this stacked column chart I'll just align it a bit okay I'll increase the size okay so in this on x axis we'll keep month on Y axis will keep total sales okay like this so for formatting it what I'll do I'll again go to General the very first thing which we know is to disable the background visual border enable color white and this 20 roundness gone rounded corner near about 20 you can keep okay now the next thing here is to give the color of the bar okay so before that we will do one by one so on x axis we'll show the numbers with the help of yeah this is clearly visible will make it bold so that it will be more clearly visible we will select light color yeah so the month are visible now title of x axis again will make it bold color will select the same then Simi same formatting we have to do for y axis value bold color same then title of y axis bold with same color okay now the next thing which we can do here is grid line okay so instead of dotted we'll select solid grid line with the same color okay go to General so in this General title Select Title make it bold Center align and with the same color so that it will be clearly visible okay so this is the graph for total sales by month we will format this column later on the next thing which we can do is we can create one donut chart in this particular space okay so in this particular donut chart we can show total units sold by beverage brand we can say I'll select Legion so total unit sold by beverage brand and the runner chart is like this so what I'll do I'll just copy this format from here I'll paste it here okay now everything is visible except this one so what I'll do I'll click on format your visual so in data label for values I'll make it bold and I'll select the same color okay now data labels are also visible now the next thing will be Legend so the position I'll select a stop Center text bold and same color okay so this is also visible so for this particular thing we can do one more thing which is data label we'll enable it okay so that the numbers will also be visible at that particular place for data level values will do like this now everything is there so the next thing which we are going to do is we'll create uh like two to three more visuals in our remaining space so that we can show the entire data in terms of graph okay the next graph which we will be making is we will use this one basically stacked bar chart okay so on this particular graph what we will do uh we will show total sales in terms of retailer so I'll select total sales on x-axis on y-axis I'll select retailer okay so we have four retailers basically so this is the total sales for retailer so what I'll do I'll just copy this format and I'll paste it so that I don't need to format it again okay there is no need to format it again okay so I'll just resize it okay this is fine the next graph would be for total units holes by City okay so for that uh the visual which we can use is the Stream app I'll select this tree map okay I'll just copy the format I'll paste it here so basically in this Stream app what we are going okay I'll just paste this stream up here okay I'll drag this stream up here okay so in this Stream app we want to show total unit sold by city so category will be City I'll drag this city to the category and the values will be total unit sold so based on this one so there are so many cities right but we want top 10 let's say so what I'll do for selecting this top 10 we can apply filter for top 10 now how to do this see when you select the visual so we have one filter pane here okay in this filter pin as we have selected City you can click on this particular Arrow so three types of filter you can apply basic filter based on your selection you can do then we have advanced filtering option and the third option is top n so we can select top end here so basically how many cities you want let's say we want 10 cities top 10 cities so I will type 10 by which value based on totals uh okay let's say based on total unit sold value we want this top 10 cities you can just apply filter so see these are the top 10 cities okay by total units sold now we can create one more visual okay the visual will be uh donut chart only so I'll select donut chart I'll just resize it okay I'll copy the format I'll paste it okay so in this particular donut chart the thing which we want to show is total sales by City okay total sales by state so I'll select Legend as instead of total sales I'll select the total sales value sorry and for legend I'll select state so there are so many states right so we want let's say top eight so again in this filter options State filter type will be top and we want eight only so I'll type 8 here then by which value total sales and click on apply filter so top 8 cities data you will get okay so this is the thing now only one thing is remaining here okay and the thing is basically foreign thing is remaining and the thing is uh to just format this one now how to do this I'll tell you okay let's format the donut chart one by one so basically I'll select this one so the very basic thing of formatting is just use one color with different shade okay so go to visual option in slices you will get option for selection of color okay so what I'll do for the very first one I'll take this shed red color one okay then little lighter then third one then fourth then fifth and at last which is remaining we can select it as this one right so this looks good now the next thing which we will do is for this one so for this we can use yellow color shade okay dark then lighter okay now uh the next thing is to format this one so for this what we will do uh the only one thing is remaining in this particular graph is to give the color okay so basically instead of blue color we will give some light color so that the graph is clearly visible right and for this one also we can give the color so for this graph we will use this column so basically no uh now the main thing here is we can show average line for these two particular column chart and bar chart right now how to show this so basically what you have to do just select your visual go to this analytics pane okay after going this one there are so many things constant line okay constant line and error bar so basically we'll select constant line now which line you want to show average one so what you have to do click on add line so here in this line so I'll just type the name here average okay now color of the line you can select style of the line and position also okay so I will tell you how to do this now so basically the thing is we cannot show average line for this particular graph okay but let's say uh instead of this column chart we have line chart like this okay and we want to show average line of this so for that you have to go to analytics pane okay now here average line add one line so see this dotted blue color line it is the average okay but for this data we don't there is no need to show average line okay so I guess everything is done now what we will do okay uh basically here we will add one icon and we can add one text box as well so what I'll do I'll go to insert I'll insert text box I'll type the name is just increase the size I'll make it bold medulla line color I'll select white so that it will be clearly visible and size 16 will be good okay uh what we have to do I'll select this visual basically and I'll go to effect and disable the background okay so because of the background whatever we have written it was not visible now the next thing is I'll drag this one facing a problem while dragging okay now we can add one icon as well if you want you can add icon otherwise it's fine you can just increase the size instead of 16 you can select 18 or 20 so it will look good so done so this is our complete dashboard so basically one more thing which we can do if you want to you can use tooltip in your dashboard okay so we have already discussed tool tape in our previous video I'll tell you how to do basically for that what you have to do you have to add a page let's say the very important thing which you want to show is in in terms of pie chart okay so what I'll do I'll add one page in in this canvas setting and type you have to select tooltip actually okay after selecting tooltip go to visual and select pie chart okay now let's say we want to show some data okay for total C okay uh B is uh let's say we want to show the data for operating profit so for values I'll drag operating profit and in terms of uh we want to show by beverage brand okay so I'll do like this one now the next thing is I'll just format it so I'll just copy the format from this one and I'll paste the format here I'll go to effect and I'll disable the background so basically the main thing here is chart type so I'll select do not okay the things are not clearly visible we'll format it again okay so General background I'll enable the background background color we will keep it as this okay then title bold text color will be black and go to visual Legend text color bold black now data label values bold is already there and we'll select black color so the for uh like the numbers are not really visible because the background color and the text color was same okay now what we will do so basically what I was thinking is when we take cursor to some data so it should show this graph for that particular data on which we will drag the cursor okay so that is called as a tool tip now we are using tooltip for beverage brand okay so what we will do I'll select this chart okay this visual I'll go to this one go to General under tooltip is already enabled okay so type will be report pH and the page I will select as page 3 because we want to use page 3 as a tool tip now see if I'm like selecting default here so basically when I drag up my cursor so it will show the data in the form of that the month name is October on that particular month the total sales is six to three 375 right but I want to use tool tip so what I'll do type I'll select report page and page page 3 because we want to use page 3 as a tooltip now when I'll take the cursor to this it will show the data in the form like this so basically the total sales is 0.6 million okay and the division of sales in terms of beverage brand is like this okay and this looks good actually okay now if we want to do same thing for this visual so again report page page three now if you drag the cursor will show data like this okay so as this graph is for beverage brand only so it will give the data for one beverage brand so no need to use tooltip here so we'll select default like this okay or we will disable the tooltip for this particular graph now again for this one tooltip is enabled report page page three for this one as well page 3 okay now when we drag it will show the data like this so this is our final dashboard so now we will just check whether our dashboard is working or not based on our selection for checking that what we have to do we'll just click on quarter number one so when we click on quarter number one the data should sort based on that particular quarter so for this quarter number one the total sales is this much the total unit solds are this much okay this is the operating profit and operating profit margin is this one so let's say we want to add one more quarter here quarter four so the data is like this and it's changing actually now let's say we want data for February November and December only so I'll select these three So based on the selection the data is changing okay now we can select beverage brand as well so basically let's say we want these three only so data is changing okay now retailer we want dream company and physicip okay so this is the data for this one I'll select one more the next one let's say we want data for the south region only so for South Region this is the data okay now and here we have given the option for Clear selection as well so when you click on this one okay every selection will be cleared whatever you have selected it will be cleared okay so I hope you got the idea how to create a dashboard like this okay so basic thing is you have to decide a proper background you have to select proper visuals okay and the third thing is you have to use color formatting so see the basic thing here while giving any color to any visual no think of background color background disk dark then select the color visual appropriately so that the color contrast is there okay whatever the numbers you want to show it should be clearly visible and all so for that you can use a color hunt website okay on that particular website there are so many shades of color you can directly copy the color code and paste okay so based on that color combination your dashboard will look good okay so thank you for joining in this video with me I hope you have liked this video if you have any query regarding this particular dashboard you can just comment below okay I'll try to resolve this in that particular query as soon as possible so thank you for joining

Channel: Google Sheets Tips and Tricks

Views: 26,266

Keywords: datascientist, sharepoint, dax, microsoftexcel, analisededados, sqlserver, dataanalyst, programming, microsoftdynamics, powerquery, qlikview, inteligenciadenegocios, indicadores, microsoftteams, artificialintelligence, powerbidesktop, powerbi, microsoft, businessintelligence, dataanalytics, datascience, data, tableau, datavisualization, dashboard, analytics, python, business, dynamics, bi, software, bigdata, digitaltransformation, azure, dataanalysis, machinelearning, technology, dashboards, microsoftpowerbi, cloud, businessanalytics

Id: 2kMYTN5UHvM

Length: 37min 53sec (2273 seconds)

Published: Sat Feb 04 2023