End to End Project- Power BI | Superstore Analytics Dashboard | Complete project - 2024

hey everyone in this video I'm going to create an end to end P project on Superstore analytics dashboard using the sample Superstore data set I'm going to provide you this data set in the description so that you can build the dashboard along with me so in this video you will learn how to create a powerb report from scratch starting from the data transformation technique to data modeling concept and creating calculated measures and calculated tables and then creating visualizations on top of that I will also teach you how to remove clutters from your chart and how to answer the client's questions effectively using your visualization as you can see on my screen in this report you can easily figure out the answers because I have used the storytelling techniques using conditional formatting by making clean charts by removing the Clutter by using some techniques to increase the readability of the chart and I'm sure you are going to learn a lot of things from this Pia project you will also learn how to create a slicer panel using bookmarking techniques so you can filter it from here and you can close the pan so let's start the project now let's proceed to build this dashboard so here is our data set here we have the orders table with these many columns and then we have people which we don't require in this dashboard this will be required when we create RLS and this is the returns table so so this basically says which products are returned so we need these two tables orders and returns now let's see the requirement and the questions that need to be answered so here we have the question that we are going to answer with our report so these are five points let's read one by one so first is to show the metrics that are sales profit percentage of returned orders and also we'll have to show the percentage change versus previous year so we can cover this using the kpi visual second point is compare sales performance versus previous year over time third is determine the most profitable product and most loss-making product then we have find the places where most of the profit is happening and the next and the final one is to show the sales by segment so these are the questions that we are going to answer with our report now let's move to the powerbi report uh let's import the tables that we require in our report I will click on Excel workbook here we have the sample super store data set I'm going to load it this is the order table that we require and this is just the duplicate people we don't require returns let's see the returns this second one is the table that we require because it has the order ID in it so these two tables we require in our report let's go to transform data in the returns table we first of all let's rename it to returns and we can see we'll have to promote the headers here use first row as headers and it will promote the headers now here we can see in the returns table there are two columns returned and Order ID in the return column there's a single value yes which is saying that yeah the these products are returned so basically we don't require this column returned because we know that these products are being returned so this return column is not required so in the data transformation technique the first thing that we have to keep in mind is we will only import those columns that are required to build the visual or that are required to answer the questions in our report so we will have to import the least number of columns in our report so in the returns table we don't require returns so we can go to choose columns and we can uncheck the return only the order IDs are sufficient now let's go to orders table here we have many columns but we can see from our requirement we don't need all these columns so let's move to the questions to see what all columns we should keep so in the metrics we can see that sales profit and returned orders so whatever which is order ID we require that so these metrics require from here we can see sales performance versus previous year over time so some date column is required so order date is required from here uh we can see profitable product so it says product so we requir The Columns which are dealing with product from here we can see find out the place so place as in we required the geographical columns and in the sales by segment we requireed the segment column so let's move to the power query and here we'll do choose column and we will unselect all the columns I will select those columns that are required in our report so order ID is required order date is required customer we not using it still we can choose customer name for slicing the report segment country or region city state and Province postal code region category product carry subcategories required product name these four metric columns are required uh click on okay so it will remove the other columns so these are the columns that we require in our report so this table looks fine to me no other changes are required because the data types looks fine so whenever you are creating a report you can check your data types so you can check that in my table everything looks fine so I don't need to do any data type changes so let's proceed and uh do close and apply so it is going to load these two tables uh after transformation let's go to the model view and let's build our data model so from here we can see these two tables are there and these are the properties from properties pin you can click on pin related fields to top of the card so this basically will show you the related columns on top of the table let's close this and so this is our return this is our orders so this we can create a relationship between order ID and let's say return filters the order and click on okay next is we'll have to create a date table so it is very important to create your own date table in your data model why because when we don't create a date table in our report powerb doesn't recognize any table as date table whatever date columns are present in the dimension table it will create those many tables in the backend and it is going to slow your report so this is one reason we should create our own date table so and the second reason will be so whenever you are trying to create measure using time intelligence function it requires a date table to avoid any confusion from here let's click on new table let's create this date table I'm going to create add column so this under this add column let's add calendar function so this calendar function takes start date and end date so in the start date we can take minimum of the order date which is in the fact table and uh end date we can take the maximum of the order date so this will create a list of dates next in this column we going to create a monthly date column so I will just say start of month uh here I will say EO month so EO month basically Returns the end date of a particular month so here I'm going to give this date and I will say minus one so this function is going to return the end date of the previous month and when I do plus one it's going to give us the start date of the current month so let's close the bracket and our date table is ready so let's go to the table view to check the dates so here in the start of month let's change the date type to date and uh we can also change the format maybe let's say uh month and year we want to show so this date also we can change to date and we can mark this table so we can go to table tools we can mark this table as a date table so here we can see Mark as date table uh turned off you can turn on and choose the date column so this is our date column validated successfully click on Save Now power recognizes this table as a date table let's go to the modeling View and let's create a connection so we can create a relationship between this date and the order date so this is the order date let's create a connection with this date so our relationship has been created next is we'll have to create calculated measures so keep this thing in mind we should always have a measure table where we store all the measures in our data model so that way it's very efficient to find the measures so let's go to enter data and we can rename this table as key measures and just load you can see this uh key measure table has been created and here I can see one column one is there which is not needed actually so what we can do is right click and create one measure and I will say sales this will be sum of sales and let's enter so this is our first measure so simultaneous we are going to format it as well so this is created and I cannot see the format option here so let's go to the table View and uh this is our first measure that we have created let's add this dollar value in the formatting and the currency should have the two decimal places so our first sales measure is created now we can delete this column one from this key measure table so once we delete this column one from this table this table will be recognized as a measure table and this will be moved to the top of the table see what happens when we delete this column so once we delete see it's moved to the top and it recognizes as a measure table now we can create all our measures in this table only so let's go to new measure and let's create measure for profit which will be sum of profit here also I'm going to give this same format dollar and the currency will be two let's see our questions and check what all measures we require so we have created sales we have created profit next is percentage of returned orders so let's create that let's click on new measure and let's say percentage of returned orders so this basically we'll have to calculate orders and return orders and then divide those things so let's create variable for total orders and uh this will be distinct count of the order IDs so this will give me the total number of orders next let's calculate the returned orders so here we will calculate distinct count from the returns table and here we can see this order ID from the returns table so if I do the distin count from here it will give us the returned orders next we are going to create a variable for the percentage which will be divide so I will just defy the uh returned orders by the total orders and this will give us the percentage of returned orders so in return I'm going to return this percentage variable and let's click on enter and in the formatting we can just uh make it percentage and our three measures are created similarly as you can see in our questions we require to show the percentage change versus previous year so basically we need to show the percentage change from the last year so for that we'll have to create the last year measures as well so we'll again go to new measure and say sales py and here I will show calculate sales same period last year and from here we'll uh in the in this same period last year we'll give this date from the date table and close the bracket so this will calculate the sales for the last year and here also I will change the format to dollar and currency to two so now let's create our measures a little faster so so we can copy this measure and we can do new measure I will just paste it here and I will have to calculate the profit py so I will select this sales and do control shift l so it will select all the sales value and from here we can directly replace it to profit and click on enter so this will calculate the profit for last year again uh we can do a new measure and uh here I will again select sales and control shift L here I will uh replace it with percentage returned orders orders if I click on enter it will calculate the percentage return ERS and we can say percentage we have created the previous year values now let's create the percentage change so for that uh we'll have to create new measure again and will say versus py for sales so here I will just divide so the formula is sales minus previous year sales divided by previous year sales so I will say sales minus sales py and then I will divide it by sales py so this will give the percentage change and we have calculated it for sale so let's copy this and the format will be just percentage create a new measure for profit percentage change so I will just replace this sale with control shift L and uh sa profit so this will this is the measure for percentage change of profit let's create a new measure and this is the last measure so we can again do the same thing and we can again do the same thing but but for percentage return orders it is already in percentage to calculate the percentage change for a percentage value we'll have to just subtract instead of doing divide we'll just have to subtract returned orders so here we don't need these things we will just uh say percentage return orders minus percentage return orders py so this will give the percentage change for percentage return orders we have created all the required measures that uh is required in our report now we are going to do one more last thing in the data modeling we are going to do this last thing to organize these measures so we can easily find out the measure so let's go to the modeling View and from here I will select profit sales and another measure that is return orders and in the properties pain I will say let's store these three measures in the metric folder again for previous year I will select profit py sales py and percentage return py and say it as uh py measures or simply py store these percentage change measures in the versus py measure folder so now go to report view you can see if we can easily find out the measures from here if I want the Matrix if I want the previous year Matrix or if I want the percentage change Matrix so this is the efficient way to store the measures in our measure table so our data modeling is complete so now we are left with the report creation let's go to our requirement document here we can see first of all we have to create the metrics for sales profit and percentage return orders and show the percentage change values for this we'll take the new card visualization and in the new card visualization I'm going to take the metrics that is sales then profit and then percentage return orders let's open the format pan first we can go to call out values and we can decrease the value to 18 and make the font to s semi bold callout value is fine now let's go to reference label in the reference label in the sales series we can select the series so we have three measures so we have these three measures showing so sales is selected in the sales we can add so we'll have to pick this up and we can add here so sales py is added we can add uh so we will add one more versus py measure for sales again I will go to profit and in the profit I will add profit py and versus py profit it I'll go to return orders and do the same thing I will now let's do some formatting here let's go to the sales first and in the select label we can say first we want to rename this uh sales py to py so sales py uh this title we can change so it's saying it's saying content field name so we can say select custom here and we can change this to py and add some spaces in the select label we can change it to versus py and uh again I will select custom select and say we just want versus py and some spaces so we can do the same thing for the other two measures in the reference label I don't want this uh grayish color so select series we can select all and then we can see this divider option and the background option is enabled so in the background option let's select y divider let's say we want a lighter color so in the cards we have this border remove this border and these values are very uh closer to each other so it's difficult to read so what we can do is we can separate this in the Tabo format so for that we can go to layout style uh we need to choose currently it's sentence so that's why it's showing in the sentence we need to choose this taba so now it's separated and it's clearly visible and uh let's add the padding as well so so search for padding and I will say I need a wide padding so this looks good so let's go to the requirement document and here we have some color codes for the background and the positive and negative colors so I have these colors uh let's add it to the background with 40% transparency go to Canvas background here I will add the color and will make it 40% transparent and let's add a title so I will insert a shape rectangular shape and I will change the fill to White and I will turn off the Border align this to the top and uh let's turn on the text and say super store so let's change the font to Black and let's increase the size to let's say 20 and make it semi bold and align it to the left and add the padding to 25 it looks good uh next let's come to the kpi and here I will remove the background so go to size and style and just remove the background so now we can see see this is separated and clearly visible reduce the size a bit this is fine I guess so now the kpi is not done yet so what we can do is we can add a conditional formatting to this versus py so this percentage change we can add a add a rule and add a conditional formatting so if it is increasing if it is uh good for us we can mark it as green and we if it is bad for us we can Market as red so we have our green color for so this positive color is basically a green color so we can copy this color add the condition formatting so we can go to reference label again in the reference label let's select uh s and for in the select label let's say selected versus py so let's select this versus py label and let's go to Value so in this value section and the font color add the condition formatting so I want a rule instead of gradient so and let's change this field as well to this percentage versus py sales and let's add two rules so if it is greater than minimum less than zero and second will be if it is greater than zero and less than maximum so this will be red so here we can choose a green color a dark green color and copy this green color so click on okay and Let's uh make it bold so this is not visible clearly so let's make this s bold yeah this looks fine and similarly I will do it for profit and percentage return orders so you can see uh this is negative is Mark as red one more thing to note percentage returned orders if the percentage of returned orders is decreasing it is good for the company because there number of returned ERS are decreasing so we will mark this negative value as green and the positive value as red so we can go to the conditional formatting again and we can reverse the color order and click on okay so now it is green so the negative return orders are good for us so the less the the lesser the return ERS the better for us so our kpi is done now let's go to the PowerPoint and let's see so we'll have to create a sales performance chart and compare it with the previous year so for this I will use a line chart and uh I will use sales measure last year sales measure and in the x-axis we need this monthly start date in the xais this is fine let's do some formatting here so I will double click and change the title to sales versus previous year over time so this is our title let's over over add Legend So that the formatting pan will open and let's turn off the title of the legend and uh in the legend option Let's uh change the position of the legend to top right so I prefer the Legends uh to be in the top right section we don't need this x-axis and y-axis titles because we can see that this is sales from the title itself so it's already identif iable so let's turn off this title again go to this xais title we can see the date so we don't need this title we can see these grid lines which is not looking good so I'll go I will go to the grid line and here I will choose solid here we don't need vertical grid lines so I will turn off the grid lines now see our chart looks little cleaner we can search for padding and add some padding in it so I will add 20 padding on each side so if you see uh after adding this padding our chart is looking more clear if I say I have to capture the sales performance versus previous year we cannot identify which is previous year which is current year at the first glance so I'll show you how to handle this so let's click on it and go to lines in the formatting Pane and I will select sales first and add our color so we have our own color so this is the positive color so we are going to add this positive color here so this is the sales color and for sales previous year we'll select a lighter gray color select this 30% darker in the line style uh do a do dashed so I prefer dashed so now anybody will see the the primary metric is the sales that which is in green color and and the secondary metric is the dashed line that's what I I was talking about these small changes in your chart can improve the readability and the readability is the most important factor when you create a dashboard so I have done the formatting and I'm going to use this here and let's align it a little so we have this second chart here let's see what is the next one so next one is determine the most profitable product and the most loss making product so for the most loss making product and the most profit making product we can uh create a bar chart or the column chart so what we can do is I will just comp this chart because we have done all these formattings and we don't have to do it again and again so I will just copy paste this chart and uh I will change it to a bar chart and uh here uh I don't need this month I want from orders I want category and subcategory so I will choose category and uh in the let's add subcategory as well and uh in the y axis uh we want profit instead of sales so let's choose profit and now let's add the conditional formatting also we can add vertical grid lines which will separate the categories currently we are not able to see under forming Furniture what all categories are there so we can enable the grid line so we can go to grid line and enable the vertical grid line here and uh I will choose see this segregation so this table comes under furniture and appliances comes under office supplies so these are the minor changes that you should keep in mind let's add a rule to this color let's add uh gradient and uh it will be profit the key measure is going to be this profit and in the lowest value I will say this red color and in the highest value I will choose the green color which I'm using till now so let's copy the green color from here again and I will add it here so here uh in the midal color I am not liking this color so for this uh metal color I will add a middle color option as well and here I will choose this lighter gray so the most profitable one will be The Greener ones and the most loss making ones will be the red ones and the average ones will be the gray ones so let's click on okay and see the change so after adding this condition formatting we can easily identify that this product that is technology copers is the most profitable product and this tables is the most loss making product see the power of conditional formatting let's change this title profit by product let's align this little I think it's fine so we have answered our third question as well let's see the fourth one so find out the places where most of the profit is happening so here we will create a map so again I will just copy paste this chart I will make it a shape map and here I will remove this Legend so here we want by profit so let's add the profit and remove this tool tip let's add it so we we have added the profit and in the color saturation and in the location we are going to add the state of Province let's search state of Province so we can change the map settings as well so here you see map type is USA state so since we have the States from USA that's why we have used this else you'll have to use something different I will close this map settings and I will go to fill colors and in the gradient see it's already taken the gradient so in the minimum value I will choose this lighter gray and and in the maximum value I will choose the green that we are using so let's change the title profit by state so from this chart we can easily say that this California is the most profitable State and this Texas is the most loss making State let's create one more chart here so the last one is sales by segment so this will be a donut chart and here we'll add sales instead of profit and uh in the legend we can add segment since we have only three segments that why have used the donut chart but if it is more than three don't use py chart or tuner chart that is not preferred let's do some formatting so Legend we are not able to see since we have less space so what I will do is I will just turn off the legend detail label I don't need all this information I just need the percentage value so I just say percentage of total let's change the title as well so this is sales by segment now one more thing is left we should add the conditional formatting to this chart as well and to add the conditional formatting if we go to the slices we see we have three different slices and we don't see any conditional formatting option here so in this case how to apply condition formatting in the pie chart or the donut chart so I have a workaround for this so we can go to a bar chart and here we can add a conditional formatting so we can go to columns or bars maybe a bars and here we can add conditional formatting here in this color option so I want gradient and this is sales value so I will select the sales measure and here we can select that green color which we are using and uh in the lowest value we can use this lighter gray color click on okay so in the bar chart we have added the condition formatting now what if I switch to Donut chart see is automatically added so this is the way to add the conditional formatting in the pie chart increase the inner radius so I will make it little thin yeah this is fine let's align these visuals yeah so we have answered all the questions in our report so one more thing we can do is we can add slices we don't have any spaces to add slices so we can add a slicer pan and add bookmarks here so I will show you a designing technique to add a slicer panel so I will go to the next page and here I will add this slicer and I will add is customer name and do the formatting like make it drop down add a select all and also add search option from here what else so this is fine I guess let's create three more slicers so this is customer this is for Country so let's say let's add it for Segment so let's add this for date and uh we'll change the styling options like we'll make it between and let's increase this space uh let's turn off the slider yeah so I've have created this uh slicers now let's create a Slicer in so I will use PowerPoint to create Slicer in so let's create a new PowerPoint I will delete these things and uh I will insert icons let's say I want a filter icon so I will use this filter icon and insert it to the report and also I want to insert the design so so I will use folder so insert this as well so this folder I can I want it in this way and here uh let's right click on this and say format so in the fill we can let's add a gray fill in the line no line is fine let's go to shadows and in the presets we can add this outer Shadow me are ready so I will just import so I'll right click and save as picture so save as PNG I will say panel BG and this also I will save as picture here let's import those images so go to insert image in the image style let's say scaling should be fit let's make it more bigger on top of it let's add these slicers at once go to background and turn it off go to Value turn on the back background of the values so make it white so our four slices are ready now I will group all these slicer so this let's write it as filter so that we can recognize this one and let's create a group so we will select all these and right click and say group let's insert one shape this one this one will be for closing the panel we can add a color to this go to shape style and and fill the color make it white and let's turn off the Border yeah I think this is fine and let's add this uh shape inside this group yeah so our slicer panel is ready so we can also create a group for these two slicer panel copy this slicer pan to this page here and move this to top select the slice of pan and I want to create two bookmarks one will be for pan open and one will for pan close so let's right click and turn off the data and selected visuals select selected visuals now we are ready to bookmark so I will select this slice p and uh select this filter as well and after selecting these two I want the state to be in the pain open state so I will update the bookmark and I will turn off this SCE Pain Group and again select these two and update the pain close bookmarks the pain open pain close is fine pin open is fine so bookmark is done so let's add bookmark names uh so in this filter image I will go to formatting and uh go to action turn on the action and in the action type bookmark and in the bookmark choose pan open and in this back shape p pain close so when we click on that it should close it see if I close it here we're going back to the pain close date and if I click here it will open this and we can filter it from here for any customer for any date for any country and we can close it so that was it for this video I hope you like this video I learned many things in this NN PBI project if you genuinely like this video do comment on this video so that I can make more and more videos of this kind and it will be helpful to you to become a good powerb developer so I hope you are taking something from this project and you have learned many things from designing to data modeling so let's wrap this video uh till then goodbye and thanks for watching my video

Channel: BI Tricks

Views: 3,095

Keywords: End to end Power BI Project, Power BI Dashboard, Power BI tutorial, Power BI Report development, Power BI end to end tutorial, Power BI Classes, Power BI Dax tutorial, Power BI Dax tutorials, Power BI sales project, Power BI Service tutorial, Power BI KPI, Data visualization, Power BI data modeling, How to use power bi, How to create dashboard using power bi, end to end project on power bi, Power BI project development, Power BI Design tutorials, Power BI real time project

Id: 5se3nFK_dFo

Length: 35min 39sec (2139 seconds)

Published: Mon May 13 2024