Original subtitles

In this lesson, I'd like to speak a little bit more about different types of calculations that we can

do in our tables.

So, so far in the course, we've been summing everything, even this activity that we've got on the

screen at the moment, you can see that we've got the sum of sales and we've just basically been selling

those sales.

But what if you wanted to know what was your average sale value or what was your highest sale value

or your lowest sale value, or you wanted to come to the number of records.

So you might be used to these types of calculations in Excel.

So let's have a quick look and see how we're going to do these in Power BI.

So I'm actually going to go back to my page one and I'm actually going to delete this existing matrix

that we were using.

Let's start off with a table again just to show something quite simple.

Let's use our manufacturing sales.

We've seen this table quite a bit in the course so far, so you should be quite familiar with the values

that we're seeing.

But what would happen now if you wanted to know what was my average sales value for each of my manufacturers?

Some some manufacturers.

You might have products that are actually selling at a much lower sales value than other manufacturers.

And you want to compare these.

Now, what you'll see is that when you go to your sum of sales is that you actually get a little dropdown

and when you select that, you get a lot of options that you can do with this field.

So you can see the top, we've got to remove fields, so that's quite useful if you want to remove it.

Although the X are easier to use, you can actually rename for this visual and that's something I'm

going to be showing you just now, that it's actually useful sometimes that you might want to rename

the title of this so that in your header you get a different name.

You can actually move this as well.

Let's just actually have a look at a couple of these.

And remember when I picked it up and I moved it up and down, depending where I wanted it to be placed.

So you could use the up and down here to do that.

There are different visualizations that you can add to this as well, such as spark lines, conditional

formatting.

So I'm going to have a look at those a little bit later.

But the ones that I wanted to get to was these set of calculations here.

Now the first one is actually don't summarize, so we could actually tell the system, don't summarize,

show me all the individual values that are in the that are actually in the database.

So this is now going and it's row by row now just showing you all of the values for each of the sales

that are in the actual database.

So you'll see there'll be quite a lot of these.

So this is the first one we could do is we could tell it to don't summarize.

Otherwise we've got the sum which will now sum up the values, which is our default.

Also, we could say that you want to average this.

So when I click on average, you'll see straight away that we get average of sales.

And now it is showing me what each of the average values is.

So what it's doing here is actually taking the total sum of sales and is dividing it by the number of

transactions or number of rows that are actually in the data set for this manufacturer.

And that's really how it's getting the average of sales.

Let's continue let's just look at some of the other options we've got here.

So we've also got a minimum amount.

So when it goes to the minimum amount, what it does is it goes through the data set.

It looks at the range of values for each of the manufacturers and it picks out the lowest value within

that range.

So in this case, what it's saying for a datum is the lowest sale that it has is 439.

Now, one thing I do want to note is just how your grant totals are changing.

Now, you may have seen on the average what happened was it actually now shows you an average for the

entire data set here.

It is showing you the lowest value that is actually in the data set.

So basically, just be careful of when you're looking at these totals.

It is according to the method of aggregation that we that we're looking at.

So you can see that the lowest value is at five and that we've got that five value there.

You'll also see that we can do a max.

So if we go to Max, it's going to go through your data set again for each of your manufacturers.

It's going to pull out the highest individual range.

So as I said previously, you can imagine if you're working with Excel, you're working with equal sum,

equals average equals min equals max.

That's really what we're working with here.

We're working with the same type of functions.

Now in Excel, you do get another function which is called equals count.

So equals counts will go through your data set and it will count the number of rows that each of the

items of God.

So in this case, each of the manufacturers, how many transactions or how many rows do they have in

the data set?

So if we go across here, we get a equals count and you'll see now that we know that there's 15,000

rows in this dataset, because we saw that right at the beginning of the course, but now you can see

that a datum, for example, has 1342 transactions.

You can see that a venture works 1125.

So this is now doing a count of the number of transactions.

Now, this is really useful when you want to know the number of transactions, but let's say, for example,

for each of the manufacturers, what I wanted to know was how many products, how many unique products

does each manufacturer actually provide to me?

So my data set, how many product names are against each of the actual manufacturers.

So if we were to take our product name and let's say we dropped it in here and we wanted to now do a

count of this, I can see by default what it's doing is it's actually using the text.

So it's actually show me actually manufacturer by product what my cost of sales would be.

So I'm going to change that now and you'll see that there's some options.

What you could do is you could just say, I want to see the first product name.

So if I said the first product name and now we'll just show that first product name or you could show

I want to see the last product name.

That would be the last one that it's got.

But what I'm more interested in here is to do a count.

So when I do a count, you'll see that I get exactly the same amount as I would if I just did a count

of my sales.

Because, as I said, this is now telling you how many rows or how many transactions are in the system

or in the data set for that manufacturer.

So in this case, it's telling me I've got 1342 products for a data.

Now, I know that's not true.

I've got a lot less products.

So what I want to highlight here is a really useful feature which is called Count Distinct.

And what this will do is it will go through your data set and it will then count the product.

So once it comes across the product, it will count at once.

But if it comes across it again, it does not count it again.

So this will tell you how many unique products you've actually got in your data set.

So this is really useful calculation when you're often doing your data analysis.

So let's see the result of this, right?

So you can see now that a datum actually has 131 products.

And overall in your whole data set, you have 1638 products, which makes a lot more sense.

So this is really useful, this distinct count.

And the next part that I want to show you in this table is the ability to be able to combine these methods

of aggregation.

So this can be really useful.

If you wanted to have a table and say for our manufacturer, we wanted to know what is our total sales

value, what was the average sales value, what was our highest sale, what was our lowest sale, how

many transactions were there and how many products are there?

So what I'm going to do is I'm actually just going to remove these two fields.

We're going to start off with our manufacturer and we're going to drag the first sales back in again.

Now, by default, we get the sum of sales and this would give me my total sales value.

Now what we can do is we can actually use the sales value again in our table.

So if I take my sales and I actually drag it and I take it into my columns again, remember when the

thick line appears and I can drop it, drop it in there, I now get a second some of sales appearing,

which currently because it's summing up, the sales will show you exactly the same.

So when I click on the dropdown now though, and I say I want to show this as my average sales, you'll

see now that I can have my sum of sales and my average sales being displayed in the same table, so

I can use this for quite a bit.

So what I'm going to do is we quickly just going to add our high sell and our lowest sell.

So again, we're just going to add our sales value.

And this time, though, we're going to say we want a maximum value that will give me my highest sell.

And again, let's just do another one.

In this case, we're going to do minimum and we'll get our min of sales.

So as I say, you can add the same field more than once and you can do a different method of aggregation

on it.

Now, at this point, I just want to pause slightly, just to talk a little bit about the ability to

change the name of your field, because when we've got a table and we're presenting it to our users,

summer sales, average of sales, Max of sales, not great headings.

If you go across to your columns and you double click on the field, it now allows you to actually change

the name that is going to be shown.

So I could say I want to call this my total sales.

I could say this is going to be my average sales.

All you do is you just double click on the field.

You type in what you want and you press enter.

And there we have it.

I'm going to have lowest sale.

So there we go.

Now we've got a much better looking table with the names that we're using.

Now, the last part that I wanted to do here was to have that count of the sales so I can actually put

my sales in here.

Again, I could now change this and say, I want this just be a count.

And again, I can just double click on this and just said number of transactions.

And that would show me the number of transactions.

And the last one was we were going to reshare that product name again.

So I'm going to drop that in.

And one another number of products.

So just remember, I just got rid of it.

So we're just going to drop it again.

Let's try that again.

Going to drop down.

So in this case, just remember we're using the count distinct.

So we're going to click count distinct on this.

And we got count of product name, not name.

So we're going to say this is my number of products.

So there we go, Inter.

So there we go.

We've got a really useful looking table.

And just remember, if you change a manufacturer here, you can get all sorts of calculations, could

remove this and then it will show you the totals for the data set.

Or you could add another field and you could say, I want to see the same by my country.

Let's drop that in the front there and you'll see.

Now all of these will be now recalculated for each of the countries to show you each of those calculations.

So it's really using a table.

What I want to show you just quickly is that we can also do this for a matrix.

So let's say, for example, we set up a matrix and let's create, say, one with our product category

in row, our channel in our columns and our sales value.

And again, it's defaulted to some sales.

And you'll see just using the same theory we can just click on the dropdown could change this to an

average sales value.

Or we could click to this and drop down to a minimum.

So you can see how this works very, very easily.

And the final part to show you is that each of your fields can actually have a default summarization.

If I go back to my cells, you'll remember from earlier that my column tools popped up and I was able

to change the formatting.

What you can do as well is actually change the way that the summarization works on this.

So you could say, I don't want this to sum or I could average min max count, so you could easily change

what the default is if you wanted to.

So there we go.

Hopefully that gives you a pretty good idea of the different methods of aggregation.

We're going to conclude the lesson there.

I will see you in the next one.