Excel Data Analysis Class 03: Power BI Desktop, DAX Formulas, Relationships, Data Modeling& Visuals

welcome to excel data analysis number three in this video we get to study power bi in video number one we learned about the amazing standard pivot table and in video number two we studied power pivot and a data model pivot table and we'll use the same data set and create the same set of reports but we'll get to create our data model using the power bi user interface including power query in the data and queries groups how to create a date table with dax measures and then our amazing interactive reports over here in power bi desktop [Music] now last video we talked about the difference between a standard pivot table and a power pivot data model pivot table but once you decide to jump over to power pivot then you get to decide whether to use power bi desktop or excel power pivot now all of the advantages that we talked about last video for power pivot carry over to power bi desktop except whereas you can share a powerpivot data model over to power bi desktop you cannot send a power bi desktop data model back to power pivot and that's because power bi has some dax functions and some features that are not compatible backwards and really you don't have to decide between power pivot and power bi because they work together as we saw at the end of last video you can just send your data model over and because power bi desktop is a free download might as well send it over there because the interactive visualizations are too good not to use that tool also now power bi has some amazing sharing abilities but you have to have power bi online and a pro license or higher and of course that costs money now i was at an xlmvp conference a few weeks ago and some of the fellow xl mvps said that their customers were saying forget paying for power bi online most people are using power bi desktop just like excel it's an app you create your reports your dashboards and then you send the files to people now don't get me wrong power bi online is amazing and we'll take a look but that free download all of you excel people it's just too good not to check it out now one thing we saw last video and requires power bi online is you can store a single source of truth whether it's the data model like we saw last video or the reports and dashboards all of those things are in one location and you can share them with your team another thing about power bi is the engine works faster on big data whereas powerpivot behind the scenes uses the mdx language power bi uses the dax language so if you really have big data you probably do want to use power bi also power bi allows us to create dax tables and deliver them to the data model and we'll see an example of that this video when we create our date table all right let's get started with power bi desktop the first thing is here are two links to download the free power bi desktop the microsoft download page this one allows you to install it and if you don't want to update you don't have to but when you want to update you have to redownload it and install it the microsoft windows store you download it and it just automatically updates each month like our microsoft 365 excel now the data sets are going to be the same we're going to have two dimension tables and we'll have to point to this excel file and then we'll import the two tables however our fact table data are going to come to us in three different excel files now last video we saw how to use a couple clicks to import and append text files when you have excel files because there can be lots of different objects in excel files we have a different process and it requires that we create a little m code inside of power query but it's amazing you can have all these excel files and later when we drop a new file in all we have to do is click refresh now when you open a new power bi desktop file you see this window right here i usually just x out but this what's new is pretty good since power bi desktop gets updated each month you can go check that out i'm going to x this out and right away i can see the file isn't named so we use the keyboard same as in excel for save as f12 i'm going to give the file a name the extension is dot pbi x click save up at the top we have our ribbon here's the home tab the modeling tab over on the left if you hover that's the button you click to get to the reporting area that's the area we're in now over here on the right we can choose our different visualizations and then we drag and drop fields and measures we'll see our tables and fields here and filters here the second button here is called data when i click this is where we go to see our tables we don't have any tables this is also where we can create our measures attached to tables over here this is called model when i click this is similar to diagram view and powerpivot it's where we go to look at our tables and relationships even though we create most of our measures in data view or in report view all right our first step is we need to get the two dimension tables from our excel file so using power query and the data group i'm going to click the excel button now you'll download this and unzip it inside there's one file we'll use later inside of start the top three files those are for the fact table but this has the dimension table so we're going to double click this to import one excel workbook and then look inside of it to pull those two dimension tables so i'll double click this is the navigator window that opens up when you have files that have multiple objects this is an excel file so we see objects like tables and worksheets i want to check d product and d sales rep by checking two objects power query will create two import queries to bring these two tables into the data model now down here we want to click transform data when we click this it opens the power query editor this power query window looks almost exactly the same as the one in excel over here on the left we can see two queries right now d sales rep is selected that's the name these are the steps and we want to verify just like last video that we have the right data types if we don't you simply click and select the appropriate data type so we're going to verify for both queries names steps and data types now different than excel whereas in excel when we close and load we use close and load two and then we have to decide which location we're going to use to load the data in power bi desktop we can only load it to the data model or if we want a connection only we can right click and uncheck this and then it loads it as a connection only but if we don't do that option we can only load it to the data model now the close means it'll close this window the apply means it'll apply the query steps and load the data to the data model and in the drop down you can apply steps close or both and we want both if we go over to data view we have a preview this is d product and in the field list we can select a different table this is d sales rep over in model view we can see our two tables we'll get more tables here later and build relationships although in power bi desktop it's really good at detecting the relationships and creating them automatically now what we did there was use this button to import multiple objects from one excel file now our task is different we have three excel files and we need to look into all three files and pick the objects that we want to append into a single fact table now before we use from folder to import multiple excel files and append tables together we need to go look inside of each one of these files so i'm going to look inside of 2021 power query will have no problem importing this it's a single worksheet with a proper data set 2022 same thing here proper data set on a single worksheet but 2023 it'll have no problem with this but power query will also try to import this sheet and you could see we have some data but lots of extra empty cells and it'll bring all these empty cells in also not only that but in the name box we have a defined name it'll think that that's a separate object and if you ever define a print area that also will be interpreted as a separate object now in power query it's easy to filter out objects like these that we don't want so in this excel file we'll filter out everything except for this one proper data set also the field names in all of the tables that we're going to append have to be the same field names no extra spaces no misspelled words now we can use from folder in the data group the get data button is similar to excel when i click the drop down i don't see from folder here so i come down to more and in the get data dialog box there's some categories here but there it is folder now when we use this option last video we did text files and everything's much more automatic when you use from folder on text files when you use from folder on excel files then we're going to have different steps but we click connect just like last video we have to give it a folder path so i click browse i browse to the location i'm telling power query to import everything from this file and before i click this let's go look and see what's inside we only want these three uh oh there's a text file and there's a dot xlsm now last video we had a caveat when we did from folder we said we're always going to make sure that we have the same file type but in this video we want to see power query has no problem we can have different file types as long as there's some file type or file name characteristic that we can use to tell power query which files we want and there is in this case we want files that only have xlsx so there it is i'm pointing power query and it's got a lot of different files in there i'm going to click ok click ok this is similar to last video there's the folder path and we have a preview here in the extension column of the different file types we want to click transform data we can see our third query over here immediately i'm going to come over and rename this f sales and enter up in the formula bar you can see folder.files there's the folder path as we know if we need to edit this we can change it here now what we have here is a list of files and attributes if you click off to the side not on the yellow binary but off to the side you can see sure enough there's a preview or an icon of the file down here we have a text file and here we have an xml oh that one's open now so we made this mistake let's go up to close and apply drop down i'm going to say close the power query window now this is different than excel because we can come back to a query in power bi desktop that has not been loaded now i went ahead and closed that excel file and in the queries group how we get back to the power query window is we go to the transform data drop down and transform data by the way that's data source settings that's where you can go and change folder or file pass or settings for sql databases and other data connections but we want transform data now i want to come over to the query f sales i see it right there but i can refresh and now i have the five files in that folder all right we only want dot xlsx so we come up and we use our filtering skills from excel click the drop down and when i have multiple items here if i uncheck all and select dot xlsx now when i click ok look at the power query m code function table dot select rows in the first argument of table dot select rows it's acting on the previous step for each row in that previous step it looked at the extension field and said ru equal to dot xlsx so that's how we can filter out and get only the file types that we want from a particular folder there's a new applied step over here in the content column do not click the combine files button i don't know why they don't have this disabled for excel files it does not work that works for text files now before we learn how to get at each one of those excel files we don't need any of these attributes so right click content remove other columns now the very first thing we have to do is we have to pull all of the objects out of each one of these files and in fact the 2023 file has more objects than we need the other two have just one object a sheet and the way that we pull objects out of excel files is we have to use the excel.workbook function and we have to add a column so we click on add column tab and then we click on custom column when we use custom column it's like adding a helper column in excel all right i'm going to call this new column get excel objects and down here we have to type a function excel dot workbook and watch this i'm going to try and use the tab key intellisense does not work in power query at least as of april 2021 it's broken in power bi desktop and it's broken in excel too now we're using a power query function and everything in power query is case sensitive so if you don't get that capital e and the capital w and everything else lowercase correct it won't work now i open parentheses and the screen tip shows us the first argument workbook is binary now the word binary just means a file and the content column contains a different excel file in each row so for the first argument we can come over to available columns and double click content that inserts it into the first argument of excel dot workbook now notice square brackets are used to refer to columns now the screen tip shows us a second argument so i'm going to type comma use headers now whereas in step one excel.workbook pulls all the objects tables worksheets define names but once it pulls all those objects and we filter to show just the tables we want step 2 for xl.workbook is to append the tables and the use headers argument wants to know if we want to promote headers so if we type lowercase true what we're telling excel.workbook is once we get to the tables and append them please only use the field names in the first row and so that's our formula to pull all the excel objects into power query and once we get to the tables to append them with field names in the first row now i click ok and now we have our fourth applied step notice table dot add column it worked on each row in this table using excel dot workbook now if we click off to the side not on the yellow word table that's a table that lists the objects it found now the data column has whatever the data is the other columns are attributes for that object the second excel file has one object but the third excel file has multiple objects and the kind column is one of the columns we can use to filter out and get just the objects we want now this is a table listing all the objects and each one of the excel files has a table i want to bring them all together so i can filter to get just the tables of data that i want so i'm going to use the expand button and we want to uncheck this because i do not want this part of the resulting field names i want just these so i uncheck click ok now i have yet another applied step but more importantly in the data column i can click off to the side there's one of the tables i want that's a table that's a table but there's an object i don't want this is that excel worksheet with three columns of data and a bunch of empty cells here's the defined name and here's that print area so the first thing we can do to get at just the tables of data that we want is to filter the kind column and show only sheet so i'll filter uncheck select just sheet click ok there's another step and now i have only sheets now there's this one extra sheet here so now the name and the item column are usually the same so we can pick either one and for each one of our file names we're going to notice that they start with eda so we're going to filter and we'll use a text filter this is just like over in excel begins with and we'll click and type eda now i click ok there's yet another step and finally we filter the objects to get just the one two three tables that we need to append now i only want this column so right click remove other columns there's another step now before we append we'll remind ourselves that each one of these is a table with field names so when we come up to the expand button and click we want to uncheck that this is where the lower true comes into play xl.workbook will append the three tables using these field names in the first row when i click ok bam there's our appended table one proper fact table data set there's a new step our next step is to make sure that the data types are correct for all the columns now all of these are going to have whole numbers so i'm going to click on the first one hold shift click on the last one right click change type whole number now for date we could use clicking the data type icon date but we want to consider the regional settings on our computer my computer has month day year so all i have to do is select date but in case there's a discrepancy between the format of the dates in the data and the regional settings on your computer you can use using locale this dialog box lets you change the data type and select the locale of origin so data type would be date and for me the regional settings are already english united states and these dates are english united states but if these dates were from france i could use the locale option here to get my computer to understand that these are from france but translate them into dates that my computer with english united states could understand you would simply come down select this option here click ok and bam the french dates would be interpreted correctly on my computer now i have both english united states as a locale and in the column so i don't need to do this but that's an important step when dealing with dates let's click ok it also adds a separate step change type was for all the other columns this one was change type with locale and now we have our appended single fact table now we can click close and apply and we can watch it live here it even put the relationships in now we have lots of measures to create in this fact table here but our next task is to create a date table using dax formulas let's click on data or table view we can see we have three tables now over here in power bi desktop we have a new table button that allows us to create tables and add the tables to the data model so i'm going to click new table all dax formulas whether you're creating a table a calculated column or a measure have to be created in the formula bar and we use the assignment operator equal sign i'm going to type the name of the table d date equal sign and we get to use the calendar function now we have to give calendar a start and end date and it'll generate a table with one column of all the dates between the start and the end date now a date table must have a unique list of dates with all days for all years that are present in the fact table date field if we do not meet this requirement then the amazing date related dax functions and other date features will not work now we need to make the start date and end date dynamic and they need to be pointing to the fact table so when we add new data later the date table can update now we're going to have to build a date using the date function and actually we're going to use date and year and min and max functions inside calendar and all these functions are the same as over in excel all right so for the start date what's the year well we're going to have to use the year function and then inside of year we use min and i need to pick out from f sales date column the min date so i close parentheses on that close parentheses and so now in the year argument of the date function we'll always get the minimum year from the fact table comma month well the beginning of the year is always january comma and the day is one so close parentheses on date so that's a dynamic start date and we're gonna copy it come to the end comma and for end date control v then we just need to not find min but the max so in year that'll always be the max year from the fact table and what's the month 12. what's the day 31 and those can be hard-coded in because they don't change all right now we close parentheses and when i hit enter we get a single column with all the dates even if we don't have sales on some of these days they all have to be here for everything related to dates to work in the data model now this is a dax table we didn't use power query to create this and in the data model here and over in powerpivot they have data types and i want to change it to date i also want to change the formatting although the formatting will not affect any of the calculations we make i'm going to change it to mmddyy now we also need to go over to table tools and i need to mark this as a date table so i'm going to mark as date table the column with the dates will be date and when i click ok because we've marked it as a date table our data model will be much more efficient it disables the automatic date table creation mechanism which creates invisible date tables behind the scenes when you use date columns that's a feature built in for beginners but it is not an efficient feature because you can have an excess buildup of hidden date tables i'm going to click ok now let's come back to column tools and actually both in table tools there's a new column and in column tools there's a new column button i'm going to click new column this is going to be month number equal sign and we use the month function and we can't select a column like we can in powerpivot we have to type it out d date now when i close parentheses and hit enter the formula is automatically copied down the column the dax calculated column uses row context to get the date in each row and then create the month number we're going to need that to sort the next column which is month name so new column we'll call this month equal sign and if we were over in excel we'd use text because that's the function that could take a date and format it as text but over here in dax it's called format it works exactly the same way the value argument needs the date column so we put the date column into the first argument of format comma and the custom number formatting is the same from over in excel you can put capital m or lowercase it doesn't matter that would give you the full month name but i just want the three character month name so that's it in double quotes close parentheses and enter now let's prove to ourselves that this will not sort correctly i'm going to go over to report view and all i'm going to do is check month and sure enough it adds a table visualization but there it is it's not sorted correctly so we come back over to data select this month column come up to sort by column and i'm going to say please sort by one to 12 that way january to december will be sorted correctly so now that that column is sorted we can come over here and that is beautiful now we want one more column we're going to call this year equal sign and year d d and the first column of the date table close parentheses and enter for the year column we want to set some properties because if i drag this into a table for example or other visualizations the visualization will try to add these so i'm going to say summarization please don't summarize now you can add lots of other date attributes from week number to fiscal year fiscal quarter but for us the only attributes we're going to need in our report are year and month all right let's go back to model i'm going to close the properties move the tables around and now i can create a relationship by dragging date over to date and it's smart enough to know that we have a unique list of all the dates this is the one side this is the many side now we created this date table by using the calendar function and then we added individual dax calculated columns using month format and year now i want to show you an alternative to adding individual columns one at a time that means we can use all of these formulas in a single dax table formula to create the date table now i'm not going to actually show you how to type all the code out but i am going to show you this just as a glimpse of what is possible now i want to click on this d date generate and row table and there's the single dax table formula that defines the entire date table now the way this is created is i click the new table button i define the new table we learned how to create variables last video the first variable date column that's the calendar dax function we used earlier to create the single unique list of dates and then generate is a magic function that takes two tables that's the date column from there and this is a one row table with all of whatever our calculated columns are and generate takes the two tables and it does a cross join bringing all of these calculated columns to every single row in the date column now you can see the first variable inside of generate actually has to use the date column which is automatically generated by calendar but all the remaining columns including some fiscal period columns here are created based on that one date column these are all of the variables for all the calculated columns row brings them all together into a single row that's joined to the date column and the beautiful thing about this is you can copy and paste this code and the only thing you have to change for whatever fact table you have is the name of the fact table and whatever that d column is in both the min and the max and then the full table is generated alright so you don't have to do this the method that we used back over here creating one column at a time is fine but if you want to try that you can check this out in the finished downloadable file so we've done step one in our data analysis process we have used power query to import clean and transform the data we have our tables then as part of step two the data modeling the relationships were automatically created then using dax formulas we created a date table and now using dax formulas we need to create the measures we can create measures either in data or table view or over in report view we'll start in data view you want to select the table where you want the measures to appear f sales already has some fields we actually will hide these later and show just our measures now with the table selected you can come up to table tools calculations and select new measure quick measures opens up a wizard and steps you through a process to create the measures for you we're not going to use that we'll create our measures from scratch you can also come over to the table and right click and right at the top is new measure when you do that the default measure shows up and there's a what looks like a calculator icon that's the icon over here in power bi desktop for measure remember over in excel it was an f of x now up in the formula bar we're going to create our measure and there's no measure grid like there is in excel power pivot we'll actually when we want to preview have to go over to report and get a preview of the result there up in the formula bar we'll type the name of our measure and for total sales we're not going to add a dax calculated column and then sum we're going to do the one step method using the amazing iterator function sum x the table argument requires the table where you would have put the calculated column so we put fs i see f-sale so i hit tab comma the expression argument is where we put our formula we would have put into the dax calculated column now we need to calculate line sales and line sales is the product price times the units for each row in the f sales table now we get to look up the product price from the d product table using the relationship and the related function and the only field it needs is the column from the lookup or dimension table that contains the item we want to look up so there it is d price now related uses row context to see the product id in each row to look up the correct price times f sales units and that's our formula that expression will iterate down every single row in the f sales calculate line sales and then sum x will add close parentheses and enter now we can see the formula in the formula bar and over in f sales there's our first measure now i'm going to create the rest of the measures over in report view so i actually rename this double click and call this test measures and we already dropped this in earlier when we were testing the month name sort order and that was by default a table so in power bi desktop you can just click whatever the visual is and when you click a different one it converts it to that so i'm going to click matrix and a matrix is similar to a pivot table and then it has rows columns and values area now if i hover my cursor this is fields and this is very similar to our pivot table user interface we drag whatever fields we want to the rows columns or values area i see months in row i want year so i'm going to drag year above and when i see my yellow line i can drop over in the visual i'm going to right click expand entire level we can resize this visual and now with the visual selected that's where we drag and drop fields but look at this we have a roller paintbrush and when you click the paint roller it exposes expandable categories for all the different types of formatting we can apply to the visual for example we could select grid and if we scroll down there's text size i can increase this to 12. coming back over to fields we'll use this visual and we'll just test each measure as we create it making sure that the visual is selected i can check total sales and it looks like it's working similar to a data model pivot table here we have a matrix showing us the year and month totals from our sum x measure now notice that the totals are at the top and that's an element that we can format in this visual so we go over to the paint roller and when you're coming from excel it takes a while to figure out where things are but this one is subtotal so i'm going to check that and down below there's row subtotal position i'm going to say at the bottom and now it's at the bottom now we'll use this visual to test our measures as we create them now we can come over to f sales right click new measure our next measure is total units and we can simply sum on the existing f sales units column close parentheses and enter i can see the new measure over here selecting the visual i'm going to check the measure and it looks like i need to add number formatting look at this we did total sales over here on sum x and it had a default number formatting but this one did not clicking on total units i'm going to apply comma make sure that 0 is showing and that's much better let's go back and select total sales measure and instead of leaving it general i'll click comma making sure that there are two decimals showing now right click new measure the next measure is going to be number transactions and as we learned about last video there's an amazing dax function called count rows and because the fact table is in the center surrounded by dimension tables and all the filters flow from the dimension tables to the fact table all we have to do to count the number of transactions for any conditions or criteria we drop into the visuals is use count rows on f sales because whatever filter there is will filter the fact table and count rows will count how many rows there are so that's number of transactions and enter i'm going to add comma and this formula is counting so 0 decimals is perfect now i can check number of transactions and everything is looking good now our next measure is going to be average monthly sales and that requires we add a new column to the date table so i'm going to go back over to data view or table view select d date table new column we'll call this end of month and we'll use the end of the month function we need to look at the date column and the date table comma and we need the end of the current month for each row so we put a zero close parentheses and enter we'll add some number formatting and as we learned about last video the importance of this column is that we use it in the first argument of average x to get the total sales for each month and then average those numbers and we can't use the month column with text january because then january for 2021 and 2022 and so on all of those januaries would be treated the same so you would get a total that's too big whereas if we have end of the month for each month then we have a unique identifier for each month regardless of what year it's in now to create our measure we're going to go back to report view make sure that f sales is selected new measure average monthly sales equals and we'll use averagex and the first argument table and average x allows us to put whatever table with whatever grain or size we would like and so we want to use values which picks out a unique list in the current filter context for whatever visualization we're using on the d date end of month close parentheses so values is delivering a table for example down here is 2023 in this row right here the date table is showing only 20 23 days but the values function picks out a unique list of the 12 months for that year in the total row the values function will deliver 12 months for every year for a total of 36 rows and now when we comma and get to expression and put the total sales measure now wherever this formula is in the visualization the total sales will iterate across values get the pre-aggregated total sales and then average x will average close parentheses and enter i'll add some comma number formatting make sure it's two decimals make sure the visualization is selected check average monthly sales now the monthly sales are going to be exactly equal to total month sales because it's averaging a single number but down here in the year rows the formula is working later we'll use this particular measure in the product by year report the next measure we want is year over year percent change for total sales so for example in the march row and the visualization we somehow in the dax formula need to access total sales for march of this year but also total sales for march from the previous year that means inside our formula when the filter context for march 2022 flows in we need to push it back one year and the way we change the filter context in dax is to use the calculate function we also get to use the amazing time intelligence function same period last year we'll right click f sales new measure we'll call this year over year sales percent change and we're going to need to calculate the previous period sales two different times so when we have a formula element repeating like this we can create a variable shift enter v a r that means we're about to define a variable space and we'll call this previous period and it will be equal to well we need to change the filter context so we use calculate and in the expression argument we want to take total sales and with no filter it calculates the current period comma but when we use a filter in the amazing same period last year with in the dates argument the date column and the date table and this is one of those time intelligence functions that works perfectly with the date table to take whatever the filter context flowing in is and throwing it back a year same period last year delivers a valid list of dates calculate takes it throws it into total sales the fact table is filtered to the last year's period and bam we get total sales for the previous period now shift enter and we want to use return everything after return is going to be our formula but check this out shift enter let's just see and we'll down arrow down to the x y variable tab and we'll just enter this and see if it actually is giving us the previous period sale so enter i'm going to select the visual check our measure collapse the formula bar select f sales and sure enough for january it got the previous period sales now one thing we're going to have to do is these are all the correct previous periods by month by year but in the grand total cell that number is meaningless and we don't want our calculation there so to show a blank in the grand total cell rather than the formula we want to notice that everywhere in the report the year column in the date table shows exactly one year 2023 in all of these months there's just one year 2022 just one year it's only the grand total cell where the date table is showing more than one year so we'll create a logical test using the has one value dax function on the d date year column now let's click back on our measure and below return i'm going to tab if has one value there's the year so the logical test is asking does the filter context have one year comma when it's true we want to divide in the numerator the difference between total sales and total sales for whatever the previous period is comma and then we compare it to our variable previous period sales now we're going to leave the third argument of divide out that way if it gets a divide by zero error it'll show blank and same thing for if if we leave it empty and the logical test is false it'll show nothing with that blank function close parentheses and that's our formula actually we could format this shift enter and then before divide shift enter backspace shift enter backspace and we'll close off on the if i'm an excel array formula guy so i always type my formulas out linearly but this is the convention for dax when i hit enter add some percentage number format click on f sales to collapse that formula bar and there for 2022 we can see the monthly year-over-year percent changes in march we get correctly 16.19 percent change in the total row we get 20.19 percent and if we scroll down all these percentages are working and the grand total is showing nothing now look at this the font sizes are different those are different formatted elements so we come over to our paint roller on the grid let's say text size back down to 10 and then subtotals will match that all right so our calculation is working here now what we want to do is we want to compare each individual monthly sales amount to the yearly total so we get percent of year total we're also going to see how to compare all of the values to the grand overall total and the way we calculate the grand overall total in every single cell is to remove all filters from the entire data model and you do that with the all dax function when we want to remove everything but the year filter then we use the dax function all accept we'll create our measure up in the formula bar percent of grand total is the name and we're going to divide and in the numerator square bracket t there's our total sales so it'll see the filter context in every single cell comma and in the denominator well we need the grand overall total and any time you're changing the filter context you use the calculate function just type the letter c it's the first function at the top and tab we want to change the filter context for total sales measure comma and in the filter argument this is where we use all tab and the all function can take a table or a column and it removes all filters now we're going to put the fact table f sales into all in a star schema data model the fact table is in the center surrounded by dimension tables all filters flow to the fact table so when we remove all filters from the fact table it removes all filters in the entire data model so it doesn't matter what conditions we drop into our visualization this is going to take all the filters away close parentheses and calculate will change the filter context so that that total sales calculation has every row in the fact table now we're going to close parentheses on calculate and on enter add percent number formatting make sure your visual is selected and check percent of grand total and every single sales amount is being compared as a percent of the grand total now let's come up to the formula bar and i'm going to copy this ctrl c escape make sure that the fact table is selected new measure ctrl v and instead of percent of grand total i'll say percent of year sales total and we need to change all to all except so with my cursor after the l i type in e there's all accept and i have to click back inside because all except has a table and then i comma i want the column that should still filter the fact table d date year column so all filters are blocked except for that year column when i hit enter add some percent number formatting make sure the visual is selected check my measure and bam there it is percent of year total all the way down now the grand total cell shows 100 percent maybe you want that there maybe you don't we can amend it and i added if has one value on the date and added some formatting and now when i hit enter the grand total cell does not show a value i'm going to click on percent of grand total and i'm going to change this to indicate that the measure that we're using is the sales measure and enter now we've created our seven measures and we created them in the reporting area on a page called test measure if we look over at our f sales table we can count seven little calculator icons but there's a bunch of fields and we want to hide these fields so we'll go over to model view and as we talked about last video we don't want to leave a number field in a fact table because we don't want anyone to accidentally drag and drop it into a visual because then it creates an implicit measure we also don't want them to drag date from the fact table we want them to drag date and any attributes from the date table so i click on the first one hold shift click on the last one and over here in power bi desktop when we right click it's hide in report view end of the month was for a calculation number of months was for the sort by action we want to hide so right click hide in report view cost and price those are for calculations only we don't want to drag and drop holding control click on product id we want to hide that too so right click hide in report view sales rep id also alright so we're done with our data modeling we use power query to bring all the tables in we use dax formulas to create our date table and a bunch of measures we created our amazing relationships that allow filter context to occur allow us to drag and drop fields from any table into any report and of course do formulas like with our related function and now we've hidden the fields we don't want in reporting view so now in power bi desktop we're ready for step three reports and step four visualizations all at the same time so we go over to report view now in the reporting area these are called pages we'll add a new page in just a second these are the tables with the different fields and notice now our fact table has only measures and the date table shows three fields and the visualizations have both report like visualizations like a matrix or a chart like visualization like a line chart and there are lots more visualizations including this more button where you can get things like word clouds and then we have the filter area which is sort of like the filter area in a pivot table but it has a lot more versatility now let's see how to do this we'll come down and click the new page plus symbol double click this and we'll name it enter something like year month sales report all right i want to make sure that the white area is selected and our first report will be a matrix so i'm going to click matrix from d date i want year in the row month below and i see my yellow bar there it is and i can simply check total sales over in the row area of the visualization i'll right click expand entire level we can resize now with this matrix selected i'm going to copy it click in the white control v and drag it out here and i can convert this matrix to a line chart by simply selecting it and then clicking line chart now watch this when i resize this it comes out more perfect than a line chart in excel by a long shot there's a chart title which shows the measure and the conditions filtering that measure the measure name is in the vertical axis down here we see our conditions and criteria in the horizontal axis any visualization that is selected we have our field area which is where we dragged and dropped or we copied and pasted but there's our format paint roller so i'm going to click and for the horizontal or x-axis i'm going to look through here and there it is i'll click this drop-down and we can look through and format and sure enough look at this concatenate labels if i turn this off that looks much better so literally one or two clicks and i have a chart that is looking amazing now there's more amazing things to power bi visualizations if i hover over a data point that's called a tool tip now we have these over in excel pivot table or chart but what's cool about a power bi visualization is we can come over to fields and down under tool tips we're allowed to drag measures here and then when we hover the result for those measures will show up in the tool tip now this chart is showing sales so i'm going to drag year over year change down to tool tips and then this percent of year sales down to tool tips and just like that they show up in the tool tip for any given data point all three measures are being calculated now this is kind of wonky whatever measures we drop into the tool tip are going to show up in the chart title and i think i just want total sales by year and month that original label so we can go over to the format painter and down to title and i can't find a setting that says turn off the tooltips but we can edit this and it's kind of hard to edit down here but i'm using my arrow keys and then shift and arrow i've highlighted what i want to delete and delete and enter and i like that more concise chart title so we have our field list to drag and drop we can choose our visualizations field is where you drag and drop the paint roller rollers where you format we also have filters and for the conditions that we dragged and dropped in the field area they show up as fields on this visual and you can come over and filter it clicking the drop down you could do something like is less than 2023 and click apply filter if you want to get rid of it you can click the eraser i'll collapse that now we have filters over in a standard pivot table and a data model pivot table and if we were to drag a field here we could filter this page which is sort of like a pivot table but we could also drag some filter here for all of the pages so the three sections of the filter pane allow us to filter just the visualization this whole page or all the pages that's a pretty powerful ability to filter now the next amazing thing about these visualizations is if i click in the matrix 2022 the matrix and the chart are both filtered that's the interactive part of power bi that's missing over in excel if i click 2022 again now i see all the years now you may or may not want that but we totally have the ability to select whatever visualization it is and then go up to format and edit the interaction so when i click edit the interactions because this visual is selected now i have the ability over here if i say don't filter now when i click 2022 it's not being filtered i think i like the ability to be filtered now if i select this visualization and i have that on now i can see up here and i think i don't want this one to be filtered so now if i click a data point it's not going to change this click it again to turn off the filter here still not changed so that's a great way to control the interaction between visualizations all right now up at the top there's some controls here and they do some pretty amazing things if you click the dot you can actually export the data as a csv file you can show as a table and when i click it has a table and you can switch it side by side or vertically then you can click back to report there's also spotlight which grays everything out except for this visualization and you can do sorting we can point to focus mode makes it bigger back to report if you have any filters from here when you hover it shows you what the filters are the next four arrows are for drilling down drilling up and going to the next level right now we have two variables so if i click the drill up it'll show me the totals for year from the year variable i have two options i can go down to the next level which shows me the total of all the months together so when i click that's all the januaries together and all the februaries drilling back up when i click this option i go down one level and see both variables now if i drill back up if i don't have this option on when i click on 2022 it just filters other visualizations click again but now if i have drill down on now when i click on 2022 it shows me the months which is the next level for 2022. back up i can look at 2021 back up down to the next level showing both variables now we have data for three years here later we'll get new data and we'll have a fourth year now this is a line chart visualization and the line chart is the perfect visual when you want to show changes or trends over time now we're finished with this page we want to go and create a new page with some new visuals i already clicked the plus button and named it year metrics dashboard now here's the dashboard we're going to create a matrix a clustered column a stacked bar a clustered bar all to monitor yearly sales now wait a second did i use the word dashboard i even had the nerve to name this page in this power bi report a dashboard i say that sort of as a joke but over here in power bi online the word dashboard has a very specific meaning for a given workspace there can be dashboards and this is a dashboard that has visuals pinned from multiple different reports and even an excel workbook so technically in power bi when we use the word dashboard we're referring to this location but a dashboard built to monitor activity can be built anywhere we can build it on an excel sheet we can build it on a page in a power bi report because a dashboard like this can be defined as one location where we can present useful information and metrics for decision making that information is presented in a neat and organized manner we can have tables charts visualizations data validation pictures and whatever else you want and very importantly when new data arrives the dashboard can be refreshed we all know what a dashboard is if you've ever been in a car that dashboard in the car is helping you to make good decisions to start the year metrics dashboard we want to click inside the white come over and click matrix from the d sales rep i'm going to check region that sends it to the rows i want year but if i check it it'll send it to the columns so usually i just drag it and i want it below region then i want to check total sales year over year change in the region right click expand all and now we have a region by year report now although this is a matrix visual this is in a table format and table formats are good because they show all the detail but if you want to take the same categories and amounts and have a more immediate visual impact we can choose a different visualization and the best visual to compare amounts across categories is either a column chart or a bar chart and there are two types for each there's a stack bar or a clustered bar stacked column or clustered column we'll choose clustered column let's start off by checking region and total sales now when you have one variable like region then clustered and stacked are exactly the same it's when you have two variables for some amount that there's a difference between the stacked and clustered now i want to drag region to the legend year to the axis now we have two variables and we can see that the columns from the legend are clustered together side by side when you use a clustered column or bar the emphasis is on comparing whatever amounts are in the legend clearly we can see washington is bigger for 2021 but california is bigger for 2022. now when we switch over to stack it means we'll take whatever items are in the legend and stack them on top of each other so if i select stacked column the same two variables are there and the same amount is there but here the emphasis is not on comparing the legend but we want to be able to more clearly compare whatever the variable is in the horizontal axis so we can clearly see that 2023 is the biggest year but we still have the detail from the legend now let's select cluster and i'm going to come over and drag year over year change to the tool tip and notice the first year doesn't have that metric but it's smart enough to know the amount of change from california from the previous year was 36 percent all right so we can resize and move it around and and we can see the red lines that help us line everything up actually it's sometimes hard to grab over here in power bi in excel we get a different cursor but over here it looks the same and there we go we can try and center it all right our next visual is total sales by product and year so i'm going to click in the white and i want a stacked bar product is going to go in the axis year into the legend check total sales drag year over year sales down to tooltip percent of year down to tool tips i can see it emerging over here we can resize it click and drag come over to format title i'll adjust this that's looking better and i forgot over here i'll click on the visualization and format the title in the same way all right so that one's looking good now we want to do our fourth visual make sure the white is selected because and this is something i do a lot if i leave this selected and come over and select clustered bar oops it converts it control z so make sure the white is selected clustered bar i want the supplier for the products in the legend year and the axis and the metric i want is average monthly sales and it looks like it came out perfect including lining up with a visual above i'd like to see excel do that now this is an insert after i finish the video because i left out one teeny detail if you want a visual like this but you still want to see some of the detail select the visualization come over to our paint roller and just like in excel there's data labels i can turn them on and boom right at the top you have data labels and that's looking good now let's decide how these visualizations can interact if i go up to format and in fact before we click that let's see what the default is for these bar and columns if i select washington that's a different default than we saw on the line chart and if we select the matrix click interactions we can see there's three options i don't want any filtering filtering and that's the highlighting so for this matrix i don't want this to be filtered at all this one will leave the default highlight and this one i want to be filtered so if i turn this off and now when i click washington this one's filtered to show washington and this one highlights washington where for that full bar there that's just the washington park and down here that didn't get filtered or highlighted at all clicking back on washington all right so we're done for this dashboard now i went ahead and added the last page called it product metrics i created a matrix with product year and these measures and then a line chart with year in the axis product in the legend and average monthly sales now we have one two three different pages and a sheet with test measures now at this point you can save and share the file just like an excel file but we want to see how to publish and bring this to power bi online now before we do that when we publish these will show up as pages this will be a report that we can click on and then view the different pages but this was just for testing and we want to keep it here but we don't want to see it online so we can hide it right click hide page now we want to publish to power bi.com and to get the most out of the online power bi you need the pro license or hire but i'm going to click publish it wants to know which workspace and a workspace is a great area where you grant access to people and they have access to the reports the data sets the dashboards and more so i'm going to use this established workspace and then click select it's publishing now i can jump to power bi by clicking this if you're logged in to outlook or any other microsoft 365 app you can use the app tile click power bi or you can simply type power bi.com and enter you need to sign in here at power bi online we have our workspaces listed at the top over on the left we can choose a workspace this is where we save the report and there's our report and there's the data set now both the report and the data set are available to people who have access to this workspace so clicking access you add the emails and then those people would have access to everything in this workspace to view the report we simply click over on the left are the pages clicking on each page the page works the same as in power bi desktop when i select a particular element it does what we programmed it to do over in power bi desktop you can also share up in the share drop down if you click report you can enter email addresses an email down here is the report link that you can copy and send to someone also if you want to publish it at a website for example you could get the embed code here and one of the benefits of power bi online is when you share the report they can view it on any device their phone a mac their pc laptop now when you get the correct license you can build new workspaces it's as easy as create a workspace and then you add people's emails then you can share your data sets and reports also if we go back to the workspace that's the report but here's the data set that was uploaded if i click this if i wanted to i could create a report from scratch here and it's very similar to power bi desktop you could also bring it over into excel and create a pivot table now last video we saw that if i go back to this workspace any of these data sets here whoever has access to this in their pivot table drop down they can build pivot tables based on these data sets the last thing is a dashboard so if i come over to the workspace and click the drop-down there's no dashboard but you can pin any visualizations from the different reports and even an excel workbook to a dashboard so if we went to this report let's go over to year metrics if i want to share this and we don't have any dashboard so when we share it it'll ask us to create one up under more options i can say pin to dashboard i'm going to call this eda as the dashboard name and pin live if i go back over to our workspace here's a different report i'm going to come up pin to dashboard i definitely want it on that dashboard pin live and now we have a dashboard over here and when i click refresh then over here clicking on our dashboard this is pinned from one report this is pinned from a different report and you have your sharing options up here all right so that's just a tiny look at power bi online for sharing back over here in power bi desktop here's the moment of truth there's our new data in an excel file copy paste in power bi desktop in the queries group we click refresh there's four years four years here even in the legend and in this bar chart if we look at our query clicking transform data selecting f sales selecting added custom column step what i don't see the four tables refresh and there's all four tables now we come over and click publish it wants us to save and select a workspace click select should we replace it yes over here in power bi online we can refresh with f5 bam everything's updated all right in this video we did a lot with power bi desktop we started it off with power query we imported two excel tables but very importantly we saw how to go from a folder and use the xl.workbook function to import tables from multiple excel files we saw how to create a dax day table we even saw a glimpse of this dax date table then we created all these different dax measures all in report view so we could see the formulas evolve then we saw how to do these amazing visualizations including things like adding tool tips and how to get visualizations to interact in different ways and then of course we saw how to publish to power bi online so people can view your awesome reports and dashboards on any device all right if you like that video be sure to click that thumbs up leave a comment and subscribe because there's always lots more videos to come from excel is fun including next video where we deal with big data and we'll see how to do it both in power pivot and power bi desktop [Music]

Channel: ExcelIsFun

Views: 30,150

Rating: 4.9876924 out of 5

Keywords: Excel Data Analysis, Data Analysis in Excel, Analysis in Excel, Excel analysis, Excel Analytics, excelisfun, excel is fun, Mike Girvin, Highline College, BI 348, Microsoft Power Tools, Power Query, Data Analysis basics, E-DAB, MSPTDA, Analyze data in Excel, Learn data analysis, free data analysis class, Power BI, Power BI Desktop, Power BI Online, Date Table with GENERATE and ROW functions, DAX Measures, Power BI Data Model, Import multiple Excel files, Excel.Workbook

Id: vBLM3usYRF4

Length: 76min 13sec (4573 seconds)

Published: Mon May 03 2021