Original subtitles

So at this point in the Course it's time to shift gears into demo mode so no more slides for a little

while.

No more broad concepts.

It's time to roll up our sleeves stick right here in our reporting pain and power be-I and focus on

individual chart types and visualisations that we can use to build out this report.

Q So looking at what we have here got orders by category order info by subcategories.

Well we know that our Adventure Works client really cares about product level performance to not only

in terms of orders but in terms of how frequently certain products are being returned.

So I need to capture some of that data here in our executive summary and what I'd suggest that we do

is use a matrix which you're already familiar with.

We use this quite a bit in the DAC section and of course this is a great way to show a lot of information

without it being overwhelming.

So we'll use some of that drill through capability here and some conditional formatting to make the

numbers really pop.

Now the data that I want to show lives in the sales table.

So let's go ahead and grab total orders when it pulled into values.

And let's also pull the return rate from our returns table as well.

So you've got two columns of values and the level at which you want to view these values or the row

labels that we want to use to segment them will come from the product lookup table and we actually want

the product name.

So drag that into Rose expand this down a bit.

So now we're looking at total orders and return rates at the product level.

And one thing that's cool about these matrix news is that you can just click on the header and it will

sort.

So we can default to sort the products that drove the most orders.

So in this case we see that water bottle 30 ounce water bottles are of the most orders followed by the

patch kit and then tire tubes.

Now this is good.

It's a lot of information here.

But one thing that I think we can do to really make this come to life a bit more is that some conditional

formatting so we can go ahead and navigate to the format pain and obviously you've got matrix specific

formatting here you can change different kind of matrix styles if you want.

And again you can always revert to the default.

And generally speaking the default is actually going to serve our purposes pretty well in most of these

cases so I'm not going to mess with tons of different formatting options as we build this out.

But what I will do here is go all the way down to conditional formatting and it lets you format each

series individually.

So starting with total orders for this one I want to add data bars and you can see what each of these

looks like.

Those are data bars.

That's the font color.

And this is the background so font color and background work.

Same kind of way it's just you're either updating just the font itself or the whole background of the

cell.

So for this total orders column because it's a volume column kind of like using data bars and that will

really draw attention to the difference between some of our top drivers and some of our lower driver

products in terms of orders.

So I can see it right here in the formatting pain and format return rate.

And instead of data bars for the return rate because they're going to be kind of all over the place.

What I'd rather do here is kind of a subtle background conditional format.

So this is not subtle.

This is very loud and aggressive.

What I can do is drill into my advanced controls here and determine exactly what this gradient for my

conditional format should look like.

And in this case instead of this crazy green to red scale for something like return rate which is a

negative metric what I'm going to do is reverse this.

First of all because high values of return rate that's a bad thing not a good thing.

And I'm going to adjust the colors themselves to make them a little bit more muted.

So the maximum meaning for bad return rates.

I want this kind of pale red and for the minimum I want to white.

So this basically means that products with an ok return rate are relatively low return rate will show

up as white.

There won't be huge negative attention drawn to them and only the ones that really start to have some

of the higher levels of return rates will start to pop with this red shading.

So let's press OK there.

And as you can see it's pretty subtle but in this case it does get the job done so you can see certain

cases where individual products are popping a little bit more like this road 650 whereas the other ones

with kind of average or decent below average return rates.

Those kind of just fade.

And with this white background so feel free to make your own adjustments.

But that's kind of my personal style.

Keep it a little bit more muted while still drawing attention to the key trends.

Now last thing that I want to do with this matrix view is go back to fields and instead of only showing

the data by product just like I showed you before.

Let's go ahead and grab the subcategory just beneath my product look up bring the subcategory name in

as well as the category name.

And then you know the drill.

No pun intended you can drill all the way back down.

So now I'm seeing that same product level view.

But I've just given the user the option to aggregate if they choose to do so.

And one last note here that little forked arrow.

The third option that will expand down all the way one by one until you're seeing not only the lowest

level the products but you're also seeing them grouped into subcategories and then into categories.

This is very much like the outline table layout in a pivot table in Excel.

So just go ahead and drill back up and then back down to get back to where we started.

So I love these matrix views.

I think they're incredibly useful and insightful they pack a ton of information and these conditional

formatting tools are great ways to really bring some of that data to life and expose some interesting

patterns.

So there you go.

That's a report matrix view.