Locked lesson.
About this lesson
The matrix is similar (but not the same as) Excel's PivotTable. In this lesson we will show how to work with this specific visual.
Exercise files
Download this lesson’s related exercise files. You can download the source data files for the course from the resources section of your Lessons page.
Matrix.pbix458.3 KB Matrix - Completed.pbix
459.8 KB
Quick reference
Matrix
Working with Matrix visuals in Power BI.
When to use
Matrix visuals are useful where you want to be able to show tabular data, display subtotals, and drill in and out of different layers.
Instructions
Creating Matrix Visuals
- Select the Matrix visual
- Fields you wish to see down the left column of the visual should be dragged into the Rows area
- Fields you wish to see across the top row(s) of the visual should be dragged into the Columns area
- Fields you wish to see in the main body of the visual should be dragged into the Values area
Formatting
- Dragging multiple fields into the Rows or Columns area will activate Drill Down
- Dragging multiple fields into the Value area will render side by side columns of data
- Visual formatting can be done via the fields under the paint roller in the Visualizations area
Hints & tips
- Format your measures (on the Modeling tab) as you’d like them to show in your matrix. While the matrix won’t let you declare a specific number format, most other visuals will
- Start your formatting by choosing the closest Matrix style to what you want, then further customize via the other options
- The Condensed Matrix Style takes up the least room on the visualization canvas
- 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.