Transcripts
1. What you will get out of this course: everyone. My name is Charlie Walker, and I've got a great course of you guys on Microsoft Power Bi I desktop. We're gonna go from 0 to 100. We're gonna go from downloading the actual software, installing it to getting our beautiful report and dashboard ready todo within 30 minutes. And from there we're gonna go into some more advanced features of power bi I desktop, including the Dax query language to get some really powerful functionality, we're gonna go through some really life examples. I haven't counted on the job and skills you can take with you to your next job interview or to put in place of work to really stand out power. Bi I desktop is a free software, so you don't need a license for it. So you can download it on your work, appear and plug away at any Excel file or databases. You have access to power. Bi. I has completely changed how I perform data analysis and this course I'm gonna give you all the tools you need to be Justus effective. Everybody knows that data visualisation and data analysis are huge topics right now and adding the skill set to resume will give you a huge leg up regardless of whether you're a new job seeker. We're just trying to get a promotion in your current role. So start this course right now, and within 30 minutes you're gonna have a beautiful dashboard with great eight analysis that blows the socks off any Excel project you ever create, So I'll see you guys only inside.
2. Course Introduction: Hey, everyone, I am Charlie Walker, and I'm so glad to have you in this course. We're gonna cover a ton of information in a short amount of time, So let's jump into the agenda and go through what we're gonna cover. So we're just start off with the absolute basics from downloading the software and installing out to your computer on. And then we're gonna take a sample data set and Excel data file that we're gonna load into power bi I and really just start to play a little play around a little bit with the the interface, the software pleasurable, visualizations and see get comfortable with how it works. And we're gonna go through step by step, how to create our own dashboards. And at the end of it, we're gonna have this beautiful dashboard that's highly interactive, is has some great advanced formatting is really gonna show us some insights into the data that I think it's gonna be really fun. They were gonna slit in a little bit and go through some of the different advanced visualizations. We cover a lot of visualizations in the dashboard, but we make sure to cover all of the ones we didn't touch on and the ones would did touch on. We're gonna go through a little bit more in depth. Then we're gonna go into relational databases and Dax, and it's a little bit of an advanced function, and it sounds a little bit scary, but it's really not. You'll have no problem going through it, especially if you're. If you're familiar at all with creating Excel formulas, you're not gonna have a problem. And finally, we're going to go through a case study where I give you another set. Another sample data set, and you're free to create your own dashboard based on the skills you've learned throughout this course. And then you're gonna publish it to the assignments pain, and then I'm gonna give you gets and personalized feedback. Um, so let's jump right in
3. Download and Install Power BI Desktop: All right, now, let's go step by step on how you actually get power. Bi I It's actually super simple. You go to the website and I just I just searched for power bi I and it's gonna be right here for me. I do want to show you a little bit about the products they have and what they mean. All right, so you gotta products, you'll notice there's power bi I There's Power Bi desktop, and these are two different things. Power bi Desktop is free, and it gives you the ability, the ability to create all these kind of reports and slice and dice data and really a completely functional product. And it's completely free. Um, the limitation has is getting that data out of that report, like if you want to export it to a pdf like your reporter or you want to push it to a manager a little bit difficult because they have this power bi I service in power. Bi I is powered by itself is a platform where managers can have it's his business intelligence management. Um, you know, you can have all these different dashboards and reports and and data that streams to you and is updated constantly. Um, it's really powerful dashboard tool power. Bi I is more for the end user. So if you're creating these reports for your manager, they will be using power bi I and they can actually give it on their mobile device on the phone, tablet, whatever. And they get the reports. But so for this, we want to focus on power Bi desktop. That's what we're actually gonna do the data analysis and creating the reports. So we'll click on Power Bi desktop and you can just kind of see their little, um, you know, page here, just kind of explaining some different things. And we just clicked. Download free, will show up in your little den, would bar and then we'll install. Okay, fast forward to downloading and click on it. It's just like any other software installation. You just click on it a prompt you toe continue and you download it. I've actually got installed. So going to go as far as I can without ruining what I've already got. Okay, Next accept the terms next. Next. It was pretty, pretty simple stuff here because all right, so I jumped ahead I had to click Yes, to allow administrative rights that this software could be in stone and it looks like it's complete installing All right, so hopefully years installed just these these minded. If you having some trouble, pause the video and you should be able to get through it. It's gonna prompt you to automatically launch the software. So I'm gonna say yes, I'll click finish eventually. It should look all right. It looks like we're open. Run, Stay in the background. All right, there we get. So it's gonna show this little log in screen here. You can do this. There's no there's no harm in it. You actually don't have to sign up for anything. If you don't want to, you could just click already, have an account and then get out of it so it doesn't force you to do it. Okay, so this little splash screen here's got some helpful information. If you do want to be able to push some reports to the power bi I service the one that has paid there is currently a free 60 day trial. Um, that should give you all you need to get in and figure out if it's the right solution solution for you. Um, and just test it all tested out. All right, So I'm not gonna sign or or do anything like this. I'm just gonna kind of keep continuing on. Um all right, let's go to the next next section.
4. Importing the sample data set & getting familiar with visualizations: All right, so now we want to do We've got the power bi I downloaded and installed. We've got it up. We want to import some data. So in the course materials, I added a sample data set that we're gonna use to import into power bi I so we can slice and dice and do some different things to it. Um, so it should be called superstore us 2015. So wherever you download it, go find that, all right? And then to import data. What you do is you go to get data, My cheer, The home tab here could get data and you'll see all the options that you have for importing data. So we're gonna use excel, which is to show you you can import data from a folder from an access database. Um, I mean, there's tons and tons of different things that you can use. For example, if you're if you're looking at, ah, fearing marketing, you're looking at some of your, um, your email marketing coming in, you get a male chimp. There's Salesforce, reports, Google analytics data. You can pull in from all over the place. This is truly an enterprise ready product. So it's very fully functioning. We're going to use Excel. So DoubleClick excel and then find wherever you put your file that that we we got for you and then just click, double, click on and open, the first thing is gonna happen. Is this going to show you the three different tabs that within that file there's orders returns in users? For now, the only one we need to mess with is yours. So let's cook orders and then let's let's you can click Load, But let's just go ahead and edit it. That way you can kind of preview what we've got. Okay, so this is called a query, and a query is basically any table that it's referencing that you've imported. So you've got all these different. I mean, if you would open up the Excel file that you download, you would see the exact same information. Um, what's different about this is you can't modify any particular item, so I couldn't go in here and change it to someone, something or other value. It's static. If you need to change the data, you have to go back out to excel, modify it, and then reimported. All right, so each row, since it has a if you want to the Excel file the first row would be some sort of column column name will excel on our sorry power bi. I automatically determined that it needed to use the first Rose Headers, and it's done it that would and also noticed that for things that are this is a number. So it's got 123 for this. It's high. It's much of text. The the the data format is a text. This is a decimal and so on and so forth. If you needed to make an adjustment to that, you could do that. But I think that the way the data came in, everything is pretty good here. So we're gonna go ahead and get close and apply. Then this should go where? All right, so you got your first data set pulled in and it shows all of your field on the right side here. Next, we're gonna go start playing with some of this data, slicing and dicing it and make some cool reports
5. Dasbhoard introduction: Okay, so we've got the software installed, and we've got some data imported. Now, let's look around Power bi desktop and figure out what it is we have here and what we're looking at. So just you know, the first view that you're gonna have is this view of just a blank canvas, and we're gonna be on the left side of the videos. Three icons. The 1st 1 where we are here is just the reports pain. And this is where you're gonna be building your dashboards and reports the 2nd 1 down here is data. And this is where you're going to see all of the queries and all of the tables you've pulled in. Currently, it's just showing this one table we have, which is the orders, Um, and then we've got If you click down further, you can see relationships. This is called relationships. And what this this is going to do is if we have multiple tables, which we will later on, and we want to be able to reference some data in another table by linking it to another table. For example, row I D or customer I d. We're gonna be with a link that in here through relationships. We'll get to that later. Let's go back up here to report. So we've got a blank canvas here and get this visualizations pane and then our fields pain . So I'm gonna cover this in a second. We'll skip this fields pain. This is basically all of the columns that you have in your Excel table and a good way to think about this is attributes for your reports. So we've got our sales, and we can think about it by these two different things that weaken segmented by So we could do it by sales by region or sales by customer segment, all kind of in things. And it's super easy to be able to just drag and drop and click and just create these awesome reports. This may, you know, this may feel like a pivot table in Excel if your experience with that and it is similar, there are some very similar. There's some similarities there. There's a lot of things that this software can do that here that are way above the scope of excel. So let's go into the visualizations pane. So you notice when I click sales and customer segment, it automatically selected this clustered column chart. So it's just trying to find something that would be a useful report. Automatically, we can obviously customize it. So what we've got here is just different reports or different visualizations, which are different types of even thinking, like you can think of them like pivot charts and pivot tables and so easy to just click through all these different things. And it will just automatically change the data format for to that view, and you can even just copy and paste. I'm just take control, seeing Control V and then he can see it both ways. So, you know, out of the gate here, you can see there's some really easy abilities to do some interesting data visualizations very easily just by clicking and doing all that, um, clicking and then you have selecting. So it obviously gets super customized and gets really advanced. But, um, that's just kind of an introduction to what we've got here, and we're gonna go through all these different visualizations through this course. Some of the formatting, some of the different advanced features within here, how the filters work, Um, throughout the course. So one thing I want to show you guys just in this little introductory court segments to show you some some of the cool stuff. It's got one thing. So currently haven't shown anything that is your better than excel or that you couldn't already do in excel. But one thing that I do want to show that I think it is really cool is, uh, actually, we don't need that is this slicer function. So if I click this icon here and of course, we're gonna go through all these in a little bit, so you don't need to necessarily follow along with me. I just want to show you guys some things. Uh and then this is basically a segment, I guess you could call it. It's just it just filters the data on the page by certain things. So if we wanted to do it by region, just drag that in there and so Okay, so I want to see, let's just make this, uh, since we already have that data there, let's make this product category okay? So if I wanted him, if I want to see this information by region, I would just start clicking through the progression and it automatically changes the data on the page. And when you have a lot of data on here and a lot of different reports, that makes it really cool and really easy to analyze the different information. So let's say, for example, your CFO and you are just reviewing site quarterly or monthly or annual sales profit, all that kind of thing. And you're thinking, How are we doing by region? It's easy to just click through here, and you can see it just automatically updates and actually automatically sorts as well. So this is just kind of an introduction to what we've got here. In the next lectures. We're gonna go through starting to build your very own dashboard. At the end of it, you're gonna have a really nice, clean, pretty informative report that you're gonna be really proud of. So let's jump into it.
6. Starting your Dashboard: All right. So the last lecture we went through, just a basic overview of some of the different things wanted to jump right in and just show you some of the some of the cool things that it could do and how easy it is in this lecture . In this series, we're going to start working towards an actual dashboard with an end goal in mind so that we're gonna work towards something that you can have as, ah tangible. I finish this project and this is what I wanted to do. So let's go right into it. I'm gonna open up a new tab, just going down here to the bottom and clicking page three. All right, So creating a dashboard, you have to think about What is it that you're wanting to address? Where you wanting to What information are you going to convey? Um, and one of the goals of a dashboard is to be able to convey as much information as possible without overwhelming the user. One of the great things about power bi I is that unlike some dashboards where you just give them information, maybe it's, you know, some slides or something like that Or maybe it's just a static pivot chart. You know, summarizing some information power bi I is much more flexible in that they can actually play with it and click around, and it'll change the reports. So we're gonna build in some of that functionality into it's the objective. Here is to basically take this Excel file this huge database of information. I don't know how many thousands of euros it is. So it's 2000 rows and turned it into some information that's gonna be actually useful. And so the target here is revenue and gross profit. We want to capture revenue trends and gross profit trends and how they relate to each other . And we want to make sure that it's segment and as much as possible. So the options here we have for segmentation is we've got product category. We've got product container, the product name products, subcategory, region, customer information, like the customer name, customer segment. A lot of different things can't Country city, etcetera. We're gonna pick and choose what we think is important for a dashboard. I don't think customer name, for example, is gonna be important, cause I'm pretty sure there's thousands of customer names, right? So this is not gonna be useful information, but, for example, revenue and gross profit by customer segment, that would be something that will be important. And then, you know what? If you want to see it by the region or what if one to see it on a map and see where our sales are going to our customers? So that's that's the objective. Here is is revenue gross profit with multiple segmentation. Whatever we think is most important to the user in this case, the user's gonna be a CFO Looking at this information and one of the terms you might hear often is just give me a one pager. So they say, You know, I want some information, but I don't want you to give me a 20 page power point. Just give me a one page or so I know where we stand on something, and that's what this dashboard is gonna be able to do. It's always gonna be customizable on dynamic, as I mentioned, they're gonna be able to interact with it by clicking on different things and being able to see, you know, like we play with earlier. Um, they're gonna be able to play. You let me look at the East region or the South region, that kind of thing. We're also gonna build in some things where they can look at it every time. If they don't look at the last month or the entire period, that kind of thing, they can play with that themselves. And we're gonna build that functionality in. So no, you kind of have a framework of what we're trying to achieve. Let's jump right into it in the next lecture.
7. Building blocks of an effective Dashboard: All right, let's start playing with some of these other visualizations and list also start playing with some of the other segmentation. So so far we've got sales and profit by customer segment product category. Why don't we look at some time based segmentation? So why don't we look at stuff over time to see how? See how the trends were going there. So what's pick? Uh, let's pick this guy. This one is good. Stacked area chart is good for showing things over time. Um, because it's gonna show different segments growing or shrinking over time and makes it easy to see. So what? Pull in Melissa's daughter for sales? Um, so once you once you click it in, it's in, it's in here. It's on the screen. Just click on sale in the Nepa populated in. And now we've got a segment you can see. It's got that 1.9 million that was started with because we don't have any of anything. We don't have a filter by anything else. So let's look at product category. Okay, so you noticed that it's gonna be on this access this X axis. That's where we were actually wanted to be time. Not that categories. One of the categories to be stacked. So we're gonna put that here, But it's not gonna do anything yet because we don't have an axis. So we're gonna put in time and the way they were gonna do Time is basic on this ship date. So we've got order date. We've got ship date, which having those two segments it could We could do some different things with seeing lead times. Like, how long does it take for between how long is the time between order and ship? But we're not gonna go into that now, But we're going to choose ship date to determine that the sale when when it actually shipped. And we're to use it for time. So let's do ship date. All right, and notice that it's it's not really what we want. It's gonna make us select the time period that we want. Um and so this information that we have is this databases January 2 to June 2015. So we've got six months of data, so if it was six years, I would say we maybe two years or quarters and I wouldn't want to quarter because when we have 2/4 got q one and Q two. So I'm gonna take off everything but ah, but month Because I want to see it by money. Okay, so there we got it. Now we can see over time how our product category sales did. Um, so what's just kind of look at it and see if we can find anything insightful in April, we just had the highest month of ever of this period. And in July, it just tanked. And I wonder if that's actually a component of one of this data set. Actually cut it off. Maybe, Like after Jill. After a few days in July, something like that. We could find that out. But for now, we'll just always assume to four months and looks like sales tanked. So something interesting happened here in the office applies category. All the supplies went from $56,000 in March 2 $191,000 in April. So I wonder why. You know, I mean, if you're giving this information to the CFO, um, you know, he's gonna he's gonna digest that information, see if there's any insights to gain from it. One thing you could do is just plot this year over year and see what is. My sales, like in April is April, just a month that we could expect typically huge sales and particularly in office supplies . Is there something that happens in April that makes off supplies go up? Maybe it could be that corporate budgets are getting finalized in February and March, and departments are getting their budgets released. So they have all this extra funds. And maybe that's why there there's big sales in April? Not really sure, but yeah, this is This is a great chart to give us that insight to where we can start figuring it out . Also, there's some a lot of growth in technology. In February, technology had $117,000 in February, compared to just $86,000 in April. So, you know, we just kind of play with play around with that and see different things. So this is definitely one that I want to keep. So this point, we're starting to think through. We've got a few things on the screen. We got to start considering what's most important and what's not so I'm just gonna start re sizing these so we can fit some more stuff on the screen. Um, and just think through what I want to show next. We've got some things ever time. We don't have anything about region or state, so it's added in. And one thing that power bi I has that's I think, is really amazing is the map functionality. So if you go over here to the visualization and you see this filled map, just click it. And now we actually have information about the location of where this is being sold. We've got the region, State, city, even the zip code. It's weaken drill down really, really detailed into this for this particular dashboard, Let's just do a by state. So we want to ad sales and then notice that got the world map has automatically populated. Okay. And we're gonna add these state. So look at that. I mean to me, I think that's super cool. It automatically zooms in on the United States, and it fills the maps based on where the sales are, and it's doing this through being so depending on the information that you have, the data said, you have it sometimes is gonna spin because it's gotta send information out tubing and then pull it back. But to me, that's I mean, how cool is that? The information here is not very exciting. The, um their sales in New York and California, that of leaders and then Texas and Washington. Oh, that you can't really see that much difference. But, um, what will happen is when you start segmenting but segmenting it by region, it is gonna be more distinct. If you look at the set Southern region, you're gonna be able to see you know, some differences there or, you know, the different regions that you look at. So we're gonna keep it. But for now, it's not really that exciting of data except for the fact that the functionalities super cool. So let's also throw in one of these donut charts, and we're gonna use this also for sales by state, just to give us a rundown of water, mind what's my ranking? What's my ranking of sales by state? And so you just click this and we've got this here and we're gonna do sales. We're in a segmented by state. Okay, so one thing you'll notice it's not exactly sorted in the way that we want it. Um, if you want to change the sorting and this goes for anything any of these visualizations if you right click, are you not to right click? Sorry. You click on these three little dots and then you sort it by you get sorted by alphabetical , or you could sort it by sales. That's what we want. Case We've got California, New Orleans, Illinois, Texas, Florida, Washington. That's actually surprising. I didn't think just based on this, it looked like definitely California. But I would have thought, uh yeah, New York. All right, so you that could be helpful just to kind of give you a perspective of where all yourselves air going and again the same with the segmentation of the region. Once you said once you drill down into just the eastern region or the southern region, then you're gonna be able to see some more from more helpful information than just looking at it. And in total. Okay, so I also want to look at it by, um let's look at some of these other categories that we have really touched on yet. Um we've got product container E. I don't know if that's really gonna be that impactful. Unless product container in in shipping costs that sounds like something. Let's say we get yet product container and shipping costs. That sounds like something that we would want to add if you want to do more of a deep dive analysis versus a dashboard, Um, CFO that wants a one page dashboard on sales and profitability is probably not going to care about information about the product, product container, the shipping method, um, and our to show you the difference I've got there and then, yeah, so that's kind of what it looked like that would be more relevant for likes that an analysis where you're trying to find some information versus a dashboard So we'll ignore that and let's see what else we got. So we've got customer name, but as we mentioned, that's just gonna be too much information. That is not gonna be helpful at all. But we've got this product, subcategory that could be helpful. And this is about 20 or so items, and we've got something like this. This is a perfect time to do the tree map. Okay, So that's this guy here. Let's just start from scratch that So we're just clicked on here and we're gonna do tree map and one thing on the show and those that we clicked on this tree map it automatically populated here right in the middle of our screen. Not over here, not over top of any of these things. It automatically tries to find the best place to put something based on the space in your area that's that's left on your on your on your screen. I think that's really cool. All right, so we've got this selected and let's look at sales by buy this product, subcategory. So cook on sales and then product category or subcategory and look at that. So what it does, it basically ranks these in terms of sales or whatever you have. It's segmented by and has different color coding, and it just goes down, and the smaller they are, the lower they are in sales. So the smallest item we got here is rubber bands, so they don't sell a ton of rubber bands. They probably do this. The price is pretty small. All right, so office machines is the leader in terms of product sub categories. Okay, um, so we'll find a place to put this, but for now, I'm just gonna let's make it a little smaller, and we'll just leave it here for now. Okay? So another thing that I know I want to do is we've got revenue by product, category by two over time. But I also want to see profit. I wanna see profit by product category over time. So what I'm gonna do for that is a bar chart. Good. So I've got profit, and I'm gonna do it. Or so So it's gonna be a stack bar chart. Um, where they call it stacked column chart, stack, portrait, whatever you do profit by. So the other thing we need is this ship date and by subcategory or will do product category , we don't. If we did, product subcategory will get to granular, and they would be able to see anything. Okay. And so we gotta fix this date here. Rumor. So we're gonna take up and want to see it by month, because that's just seems to be, you know, the most logical. It's currently by year, and it's just one bar, so there's not really tone anything. So we'll take out the year, the quarter in the day. Okay, so let's get some room on the screen here. I didn't see with God. Sorry. I was trying to play around here, and so we can see they're easy to move around, so it's not a big deal. All right, So now we can see over time when our profits are gross profits. So And also I want to highlight something. You could have negative profits in a particular month. So, um and we want to know that you know, when certain products are losing us money, so you can see there's zero here, So there's certain items where it goes below zero. So we haven't really touched on formatting yet, but I want to go ahead and show you one thing that's gonna be important for this particular chart. And it's a constant line. We want to be able to highlight the zero to see what's being so in March technology. We lost $10,000 in February, way made a profit of 35,000. So, you know, we just want how like that All right, so to make a constant line across there. You're actually gonna go this far right tab called analytics. And this does seem really advanced things more on the statistical side, we might touch on that a little bit, but for now, all we really want is this constant line. So if you add if you click down to on the constant line, I just click add and it's gonna default to zero, and it's gonna add constant line, but you can't really see it that well, so we want to change some of the changes in a little bit. Transparency. Let's make that zero. Let's make it black, and then we can see the law easier. It's a dotted line. We can change that if we really wanted to, but I think it's fine. Um, let's just make itself okay. So here we've got whatever this subcategory is off supplies very little negative profit in this particular month. And then these other categories air doing better. So it looks like the earlier part of the year there was lot less profit than the latter part of the year, so this is just definitely something helpful. I think we definitely keep this. Maybe we play with segmentation. Maybe we look at it by customer segment versus product segment, but I think we keep it for now. Actually, I'm actually just curious. I really want to swap those out just to see what that looks like. So we're gonna swap out customer segment for product category. Okay, so So you know, So I mean that the totals didn't didn't change, but uhm ki one was still bad. The 1st 1st quarter was still rough, but Okay, looks somebody even ah, small business really started showing some gains in the later part of the year versus earlier part of the year. But we'll play with that later on. Maybe said, but I must go to swat it back out. I do want customer segment there instead of a product segment there instead of I'm a customer second. Okay. All right. So now we're kind of trying to get back to our dashboard. We had to move some things around toe to look at it. And another thing that you may just want to do if you're playing around, is just put it to another page. Um, So I was kind of lagging there if you wanted. If you're starting to get a little bit constrained and you're starting around space. Just just do it on. Look at it on another page, and then you can add it back when she figured out what you what you finally want? Okay, so we've got got quite a few things on the screen now. I think it's a good stopping point. In the next lecture. We're gonna go through some slicers s so that we could make it really interactive for the manager and just finalize our dashboard.
8. Advanced visualizations and customization: in this section, we're gonna pick up where we left off from the last section where we've created some of these visualizations and start to hone in on what we want to show in the final report. So we're gonna be making sure that everything is sized appropriately. All the formatting is looking good and its and we've got just the information that we need and nothing else. And I also want introduce the slicer function because that's gonna add some interactive capabilities to a report for whoever is looking at this to be able to dive into whichever segment they would like to look, I'm gonna put the slicer functions at the top. That means we counting to make some room on here because we're starting to get a full. So I'm gonna start just pulling slotting these down to make some room. It doesn't carry it. Doesn't matter how how they looked right now, cause we're gonna we're gonna play with sizing in a little bit. Okay, so now we've got some room at the top and we want to add some slicers to add some interactive capabilities to our report. So this little icon here is the slicer. So we're gonna click it and we're gonna start to put in some some of the segmentation that we've been working with. So let's start off with customer segments, okay? And then we can just do control C control V to copy and paste. And let's change that to instead of customer segment. We also want to look at product category. Okay, resize this and let's see. So what else do we have? We've also got region, so it's do control c control V and now we can do it by region. All right, so now if somebody is starting to look at these reports and they want to resize this and they want to say Okay, this is great. This is an overview. What if I want to dig into a specific area? So let's say just a central region now it filters everything when I report kind of this kind of looks rough because we've had to do some re sizing. But, um, so now they could drill into just the central region, just the eastern region south, etcetera. And they can also say All right, so I would look at the central region, but I want to look at the corporate level. So then we've started to get some even further segmentation. And some of the reports you're going to see start to look limited because you've got it's segmented by certain things. So, you know, they're just like this one, for example, it's is gonna look odd, but there's other reports that are gonna be very useful that will still provide some information. Okay, so we've got a little too segmentation there. Um, these reports, I don't like how they how they look. They just kind of look old, outdated and not very polished and professional, So we can actually change how these air styled, Um and we're going to do it a little bit differently. So if you click on one of these, one of these slicers will let's go through the region. So we click in it and you know, you can see we've got this format painter were you click on that and then you can change the orientation of this. The oda General. You'll notice you've got a bunch of different options. We're gonna look at this orientation one and is currently vertical. We're gonna change that to horizontal. And when it does that. It doesn't just change the the orientation. It also turns it into these boxes that I think look pretty good. Um, and I think we're gonna use this for all of the slicers. Okay, So this kind of another topic when you want, when you want to change the formatting of multiple items, there's an easy way to do it by using this format painter. If you're familiar with Excel, you're probably familiar with this. So all you need to do is click on this one. So we're gonna click on Region to select it and then click the format painter and then we're going to select one of the other items. Okay, so we've done it now for product category, and we could do it as well. We can also do it for a customer segment. Also note that, um, the format painter, unlike Excel, you can't double click it and then just start clicking everything. You can't. You can't format multiple items by double clicking. Just a little nuance. Maybe that I had that later on. Eso will do for a painter, and they won't do customer segment as well. Okay, so we've got we've got these kind of built out. Well, maybe we'll play with some of the coloring formats later on, but Okay, so we've got that. But I also want to take out these headers. They were taking up space, and they're not really necessary because whoever is looking at this is going to know that these air product categories. So this just kind of redundant information. And that's kind of a general theme of dash boarding, Honestly, is that you don't want to show things you don't have to. Less is really more so we're gonna take this this header off. So if you click on one of the items, we'll go back to the format painter. We're going to see some of the different options. We've got titles off, backgrounds off. Other things were off this headers checked as only. So if we just click on it, it will just take it off and then I'll give us some more room, just kind of playing with here. And I'm gonna use the form a painter again to transfer those settings to the other items. Okay, so if you're following along, you should have You should have what I'm seeing up here. The top. It is not perfectly align exactly like I have it. That's fine. We're gonna get to that a little bit. Okay? So I'm just gonna kind of resize these and one thing one final thing I want to do for now on these slices and will move to something else. They're kind of just like words floating out right now. So just for now, I want to go ahead and add some borders to them so that they these different items, are a little bit more distinct. Okay, so we click on here, we go to formatting, and we're just gonna click on on the border. We're not gonna mess with any special formatting right now. Um, but just to show some distinction. Okay. All right. So now we've got we've got these things built out. Let's look at some of the other areas. So what other slice that we want to put in here is the date function, So that would give the manager whoever's reviewing this information, Um, the ability to look at it over time and then segmented by time. We've already got a chart down here. That's over time, but it will be nice to have some interactive functionality that they can look at it month by month by changing these other items as well. So we're gonna add that in, and the date slicer is gonna be a little bit different. You'll see that. Okay, so we got a slicer, and then we're gonna use ship date. So we dragged that in and notice. Now, when I when I have this the slicer for when it recognizes that it's a date, it's gonna kind of make the mixing room here. It recognizes that it's a date. So it adds this little slider here, which is pretty useful. You can just drag, you know, to get whatever date you want. If you want to look at just February, just drag and it automatically changes all the data. So if we would look at February, there would get, um so kind of me. One thing that I want to note in this kind of gets to another topic. You notice how it goes to 78 July A. Well, that indicates that this data set doesn't have any dates past July. So I don't actually want that information even shown. I would really rather it just be six months of data January to June. So I want to cut off after I want to cut off July and I could just move my slicer back here so that you know, that information shown that way. But there's another way we can do a filter on the entire page or in the entire report that I think is gonna be better. So when you if you're not clicked on any item just clicked in the background and you look over here in the visualization pane, you'll see these filters. There's page level filter drove through filter and report level filter we're not gonna go into drove through. But basically these filters, the page level filter will filter things on this particular page. It won't affect the other pages. However, if you add in a filter on the report level, it will filter every single page. So in just this instance, because we're creating a one page report, I'm just gonna put it on the page level, filters. So to add a filter, you, of course, just drag it in there as you expected. So we're gonna drag in the filled date our ship date and innocents starts off by default with this basic filtering. And we could just go all the way down. It's only a few and just not quite there yet. We could just uncheck thes and it's automatically going. Teoh. Well, sorry. I need to do a reverse. I need to do select all and then go on, select those items. But we're gonna do another way anyways. Okay, so now you see, it's automatically cutting it off, so those extra days aren't even gonna show. But it's You may not be in a situation where you can just check off. You know, it could be hundreds of days, so we're gonna do advanced filtering. So we're just gonna go into it and click on the basic filtering and select advanced filtering. Okay. And we're gonna change the show items when the value is to own or before. And then you can use the calendar function, or you can just type in. We know our date. It's gonna be 6 32,015 Okay, so and that does the same thing. So it cuts it off so that data isn't shown. Okay, so we've got a few different slices up here we've got a date slicer, um, starting to come together. Why don't we wrap this section up and then we'll pick up in the next one, See guys in
9. Spacing and sizing for prioritization of data : All right, folks, in this section, we're gonna be working towards wrapping up our dashboard. Were mainly gonna be focusing on formatting were in and introduce a couple couple new things, but mainly just focusing on formatting and spacing and some of the best practices in dash boarding to make sure that you're showing the information you want to show as elegantly as possible. Um, so let's diver I did. It's the first thing that we want to do now that work towards the end of getting everything on the on the dashboard here. We know we've got We might add something to take away some things, but we're getting close to the end. We're going to start thinking about structure now. Now, I've got these slices of the top and I basically got four items. I've got this ship date slicer. I've got your just my other just my other little segments. Now, if I have each of these items and in these air boxed currently so they show a border now I don't I wouldn't want to, for example, just have necessarily these things lined up like this, for example, like that just doesn't look good. What does look good or looks a lot better is if you can have these perfectly sized so that the intersections are basically just just fluid and and are really tight. So you have them, Um, you know, you basically just have a bunch of right angles, you know, versus that, For example, you know, So if you have them all lined up with this axis, it's gonna look a lot better. And it's and just doing that across the board is gonna make a lot better presentation. So one tool that we have is if you go into view, you can do show grid lines and then you can you snap objects to grid, okay? And so when we do that, that's going to give us the ability to, um, you know, way have something tight like that. It's going to it's going to make it perfect, right? It's gonna be a clean fish. No, we go ahead and add the border in here so that you can actually see where it's gonna land. Some just gonna go into this one here and they go into the formatting click border on. Okay, so that's what I want now that you could see the borders. You could see exactly how you want to line them up. You could, All right. And I'm gonna drag this one up so that it's and then and then it's just a matter of playing with it. This one's kind of being difficult for us. All right, That should be Well, see, there you go. And it's gonna take a little bit of playing around with it, but eventually, you should be able to get just what we're seeing there where you've got and I'm just going to this one as well. I'm not certain that this is where we're gonna stick them. But just as an example, if you can get what what you're seeing here, where the lines are touching perfectly, that's what you want. Okay, So this guy, I might need to be adjusted a little bit, so that Okay, so it's like it did it. Okay, so you see how that that looks just it just looks really clean and looks really just polished and professional on. And that's what we're going to try to achieve here. So I think this spacing now I'm thinking about the spacing. So this one has four items that has three. This has four. Um, And if it's gonna if it's gonna affect these reports below, we kind of want to think about OK, Do I want this one their order. I want this one here. Um, so I think I think the spacing here it's in this customer segment seems OK. I'm gonna drag it down a little bit. I'm gonna make it a little bit taller, So Yep. Make it a little bit wired. Okay, Perfect. Now all of the items actually show up. They actually all display, and that's what I want. Okay, so we're just gonna never give hope, all right? And if you're following along here, um, one of the tools that the formatting has is it'll show you exactly how wide it is. So if we go into the four matter, you can see exactly where it is on the X axis, where it is on the y axis, which is zero. And how wide? How talk. So this one is saying that it's in the exposition where X axis 272. So it's 272 pixels to the right, and it zero, uh, pixels down because we're We've got it right up against this Y axis here, right up against the top. So it's, um So I'm to get back to that. So it's to 72 from the left side here, zero from the top, and it's 368 pixels wide and 80 inches tall. Now, I guarantee you, every single one of these is also gonna be 80 inches inches 80 pixels tall because they're they're basically lined up perfectly. Okay. And that's and when you do snap to grid, that's where you're gonna get that If you un snap to grid if you own clicked this snap objects to grid. It's not gonna be is fluid is that Make sure you have that checked when you get to the point where you kind of have a good idea of where things are gonna go in your report. Okay, um, I'm actually gonna take off grid lines because I don't think that necessarily I'm gonna need that. It can help, depending on what you're using it for. But for example, I'm not. I'm flush with this grid line here, but I'm not flush with with this one or that one or or even that one. Some kind of going off of there. Little great here, but I think it's fun. Okay, so I also want to The next thing to do is add a border to the ship date here just because I want everything to have a border. So I'm gonna click into this ship date, and I'm gonna click on Border On, okay? And I think it's gonna default automatically to black. That's what you should have. Okay, so just a recap where we've done, I'm gonna make this just is the same with these guys. Make it in line with those guys. All right, so you maybe a a point when you're following along where you don't have it perfectly, and you may want to basically try to replicate exactly what I'm doing, and that could be a good idea. Um, So what I'm gonna do is show you the exact width of each of these items. So for the the ship date slicer, we've got, it's it's in the top left corner, so it's gonna be zero and zero and the with the 272 and the height is 80 as are all of these items, Um, in position to 72 3 68 and 80. And then why? Positions 6 43 36 in 80 and three or four in 1976. Now, you also could make these all round numbers and, ah, you know, basically, So you can change this with here, so I could do, you know, make it a good round 3 60 and then I need to do some math to figure out. OK, do I need to make this 3 60? Should I make him all 3 60? Um, you know that that could take a little bit of, ah, math at exercise. Honestly, I'm not sure that's that's super useful. I mean, the user is not going to be about not going to know or whoever's reading. It's not gonna know that it has 368 pixels. Why did they choose such an hod decimal? Odd? Um, yeah, amount of pixels. That's not gonna happen. So if it's there, if you want to play with it at that level, but I don't think that's necessarily I'm gonna be that important. OK, so if you're following along at this point, you should have at least what I have on the top row. And then you may have Hopefully you've got these items here as well. Um okay, so the next thing we want to start working on his basically work our way down. Um, and the thing that I'm thinking about currently is I've got my dashboard. Here is things that need to be side by side or next to each other. For example, I've got my sales by state of profits on the map over here. Then I've also got this doughnut chart that's ranked by state. I need those to be next to each other. If somebody wanted to be looking at it with the mindset of territories and that kind of thing, it makes sense for them to not be across the page from each other. And then we've also got the sales and sales by month and product category in profit by month and product category. Those should basically be pretty near each other as well. If I were thinking about it, I'd wanted basically, just compare these two and see OK, all my my sales went up. My profits went up, sales went down, profits went down that kind of thing and then looking at it by the different segments that you have. So these two need to stay beside each other and we've got the sales on the map and then the sales ranked by state on the donut chart. They need to be beside each other as well. And then, in addition, I've got this chart here, but it's basically duplicate content. I've got the same information down here in these two charts below, so it's really just taking up space. So I'm gonna delete this one so that I could make some of the other charge bigger. So I'm just gonna hit delete, and now it's gone. Okay, so we wanted to stay this. We wanted the sales by state to be beside each other. So I'm gonna put those here, and then I'm actually going to make the sales by month and product category and the two charts that air over time, I'm actually gonna make those bigger than the rest of the reports. I'm gonna make them basically only two rows instead of three. So what I'm gonna do is gonna put this one up here, and I'm gonna make these half the way down. Okay, I'm glad at a border to this profit by month. So, as I said before, just go into the the visualization, go to the format and then border on. They should default to black. Okay, so now I'm gonna, um Now I want I This is an instance where I actually do want to make the sizing perfect. You know, saying up here, it doesn't matter if it's if it's not exactly perfect round numbers, But in this instance, I actually am going to make sure that this is the exact same height. Is this so one little trick that I'm going to use to figure that out is I'm gonna drag us all the way down, and I'm gonna see how tall it is. Okay, so it's It's 640 pixels tall, so I need for each of them to be 320 pixels tall. And they should be perfectly, um sized. Yep. Looks like that worked perfectly. So great. So this one is just gonna be to rest for each item. And you can notice another thing that I'm looking at now. I've got this item over time, but It's kind of narrow this central or this this slice of by region, it's it's not really wide enough for me to get some good information on these reports. So I'm gonna make this one cent, especially since it's only three gonna make it smaller so I can make some more room for these. Ah, these reports that are showing data over time. So now that's gonna be to 88 and 3 52 Okay. And I'm just gonna resize this and this guy down here, okay? So now I'm just gonna in this and I do want to stick with these two things here. These these by estates. So I'm gonna resize these to make sure that, um, they're perfectly in line. So I'm gonna add a border to this donut chart and then make sure that I've got it perfectly perfectly situated here. Okay, so now that I'm looking at this, I'm thinking OK, so I've got this. This slicer appear is more nearer than this one, because it's got three versus four. Yeah, I think I'm gonna flip this. Ah, the map here with this donut chart because the doughnut chart doesn't need as much space but the map actually does. So I'm gonna so much change these out, and I think it'll be fine not having as much space. Sorry. So another will tip. Um, when you do something you didn't mean to like, I just did. You can just do control Z and I basically just go right back to where it waas or you can click the back era at the top for under. Alright, folks, let's speed this up and let's get through the rest of it. We don't have much more to go, but there are some things I want to show you. So resize thes so that you know, they there, you got your pretty great here. And this thing here it's the is the tree. The treatment. I actually want to drag us all the way across, so I'm gonna add a border. So I know exactly where the border is gonna be in a matter of water for the other items as well. Cup. All right. Looks good. Now, these two items, Um, because I've got something separating these up here. These don't necessarily have to be perfectly in line, so let's make these take up the whole space down here. Okay. All right. So, yeah, we're start to get to the end of it. Um, I'm actually gonna cut this lecture off and were against this, get a little bit longer, and then we're definitely gonna finish in the next lecture. Promise, guys.
10. Formatting and Completion of your Dashboard: All right, We're back in this section. We're gonna finish our dashboard completely this May. This section is mainly focusing on just doing some final formatting to our reports, and we're also gonna introduce the card function. So as I'm looking at my dashboard, I'm thinking about a few things. One, this side over here. It's actually a little nearer. I've got monthly data it. So I've got six different data points here. I will. I think he'll be better, too, if I had, if he's had a little more room, so I'm gonna slide them over. And so what I'm gonna do is kind of borrow from some of these other areas up here. This slicer function appear it doesn't actually need all of that room. So I'm gonna move it over some. It could probably go there and be just as effective. So I'm gonna move this over, okay? And all right, we're just gonna slide all over. Okay, so now for this, I need to Oops. Sorry. I want this to be in line with this. Still OK? Notice how it snaps to it. That's just a great feature that it has for formatting and making it a lot easier. Okay? And then there we go. And if and if yours is not snapping like you're seeing here, um, if you get a view and make sure it's clicked on snap objects to grid and you'll get that I move this one over here. Okay? Now, that's a little that's that's a little better. Yeah, I'm liking that much better. When we saw this one ever. Just expand that, Make sure I use the whole space, and we're gonna put a card here. So a card is just something that should displays data or some sort of metric. It's not these visualizations just back to just simple told information in numeric for or data form. So we're gonna Sarkar Adoptions are just this card or multi Rocard, and that's what we're gonna use. Okay, it's gonna go in this little box here, so I'm gonna go ahead and put a border on it so that I can format it a little easier, and I guess we'll go and drag in. So we're just gonna have a little place where we can show sales, profit and quantity of sales throughout whatever segment, a segment of time period that We're looking at some of the Dragon Hman sales profit and then quantity ordered and wanted. In that order, I could never find which one is the product quantity ordered. New there again, cans. Put that back. And if I wanted to reorder them, I would like that. Okay, so you probably figured that out there. As much as we've played with that now notice over here, it's not doing anything like we wanted to do. What it's doing is it's basically just labelling every single instance and when you click it. So when you go into and you click on the little dropped on era, you see this saying Don't summarize I don't know why Power bi I didn't know that we wanted to do a summer ization, but we need to do a summer's ation. So just in each of these items, click on the little era, Click some, Okay, and now we've got sales profit quantity ordered. If I slide this over, it should Yep, there were good. I was hoping that it would make it just one, you know, one I didn't like that profit was over here when it all be left aligned, and that looks good. Okay, so I want these a little bigger. You can't really see these in the value of having this card is so that something just pops out and you can see you can see really quickly, and it's more. And she is great to be. It's mostly used for something really high level like this. Like, we just want to call out sales total profit for whatever segment you're looking at. Okay, so we want to increase the size of these. Okay, So if you go into the format, okay, and then you want to go to the category or Sorry. So the data labels it's a little bit, um if using the data labels is gonna be referring to the actual fields that we've added in , like, the numbers, the attributes. So that's where we want to go if you want to increase the font size, and we're gonna take it from 10 to 16 okay? And we also want to increase these labels here and then that they're called the category labels were increased from nine to 14 so I could get it right. Okay. All right. So there we go and folks are dashboard is basically done. Um, the next thing we're gonna work on it. Some formatting, which I think it's definitely still valuable. But if you're if you're just trying to figure out some of the functionality and just building reports for the purposes of data analysis, feel free to skip ahead to the next lecture where we're talking about some some other functional topics. But I think if you're actually building some reports for for some money organization that that they would definitely be worthwhile to stick around and learn about some of the different formatting options you have because, as we have it, here is just a plain old vanilla. No formatting applaud. You know, the power bi eyes automatically given us some color templates that looked kind of nice, definitely better than something that Stock Excel would give you. But there's a lot of different things that we can do to really just boost the presentation . So let's jump into that, And the first thing that I want to do is only change the background color of all these different items. So I'm just gonna go into this one of the bottom right, and I'm gonna go to the four matter, and I'm gonna go into the background and I'm gonna click on and I'm gonna change the color . I'm not gonna make it something that's, you know, gaudier drastic toward you Can't see the information, but just something a little bit besides just this transparent color. So if you go into the color and want you to pick, it's gonna be a little difficult to find. But go 1234567 over. And it's a little just kind of a gray color. Yeah, so you know that that's a good looking color. It's kind of neutral, but it just looks better than transparent. Good. And then we're also going to add some, um, some of the lines. So in the y axis, we're gonna go in here and we're just gonna make a line. And by line, I mean, just these little access points here. Okay, so grid lines are owned, but the color must be just so. I mean, I can't see it, So let's make this dotted. Let's make it two, and let's make it so Now go to this. Um, in the same row is we had our background go three down and make it gray. Okay, so now that was great, because the data pops out, but the information is there for you to see it. Okay, um, one thing, the next thing we do is I'm gonna change these data labels because they're actually not that easy to see. I'm gonna make him darker. So if you want to change the data labels on the left side or the bottom left side is obviously why access in the bottom is the X axis. Okay, so we're gonna go in here, and we're gonna make the font color. We're gonna change it from this gray to just black, okay? And we're gonna do the same for the X axis. So click out going to the eggs and then font color block. Okay, Now, let's also do that for the title. We're gonna go back in, we're gonna get the title. We're gonna go font color. We're gonna were to increase the font size to 10 and we're going to change the font color to black. Okay, well, that looks okay. I think we could do a little a little better. Okay, so we're in the title now. One thing that I would do is I would just center the titles, so you click down. So if you're in the title gives you once different options, one of them is click in and you couldn't make it center lined. Okay. I don't think that looks that good. I think we need something to make these titles pop out a little more. So one thing you can do is have contrasting background colors between your title in the body here. So we've got a great background with a black font. But what if we switched it to a white font in a gray background for a title? I think that would just look better. So we go into our title change the font color two white. Okay. No, you can't really barely see it. We're gonna change the background color, which is currently nothing. It's currently just transparent. So it's just picking up whatever's in, whatever is the background of the body. I'm gonna change that to still in this gray road here. We're going to grab the fourth item. Okay. See, Didn't look so much better than what we had. Or doesn't this look but so much better than this graph here I think so. So let's make sure we just format paint all the rest of these visualizations, and we're going to the slices as well. But we're gonna do some other custom formatting to these. Okay, so we're going to play with this a little bit. All right? So let's make sure this is resized so that there's no there's no white space there. And one other thing I noticed is this is pretty cramped and this seems like it has too much space, and so they don't necessarily have to line up with ease. So I'm gonna move this one over and make some river this one. And at this point, you know, I've done a lot of things that you may not be following along perfectly, and you may have to play with your own dashboard. Or you may have ideas of your own that don't necessarily follow every single step that I took And maybe your dashboard looks identical to this maybe looks similar, but at the end of the day, you know if if, if you think, if you're proud of it, if you think it looks good, I think that's what matters. And hopefully you've learned a few, I guess Dash boarding tips or best practices that you could just apply to any sort of dashboard that you have. Okay, we're gonna finish this off. I do want to play with some of these slicers, um, and put some extra formatting on these. All that I want to do here is changed this color to match the same as these headers here. And then we're gonna change the font color, toe white. So if you go into any of your slicers, we have the format, and we're gonna goto items because we want these little blocks to change. And so the font color, I want to be white in the background. Memory. Was this fourth gray here? Right there. Okay. All right. Perfect. So I'm gonna apply that okay. And very last thing. I want to change these, um, thes labels. I want to change that font color. We'll see what would look good. What's Let's go with the let's go with the black, Okay? And they're called category labels. Don't make that black. Okay. All right, folks, there you've got it. I hope you enjoyed the process and learned a lot about how power bi I works and how to create a really nice dashboard
11. Publishing your Dashboard to Power BI Service: Okay, so you've created your first dashboard, and now you want to share it with your team members or management or whoever it is through the power bi I service s So what do you do? Well, as I explain, the beginning of this course power bi desktop allows you to do all this back end stuff. So you're playing with the playing with the queries, playing with the relational databases, getting into a meaningful format so that you can create these visualizations. Then you'll publish it to power BI. I service where, Let's say, the CFO where someone in management can receive your visualizations, which we will have got here in the form of a report. And they can click to add these reports to the visuals to their to their dashboard. So to publish it to this power bi I service, all you do is you click this little button here, but just noting that, um, whoever you're pushing, this too are sending this to They're gonna need to be a subscribing member to the power bi I service, which is different than power bi desktop. It's a very cheap option. That's $10 per user per month on and there's a six day free trial. But it's one kind of explain that you won't might not be able to publish it to, you know, the cloud if you don't have a subscription. So if you want, If you're an organization that does have power BS service, you could publish you would save your work, then you would. You ultimately is gonna tell you to tow law again, right? I'm not gonna go through all the steps there. Um, but you basically log into power Bi desktop power BI I service you sign in, and then if you're linked to someone that hasn't account that's linked to yours, then you'll be able to push it to their to their desktops or to their to their dashboards. Um, so I'm not gonna go through all the steps, but I did attached a pdf to this lecture that will explain a lot of those steps and how to go through them. One little tip one did want to share is if you're if you're in an organization where they don't have power bs service, but you still want to make the most out of this This software, you don't necessarily have to happen. Um, I don't think you can do Is like, let's say you just want to put it into a ah, power point. Right? You don't want to do all the fancy power bi I service stuff. One little trick is you can use the windows snipping tool. And you can basically just, um, you basically just sniff it and then you'll be able to you'll able to paste it into a power point if I could get to it. Okay. You gonna make sure you're not selecting a certain window like that. So you'll you might play with a little bit. Get out of the viewing window. Okay. All right. So there you go. Now, you could put that into a power point. You probably want to segment it by you Probably just want to grab something like that so that it's not a huge piece. Otherwise, if you try to grab the entire window, you know, it's gonna be this is gonna be too small for my to see on power point, But just another tip for you guys to take with you. All right? I'll see you guys in the next lecture.
12. Advanced Visualizations: Bar Charts: never. We finished creating our first report. Let's start taking a deeper dive into the visualizations that we have and learning some of the functionality we're gonna start off with looking at bar chart, which are, you know, one of the most common visualizations if you're going to see on dashboards and reports, so they're definitely very useful. And I think that one of the best ways to learn about these different things is just to put a bunch of them on the screen and learn about which ones have different benefits because they all they all have their own unique purpose. Ni even each of them is not necessarily better than the other. Um, they're just It just depends on the circumstance, while you would use one versus the other. So we're gonna go through some of those examples Somebody go ahead, just click on the very 1st 1 the horizontal stacked bar chart. And, um, let's just go ahead and continue on using sales to tell you a little bit about these different options we've got here. So you got access, legend value, color saturation and tool tips. Uh, so values is gonna be if we're thinking about number. You know, if everything about sales, that's where value is gonna be, that's gonna be the number. So it's good and dragged that in, and obviously we haven't segmented by anything, so it's just total sales. Um, so we need to figure out how we want a segment it. Now the axis in this instance is going to be the y axis. This is why access over here, where we're gonna have the different options for the different bars. The legend is gonna be If, for example, the legend comes into play, wouldn't you have this stacked aspect? And it's easy to just show you. Okay, so let's just do, um, product category as the Axis. So that's what we want. Each of our little, um that's we want the left side showing us the different bars for the sales by category and within these categories, let's say that wanted to see customer segments. I wanted to see how much of this technology that $700,000 is made up of each individual customer segment. So then you would go in and dragged the customer segment into a legend. And there it will show you, and we'll show you the top what each color represents and you can see within each sex segment. You know the amount that's related to it. Okay, um, so you've got ah, basically the identical chart. And again, I'm just doing control. C Control V um, to copy and Paste. We've got the exact same chart, but it's just gonna be vertical. That's the only difference. Okay. And really, why you would choose that versus another? I'm not really sure. I think I think it's easier to see the distinction between these categories between furniture, technology, office applause. It's much easier to see the technologies of leader. Um, then it is in this view, Um, but it's easier to see that within these within the customer segment of the legend, Um, which is more distinct in this few. So there's this kind of pros and cons, and it may be depending on your data set, which one looks better and which one you're more easily able to see the information from. Personally, this one is looking better in both instances, in this case, just based on the nature of you know, a lot of these groups are pretty close together in terms of size. So anyway, so that's the 1st 2 we've got the horizontal and the vertical. So they just call it. They call it stack board short and then stepped column chart. But it's just the horizontal stack chart. Okay, so I'm gonna get this out of the way. Um And then what's good? Duplicate this. I could have just one ahead and not didn't net, but Okay, so now let's look at the clustered bar chart. So the difference in the clustered bar chart and the stacked bar chart these your identical . These are identical visualizations as well, But this time, instead of its being stacked, it breaks them these individual pieces out by themselves. So in this, you know, here you can see the total. The total with this bar chart is all the way 2.7 million, almost whereas these air only going out to 11.4 point one million. So the some of these individual things makes it the total of furniture. So you get the trip there and this is much. But this this chart is when that you would prefer if you really wanted to see okay within furniture, which customer segment is the leader, and you can see without a doubt that it's gonna be It's gonna be this corporate segment, you know, versus the corporate segment within technology versus furniture. I can't really see that distinctly. Which one is higher? It looks like the technology has more more sales in the corporate segment, but I can't see it super clear in the same with these small business versus small business and furniture in the small business and technology. I can't really see that distinctly. But in my cluster chart, I can see a little better that, um, for corporate sales technology has more than the fern. The furniture segment? Um, yeah, this bar is longer than that bar. I could just see that these air still pretty pretty, pretty, pretty close. But I can see that furniture is the leader in terms of, well, furniture has more sales than in small business than furniture has cells and technology. So it's gonna look confusing. But, you know, these are things that you may want to know. Um, and picking the right visualization is important in determining those things. So all right, let's continue on two. That's the Clustered bar chart and then we've got basically just the exact same thing, but vertical. So it gives you the option to do both. There. This is the stag, these air, the cluster. All right. And now we've got the 100% stacked bar chart, and that's gonna do something a little bit different. It's going to basically even the playing field between these different categories and say, I don't really care which has more sales in total. I want to see how the how the customer segment is doing within each segment. Okay, so let's do this. And this will make it really fun to be able to see some of the different comparisons we're going to flip. The access is here for both. Come on. Sometimes it's just doesn't want to do it. Okay, Alright. And then we'll switch these two, okay? And now we can see that you, for example, small business within small business. The largest section is by far office supplies, which is 42%. And you can see the 42% that I'm looking at their that's in this little black boxes called a tool tip. Um, versus in the consumer section which you know this this makes complete sense, right? If we just think about it logically, you know, Why would consumers have a big portion of office supplies? But just what? So I mean, it just adds a different sort of insight that could be useful, depending on what you're looking at and what you're trying to achieve with the data. So I think this is a pretty good overview of the bar charts. Next, we're gonna jump in tow line charts and see what visualizations they have to offer.
13. Advanced Visualizations: Line, Combo, and Ribbon: all right. The next thing we're looking at, his line charts and line charts are gonna be Justus common, maybe a little bit less common. Two bar charts is forced dashboards, but they're more frequently used for ad hoc analysis. So if you want to take a deep dive into a certain a certain aspect or look at some serious trends, um, the line charts are a lot more common for that type of analysis. But they are still common on dashboards, so line charts the options that we have here in power. Bi I is just this line chart just your typical line chart, and we've got this area chart, which is a filled line, charts so very similar to this line chart. But just add some coloring for, um, you know, just to show some contrast. Then we've got a stacked area chart, which is also filled. It's basically taking this chart here and just stacking it on top of each other. Um, no, we've got some combo charts we're gonna go into. So it started for the line chart, just the basic generic. And let's just put in some sales and the same segmentation we've been looking at across the board, this course customer segment and product category. Okay, so there we can see we've got sales, you know, just the same. The same information we've been looking at before. We probably just seen it looked like looking like this, or you know what? That that kind of thing. But this is what it would look like if it was a line chart. No, if this was the data set that I was looking at sales by customer segment and product category, I would probably just use a stacked or clustered bar chart instead of a line chart. And the reason is because it's just easier to see and line charts are better to show. Ah, data over time for seeing trends. Eso where you would have what we have the customer segment down here. You had more frequently see time or sales over time, so let's pop that in there and see how that looks. So we're gonna take out the bottom part Here is gonna be your axis. You're its It's your x axis. And the legend is gonna be so the legend is gonna be basically just your your segmentation . Um, in this case, is these different lines, and then the the Y axis. Of course, it's gonna be your values. So we want to take out this X axis, which is this customer segment, and replace it with the ship, date or ship date, which were? We have been associating with the timing of revenue recognition. Okay, so dates are a little bit finicky and net suite for, so they can have some more advanced customization. But what we want to get, we don't want to do every single day. We basically just want to look at it by month. That's going to give us six items. We haven't set the filter here to cut off that that piece and in July, but it was gonna give us seven months. And that's pretty good number of items on your, um, on the axis here. So, what, you're gonna do you notice you click on Ship day and then put it in here? We're to take away everything but month. Okay? And now, basically, we have left its sales over time, back by product category. So now we can see that in April, you it's very visible to see these kind of trends where in April. Um, sales of office supplies went way up, though it tripled, actually, and we could see from the tool tip, it tells us. In March, off supplies were $56,000 and then in April it was $191,000. So that's why it's great to use line charts for seeing information over time. We could just use a bar chart, but I think it's yeah, and let's just look at it. But I generally think it's just better to look at it trends over time using a line chart so we'll see what that looks like. Yes, see, I mean, you can see that it's it's going way up. Um, but it just looks better in this to you Here. It's just cleaner, and you can see you can follow along easier. Okay, so that's just your standard line chart. We can also do you the area chart here. There's not really a significant difference between the two. This one. Just add some shading. Um, it could be important to pitting on the days that you're looking at. So the stacked area chart is basically taking this area chart before, like I mentioned Um, and just just layering it. Um, and I think it's just a great visualization to show. Instead of just seeing the growth in the lines, you can see the growth in terms of just the area that it's taking up in total. Um, another benefit to using this chart versus just a line chart is that you can see total revenue as well. So in here you can't see total revenue you'd have toe. You add up these different items the three different lines but the stacked area chart. You've got total revenue by month and how it's broken out. And then within it, you can see you know which which pieces are growing. Which pieces air shrinking. Um, you know, it's a you can obviously that it has a lot of power there. Okay, so now let's go into combo charts. Combo charts are great for, and I think I'm missing this before in the we're building our dashboard combo. Charts are great for when you want to have when you want, when you want to look at something that has two different scales. So maybe something that's in dollars and something that's in percentage or revenue and profit. Where revenue have you millions of dollars. Profit might be only $100,000. And I know we dug into this earlier, so I'm not gonna spend a ton of time on it. But let's just kind of go into it. Uh, what's more? Okay, so and let's look at it. Ah, let's look at it. We in this case, we'll look at it every time. Okay, so we've got our So So you click on the combo chart, the current first with guys the line and stacked column chart. Um, and you notice there's not It's not It's not a combo chart. Right now. It's just a stack bar chart. So to add, to make it a combo chart, you need to add in the additional additional item. So appear we've got our our we've got, we've got a column. Values are going to be, um, what's in the bars and then our secondary access where the line is going to be in this value here. So let's look at profit. Okay. All right. So if first cleanse, it doesn't automatically put it on this second access like we want to, so we're gonna need to move that line to We need to add a secondary axis. Okay, so we're gonna go to the y axis in the four matter we're gonna screw all the way down for an option to show secondary axis. Okay? And we're click own. All right, so there, we could see, um, you and automatically plots it on this. This the secondary access here. So interestingly, for some reason, profits in March were horrible, despite somewhat average. Too low revenue. Um, and then profits peaked in May when it was also somewhat averages for his revenue. So it just gives you, gives you the opportunity to show something that you need to look into when you're contrasting two different things, like revenue and profit. We could do, you know, revenue and gross margin percentages and likely show at the very similar graph. And then and then the gross margin percentage for each would be on this line at him here. Okay, that's an example of a stacked column chart. Um, so we could also do the line and clustered chart, which is just gonna be this cluster chart here and the line. Um, you know, in this case, I definitely don't like it as much as the stacked column chart Jubilee rate. There could be situations where it's it's more applicable. Okay, let's go into the ribbon chart. So the ribbon chart, the purpose of it looks kind of weird, right? The purpose of the ribbon chart is to show when not just, you know, growth like this is obviously growing. This black areas office supplies in April, it grew from however long this is to this large amount, and it doesn't even show the axis. Um, initially doesn't show the axis. The point of a ribbon chart is to show changes in ranking over time. So in January, February March, the leader within the segment was this technology group. And then in April, the technology group moved to the bottom and then office supplies moved to the top of the break. It has the highest sales. One thing that I would almost always do for when you're doing a ribbon chart is to add the data labels. Okay, so you just go to the four matter and you just click on an automatically pop in this data labels and say this is a pretty good looking graph automatically without having to any any kind of crazy customers ations. Um, so I think it is a very death definitely useful chart in doing dash boarding. So, you know, we concealing the example. We're talking about 54,000 to 192,000 and then technology went from 113 down to 86. So, as I mentioned, I don't know a dozen times in this course already. The visualizations they are, they're all great year. There's not a right answer. The reason why you would choose one visualization over the other is really just what you're trying to per what you're trying to convey the information you want to come across and picking the right visualization makes it a lot easier when, depending on the data set and depending on how the information that you're looking at. So hopefully just by looking at all these different options, you can be better informed on which ones you need to use in a certain situation. All right, so that's it for line charts.
14. Advanced Visualizations: Map Visualizations: So the map options we have with power bi I is this filled map this is just called map or bubble map is really what it is. There's also this our g i s mass for power bi I, which this is just a super advanced feature that can get really granular and do some really cool things for the for the purpose of this course, I don't think we need to go into it because I think these other two maps are really gonna do just about what we need. So let's let's just start playing with this filled map. So let's do sales by state. And I think we play with this earlier and then one of the dashboards. But with filled map, you can see the darker it is. The more color saturated it is, the more sales that are in that particular state. This other math is just a bubble map. So it will just put the bigger the bubble, the bigger the state. It can also have some color saturation there, but this is just helpful to see you know what are the biggest or the biggest sellers. So Washington's actually pretty pretty big sized California's the leader, and then New York is next. Here's where it gets a little bit difficult with these maps. If you wanted to drill down into the city but close, let's just let's just see what happens. What happens is it pushes the data to being, and it says, Okay, here's a city. Where is this located? And it has a tough time with ambiguous data like that, because there's an Athens in Greece. There's an Athens in Georgia. Um, so let's share of postal code, both postal code and see if that helps is and it's not going Teoh body play with it and it basically just there's guesses. Postal codes all over, and it okay sorts. Drag that to some reason to put into color saturation causo because it thinks it's, Ah, it's got a signal here. So it's think it thinks that it's a number or a data field. All right, so this is not we wanted, right? Our data on Li lives in in the United States, so there's not There's not sales outside of the United States, so it should only be this area here. So why is it doing that? It's because there's other postal codes all over the world. That happened to match the numbers that we have for our postal codes. So what we do? Well, if your data has latitude and longitude for where the sales are taking place, that's an easy fix. You just put it in there. If not, you have to get a little bit creative, and I'm gonna show you one feature that you can use. It's a little bit more advanced toe fits. If this is just going too fast, it's not a critical step for understanding the maps on. We're going to go into some of the Dax functions that this is what we're about to do is quite a Dax function were to go into some of those later. So if it's a little bit overwhelming or going too fast, just just kind of breeze by and move onto the next lecture and then eventually we'll get to it. We'll get you brought up to speed. We're gonna modify our some of our data So we've got City we've got, We've got City, you've got state or province, we got region and we've got country. If you can have your data set up so that it says, like Cincinnati comma Ohio. Then the data will be perfect. In your map will just show North Carolina or just know it'll show North America because it knows that its where it it it noses. It's Athens in Georgia and non Athens in Greece. So let's go and play with it. We're gonna create. So we're gonna go into our data tap here and we're in. We've only imported one table, so we're in this orders table, and we're gonna insert a column so you can cover over any of these right click and do insert column. Um, where you could also just go to modeling. And then you can also do it. There you call. All right, so the way this is, if you have any experience in excel, you're gonna be a little bit more. Um, it's gonna become a little easier for you. If not, just bear with me. So the way this works is, you name it, you name it first. And then instead of having to drag the function down, it does it for the entire column. And the first part is just naming it. So let's name it. Location, location, equals And what we want is basically forward to say, the city comma Space state. And then because we have the data here, you we would be Red Redman, comma space Washington and then being we know exactly where we wanted to go. So how do we do that? In excel, you have something called Can Captain eight. Or you can do equals and and And parentheses are quotation marks We're gonna do, captain eight in this example. So we're going to contaminate, and then it's gonna say, What's the first text you won't. First one's gonna be city. And so if you just start typing the field in it will look it up and say, Oh, you mean the city is the column city in the tap in the in the table orders. Yep, that's the one that I want. So what's the next? What's the next text that we want? We want to be common in a space, So we wanted to be common space. So my syntax here is text one is the looking at the city text. Two is comma in space. Well, that's only just getting give me part of it. It's not gonna pick up the state. But the issue is you can't do a candidate with three texts in a row. So that's what it's looking right now. So we need to add, um, we need to add another field, which is the sorry. You can add another field, which is the state into this contamination. So we're basically just kind of have to do a a nested can, Captain eight. So I want to say only canon eight, this text, which is City Common Space and the state That's my text. One my text to is gonna be state. Close it out and there would gets we should have city common Space State. All right, so that's our location field, and we see that it shows up here as a function. We go back here and we replace this postal code with location. We should have our map just in the United States and in now it knows which cities are go going which states and what's locations. But for me, I went to a legend. We want to go to a location and it's spinning. It's sending to being there. We get perfect. This is exactly what we wanted so well There's one big city here, the figure. It's New York that is obviously the biggest of all. And then some city in California, which is L. A. So now you can see a little bit more detail into Uh, yeah, you can also still see that California is a huge area or a huge in terms of sales. One particular city, which is L. A. Is making up the Muppets to the sales. And so that's kind of how you can look at it, and you may have to play with the location fields when you're playing with map.
15. Advanced Visualizations: Scattercharts: All right, So let's play with some scattered plots now in scattered parts of Great When when you want to show the relationship between two different values. One that's frequently used is unit price and sales. So let's just plot that real quick. So we've got our sales and unit price way click on the scanner pot, and we would look at it by Let's look at a by product name. Okay, so and then Okay, so product name is that? So for some reason, when we switched over to scatter plots, it took away one of our value fields, which was unit price. So let's add it back. And also know this one thing you only to be careful of is unit price. It's automatically going to do it at a some. A summation is going to sum up the unit prices. That's not what we want. We want to see what is the average unit price for the product, and then what are the sales related to that? So so just click on little drop down and changing from average or for take it from some to average. There's also a lot of other fete features you can do count, which would be relevant in this one, but it could be in other situations. You can do medium minimum maximum average in this situation. We're gonna do average because we don't know what is the average selling price or unit price by byproduct. And then what are the sales related to this? Okay, so you could see that most of us concentrated down here at the bottom, right? Most of the products sell for less than $1000. These things that are outliers like $6000 they're likely either furniture or technology. So let's see what this one is. It's selling for the average prices. Six point something $1000. So it's a huge videoconferencing unit, so that makes sense that it's there that they have the highest unit price average. Let's see what some of these other ones are. Some sort of dot matrix printer can and something with video. Um, it's the dot matrix. And then there was another known here on C. Yes, I'm sorry, Can and copier. Okay, so we can see that there's a There's general trending here that shows the higher the unit price. The mawr there are in terms of total sales. And this, this is this is doing the some, which is what we want, which is saying, What is the sum of this sales by product? Okay, so the number one seller but then is normal in terms of total sales is this electric punch plastic comb binding machine with manual binds, some sort of binding binding machine that has more sales than any other, And it's totalling $69,000. Okay, so with scattered pots, it's a great place to add some trend lines. And that's super easy to do. If you just cook on this little magnifying glass and it's called analytics, you can just go in here and click add, and it automatically just put in a slope trendline, which is great. Which shows us yep, as the average unit price increase, total sales buy that product increases. All right, so scatter plots. You can also turn them into bubble plots. Um, by and that basically adds another dimensions we've got We've got X axis and R Y axis unit price and total sales. Why don't we added another dimension which would be profit when we make that the size? So now you added another attribute, which is profit, you know, show you total profit. Um, you basically have to scatter plot. Just add in this other dimension of size and it and it changes the size of these bubbles. So you can see you know, this really is a contribute. Well, it's actually negative, so it's negative profit there. This is not a profitable item to sell. Versus this is contributing profit to the company. So just kind of adds another, and you kills just to be color saturation instead. Um, that's a little bit more difficult to see because there's not as much variability. But you could still see. I mean, this is barely transfers almost transparent, and this is very, very thick, so very dark, so you can kind of see it that way. All right, so let's put profit back to size. We can see it that way, and then I want to show you guys this play access. This is really cool. It basically well, I'll just show you, but it's basically acts like a slicer, Um, but it just you can click play, and it'll just progress through the different slices. So let's say product category so I just click play and we'll start with furniture. Okay, so it Yeah, it does. Furniture office plus technology. So just kind of does it over time. It's pretty neat. And you can also manually. Um, just click and move it where you want. So let's looking off supplies, uh, furniture and technology. What I think will be interesting. Yes. So, technology I would've expected toe have, ah, wider range because you've probably got some. Really? This is probably that Yeah, the come the video conferencing unit. So now I want to look at it by by customer segment cause I would expect that consumers are selling a lot or buying a lot more things that are at the lower end. And corporations are buying more stuff at the higher dollar unit price. So let's swap those out. Okay? So small business. And so it's the reason it starts to the end and then works his way back. So it's consumer. So there are some things that are really far out here. The pilot conferencing unit, I guess some consumer problem with those corporate Okay, Home office? Yeah. Not really What I expected. Honestly, I expected more high average unit prices to be higher for corporate than foursome these other groups. But it when it's interesting. So you can kind of see how the possibilities of this and it's really neat. You can also do the play access using order date. And I don't just play through the The Times. So let's see there again, trying to drag it there we have and I want. So it's only gonna do year for some reason. So you have to change this, um, order date and to change the hierarchy. And then you can have it said that it what's his plan and see what happens. You probably want a segmented by month, but I'm not gonna go through this whole progression. You can kind of see the options you have. You can you really powerful especially, you know, depending on the data set you're looking at and what attributes you have. All right, So let's move into, uh, pie charts. Get rid of this
16. Advanced Visualizations: Guages, Cards, and KPIs: All right. Now, let's take a look at gauges, cards and KP eyes. We didn't have any of these in our dash sports earlier, but these are really good dashboard tools, especially when you're looking at information on, uh, like at a particular month. So if if of sales manager wants to have a dashboard of how they're progressing towards their goals for the current month, this would be a great visualization for those. So we've got a gauge here and now Let's just pop in some sales information. So it's like it sales by, um OK, so it's it's see you pop in sales and it's merely just gonna pull in everything from the beginning of your of your time, beginning of time. So we've got data from the beginning of January 15 to June 32,015. If this person wanted to look at their monthly dashboard, we want to add in some segmentation there, so it's added a slicer. It's not what I want to do. So slicer. And then we'll add order, date. All right, so we saw slices on the on the previous lecture out dashboards. So I'm just gonna change this to let's say we're just looking for the month of June, Sprint said. Total sales is 355,000. Eso these air great for if you have a monthly budget. So let's say you did your annual operating plan and you had a target of, um, $500,000 for, um in sales for the month of June. You can set targets on here, and it's automatically you're just gonna double it and say, That's the target. For whatever reason, let's say, um, the maximum you can so you can dragon information. If you have a budget information in here, you can drag that in here. Or you can go to this format painter and just pop something in. So let's say that Max is going to be $800,000 in our target is $500,000. I think it got a little crazy with mind zeros here. Yep. Okay, so there we get. Now, let's say that this date is in from this. This data has information for the entire month, but let's say it was the 15th of the month and we were halfway through the month and we had this selection here for the entire month. It would show us where we were in progress towards that goal. So if the goal is $500,000 we're not quite there yet. So that's Ah, that's a gauge, which is great. Great tool for dash boarding. Um, so let's look at another one. Let's get cards and multi row cards. Actually, I'm just gonna copy and paste this guy because we're gonna use the same information. So I changed that to card and this one, I'm gonna just do a multi record. Okay, So, card, you're great when you just wanna have one number that just stands out that they wouldn't look at really quickly and see without having a ton of a ton of information. They just want to see what's my sales for the current month. And so it would be 3 to $55,000 be nice and clean, and in a certain particular area that they can focus on. You can also do multi records, which is basically just this card like we have here. But you can do different, different things so we can add in quarter quantity. For example, one of my total orders for the month so can never find it. Have a go. And you just added into the fields here and automatically. Um okay, Yeah. And so you see, if you automatically put in there, it's just going to start doing it by, um I think the date or something. That's not what you want. You want, it's gonna it's not gonna not summarize it and show they just individual orders. Does he want to sum it up? So now you can see for this period, you've got total sales and total quantity ordered. Let's also look at profit so we could get three measures in them. All right, So to do the same thing again, we had to change it to some. And so you can see you may want to keep it like this where it's horizontal, but I personally like it. Uh, I would like to see it more vertical. So if you actually just drag this down and automatically resize it and you can place it wherever you want And then finally, we've got KP eyes and KP eyes are great for showing values over time or trends over time. Um, let's just jump in and we'll just do it by sales, so sales by most to order date. All right, so you notice it's something. It's just not what we expected, right? I would have expected to see $355,000 in sales. But what it's doing is it's showing you these values over time, and it's reading the very last item, which is, I guess, the you know, the last day of this month in this period is 6 32,015 So that's the daily sales on that last day. So and we can see that by right clicking on these little three dots and going to show data . We can see what's happening if looking at the total sales each day. And our very last day is Tuesday, June 30th and there was $24,000 that could still be great, even though it's not showing the values for the total for the entire period. But it's great if you want to see that sales in the most recent periods of If this is that this was your selection and you refresh your data and it was June 15th you would see the data data for June 15th on there So that's a K p I. And it's it's great for We could also do quantity ordered. So it's watch that swap that out Sales for quality order. Okay, so the same thing you can see the quantity ordered, um, by day and we can actually been layer sales in order in quantity and just kind of see the differences we've got there. They should obviously lawn, right? I mean, and it doesn't interesting. Okay, so? Well, I guess there's differences between the days things were ordered and when their ship, So there's that's likely are difference. All right, I'll see you guys next time.
17. Advanced Visualizations: Pie Charts: alright Pie charts. Everybody in the entire world has seen a pie chart, so I'm not gonna go super in depth. So we're gonna click pie chart and let's just look at sales, all right? And let's look at it by state. So we'll click state okay, and so it's automatically gonna put up in there. It's automatically going to default to alphabetical. I don't think that's helpful. Um, so if you click in the top right corner, you can see sort by State of province, which is what is currently on or sort by sales. So that's just a lot easier way to rank the sales. And then you can see you within each one how much they what percentage of the total they take up, which is really the benefit of a pie chart as percentage of total. And that's when you, when you're concerned about that particular aspect, you can also change this so that and I probably will. You can add, um, what percentage it is so that it shows up. We'll percentage. Each state is of the total. So if you wanted to do that, you go to the format painter or the four matter Um and then within this detail labels, So it's currently set to own so we can see these states. So you click it off. It just takes it away. It's currently set to label style his category. You have a lot of other options. You can do data value, which is going to give you the sales, which is, I think that could be a good option. But let's look at percentage of total, so we want to category and percentage of total. So California is 14.98%. Okay, and that could be a good This could be a good chart If, for example, you wanted to look at your concentration of revenue. Um, small companies that maybe their contract based, they may have a concentration of sales within a few customers. So the larger the pie is for the larger the slice per customer base of the bigger, bigger risk you have that if you were to lose them, it could be significant impact to your business. All right, so that's pie charts
18. Setting up table relationships: So now let's say you want to get some information from other tables. Other spreadsheets. If you remember, when we import our data, we just select this orders tab. Well, there was two other tabs within that database or the within that Excel file that we may need to get some useful information for. So let's go back there and grab those other two tables, and we'll learn how to manage the relationships between them so that we can pull that information into our into our visualisations. We're gonna go back to get data, make sure you're on the home tab thrown home and then get data. And this is where we were earlier with first imported, our orders, information, our sales information. So it's in an Excel files. We'll click that and then find your data, and it's within the superstore US file. So we'll open that, and it'll prompt us with the three different tables that we have. We've already got this orders in there. We need to import these two guys returns and users, so this returns is going to tell you these are all the items that have been returned, and these are the users and It's basically just setting up a relationship between who is the managers within these regions. So don't click load yet. Let's go to click at it really quick because I want to show you as a couple things. All right, so it's got my three queries. Its queries is looking at the different tables, right? So this is when we already have. We've got returns, which is showing the order i D and status. I think the only statuses is returned. Um, and they've got the users, and it's it's the managers. We'll notice something different between this returns, columns, the headings and this one. This title is called Order I. D and Status so that when I go over here and it has been loaded in yet, but it shows me what the name of that category or that row is. This one isn't doing that right. So let's go back to God. Okay, so this is showing Column one and column, too. I really wanted to be region in manager, so you need to go over here and go Teoh use first row as headers. Sometimes it won't automatically do this because it just won't know, especially when there's text, and it doesn't know that region is not and the same is these guys so used to used first rose headers, and it'll take out that first row and turned into a header. Okay, so and then these that these air good closing apply. All right, so now let's look at these relationships. So do you notice that all automatically set up our relationship? It said it knew that there's something on here that is equal to this, and it's the region. That's what's happening here. So it it automatically detected that relationship you see here. If you if you double click on it to edit the relationship, it'll show you. It's highlighted already. Region in Region. So it knows that Chris is responsible for the central region, for example. And so any time there's a central region here, they don't know that it's it's relationship is to Chris. So if we wanted to load in some sales information by Chris, we can now do that, and that's that's really that's a really powerful function, especially the fact that it automatically identified it. Okay, so we've got sales and we're going to get at this table and click manager. Wow. So now we've got total sales by manager. That's great. Now let's try to do it for, um, returns. Let's see what the sales by. Um, let's look at where sales by regions. We've got sales six to region. Okay, their sales by region. What if we wanted to do returns by region? Let's see, How would we do that? We'd have to see if we have that relationships that relationship set up and it doesn't look like it automatically detected that relationship. So what we have on the orders or on the returns is we've got order, idea and status, so we should be able to link this order. I d to the order I d in this table, which is over here, and we can do that. But I'm going to show you guys a little bit of an issue with doing that. But let's just do it for it. For purposes of the example. If you want to link something that's not automatically linked, it's as easy as finding it on here and keep Can't find it. Have you looking for order? I d keeps evading me. Here we go order idea something to drag this This order I d. And look, it's already it's got a set up. If I double click on it, it should have order. I d highlighted there does order. I need to order i d. So it knows that there's a That's the relationship. Now we're gonna try to pull this in, but the issue is that this data set that we have is year to date june 30th 2015. So it's only got the orders that have gone out, whereas the returns data set has got all of the returns that have come in and they may not necessarily be related to they may afford to show up. It would have to have been a return within the within that same time with have to be returned that has been sold within January to June 2016 or 15. But let's just look at it anyway, just for just for practice. So what I would do here is I would want a filter by order, I d step or the status of returned. I'm gonna move it down here, and I'm just gonna make sure that silted Alright, So I've got basic filtering and I want to say returned, so you can see it's gonna be pretty small, the only returns we've had or from the East in the West regions. And it's total of $36,000 in terms of sales on What's that? What that's doing is it's saying, Okay, find one here, um, we get to it, so find the if you can find one of these numbers in in this table that matches up here than some up the sales by region. So that's why we're seeing very small amount of returns. Because we don't have the complete data set we would need. We would need to have the data set of sales by the entire since the beginning of the company. That way, because, you know, like I said, not all of the returns are gonna are gonna be from sales that have occurred in the same timeframe. That's how you can set up some relationships
19. Introduction to DAX (Data Analysis Expressions): What if we wanted to do some advanced data manipulation to the files that we have? Like, let's say, for example, we wanted to know. I know you guys can't see this over here. I'm just pulling in sales and we've got profit. Ah, by let's just look at each individual water I only put into a table. Okay, so let's look at these orders we've got, you know, just the top we've got. Okay, so we've got this very one of the top $47,000 in sales and $8000 in profit. What we don't have in this data table is what's our total cost. And then, you know, we don't necessarily need total cost for this. But what is the gross profit percentage on that? Um, one way we could do that as we can add a measure. Or we can add a new column, Uhm And what? The language that we use is called Dex or data analysis expressions. And you can think of it very similarly to the language you would use for excel. It's a query language. So whenever you have your neck, selling you do equals anything. Um, that's a query language, and we're going to use some of the similar components of Excel in power bi I in this Dax language that we're gonna be using.
20. DAX: Calculated Columns: Alright, So I've got this table here and I've got total sales in total profit, but I don't have cost, so I don't have the other piece of it. I mean, it would be nice to have and it's not including my data set. I'm sure there's some analysis that I'm gonna be doing where I'll just need that information. So instead of, you know, like I was saying, instead of taking this data back out to excel, adding in a column that subtracted it to get the total cost, we can actually do it within power, bi I using the Dax functions. So I'm gonna go to modeling up here and I'm gonna new column. And whenever you get a new column, it's going to prompt you to rename your your Newcomb. So anything whatever is to the left of equal sign is gonna be your column name. So let's call it cost cost of goods sold, and that's gonna be equal to sales. And when you type in, when you start typing, something in is gonna automatically try to figure out what you're talking about. So if I type in sales, it's it says Okay, I'm looking in the table orders and in the column sales. Is that what you want? And so if you just click space, it's not It's not gonna do anything. What you need to do is hit tab. When you get tab, that's gonna say, OK, we got it. So you want your sales minus your profit and also notice it's a little bit different in Excel, where you know, if you have a space and excel, it's it's just not gonna work. But in this Dax programming language with this query language, it's allowable. So we're going to do orders, sales minus profit, some type profit. And I hit Tab and it pops it up for him. So I've got this. Um, this Dax function is orders minus profit equals cost of goods sold. So his inner, it'll calculate it for me, and then I'm gonna drag it in. Sometimes when you do that, it will kind of spin a little bit it It can churn through a lot of data, depending on how much you have. All right, so let's find cost of goods sold. Yep. Okay, then we get all right. So for this order, it was $45,000 in sales. The prophet was 9000 so the cost of goods sold was 36,000. Okay, so that's a good example of a new column. There's a tone of functionality that you guys can use in this Dax in the stacks language. This course isn't gonna cover the entirety of that. It's it gets extremely advanced. But I'm just trying to show you guys a few things that you might need or just examples to kind of give you an idea of what's available. Um, another one that we can do. And we touched on this a little bit in earlier lesson. Um was adding Cem adding two columns together. If you remember, we had the location, Um, we couldn't think power bi I or being didn't know it wasn't able to differentiate what city a certain thing was in eso. If you remember, I believe it's this location. So we did a concoction. Eight on again, Captain eight. That was probably a little bit more difficult, made a little more difficult than we should have, So why don't we do that with a little bit differently and give you a chance to do another one as Well, all right. So let's swap out this sales data for the location by order. We want to see, um, Let's look at City. All right, so the city and state All right, but if you remember, we try to plot this, and, um, it just wasn't working, so let's let's do it again. All right? Now, when I'm doing a map and see what it does if you remember, it didn't work out right, because it was trying to put them all all over the world, and that's what we wanted. All right, so let's back out of that all rights. We've got city, state and sales. All right? We want to add we want to make it so that power bi are being can No. Which location? It's actually. And so in a city comma space and then the, um, and in the state All right. So we'll do you go back up in a new column and then what is called location to since we already have one location, function, location are your location to All right, So I mentioned Aken Captain eight function earlier. We're going to use a different one this time. It's called and Okay, so I want my text to be city and and then I I can't just do city and state because it's just gonna merge them together. And there's gonna be no space between them. Some of you and, uh, quotation. More common space, quotation marks and state. So see what that does. Location two. We drag it in. Perfect. That looks right now if we try to map it and should all be in the United States. Oh, okay. Sorry we didn't We've got location in there. We want to say location is location to, and it should automatically zoom into the United States. Yep. Here we go. Those are a couple examples of some calculated columns you guys can use. The next lesson. We're gonna look a new measure, which is slightly different, but it's still pretty similar. We're still using that data programming language or query language.
21. DAX: Measures: All right, So now we want to look at, um, using dax functionality to calculate a new measure. Um, so let's just put the table with sales and profit and total cost. Cost cuts old. It's put into a table. Let's do it by. Ah, let's do about customer second. Okay, so there we go. We're kind of rearrange this here. All right? Okay. So we want to know what is the gross profit percentage by a certain type of segment? Um, when you have some like this, this is a great opportunity to do a new measure, Um, versus a new column, and I'll explain that in the next lecture. But basically this new measure we wanted to be like with the column we wanted have individual data for every single, every single sale. We want to know what was the took the cost of good soul. Um, but here we want we're using a percentage or some sort of calculation. So we're gonna want to use a new measure instead. And this measure is going to calculate what is my gross profit percentage. So what we'll do is we'll rename it and let's just call it percentage gross profit and then , in the last example, went through a column, right? We had a new column. We said column a minus column B. Well, it won't let you do that in here. So if we type in sales, you know, trying to find this column here, it's not gonna allow you to do it for new measures. You have to do it in terms of functions and then yet do some sort of function on the actual Collins themselves. So let's say we do some of profit, okay? And then divide some of sales. So now we're doing this calculation saying, What is this? Some of the prophet divided by the sum of the sales. And then however you segment it, it's going to re calculate it based on that segmentation. So I hit in her, and then I can drag it in. Yeah. So click on the click on the table. Come on. Still didn't do it right now. I clicked on it. All right, so there we give. So we've got the gross profit percentage and we can see for the entire company over this time period, the gross profit percentages 12%. The highest is small business in the lowest is corporate. Looks like a great opportunity for a combo chart. Let's get some of this noise out of here. Must do sales cost of goods sold. Get those out of there and then drag the gross profit percentage to the line values. And you can see um, yeah, just on this, it automatically formats the Y axis. So it looks. It looks pretty dramatic that small businesses way. But in corporate, um, and you can refine your X axis, argue y axis. Obviously, let's let's actually do that. So it's not so, you know, significant. Let's say 2.2. Oh, it's so also when you when you're formatting or why access and you have multiple access is it's going to start with the first access, which is this left side. So what? To scroll down and go to the secondary access for the line. We'll start at zero and go to point to here again, that's a little bit more reasonable in something that we can see and again you, as we've seen with many asses in this data set, the things that the highest sales have been having the lowest in terms of profitability. So this is just a great example of a new measure. But let's go through another example of a new measure. Let's play with some Let's play with this shipping cost. So the data that we have for shipping cost is just This looks like a flat rate for almost for a lot of them, at least $20 depending. I guess it's the stained area for regular air. Um, I don't know. We're gonna play with it. I would be curious to know what is my What is my shipping cost as a percentage of total cost. So my total cost is something my cost of goods sold is something that I calculated earlier based on just subtracting my total sales and profit. Let's see if I can calculate. I'll be curious to know what is my You know what? My shipping costs as a percentage of cost. Good. So So we're gonna calculate a new measure. And let's rename this percentage shipping of COGSA COGSA short for cost. Good sort. Okay, so we want to do remember for these measures, you have to do some sort of calculation. You can't just reference a particular column so Let's do some of shipping cost. Remember, I'm just doing when it finds the thing that I want in a solid stone blue. I'm just doing tab. So some of that divided by and we've already done it. We did it. We did a column. We did a calculated column over here for cost of goods sold. So now that I have that information, I can reference it in this in this measure and get the percentage. So let's do some cost of goods sold. Have a good All right, So now let's let's look at it by, um, let's segment this, but I don't think we played with product container at all. So proud of container and then percentage shipping as a pair of dogs. Okay, so let's see if we can't play with this a little bit. Um, all right, so showing up as you 0.2 it's not really helpful. We really like to see it in a percentage. So what you want to do for these measures? If you click on it and then you go up to the modeling tab, then you can play with formatting. So right now, it's just a common with two decimal places. Let's make that a percent, Okay? And then let's filter by that. Okay? So now we can see that as a percentage of COGSA, the wrapped bag, whatever that is, is the most expensive. And then Jember box, medium box, small box. Um, Teoh. So that means that that could be helpful information, depending on what you're looking at, Not just another example of a new measure.
22. DAX: When to use a Measure vs Calculated Column: figuring out when to use a calculated column versus using a measure can be sometimes tricky , and there can even be instances where you can use either or and get to the same result. But there are some best practices and some reasons why you might choose one over the other . So a calculated column we went through the example earlier. It's just like if you were an Excel file and you wanted to just add a column and do something, some sort of calculation of some sort of function across the room. So in our example, we had sales by each individual order and we had in this. Except we had growth profit by individual order, where the corner profit and we backed into total cost of goods sold what it costs us to make that for each individual row, each individual order. What happens when you create that calculation is that it gets stored in ram, and so it can. It can take a lot of take up a lot of memory, and so depending on how large the data set you have, it can end up making, causing some lag and causing it to kind of turn so wherever possible, it's always better to try to use a measure if you can. Are there certain this is where you just can't avoid using a measure? And you have to use a column. Um, so you would need to use a column if you want to filter something. So in our example, we had Well, that wasn't a great example, but Well, okay, so the other example that we went through was we added a column and we did some contaminating on the name. So we did. We did city and then state. So if I wanted to filter by a particular city and state, I wouldn't be able to use a measure function. I would have to use a calculated column. And that way I can filter by that particular item. So measures are like I said, they're pervert their preferred whenever possible because of the reason that because it doesn't take as much ram to use them, um, ano calculate data sets much faster. Eso calculated columns like that are stored in databases, so it takes a little bit more ram and they're calculated right after you actually hit. Enter on the on the formula versus measures measures the formula exists, but it's not actually used, is not actually calling on any data until it's actually applied. Then it says, Well, let me look at my function and see what it applies to in return that information That's the limit of a difference between the columns and the measures. Um, so the measures cannot be used in slicers or they can't be used in any kind of filters. Some examples I think the best way to probably understand the difference between a column and a measure is by going through some examples, and we went through women through a few earlier in the lecture. But just to go through some again, you would want to use a calculated column when you want to do when you want to add in gross profit by each individual row, just like we did in the exam. Before we wanted to take column sales minus column. Well, you could say I'm saying com sales minus gross profits what we did. But if you had, if you already had cost of goods sold, you could calculate to get to gross profit or if you want to add a modified version of another row at the individual role level like we did with the comm captain, a function where we added the city in the state together. You couldn't do that with you. Couldn't do that with a measure function. You'd have to use a calculated column he also can use. We also need to use a calculated column if you want to do and if then function. So let's say, um, we wanted to do an if then function to categorize sales in terms of volume or or dollar value. You could build a function that says, if the sale is greater than if to say was less than $1000 label, it is low. If the sale is between, um, $1000 in $2000 label it medium. If it's greater than $2000 labelled high something like that, then on each individual line item you would have, whether that say was hi, lower medium, and then you can filter and do kind of any kind of analysis like that. You would like like that with a measure says it doesn't apply to every single row. It's gonna be impossible for you to get that functionality with a measure because it's gonna be getting some sort of some calculation on. It's not going to give you what you need there. So measure some examples of measures would be like we went through if you wanted to get gross product gross profit percentage by segment. So we looked at way calculated gross profit percentage, and we were able to segment it by. I think we looked at customer segment and found what is my gross profit percentage about customer segment? If you wanted to try to do that with a calculated column, what you would end up getting is either a sum of gross profit percentage, which would be something more than 100%. Because it's some of your the average on the entire company for that year was, or the period we had in question was like 1.2. So if every single gross profit percentage was 1.2 and you sum it up, it's gonna give you like 1000. That's a that's a value. We're looking for a percentage, and you also don't want to use an average, because then it's gonna take the average of aggress profit percentages, which may not be the same thing as the gross profit percentage for that group, because if you have because it's weighted differently. So if you've got a if you get a $1,000,000 sale that has a really high gross profit percentage, it's not gonna get applied to the same average. Um, it's not going to apply the same as just having an average. So that's why I measure will be great for gross profit percentage. You can also use a measure when you're calculating average selling price, which again would not be the same if used a calculated column. And you want to use a measure when you don't want the sum of a calculation. Just like I was explaining before the gross profit percentage, we wanted to get basically the weighted average, and that's why we needed to use a measure
23. 6 Case Study Skillshare: all right. Now that you've learned a pretty good amount about power bi I, um some of the more advanced mechanics livid about dash boarding little about the visualizations. Now it's time to bring it all together and create your own dashboard. So you walk by, you walk through step by step with me, and we created the sales dashboard earlier. But now we've got a sample data set, and I want you to use your own imagination in your own creativity and bring this data life by creating your own unique, um, your own unique dashboard based on what you think belongs within the dashboard. Um, so we've got a scenario. We've got this. We've got a bunch of data about a coffee chain we're gonna call. We're gonna say it's Starbucks just pretending Starbucks. It's not their actual data, but would have pretend. And your objective is to produce a dashboard for the CFO of Starbucks that he can have as a daily snapshot. And he can view a lot of different areas about profitability and sales by different kind of segmentation. Um, so use a lot of the a lot of these skills you gained during the course to really bring it, bring it together for him. So we've got some criteria here just to make it a little bit fun, just to make sure that challenges you and you use a lot of the skills we learned. So we're gonna make sure they use one of each of these visualizations and not just put them in the chart are the report, But make sure you're using them in an appropriate manner. We talked about how line charts are better for looking at trends over time. Um, so you wouldn't have something over time. That's a pyre donut chart, for example. So just try to make the best use of the visualizations based on what we had discussed earlier. I do want you to try to apply some advanced four men into the dashboard. It's honestly, you're gonna learn the most about formatting and a lot of these different things just by playing with it yourself. So create your visualization, go into the formatting pane and you start playing around seeing what happens when you turn things on and off and change things from one thing to another and seeing how the visuals X reacts. That's really where you're gonna get a lot of a lot of value there. But I've got a few few, um, a few examples of some of the some of the formatting things that that you can apply, um, for relation database. This data set does have multiple multiple tabs. We've got sales information here based on this product, I D. But then you've got location on a separate tab. So if you wanted to see what are my total sales by, let's say, the east region or the West region, you're gonna need to set up this relational database so that it can pull that information in. Um, And while we're kind on the topic, let's just go through this Excel file and talk about what the actual data set we have is so that sales location and then product So sales, we've got margin, which we're going to say its gross margin sales. We've got budgeted cost of goods, sold, budgeted margin budgeted sales, and then area could. So within this budgeted information, we could put together some pretty interesting visualizations that say, what were we budgeting for sales for this particular sale? And what does it actually turn out? to be That's gonna produce something that's it's get really powerful because you're gonna be able to see what you expected vs reality. Whereas in the dashboard that we were creating the data said that we had it was just trying to find some different trends about, um, you know, while we're there, lower sales and one segment. But I had higher profitability. Why was it so much more profitable? This adds another element in that we have expectations about what something costs and what that something is supposed to sell for. But in the real world, that hasn't actually happened like that. So looking at Budget versus actual could be pretty insightful. So it's got your sales and your budget and information. Here, we've got area code and then product I d. So product I D. Is what you're gonna be able to map back to this product file when this product have yet product I d one through 13. So if you wanted to link ah, this particular sale, you would be mapping this product. I d. To this product, I d. And seeing that product I d one is an amaretto coffee regular. It's not decaf, so That's how you can kind of link those together. The location is going to be picked up by this area code column here. So area code 719 We know that that's gonna be in Connecticut. So if you wanted to do sales by the field map with the bubble map, you're gonna have to create a relational database that links us together so you can get that information in there skills. Just jump back to the criteria here. Yes, we've got the relational database we just discussed. And finally, I do want you to try to use some Dax some of the Dax that we that we had worked through, whether you're creating a calculated column or calculated measures, one or both, obviously, both will be preferred to give you some experience in playing with yourself. Some examples would be gross margin percentage. So in the in the examples that we went through, we had to calculate this marker we had. We were calculating cost of goods sold. You could do the same with this data set because we've got sales and gross margin. Um, you can also do gross margin percentage, which it's not obviously not in this data set here, you could create a calculator, er a measure to calculate that gross margin percentage to use in your visualizations. The other example I gave his budget versus actual you can use ah, calculated column to calculate the budget verse actual for each of these sales. And then you can pull that information in on and slice and dice it with some segmentation. Once you're finished with your dashboard and you're happy with how it's looking, go down here and just go to your went to the windows snipping tool. And we're just gonna take a little snip of this and then save it as a jay pek file. So the dashboard, and then in the course come over to this tab, your project, and we're gonna create a project. And what you're gonna do is basically upload your dashboard as an image on. Then you make it private or not. But I'm gonna give you some feedback on your dashboard, um, and give you get some ideas off, maybe how you could improve it or some things that you definitely good. Some things didn't look that didn't look so good. Um, so you could just name it. Whatever you want, my project, and then we're gonna add the image in there on. It'll just upload it for you. So there you go. Then you can take a look at some of the other students projects and wait for some feedback as well.
Charlie Walker, Finance Director / Digital Marketer
