Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:00,820 --> 00:00:06,310
So at this point in the Course it's time to shift gears into demo mode so no more slides for a little
2
00:00:06,310 --> 00:00:07,120
while.
3
00:00:07,120 --> 00:00:08,930
No more broad concepts.
4
00:00:08,950 --> 00:00:14,640
It's time to roll up our sleeves stick right here in our reporting pain and power be-I and focus on
5
00:00:14,680 --> 00:00:19,680
individual chart types and visualisations that we can use to build out this report.
6
00:00:19,720 --> 00:00:26,740
Q So looking at what we have here got orders by category order info by subcategories.
7
00:00:26,740 --> 00:00:33,370
Well we know that our Adventure Works client really cares about product level performance to not only
8
00:00:33,370 --> 00:00:38,320
in terms of orders but in terms of how frequently certain products are being returned.
9
00:00:38,320 --> 00:00:44,200
So I need to capture some of that data here in our executive summary and what I'd suggest that we do
10
00:00:44,200 --> 00:00:47,020
is use a matrix which you're already familiar with.
11
00:00:47,020 --> 00:00:53,860
We use this quite a bit in the DAC section and of course this is a great way to show a lot of information
12
00:00:54,220 --> 00:00:55,950
without it being overwhelming.
13
00:00:56,140 --> 00:01:00,760
So we'll use some of that drill through capability here and some conditional formatting to make the
14
00:01:00,760 --> 00:01:02,300
numbers really pop.
15
00:01:02,350 --> 00:01:07,170
Now the data that I want to show lives in the sales table.
16
00:01:07,270 --> 00:01:11,550
So let's go ahead and grab total orders when it pulled into values.
17
00:01:12,070 --> 00:01:18,410
And let's also pull the return rate from our returns table as well.
18
00:01:18,410 --> 00:01:24,590
So you've got two columns of values and the level at which you want to view these values or the row
19
00:01:24,590 --> 00:01:31,030
labels that we want to use to segment them will come from the product lookup table and we actually want
20
00:01:31,030 --> 00:01:32,520
the product name.
21
00:01:32,650 --> 00:01:38,730
So drag that into Rose expand this down a bit.
22
00:01:38,780 --> 00:01:44,780
So now we're looking at total orders and return rates at the product level.
23
00:01:44,840 --> 00:01:50,060
And one thing that's cool about these matrix news is that you can just click on the header and it will
24
00:01:50,060 --> 00:01:50,510
sort.
25
00:01:50,510 --> 00:01:55,130
So we can default to sort the products that drove the most orders.
26
00:01:55,130 --> 00:01:59,780
So in this case we see that water bottle 30 ounce water bottles are of the most orders followed by the
27
00:01:59,780 --> 00:02:02,130
patch kit and then tire tubes.
28
00:02:02,130 --> 00:02:02,870
Now this is good.
29
00:02:02,870 --> 00:02:04,480
It's a lot of information here.
30
00:02:04,640 --> 00:02:11,000
But one thing that I think we can do to really make this come to life a bit more is that some conditional
31
00:02:11,000 --> 00:02:18,710
formatting so we can go ahead and navigate to the format pain and obviously you've got matrix specific
32
00:02:18,710 --> 00:02:24,750
formatting here you can change different kind of matrix styles if you want.
33
00:02:24,770 --> 00:02:27,070
And again you can always revert to the default.
34
00:02:27,460 --> 00:02:32,720
And generally speaking the default is actually going to serve our purposes pretty well in most of these
35
00:02:32,720 --> 00:02:38,080
cases so I'm not going to mess with tons of different formatting options as we build this out.
36
00:02:38,450 --> 00:02:44,540
But what I will do here is go all the way down to conditional formatting and it lets you format each
37
00:02:44,540 --> 00:02:46,340
series individually.
38
00:02:46,340 --> 00:02:52,670
So starting with total orders for this one I want to add data bars and you can see what each of these
39
00:02:52,670 --> 00:02:53,150
looks like.
40
00:02:53,190 --> 00:02:54,800
Those are data bars.
41
00:02:55,250 --> 00:02:56,930
That's the font color.
42
00:02:57,290 --> 00:03:00,410
And this is the background so font color and background work.
43
00:03:00,440 --> 00:03:05,810
Same kind of way it's just you're either updating just the font itself or the whole background of the
44
00:03:05,810 --> 00:03:07,010
cell.
45
00:03:07,010 --> 00:03:13,340
So for this total orders column because it's a volume column kind of like using data bars and that will
46
00:03:13,340 --> 00:03:19,190
really draw attention to the difference between some of our top drivers and some of our lower driver
47
00:03:19,280 --> 00:03:21,150
products in terms of orders.
48
00:03:21,200 --> 00:03:25,520
So I can see it right here in the formatting pain and format return rate.
49
00:03:25,520 --> 00:03:31,170
And instead of data bars for the return rate because they're going to be kind of all over the place.
50
00:03:31,190 --> 00:03:35,760
What I'd rather do here is kind of a subtle background conditional format.
51
00:03:36,170 --> 00:03:37,910
So this is not subtle.
52
00:03:37,910 --> 00:03:40,760
This is very loud and aggressive.
53
00:03:40,760 --> 00:03:47,810
What I can do is drill into my advanced controls here and determine exactly what this gradient for my
54
00:03:47,810 --> 00:03:50,080
conditional format should look like.
55
00:03:50,150 --> 00:03:56,930
And in this case instead of this crazy green to red scale for something like return rate which is a
56
00:03:56,930 --> 00:04:01,580
negative metric what I'm going to do is reverse this.
57
00:04:01,580 --> 00:04:06,930
First of all because high values of return rate that's a bad thing not a good thing.
58
00:04:07,040 --> 00:04:11,210
And I'm going to adjust the colors themselves to make them a little bit more muted.
59
00:04:11,390 --> 00:04:15,590
So the maximum meaning for bad return rates.
60
00:04:15,590 --> 00:04:21,380
I want this kind of pale red and for the minimum I want to white.
61
00:04:21,380 --> 00:04:28,010
So this basically means that products with an ok return rate are relatively low return rate will show
62
00:04:28,010 --> 00:04:28,960
up as white.
63
00:04:28,970 --> 00:04:35,150
There won't be huge negative attention drawn to them and only the ones that really start to have some
64
00:04:35,150 --> 00:04:39,920
of the higher levels of return rates will start to pop with this red shading.
65
00:04:40,010 --> 00:04:41,380
So let's press OK there.
66
00:04:41,450 --> 00:04:48,020
And as you can see it's pretty subtle but in this case it does get the job done so you can see certain
67
00:04:48,020 --> 00:04:56,190
cases where individual products are popping a little bit more like this road 650 whereas the other ones
68
00:04:56,190 --> 00:05:01,200
with kind of average or decent below average return rates.
69
00:05:01,200 --> 00:05:02,240
Those kind of just fade.
70
00:05:02,250 --> 00:05:05,780
And with this white background so feel free to make your own adjustments.
71
00:05:05,790 --> 00:05:08,370
But that's kind of my personal style.
72
00:05:08,370 --> 00:05:13,200
Keep it a little bit more muted while still drawing attention to the key trends.
73
00:05:13,200 --> 00:05:19,620
Now last thing that I want to do with this matrix view is go back to fields and instead of only showing
74
00:05:19,620 --> 00:05:22,740
the data by product just like I showed you before.
75
00:05:22,800 --> 00:05:31,790
Let's go ahead and grab the subcategory just beneath my product look up bring the subcategory name in
76
00:05:32,960 --> 00:05:36,340
as well as the category name.
77
00:05:36,740 --> 00:05:38,420
And then you know the drill.
78
00:05:38,600 --> 00:05:42,510
No pun intended you can drill all the way back down.
79
00:05:42,670 --> 00:05:45,090
So now I'm seeing that same product level view.
80
00:05:45,320 --> 00:05:50,710
But I've just given the user the option to aggregate if they choose to do so.
81
00:05:51,090 --> 00:05:53,810
And one last note here that little forked arrow.
82
00:05:53,810 --> 00:06:01,450
The third option that will expand down all the way one by one until you're seeing not only the lowest
83
00:06:01,450 --> 00:06:07,840
level the products but you're also seeing them grouped into subcategories and then into categories.
84
00:06:07,870 --> 00:06:13,620
This is very much like the outline table layout in a pivot table in Excel.
85
00:06:13,780 --> 00:06:19,710
So just go ahead and drill back up and then back down to get back to where we started.
86
00:06:19,720 --> 00:06:21,790
So I love these matrix views.
87
00:06:21,880 --> 00:06:27,760
I think they're incredibly useful and insightful they pack a ton of information and these conditional
88
00:06:27,760 --> 00:06:34,270
formatting tools are great ways to really bring some of that data to life and expose some interesting
89
00:06:34,270 --> 00:06:35,030
patterns.
90
00:06:35,200 --> 00:06:35,940
So there you go.
91
00:06:35,980 --> 00:06:37,360
That's a report matrix view.
9447
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.