How to Build Power BI Reports from Start to Finish

in this video i'm going to teach you step by step how to build the power bi reports you see behind me you can download the files from the link in the video description so you can have a go yourself now if the pace is too fast click on the cog icon in the bottom right of the video and you can alter the speed settings and of course you can pause and rewind and watch as many times as you like okay let's get started first i want to give you a very quick overview of what power bi is it's a business analytics tool for visualizing and sharing insights we can get data from hundreds of sources using the same power query tool that's available in excel we then model the data with the same power pivot tool that's also available in excel and from there we create compelling interactive reports and dashboards and collaborate and share them with people inside and outside of our organization we can set them up to automatically refresh so there's no further maintenance required now there are two main power bi tools we use the free power bi desktop tool to get data model it and create the report we then publish our reports to the online power bi service where we can collaborate and share them with our audience now the data i'll be working with is 12 months worth of budget and forecast data and 6 months worth of actual data for the it department of a global company and this is a modified data set from obvious the forecast budget and actual data is stored in csv files that have been exported from an accounting system and they're saved on my pc you can see the files contain records by date department cost element country and the amount and these are my fact tables i also have an excel file containing dimension tables that contain further groupings for the cost element country and i.t departments each table is named and these names will also be the names of the tables when i import them into power bi now i'll be comparing performance against budget or plan if you prefer to call it that and because these are costs an adverse variance will be when actual or forecasted costs are higher than budget now for those statisticians who might be thinking that variance is the wrong term to use here i just want to clarify that this is a term accountants use to refer to the difference between a budgeted value and the actual value okay i'll switch over to power bi and we'll get started power bi desktop is the free modeling tool available for windows for authoring your power bi reports it uses the same power query and power pivot tools that we have in excel so if you're familiar with these tools then you're off to a head start here the power query tools are on the home tab of the ribbon and for those not familiar with power query this is the get and transform data tool built into both excel and power bi it enables you to get data from a huge range of sources and clean and transform it before loading it to the powerpivot model now my dimension tables are in an excel file so i'll grab them first or connect all i need to do is browse to the file location select it and click open the navigator shows me a list of tables and sheets in the file i want these three tables so i'll check each one in the right hand side we get a preview of the data so you can check that it's what you're expecting and if i need to i can click the transform data button to clean the data before loading it however in this case i know the data is ready so i'm going to click load and power bi is going to load that data into the powerpivot model we can now see the tables in the field pane ready for me to build my visuals but first i need to get the rest of the data i'll grab the csv files for budget and forecast there's budget i get a preview of the data and if i need to i can clean the data by clicking transform at this screen we can see that power query has detected the file origin but we can choose a different one it's also detected the delimiter and it's based that on the first 200 rows and again we can choose a different option here i'm happy with these options so i'm going to leave them as is i can clean the data by clicking transform data but i know this data is ready to go so i'll click load let's do the same for the forecast and we'll load it straight in next i need to get the actuals now each month's data is in a separate file and you can see a list of them here so i need to consolidate them into a single table i'm going to just copy the address for the folder path and in power bi i need to get data and then more and here i want to get data from a folder click connect now i can browse to the file location but remember i've already copied it so i'm just going to control v to paste it in click ok power query is showing me a list of the files in that folder and some metadata about those files i'm going to combine and transform data so that you can see the power query editor similar to the csv file we get a preview it's detected the file origin the delimiter and it's based out on the first 200 rows of the first file but we can choose a different one here the first file is fine for this example i'm going to click ok power query opens in a separate window and here i can clean and transform the data before loading it to my model on the left you can see the list of queries i've created so far the helper queries at the top are automatically created by power query for me when i combined the csv files for the actuals data on the right is the list of steps in the selector query which we can see here is actuals now i don't need the first column in this table it contains the file name now sometimes this column can be handy if you need to extract information from the file name like maybe the date but i already have a date column so i'm just going to hit the delete key and we'll get rid of that column now if i wanted to filter out any data for example if i only wanted to analyze data for the united states i could deselect everything and then just select usa and that would filter the data before loading it to my data model and that's important because you don't want to be bringing in data that you're not going to use it's just going to make your file bigger than it needs to be now i want all of the countries so i'm just going to cancel that now there's a whole load of easy to use tools for cleaning and transforming data available in the ribbon many of which i cover in my introduction to power query video so i won't cover them again here but there's a link to that video in the video description okay i'm ready to load this data to my powerpivot data model in power bi so on the home tab i can simply hit close and apply you can see in the fill pane that i now have all my data ready to work with but first i need to make sure the relationships between the tables are set up correctly and i like to do this in the model view i'll rearrange the tables so that my fact tables are at the bottom and my dimension tables are at the top this way i can easily see the filter direction arrows on the relationship connector lines all flow down from the dimension tables to the fact tables hovering over a connector line highlights the related fields now these relationships were automatically set up for me when i loaded the tables to the model but if required i can double click the line and open the edit relationship dialog box where i can make changes i'm happy with this one so i'll leave it as is now one thing i notice here is the department's table only has one relationship with the actuals table but it's missing relationships between the budget and forecast and that's probably because the column name in the budget and forecast tables is spelt slightly differently and that's fine i can create those relationships now just left click and drag and release it on top of the field that it corresponds with and we'll do the same for forecast and now our relationships are all set up now the last table i need is a calendar table and this isn't strictly required for every model but because i have three fact tables and i want to visualize data from each table in a single visual i need a dedicated dimension table that contains a list of dates one for every day of the calendar of fiscal years that i want to report on now i can create this table in power query or in power bi i can use the calendar or calendar auto dax functions by the way these functions aren't available in powerpivot in excel so to create a table from a function i'm going to go to the data view and then on the table tools tab i can choose new table here i just need to give my table a name which will be calendar and i'm going to use the calendar auto function now this function bases the start and end dates on the minimum and maximum dates across your entire model except for dates that are generated by calculated columns or tables or measures and it returns a table with a column of dates one for each day in the date range if you want a table based on a fiscal period you can specify the start month number for your fiscal year now my fiscal year starts in january so i can leave this blank but if yours started in july then you type in a seven so i'll delete that and we'll press enter to create the calendar table now the calendar auto function always starts with the first day of the calendar year or fiscal year and it ends at the last day of the calendar year or fiscal year and obviously if your data spans multiple years then it's going to start at the first day of the first year and end the last day of the last year in your data set now word of warning my model only has dates from the 1st of january to the 31st of december 2020 so this function will work fine for me but if your model contains dates of birth or other dates outside the range that you want to report on then you'd be better off using the calendar function because this allows you to specify a start and end date so now that i have my calendar dimension table i need to create relationships between the fact tables let's move it down here and we'll left click and drag each of the date fields up to the calendar dimension field now before i build the visuals i want to set some formatting and i like to do this in the data view so for example my actual column i want to set that so that it has a comma separator let's repeat that for the budget and for the forecast that way the formatting will automatically feed through to my visuals i won't have to manually do it again for each one the other thing i want to do is set the data category for my country fields and this is just going to help power bi recognize that this data is a country so that when i plot it in a map chart it's going to know what to do with it so i've got one more here and then finally country in the regions table notice that i now have an icon beside each of those country fields okay i'm ready to start building my visuals let's go to the report view now in power bi desktop we can use the built-in ai to ask a question of our data for example let's say i wanted to see the actual year to date versus the full year budget displayed in a gauge chart and if i'm happy with what the q a tool returns i can commit that as a standard visual i'll make it a little bit smaller and i'll move it down the other thing i want to do is in the formatting i want to edit the title so we'll just say actual year to date versus full year budget and with that i've created my first visual without even needing to know how to use power bi now you can also insert visuals by simply selecting the visual type from the gallery here the next one i want is kpi so let's select that and then all i need to do is select fields from my field list and notice i've just checked the date field and it's automatically detected that it's probably going to go on the trend axis because it's a date field the indicator will be the forecast and the target will be the budget let's move it down the bottom that's where it's going to live now i need to make some changes in the settings for the kpi because it's assumed that a positive variance is good but remember these are costs so when costs are higher than budget that's certainly not good we can do this in the settings so under goals at the bottom i want distance direction decreasing is positive and in the color coding i want low is good now the numbers displayed in the kpi are those of the last period in the data in this case this is december's variance and the error chart behind represents the variance trend for the whole year the next visual i want is the line chart so let's move this over here we'll make it a bit wider so this one i want again the date for the access i don't want it down to the day level of detail month is enough and i want the actual budget and forecast let's drill down on the data until we get to month level okay let's make some formatting changes here so in the x-axis i don't need the title i think it's self-explanatory that those are months likewise for the y-axis we'll turn the title off and then i'll turn the title off altogether i think this chart is fairly self-explanatory doesn't need a title because the legends are explaining what the data represents now the next visual shows the running total so i need to write some measures using the powerpivot formula language called dax i need three running total measures one for actuals one for budget and one for forecast we'll do the actual zone first click on the ellipsis and select new measure and the formula bar opens ready for me to enter my measure so this one will be called actual rt rt for running total we'll use the calculate function to filter the data we want to sum the actuals and filter all selected dates in the calendar table where dates are is on or after the max date in the actuals table and the sorting direction to calculate the running total is descending so all i need to do is hit the check mark and if you look at the field list you can see i now have a new field in the actuals table and notice the icon is the calculator and this indicates that this is an explicit measure meaning a measure that i've written myself with a dax formula now all i need to do is rinse and repeat this formula for the other table so i'm just going to highlight it and ctrl c to copy and then we'll add the budget one and i just need to change where it says actual to budget and lastly another one for forecast and i'm ready to insert the visual so this is going to be an area chart let's bring it down here and we'll line it up so it's the same size as the line chart above so this one's going to have the actual running total the budget running total and the forecast running total and we need to see that across dates let's drill down let's do some tidying up in the formatting i don't need the axis titles again i think it's self-explanatory so let's turn them off and for the title itself i'll just change this to say running total now this title is a bit bold with the black font so let's tone it down with the shade of gray and we'll make it a little smaller okay for the next visual i need some more measures but this time i'm going to have power bi write them for me i need to calculate the budget versus forecast and the budget versus forecast percentage difference so from the ellipsis beside budget i want new quick measure in the dialog box i can choose the type of calculation so here i want to do a subtraction base value is going to be the budget and the value to subtract is the forecast i'll click ok and if you look in the formula bar you can see power bi has written the formula for me so let's just change this to say budget v forecast press enter to commit that and you can see again it's in the field list let's make that a bit wider and i need one more which is the budget v forecast percent so we'll do another quick measure this one is going to be percentage difference the base field is the forecast and the value to compare is the budget and click ok and there it is in the field list if i click on it you can see the formula that power bi has written for me it's a little more complicated than the last one let's give it a better name and now i'm ready to build the next visual so this one is going to be a clustered column and line combo chart there it is there bring it down here below the other charts it's going to be the same width and this one is going to have the date in the shared axis now i don't want this one to use the default hierarchy i just want it to use the date field and then i'm going to have column values are the budget v forecast and the line values are the budget v forecast percent let's do some formatting i'm going to turn off the title for the x-axis and in the y-axis i want to turn on the secondary axis label so we're going to show secondary axis and i want to also show the title for that secondary axis i'll keep scrolling down here i want to turn the title off and we're done next i'm going to use the clustered bar chart so let's add one of those they're going to fill this space over here there's going to be three of these charts so once i build one i can copy it let's tidy up this list i'm just going to collapse them all all right this one is going to use region in the axis and then the budget v forecast percent in the values i want to sort this in ascending order do some formatting i'm going to turn off the title for the y-axis and in the x-axis i want to turn off the title and i want to turn off the axis because i'm going to add data labels and for the data colors i'd like to color code the negative values so let's go in and we'll do some conditional formatting click the function icon for data colors so this is going to be formatted by rules based on the budget v forecast percent so where the value is greater than or equal to 0 and less than or equal to zero number i'm going to give it a shade of red we'll add a new rule for the positive values so where the value is greater than or equal to zero number less than or equal to 100 percent it'll get a shade of gray we'll give it a slightly darker shade i'll click ok and now you can see my visual has the negative values color coded in a shade of red reddish orange and the positive in gray let's also make some changes to the title because it's a bit bold so let's change the font color to a shade of gray and we'll make it a bit smaller and let's abbreviate this so just be budget b forecast percent it's obvious it's by region we don't need to point that out as well in the title all right so this visual is done with it selected i'm going to control c to copy ctrl v to paste i'm just going to move it down and we'll modify this one so the next one is using the cost elements so let's add that to the fields so i want the cost element group i'm going to add it to the axis and then i'm going to delete region and that way my conditional formatting has carried over i don't need to re-apply it so let's copy this one ctrl v to paste and we might make this one a bit smaller because it hasn't got as many items in the vertical axis as these two do so these ones need a bit more space and this one's going to be by it area so in departments let's add it to the axis and then delete cost element group the next thing i want to do is add a slicer for the region and that's this icon here we'll simply check the region box now by default it gives us a checklist but i want a drop down it just takes up less space so i can squish it down in there now because my actuals only span so many months i'd like a card in the top left just calling out what the period is year to date that this report is based on so you can do that adding a card visual which is this one here now i need to add a measure for this so let's add one to the actual table this is going to be called year to date month so i'm using the format function to format the maximum date in the actuals table and i'm formatting it with mmm yyyy and i'm concatenating year to date to the end so press enter let's add that to the visual now it's a little bit big so let's go and format it i'll turn the category off and in the data label let's reduce the size of it down to about 38. what i want it to do is fit on one line so actually let's go a bit smaller okay there we go so this report is done you'll notice i've lost my actual line in this line chart it seems to be a bit of a bug in my version of power bi at the moment so if i remove it and add it back in it comes back now the next thing i want to do is change the color theme you'll find this on the view tab of the ribbon in the gallery here we can choose from a different theme or we can browse for themes that we may already have on our pc we can look for new themes in the public theme gallery or we can customize the current theme now i've already created a theme so let's browse to it it's here it's called solar m and it's a json file so let's load that the theme was imported you can see it's changed the colors in all of my visuals and again the pesky line chart has lost its actual value okay now this one down here i'm going to make some changes to the colors just to differentiate them from the actual figures and the budget figures because this is budget v forecast and budget b forecast percent these aren't actuals which are pink up here so i want to make sure that they're different so let's go and change that we'll make the budget be forecast percent line this dark purple and this one here this shade now i'll give this sheet a name we'll call it headline because it gives an overview of the it spend across all areas of the business so now that my first report is ready i can use the built-in interactive features to cross-filter and highlight the data and personally i think this is one of the most amazing features in power bi if you want to understand the movement from one period to the next power bi pro and premium users can right click and use the analyze tool to explain the increase or decrease here it gives me a waterfall chart showing the components contributing to the change by cost element and if i scroll down i've got some different charts this one's by cost element group it department department manager it area and so on and below each chart i can choose a different type of chart so the default is the waterfall we can look at it based on a scatter chart stacked column and even a ribbon chart now this feature is still in preview so it may look different when you come to try it out let's move on i'll add another sheet for my regions report the first visual here is a clustered bar chart so let's add that this one is going to be by region and i want the actuals and the budget let's do some formatting i'm going to turn off the title for the y-axis it's fairly self-explanatory i'm going to do the same for the x-axis and i'm going to turn the x-axis off altogether because i'm going to add data labels let's change the data colors make the budget the gray shade which is in keeping with the visuals on the headline sheet so i'll make this a little bigger now the next visual is actually this one here from the headline figures sheet so i'm just going to ctrl c to copy and i'm going to paste it in here let's bring it over and make it the same height next i'm going to add some matrix tables now these are a bit like pivot tables that we have in excel so here i want i t area in the rows regions in the columns and the values are going to be the budget v forecast the formatting i'm going to apply some conditional formatting i'd like to see icons let's go into the advanced controls so the rules are based on the budget v forecast what i want to do is just highlight negative variances with a red dot and positive with a green dot so we'll change the rules down here if a value is greater than or equal to zero percent and is less than zero number then this is going to be negative so let's give it a red dot and if the value is greater than or equal to zero number and less than or equal to 100 percent then that's going to get a green dot that means we don't need this rule i'll click ok and now we have our conditional formatting set so let me copy this table and paste it in again i'll drag it down below i'm just going to modify it and this time instead of it area i want cost element group so i'm just going to drag the cost element into the rows and then delete the it area that way my conditional formatting stays intact let's give it a bit more space and then we'll make this one the same size and let's make this column wider so that they're lined up closer together and we can give europe's column a little bit more space as well okay that will do i think you get the idea with the formatting all right the next visual is going to be this filled map so we'll pop it up here let's give it a bit more space and here i want to look at the actual costs by country now at the moment everything's the same shade so let's go in and give our data colors some conditional formatting so here we want the color scale based on the sum of actual the lowest value will give it this shade of purple and the highest value will give it this darker shade so click ok and now we get a better feel for the size of the data with the usa being by far the highest values and the last visual for this report page is scatter chart so let's pop it in the space here this one we want the country in the legend and then we want budget for forecast in the x axis and budget v forecast percent in the y-axis and for the bubble size we'll use the forecast so from the scatter plot here we can see that usa has the largest cost by far and while it's nearly 1.7 million over budget that's less than one percent over so it's not doing too badly in the scheme of things whereas down here we've got spain that's 17 over budget with a 300 000 overspend all right let's edit this visuals title we'll just call it budget v forecast and let's tone down the color i'll give it a shade of gray while i'm here let's fix this one so that its title is also not so black okay the last visual i want to show you is called the decomposition tree it's this icon here in the visual gallery let's make it really big it needs lots of room now the decomposition tree is available to power bi pro and premium users and it enables you to conduct root cause analysis for example we might want to understand the factors contributing to the adverse variance to budget by visualizing data across multiple dimensions power bi automatically aggregates the data so we can drill down into dimensions in any order i can click on the plus beside budget v forecast and i can use power bi's ai to find the biggest contributor to the negative variance by choosing low value now by default it sorts in descending order but since i'm interested in negative variances i want to sort in ascending order here i can see the administrative cost element is the biggest contributor and again we can continue to use the ai to drill down and find the next low value so we can see usa and let's continue in the cost element group administrative as you'd expect because the cost element is administrative and lastly into the department now the colored dotted line indicates i've used the power bi ai to find the next level i can click on external labor and the decomposition tree updates to show me the factors contributing to this variance and i can continue to choose different paths to analyze and understand the causes of the variance let's delete these and i'll choose the fields myself so this time i want to see by cost element group and then we'll drill into administrative into the cost element and then i want to know which country and obviously it's returning the same results as you'd expect it would but i'm seeing it in the order that i've specified so again i can choose labor and this time i can say well there's a positive variance in internal labor let's have a look at that you can see usa is contributing to the positive variance for internal labor but it's also contributing to the negative variance for external labor so as you can see the decomposition tree is an amazing tool for understanding contributing factors to your business performance so now that my reports are built it's time to publish them to the power bi service the power bi service is a web-based online portal for publishing and securely sharing your power bi reports and dashboards i'm going to save my file and then on the home tab i can click publish i can choose the workspace i'm going to put it in this one called demos it's all done so now i can switch over to power bi and we can view the reports online so here i am in my web browser i'm in my demos workspace and if i scroll down you can see i now have a data set for it spend and i have reports for it spends so let's take a look at those it's opened up on the last report i had let's go to the headline here i can interact with the reports in the power bi service just like i can using power bi desktop you can see the cross filtering and highlighting all works in exactly the same way if i want to focus in on a specific visual i can go into focus mode and that will open it in a larger window on the right i have the filters pane or i can continue to filter down into this data if we go back to report view you'll notice the filters pane is available here as well so as a user i can continue to add filters but any filters i add here won't be saved i can also click on the ellipsis and show this as a table and then i get the chart at the top and below i get the actual values that are displayed in that chart and just like in power bi desktop pro and premium users can use the analyze tool to understand what the cause is of any increases or decreases again we're going to get the waterfall charts that help us understand that movement from one period to the next now if you want to provide a high level view of your data and restrict users ability to add more filters or export the data then you can create a dashboard on the ellipsis here i can pin this whole visual to a dashboard it's going to pin the live page and that means any changes to the report will be visible in the dashboard as well so i can create a new dashboard we'll call it itspend and pin live and then if i scroll up and see it's created the dashboard if we look on the left hand side i can see it here i can navigate to it notice here we get a more streamlined view there's no filters options on the right hand side but the cross filtering and highlighting works in the same way there's no right click menu so there's no option to analyze but at the top we can ask questions about our data using the built-in q a there's a list of suggested questions to choose from or you can ask your own for example let's say i wanted to know infrastructure forecast by department manager or even infrastructure budget fee forecast by department manager and if i like this visual i can pin it to my dashboard as well let's go back to the reports and we'll talk about sharing power bi pro and premium users have the option to share we can click on report and in the right hand side we get the sharing pane where we can enter the email addresses of those users that i want to share my report with the nice thing about sharing using the power bi service is that your data is secure you can modify the settings below to restrict what recipients can do those you've shared your reports with must log into their own power bi account to view them of course if your data isn't confidential then you can choose to embed the report either in sharepoint online a website or portal like an intranet or even just to a public website now there's a lot more to the power bi service and sharing reports and dashboards than i can cover in this video but hopefully you can see that power bi gives you far more control over who can see and share your reports than you can achieve with excel for example so the next thing you're going to want to do is update your reports and the procedure for refreshing the data depends on the location of your source data and again that's a vast topic so i can't cover everything in this video i've set up a personal gateway between my pc and the power bi service which enables power bi to securely communicate with my pc to get new data so all i need to do is add the july data to my folder on my pc so let's go and grab that i've got some july data here i'll copy it let's pop it in this folder here so you can see there's now a new file let's go back to the power bi service down in the data sets all i need to do is click on it spend and then choose refresh now so the data's been refreshed but my report doesn't reflect it yet all i need to do here is refresh the report clicking the refresh icon here and if you keep an eye on the area chart you'll see that july data is added we can see the tool tip shows me that i now have an actual for july and if we go and have a look at the dashboard you'll see that it also contains the july data and that means those you've shared your dashboards and reports with will also see that updated data now i've triggered the refresh manually here but you can schedule automatic refreshes to run at specific times so if we look at the data set you can see there i have the option to schedule a refresh and in here i can set the frequency now before we wrap up there are some things i want to point out and reiterate the visualizations and the related data set have been published to the cloud and they reside in the secure power bi service now this may be an issue for some companies so i cover the alternatives in my course if you want to share your reports and dashboards you and those you share them with require a power bi pro license which is currently us ten dollars per person when you share a dashboard those you share it with have access to the same reports and underlying data but they can't save any changes unless you allow them to this access to the underlying data allows them to dig into the data and find answers to their own questions now if you want to restrict access to data based on a user's role for example you want each regional manager to only see their region then you need to use row level security which i also cover in detail in my power bi course the other thing to bear in mind for excel users is that the same power query and power pivot tools we have in excel are used in power bi so if you already know these tools then you're off to a head start but if you're one of the millions of users who still has a version of excel that's not compatible with these tools then you don't have to miss out any longer since they come free with the power bi desktop application so now you can get your power query and powerpivot skills up to speed and when your company eventually upgrades your version of excel you'll be way ahead of the curve well i hope you're excited to give power bi a go if you'd like to get up to speed with power bi power query and powerpivot please consider my courses you'll find a link to them in the video description and if you'd like to have a go at building these reports yourself you can download the files for this lesson from the link here if you like this video please give it a thumbs up and subscribe to my channel for more and why not share it with your friends who might also find it useful thanks for watching you

Channel: MyOnlineTrainingHub

Views: 401,817

Rating: 4.9568186 out of 5

Keywords: power bi, power bi dashboards, power bi reports, power by desktop, dashboard reports

Id: Z2t7l8b1uWU

Length: 41min 7sec (2467 seconds)

Published: Mon Nov 02 2020