Matrix

Quick reference

• 00:05 We're now gonna look at the matrix visual, which is this guy over here that has
• 00:09 little shaded bands on the size of the table.
• 00:14 So I'm gonna select that and
• 00:15 I'm going to give myself a little bit of space to work with this.
• 00:18 Because basically what this visual is it's equivalent to what you might be familiar
• 00:23 with from Excel as a pivot table, so how would we go and
• 00:27 build up a good pivot table?
• 00:28 Well we might take something like sales dollars and
• 00:31 we might drag that into the values area, and
• 00:35 we might grab something like budgets and put that into the values area as well.
• 00:40 We could then also take something like locations and put the location name on
• 00:46 rows, we could put our end of month on our columns.
• 00:53 We could also take categories and
• 00:56 slide that in underneath the rows on our location name.
• 01:00 And yet oddly, that did not seem to expand our pivot table the way that we would
• 01:05 expect it to actually happen if we were working with Excel.
• 01:08 Now this is where we need to start looking at and
• 01:12 playing around with this particular visual to figure out how does it work.
• 01:15 And one of the things you'll notice is that as soon as we started dragging these
• 01:19 fields on we got our levels here for expanding and drilling down into data.
• 01:24 But we got one more field here and
• 01:26 that's drill on, drill on columns or drill on rows.
• 01:30 So the first thing that I'd like to do is I'd like to expand this matrix to
• 01:34 show a little bit more detail on this, I'd like to see my categories.
• 01:37 So what I'm gonna do is I'm gonna click on the expand all down and
• 01:42 expand my matrix down so that I can see all of the individual components.
• 01:48 What if I also wanted to drill in to see
• 01:51 a little bit more about what's going on in the actual sales here?
• 01:54 Well I can do that too, I can grab my columns and I can go and drill down
• 02:00 and to get into those, or I could draw back up, and I could drill into a specific
• 02:05 year if I wanted to in order to just see what's going on in 2016.
• 02:10 So, it works similar to a pivot table when we sort of understand
• 02:15 how it's actually coming together here, but it doesn't look quite as good.
• 02:20 And this is one of the things we'll find now as we start to go and
• 02:22 play with formatting, is that there are definitely some shortcomings compared
• 02:27 to what you might be used to in Excel with formatting a pivot table inside Power BI.
• 02:32 Now to be fair, this is actually probably one of the visuals that
• 02:36 you don't really wanna use a lot, and the reason being is that this
• 02:39 actually reminds you of the classic accounting world.
• 02:42 And speaking as an accountant I can tell you
• 02:45 that we used to present data in pivot tables and other reports, lots of numbers,
• 02:49 and columns and columns of numbers, and people had a hard time consuming it.
• 02:54 This is actually one of the really cool things about Power BI
• 02:57 is it starts moving things into a more visual layer, so
• 03:00 if we can start using other visualizations we can tell better stories.
• 03:04 So this isn't exactly the first visual you should reach to, although if you come
• 03:08 certainly from the finance world you may feel that you need to start here, so
• 03:11 I would tend to try and get you to try and experiment with some other things.
• 03:15 But let's go and take a look at some of the formatting options that we have with
• 03:18 this too, can we make this look a little bit better?
• 03:20 So we'll go click on the paintbrush roller.
• 03:23 And you'll notice that the first place you probably want to fool around with is
• 03:27 the matrix style, this is the area where we can actually go and
• 03:31 choose what the overall matrix looks like.
• 03:34 So we could say that we want it to have no style at all, or minimal,
• 03:39 we can take a look at bold headers, alternating rows.
• 03:45 And this is one of the challenges that I sort of find with this is that there's not
• 03:49 a lot of styles in here that I actually really like the look of generally, and
• 03:52 I mean we can change the colors and things afterwards.
• 03:54 But generally what I end up landing on most of the time for
• 03:57 report is condensed, because it actually shrinks this down to the default
• 04:00 smallest font size which can get a little bit more on the page so
• 04:03 that I can actually overwhelm my audience with numbers.
• 04:07 Now, what other things do we have that we could play with?
• 04:10 We could play with our column headers, we can change the font colors and
• 04:13 the background.
• 04:14 So if I wanted this to have a yellow background up at the top and
• 04:17 go with a black font instead I could absolutely do that.
• 04:22 There's a lot of different options in here that we can play around with, but
• 04:25 the challenges are is that there's ones in here that are lacking as far I'm
• 04:28 concerned.
• 04:29 One of the things that I might like to do is I might like to change the values to
• 04:33 show, right now I've got a 0.5 decimal on this particular piece,
• 04:36 maybe I wanna get rid of that.
• 04:38 Well if I go into the values area, that's where you'd think that you'd be able to
• 04:41 control the formatting in this particular regard.
• 04:43 And when I go and take a look down through all this thing I don't see
• 04:47 any option in here whatsoever to go and control the values, so that's not so good.
• 04:52 What about, hm, let me see here, subtotals?
• 04:55 All right, well we could take subtotals and
• 04:57 we could turn subtotals off for total rows and total columns.
• 05:01 Let's take off the total column, we'll get rid of that one, there we go, so
• 05:04 maybe that looks a little bit better,
• 05:07 we could take off grand totals if we wanted to as well.
• 05:10 How about column formatting, this sounds like something that might be promising.
• 05:15 And if we look into column formatting we can see that we actually have the ability
• 05:19 to control each column, each measure individually.
• 05:22 But again, and let me just collapse this and
• 05:25 make the visualizations pane a little bit longer so we can see all the words here,
• 05:29 you will notice that once again there is no option to change the number format.
• 05:33 So basically the number format that's designed and defined for
• 05:37 the measure, based on the modeling tab up at the top here,
• 05:40 whatever we've set in this area that's what you get on your matrix,
• 05:43 there's no way of changing that, which is really unfortunate.
• 05:46 Now we could change the font color, the background color, so if we wanted to
• 05:49 shade it so that a specific column ends up in a nice light gray or
• 05:53 something like that we could do that, but it's not the same as the number format and
• 05:57 that's a little bit of a challenge.
• 05:59 There's other options like conditional formatting as well,
• 06:03 I find the conditional formatting also somewhat weak.
• 06:06 But data bars are kind of cool, we could turn those on here and
• 06:09 it'll actually throw us a nice little data bar to show us the proportional sales,
• 06:12 those are kinda neat.
• 06:13 The conditional formatting itself for working with is not quite as robust
• 06:18 as Excel unfortunately, you get the option to change your background color and that's
• 06:23 kind of about it, so hopefully we'll see some improvements to that in the future.
• 06:28 But that'll give you a good gist of the general controls over what happens with
• 06:32 a matrix.

Lesson notes are only available for subscribers.