All language subtitles for 3. Assembling a Dashboard - Part 2

So we're making pretty good progress on setting up the account relations for our dashboard.

The only other calculations we need to do are the calculations that we need for those high level headings.

I'm talking about these ones at the top here, highest number of orders, highest costs and most profitable

now because of the way that we've set this up.

We can utilize the information in this table and just basically find the maximum value in each of these

columns.

And what I want is the value and also the label, in this case, the product.

So all we need to do here to find the highest number of orders is equals max and select our range.

Close the bracket, enter and there is a highest number of orders.

So we need to do the same for the others.

So this time max.

And this is going to be of that COGS column to enter.

And then finally, max of the profits column like so now, in order to find the corresponding label,

i.e. which product has the highest number of orders, the highest costs and the top profit, we need

to do an index and match again.

So we're going to type in equals index our array.

So our array is always what we want to return.

So I want to return the product.

So this is my array.

The row number will we can automate that using match and I want to find.

This look up value, and we're going to look for that in the number of orders range.

And we're doing an exact match, close of match, close of index Hanta, and now I can see that one

71 relates to the Vermont product.

We're going to do exactly the same for these others index.

What do we want to return?

We want to return the product.

How are we going to find that?

Well, we're going to match this look up value in this.

Look up, Ray, and we're doing an exact match close of both brackets and hit enter and then finally,

we're going to do an index again.

We want to find the product.

We're going to use Match to automate.

This is the look up value.

We're looking for it in the profit column this time, and we're doing an exact match, closed both brackets

and hit enter.

Now for this one, it looks like Vermont is the best selling product for 2020.

Let's quickly change the dropdown up here to 2018.

If you look at our calculations, I can see that now that has changed slightly.

Highest cost is Burlington.

Top profit is Mandarin.

So everything is flowing through really nicely.

Now those are pretty much all of the calculations that we need.

The only other things we need to add before we start assembling all the different moving parts onto

the dashboard are the charts that we want to use.

Now I'm only adding three charts.

You might choose to add a lot more than that.

Now, as I said, the charts that I'm using are not pivot charts because we haven't actually created

any pivot tables.

So if we go across to the chart units sold tab, currently I have nothing on here.

Now where am I going to get the information for my charts?

What if we go back to our Calculations tab?

This contains the information that I want to use in this chart.

I have a breakdown of the countries and the amount of units sold.

Now, this actually shouldn't be in dollars because this is the the number of units sold effectively.

So I'm quickly going to change that just to number.

Let's take down those decimal places and I will add a comma separator.

So what I can do here is I can simply select this data, arrange go to insert and then select a chop.

So this first one is going to be a bar charts and I'm going to select it control x two carts, and we're

just going to paste it over on the units sold tab.

And of course, we can apply whatever formatting we want.

So let's quickly do some formatting here.

Let's change these bars to green.

I'm going to make the gap with a little bit less.

In fact, quite a bit less.

Let's add some data labels, and I'm going to put these on the inside bass this time.

I'm also going to format these let's make them bold and also white.

And because I have them there, I don't really need this axes.

Let's delete out.

And I think I'm going to change this title as well.

Don't really need it to say some if we just want it to say units.

Oops.

Sold.

I'm going to make this title slightly bigger and also bold, and let's make it dark green as well.

And just for fun, let's add a little trend line in so we can see the trend of that data.

Now, of course, you could go into a lot more detail about how you format your charts.

But just for quickness, we're going to leave it just there.

Now this is all well and good, but currently this chart isn't linked in any way, shape or form to

the combo box over here.

And what we ideally want is that when we put this chart on the dashboard and we switch between these

different years, the chart is going to update.

So how do we get this chart to link through to that combo box?

Well, we have to think a little bit outside the box here.

The way I'm going to do this is I'm going to use a very small pivot table, but I'm going to use it

in a slightly different way to what we have been using them.

So let's go back to our pivot tables data source and create ourselves a pivot table.

We're going to create it from a table or range, and I'm going to place it on an existing worksheet

because I want it to go onto the units sold worksheet and click on OK, now I don't want it over there.

Let's just cut and paste this over here.

Now, the only thing I want to display in this pivot table is the year.

So I'm going to grab the year filled and I'm going to drop it into columns so that we have our years

running across.

What I'm also going to do is turn off these grand totals.

We don't need those for what we're doing here now.

Underneath my little pivot table, I'm going to create another index formula.

I'm going to type in index and I'm going to index.

The year's just here and for the road, no, we're going to go across to our calculations page and we're

going to use this sale just here, a 12 F4 to lock that in place, close the bracket and hit enter.

So now notice it's on 2018.

If I select 2019 in here, that also updates and so does my charts because everything is linking through

the chart.

Data is coming from the calculations page.

It's coming from this table and these values depend on whatever is in this cell over here.

So when this changes, these values change and in turn, this chart changes as well.

So I'm sure you can see that when it comes to designing dashboards that are a little bit more complex,

it can get quite complicated.

Now we're pretty much going to use this same process to create other charts.

So what I'm going to do is I'm just going to copy my pivot table across to these other pages.

And now let's create our second chart, and this one is going to be orders by product.

So if we go back to our calculations page, I want to use this table just here.

Now, I don't want to use the entire table to create my charts.

I just want to use these two columns.

I want the product and the number of orders.

So if I select these two columns, go up to insert, and this time we're going to do Control X to cut

it and I'm going to paste it over here.

Control V and we can now go through and apply our formatting.

So let's change this to a different color scheme.

This time we are going to format the data series and we're going to make that donut hole size quite

a bit smaller.

Let's add some data labels.

I'm going to make this donut chart a bit bigger.

Let's move that down there.

And I'm also going to move the position of this legend to the right hand side.

And of course, you can just jiggle this around and position things wherever you see fit.

Now, I think I'm also going to make these labels slightly bigger in bold.

And let's make them white.

Now, my title here is going to be number of orders by

product.

Let's do the final chart.

This one is going to show the cost versus profit.

And it's going to be a line chart.

So this time from this little table, I want to have the product and then I want the costs and the profit

included in my chart up to insert.

Let's choose a line chart, and I'm going to choose this one just here.

Control X to cut it.

I'm going to place it on the correct tab.

Let's drag this out, make this quite a bit wider.

I'm going to move the legend up to the top.

Let's give this chart a title.

So we'll just say cost less is profit by product, and let's change the color scheme just to keep this.

Similar to everything else, let's go for that green colour scheme.

And that is pretty much it.

We're now at the stage where we're ready to put everything together onto the dashboard.