Building a matrix and visuals

- [Instructor] Decision makers will literally ask for everything on a dashboard. That's because they're not sure how they want to consume the information in a glance. When designing a dashboard, you might want to consider that you really can't put everything on the dashboard. It would be too overwhelming to really be meaningful. I like to create what I call artifacts which are samples that might go into the dashboard, or might not. Let's start by connecting to our data so we can take a look at our product lines. I'll go ahead and get data, go to Excel and choose my 2020 sales. This workbook has tables defining the data as well as sheet tabs called returns and sales. I can choose either one, but I'll go ahead and choose Order Status and Sales2020 and I'll choose Load. It's detected a relationship between our data. So I'm going to go check that first. I'll go to the model, I go ahead and minimize my properties here and I can see the join line. Click and go to Properties and just confirm what I already know, which is it's connected by the sales order ID which is appropriate for this particular example. I'll go ahead and click, OK and then I'm ready to begin visualizing. We want to show a visual that shows the total line amount for each sales order for each month by product line. And in Power BI desktop, we use the matrix tool to create this type of pivot. So I'll choose matrix and just like a pivot, I have rows, columns and values to work with. Numbers or behind the scenes information for a visual and I really can't start visualizing until I've taken a look at the numbers first. So I'll typically start with a matrix. I'll go ahead and go to my Sales2020 and I'll bring in my product line. I'll bring that to my rows and I'm looking month by month. So I'll expand order date and because it's a date time field, it gives me a date hierarchy to work with. Now this single dataset is for one year so I don't need to bring the year in, I just simply need to bring the month to columns. And as you can see, it's beginning to build my pivot display. Because they've requested we use the line total, I'll drag my line total to the values. Go ahead and expand, great. I'll close my filters for now, I'm going to close my fields for now. Just give me a little bit more screen space to look at. With all these numbers, it's actually hard to visualize what the top product line is based on the line total. So let's duplicate this page so we can play around with the visual and not lose our matrix. Let's go ahead and right click page one and choose duplicate page. Now, everything that was on page one is now duplicated into a duplicate right page one. Let's go ahead and call this artifacts. Just like duplicating a page, I can also duplicate visuals. I'll go ahead and right click and copy this visual and then on my home tab, I can paste it. Now I'll go ahead and play around with different options to visualize it. I'm going to start with a stacked column chart. And then in that visual, you'll notice that it took my product line and my order date and put them in the access. I'm going to go ahead and move my product line to my legend. That way it'll highlight each one of the columns in each one of the product lines. So I can see the total of January and what part - Of January was made up by each product line. I don't have this visual, but I also don't love it. I can't really see pretty clearly, which product line is top one. The orange and blue, they're pretty close. So I'll choose my second copy of the matrix and I'll try a line chart. I'll drag that product line down to the legend. Do you immediately say the S product line, the orange line at the top? Yes. Did you see that data as easily before? No. I'm not here to make the case for what type of visualization but this is a prime example of one showing something a little bit better than the other. I can see all of my product lines and their performance month over month. Let's hover over June where we have a little bit of a dip and then you'll notice that the visual pop-up comes up to tell me, what are the actual values for each one of the months. Now, users don't necessarily know exactly where to hover so it might be better to do just a little bit of formatting to make it a little bit easier to see. So I'll go over to my roller and on my X axis where it shows the months, I'll go ahead and turn that title off. Watch month disappear. Again, it's obvious January through December are the months. Scroll down a little bit further and I'll go to Shapes, I don't know tell it to turn on the marker. The marker makes it easy to visualize where you might want to hover to see information. Okay, great. Now I really love this visual, but after a feedback review, I've discovered that it only needs to show delivered products. Let me go put a hard filter on this visual. I'll go to filters and I see the filters on this visual automatically it gives me filters for everything that makes up the visual but I need the order status. I'll go to my Fields, I'll go to my Order Status and I'll bring that in as a filter on this visual and I'll check the box for delivered. Now, this is a hard coded filter, meaning that it's going to always only show the delivered products. It'll still interact with the soft filters which are slicers that we'll add later, but I'm hard coding it to only show the delivered products. Let's go ahead and duplicate this visual again and adjust it so that we can say the returns. I'll go ahead and copy and paste. Here, I'll adjust my visual to show my lost and returned products. Perfect. Now for this one, instead of showing the product line, I do want to show the order status. So I'll close out that product line and I'll drop in my order status. Now I see the two lines that represent the order status of lost and returned. I want to say something similar for our back ordered objects. So again, I'll copy and paste this visual and I'll adjust that order status filter for the back orders. So these are the different artifacts that I want from my dashboard. Now I'm ready to sample out a dashboard, so I'll go ahead and create a page. I'll call this Sample Dashboard and I'll go copy. So I'll click, I'm holding Ctrl, I'll copy these and paste them under my dashboard. I won't do a ton of sizing here but I'll go ahead and move a few things around. Remember, formatting options will change based on the visual and each object may have its own format options, and even its own filters applied. From one matrix on artifacts, we've created three really nice visuals that let us explore the different parts of the sales order story for the company.