Excel 2013 PowerView Animated Scatterplot/Bubble Chart Business Intelligence Tutorial

Learn how to create animated scatter/bubble charts that show the changes of correlated data over time. This tutorial uses the free tools built into Excel 2013, meaning you will not need to purchase expensive BI tools from 3rd parties. This technique can be used in many analytics scenarios, from "big data" analysis to sales and marketing reporting

---

Closed Caption:

scatter plot charts are now available in
Power View for excel 2013 scatter plots
are a great way to see how to correlated
variables affect each other for instance
is there a correlation between age and
income so that as your age rises so does
your income and if so is there a point
at which this relationship changes such
as after retirement age
this simple demonstration will show how
to animate the scatter plot chart over
time to see how the relationship between
correlated variables may change over
time to give analysts an idea of where
to drill to understand how these
different points of data relate to each
other as you can see from this slide
you're required to have excel 2013
professional and the adventureworks data
warehouse
this is the website where you can
download the adventureworks data
warehouse files to install on any
version you have a sequel server
in this simple example we will be using
three tables from adventureworks the
date dimension the sales territory
dimension and the internet sales fact
open up Excel 2013 and let's get going
we'll start by creating a blank workbook
from the data tab we're going to connect
to our local sequel server just like
this
after you select your database make sure
that you check the box to enable the
selection of multiple tables
then select the three tables which I
mentioned
click finish and then choose a power
view report
the blank report
we'll show
three tables that you've selected one
thing to notice is that the date
dimension has a lot of fields but it
doesn't have any easy ones that allow us
to sort / years by month
so what we're going to do first is we're
actually going to create one let's go to
power pivot and open up the powerpivot
window
we're actually going to use a Dax
expression Dax to create a new column a
new calculated column inside our date
dimension
after the last column in the table click
on add column and then you'll be able to
enter the decks formula this formula is
going to multiply the calendar year by a
hundred and then add the month number to
that value to basically give us a way to
sort by year and month
click enter
finished entering your formula and it
will populate the new field right click
the header of the field to give it a new
name that makes more sense than
calculated column one in this case it's
year month
you can
close the powerpivot window which brings
you back to excel in power of you asks
you to refresh the data
you can check out the date dimension now
and you'll see your new calculated field
at the bottom
so let's start building the chart the
main number were interested in is the
sales amount that's our main measure and
that's what that's going to become the x
axis of our chart
so when we check that notice that it
creates a table over in the blank canvas
let's resize this table
on the other chart button we're going to
scatter to make this a scatter plot
chart
you can see at the bottom that our
x-axis is the sales amount
in this first example let's make the
count of customers are y axis to see how
amount of sales is correlated to the
number of customers
notice that i change that to a distinct
count
our details in this example are going to
be our sales territory region to see how
the count of customers and the amount of
sales are correlated by region
in this example I'm going to use the
quantity in the orders to set the size
of the bubbles on the chart
since we haven't done anything with the
date dimension at this point the numbers
reflect all sales overall time for all
territories
I'm going to hide the filters and resize
things remove my title and just make
this thing a little cleaner and larger
so that we can view our data
now the pièce de résistance is adding
the Year month to the play axis
this will allow us to view the changes
of the data over time
notice that another axis was created at
the bottom of the chart and it's got a
little play button next to it so that
you can actually click that and view the
changes over time of your data as the
count of the customers and the sales
amount change across the different
territories
remember that the size of each bubble
corresponds to the quantity sold
but with a quick change here i can make
the order quantity the y axis and I can
actually make the count of customers
reflected in the size of the bubbles
you can see when I play this that it
took a few years for sales to really
kick off but you can tell it at the
point at which they do start to grow
which happens about right now
please remember
I