Original subtitles

Welcome to this lesson.

In this lesson, I'd like to spend a little bit of time just talking about another set of calculations

that we can use in our reports.

Now, when it comes to percentages, there can be really useful to get an alternate view of your data.

Percentages are really a quite an important part of data analysis.

Often we want to know how big something is or how small something is by the percentage that is contributing

towards the total.

So let's have a look and see how we can add some percentages into our calculations.

Now what I'm going to do is I actually want to keep this page one where you're going to be using this

a little bit later in the course when we get to our filters.

So in this case, I'm actually going to add a new page and we'll just start with a blank page.

What I'm going to do is I'm just going to use a table.

And so far in the course, we've been using our manufacturing cells quite a bit, so we should be quite

used to those values.

So let's just create our manufacture and create our sales.

And as I said, we've seen this table quite a bit in the course so far.

So we've got the sum of sales for each of our manufacturer.

The question would be how much does each manufacturer contribute towards the total?

So let's have a look and see how we would do that.

Now, when we go down to our dropdowns, you will see that you get your list of your methods of aggregation,

which we've been through.

We could change it to an average, a min or max.

However, as you move down, you will see that there is an ability to be able to use a show values as

calculation.

So this is quite useful when you go to it, you see a percentage of grand total.

You'll see that your values are automatically calculated now as a percentage contribution rather than

as the actual values themselves.

So there we go.

We've actually now got those percentages so we can see that, for example, fabric, which we know has

got the highest sales, contributes 21.7, 6% of the total amount of sales.

If you wanted to see a datum 7.95%.

Now, when you're putting together your tables, often what you would want to do is you want one to

see your values and your percentages.

Now, it's not a problem.

You'll see now that this field has got the percentage calculation, but you can drag another copy of

sales in and you'll remember that when we put it over the columns and the dark line is shown, this

will tell us where it's going to be inserted into the table.

So I could drop it in there.

And now we have the actual values and the percentages.

And again, you could just change your headings if you wanted to.

You could say, This is my total sales and you could say this one, for example, is my percentage of

sales.

So that right.

There we go.

Percentage of sales.

So there we go.

We got a nice little report now showing us those values.

Let's just see if we can increase this column width slightly.

There we go.

And again, you could use your formatting if you wanted to see that.

So that's one thing we can do.

We can use this in tables.

However, we can also use percentages with our matrices and it does give us some additional functionality.

So let's have a look at that.

And I pop this to the side and let's just use our matrix, create that as a matrix.

And in this case, what I'm going to do is let's use our product category in the row.

And let's use our region.

And again, let's use our cells values.

So again, now we've got a table of data, but obviously there's much more calculations within this.

Now, the first calculation we might be interested in, we may want to know how much does audio and

Asia, i.e. this value over here, contribute towards the total.

So how much?

253,000 contribute towards 55 million.

Now, we can imagine this is not going to be a very big percentage.

Maybe over here we will have a bigger percentage.

If we look at computers in North America, that's 12.4 million.

So first of all, we may want to know how much each of these cells here contributes to the total.

So again, what we can do is we can go to some cells and our drop down and we go to our share values

as now you will see that there is a couple more calculation which we're going to be running through

just now.

So the first one we're going to be looking at is again, percentage of grand total.

So when I click on this, you'll see now that we actually got 0.46%.

So it's telling us that Asia and audio is contributing 0.46% of the total sales, i.e. the 100% sales.

If we looked at computers in North America, as we said, that value was much bigger, but it's actually

22.5%.

So if you took all the values of product category and your regions and you calculated those together,

you would get 100% of your values.

The second part that you will see from this calculation, though, is that if you look at the grand

total for your product category, you'll also take those values and you'll see that that also adds up

to 100%.

So we can see here that audio is contributing 1.86%.

Computers is contributing 38.47%.

So, again, some really interesting information that you're seeing.

We can also see at the bottom that if you took your regions that Asia is contributing 22.41% and North

America 59.

And again, you'll see that those values would add up to 100%.

So literally, we're getting three sets of values being calculated with this calculation.

However, what you might find is you might want to do a little bit more in depth analysis.

Now, let's say, for example, we wanted to look at each of the regions and we wanted to know how much

does each product category contribute to the total of that region.

So now what we could do is we could change our calculation.

So if I went to my share values, as I said, I wanted to see a percentage of column.

Now you'll see that each of my columns at the bottom here is actually calculated to 100%.

However, now what it's doing is it's showing me for each of my product categories, for each of the

regions, how much each one is contributing.

So as you can see for Asia, audio is contributing 2.05%.

Computers 38.59%.

And you could now contrast that across to North America, where audio is contributing a little bit less

at 1.74%, and computers also just slightly less at 38.2%.

So each of these calculations is giving you a different view of your data.

So that is our percentage by column.

And basically what we're able to do now is understand how much each product category is contributing

towards each region.

We may want to look at the opposite side.

We might want to look at each row and understand how much does each region contribute to each product

category.

So in this case, if we were to look at audio, you might want to know how much is Asia contributing,

how much is Europe contributing, how much is North America?

So again, we can change our calculation back to our share values.

And in this case we would use a percentage of row total.

So now we can see that for audio that is 24.66%.

Europe's contributing, 19.96%, and North America 5.38%.

So as you can see, depending on which calculation you're selecting, will depend on which percentage

that is calculated.

And it's very, very powerful.

And the number of calculations that you can get and the insights into your data that will come from

these calculations.

Okay.

So there we go.

There's some percentage calculations for you to play with.

I'm going to conclude this lesson and I will see you in the next one.