In this final course you will complete a Capstone Project using data analysis to recommend a method for improving profits for your company, Watershed Property Management, Inc. Watershed is responsible for managing thousands of residential rental properties throughout the United States. Your job is to persuade Watershed’s management team to pursue a new strategy for managing its properties that will increase their profits. To do this, you will: (1) Elicit information about important variables relevant to your analysis; (2) Draw upon your new MySQL database skills to extract relevant data from a real estate database; (3) Implement data analysis in Excel to identify the best opportunities for Watershed to increase revenue and maximize profits, while managing any new risks; (4) Create a Tableau dashboard to show Watershed executives the results of a sensitivity analysis; and (5) Articulate a significant and innovative business process change for Watershed based on your data analysis, that you will recommend to company executives. Airbnb, our Capstone’s official Sponsor, provided input on the project design. The top 10 Capstone completers each year will have the opportunity to present their work directly to senior data scientists at Airbnb live for feedback and discussion. "Note: Only learners who have passed the four previous courses in the specialization are eligible to take the Capstone."
Bar in Bar Graphs in Tableau
Loading...
來自 Duke University 的課程
从 Excel 到 MySQL：商业分析技术毕业项目
133 個評分
從本節課中
Data Dashboard
The goal of this week and next week is to build an analytical dashboard in Tableau using the data models and assumptions you have discovered in prior weeks.
與講師見面
Daniel EggerExecutive in Residence and Director, Center for Quantitative Modeling
Pratt School of Engineering, Duke University
Jana Schaich BorgAssistant Research Professor
Social Science Research Institute
Hi guys, if you're watching this video that means that you have been able to
replicate the results you got in Excel in Tableau.
That's a great accomplishment, you should be very proud of yourself.
And you're going to be glad you did it because there's going to be a lot of fun
stuff you can do in Tableau now that you've been able to get your basic model
into a format that Tableau can read and that you can use.
In this video, I'm going to show you how to make those bar and
bar charts I showed you in the dashboard examples we saw in previous videos.
In order to do this,
you're going to need to make sure that you have one set of parameters for
your fixed values and one set of parameters for your variable values.
When I say fixed I mean, the original assumptions you made in your model.
When I say dynamic or variable, I mean, the parameters or the assumptions that
you're going to change in order to see how they influence the outputs of your model.
So make sure again that you have one set of parameters for your fixed values.
And one set of parameters for your variable values and then also make
sure that you have one set of measures that refer to the fixed parameters and
a different set of measures that refer to the variable parameters.
Once you have that you'll be ready to make your bar and bar chart.
So let's go ahead and see how we do that.
So if we bring up Tableau, I want to point out, before we start,
your worksheet is going to look totally different than mine.
You are not going to replicate exactly what I have here.
So all of your variable names are going to be called something different, and
all of your parameter names are going to be called something different.
Use this video and all the videos you see in this capstone project from now on,
as an example of the concepts of how you would make a graph, but
don't try to copy it exactly,
because you're going to have setup your work sheets a little differently.
So one more time, just use this for the concepts of
how to make these graphs rather than how to do it exactly in your worksheets.
So the first thing I need to show you is how to make the calculated fields that
will let you aggregate only the properties that are profitable based
on the original assumptions in your model.
And then make a separate filter,
a separate calculation that aggregates only the properties that are profitable
based on the new assumptions or new parameters you've entered into your model.
To do that you're going to need to make different sets of parameters and
calculations.
So you're going to start by making one parameter that has a cutoff for
your dynamic model, and one parameter that has a cutoff for your fixed model.
When I say dynamic, in all these cases, by the way, are, let's start with fixed.
When I say fixed that means that this is the model that's going to use
the original assumptions.
Financial assumptions that you made and that you got from elicitation.
Want it's a dynamic or
sometimes they'll say variable model, that means it's the model
that is using the new assumptions that you've entered via your parameters.
Okay so that's I'm going to use that terminology from now on.
So make sure you have these two parameters.
Then you're going to make a set
of calculated fields that are going to make a binary output.
You're going to make one for fixed model and one for the variable model.
The fixed one is going to say,
I should say the goal of this is just to make a binary output that says yes this is
a profitable property based on my assumptions or not.
And in this case,
we want to say, yes it's a profitable property based on my original assumptions.
So we're going to say, if the profits after conversion
using my fixed parameters, are greater than the cutoff I've set for
my fixed model, then output a one.
Else outputs at zero.
The variable calculation will do something similar, except that
this calculated field is going to be for the dynamic version.
So this calculation uses the dynamic parameters that are all set down here.
Then it's going to say if that calculation is greater than my dynamic
profitable cut off, then one.
Then you're going to need to make one more calculation in order to aggregate
the fixed profitable properties separately from the variable profitable properties.
You're going to need to make another calculation that says,
if it's profitable based on this calculation, then just give me the value.
If not, then give me a null value, take it out.
So what this calculation does, is it's going to only output the calculation,
only output the profit form this calculation if
it's profitable based on this calculation and based on the profitable parameters.
The variable one will be similar, except it's going to have in the profitable or
not variable version and
it's going to calculate profits based on the dynamic parameters.
So by doing this, now when you put these two measures onto your
workspace, this measure will only show the properties that surpass
the cutoff based on the fixed model.
Fixed parameters.
And then this one will only show properties, and
only aggregate properties that have profit sets or pass the variable cutoff and
that use the variable or dynamic parameters.
So, that's how you would filter these two different
measures separately without using the filter shelf.
That's the most complicated part of this visualization.
Everything else is going to be much simpler.
So, how do you make the bar in bar graph?
I'm going to double click one of my variables.
I'm going to bring the other one up next to it on the row shelf.
As you would expect, it's putting each one of these measures on a separate row.
What we want to do is, we want to put the two measures on the same row and
we want to actually put it in the same graph.
The way to do that is, you go to the pill that's all the way to the right, so
the one that's farthest to the right, click on the drop down and
then click Dual Axis.
What it's doing is it's automatically putting both
of these measures in this visualization.
And it's putting this mark,
which is actually associated with the variable parameters using this axis,
and there's one underneath that they can't see right now that's using this axis.
So I do want to, let me, I'll show you how to change these in a second.
I'll explain this more, but let me make these into bar graphs,
just to make it a little easier to see.
And I'm going,
in order to show you the bar behind it, I'm going to say move marks to the front.
So now let me explain what I just did.
This mark represents the aggregated sum
of the profits based on the model that used the original parameters.
I can change that mark individually.
I'm using the mark's card so this, you can see that you could either click on all,
or you can click on a panel that has just this measure or
on a panel that has just this measure.
I want to use my variable one first.
And now, I can change either the type of mark it is.
I'll make it a circle.
If I didn't want to have a bar, we want it to be a bar.
And I can change the size.
Since this is my fixed parameter, I want this one to be smaller, my side.
So I can change the size.
And now, when I click out here,
you'll see that this bar is now smaller than the outside.
If I wanted to change the color or
the size of this one, which is my variable parameters.
I would click on this panel, and then I can change the size here.
So I could either make it bigger or smaller.
Okay, now there's an important thing you need to do now that you can see
both of your marks.
You can see that this data point is shown on a different axis than this one.
This one goes from zero to 200 and 2.2 million and
this one goes to zero to 2.6 million.
You really need these to be on the same axises.
To do that, you right click on either axis and you click on synchronize axis.
Now forever more whenever either one of these
marks are shown they're going to be shown on the same axis.
The axis will have the same minimum and maximum value.
If you want to clean this up so that you don't have to actually see the axes on
both sides you can go to the pill, go to either pill, this pill is
the one on the right and you'll see that show header has a check mark next to it.
If you unclick that, all of that will go away.
You can do the same thing with this one.
You can also just right click on the axis and say show header.
If you want to get it back you would go to the pill, click on the drop down, and
say show header.
There are a couple of other things it'd be useful to show you here.
If you want to change the formatting of those numbers you can format them.
So I right clicked and then I clicked on format.
Here under scale, this numbers option is where you would change that.
So on my dashboard I have this in terms of millions instead of thousands.
So you can click on this drop down and go into Currency Custom, and
you can change how many decimal points you want to see, whether it has a prefix,
whether it has a dollar mark in front of it, and what type of units.
So if we wanted to change to millions,
we'd change that here and you can make it one decimal point or two.
And that will change the number formatting.
You can also change the size, of course, love your text.
And the format and the font and all that kind of good stuff too.
If you want to change the title, you can double click on the axis and
you can either erase the title here or change it here.
Now, an important thing to point out right now is that it's automatically,
Tableau's automatically determining the minimum and maximum value of the axis.
If we wanted to change that, we would have to choose fixed.
And we are going to want to change that because of an important thing.
Let me say cancel for a second.
The reason why, is because in our original dashboard, this is my dashboard here,
we have four of these graphs and each one of these shows a different metric.
So this again is the cash flow for the conversion year, cash flow for
after conversion, profits for the conversion year, profits for
after conversion and so on.
There's really no easy way to make all four of these visualizations in
the same worksheet.
So what i suggest you do although there are other hard ways to do this.
But what I suggest you do is make each one of these bar and
bar graphs in its own worksheet.
And then you should insert them into your dashboard as a floating object.
So these here are all floating objects and I can move them around separately.
In order do that, you might want to do one more thing with formatting though.
You can see these lines here in this graph we just made.
If you want to get rid of those so they don't show in your dashboard,
you can see they don't show in my dashboard here.
Then, you can, once again, format your graph.
I'll show you a different way this time.
Go up to the format, and click on borders, you can also do that but,
pretty much clicking in any part of your graph in clicking format.
We want to do is you want to go to this icon here, that has the little square.
And you'll see that these options down here have lines in them right now.
You want to make those be none in order to get rid of the lines.
And now, if you brought that into your dashboard there wouldn't be any lines.
The reason I had mentioned axes before is because if you are going to put them all
together on your dashboard, you want to make sure they all have the same axes.
So you're going to want to go in by hand and say what the axes should be.
I made mine -0.5 million up to 3 million.
So, in order to do that, you can double click on the axis.
And I would start with.
-500,000 and I would make it go up 3,000,000.
And that's how you make your bar and bar graph.
Great job.
Have fun with it.
