As you have seen in many Power View demos, you can run the Scatter Chart over time by placing date/time onto the Play Axis. This is pretty cool and it allows you to see trends over time on multiple dimensions. But how about if you want to see time also on the x-axis?
For example, let’s take the Hive Mobile Sample data as noted in my post: Connecting Power View to Hadoop on Azure. As noted in Office 2013 Power View, Bing Maps, Hive, and Hadoop on Azure … oh my!, you can quickly create Power View reports right out of Office 2013.
Scenario
In this scenario, I’d like to see the number of devices on the y-axis, date on the x-axis, broken out by device make. This can be easily achieved using a column bar chart.
Yet, if I wanted to add another dimension to this, such as the number of calls (QueryTime), the only way to do this without tiling is to use the Scatter Chart. Yet, this will not yield the results you may like seeing either.
It does have a Play Axis of Date, but while the y-axis has count of devices (count of ClientID), the x-axis is the count of QueryTime – it’s a pretty lackluster chart. Moving Count of QueryTime to the Bubble Size makes it more colorful but now all the data is stuck near the y-axis. When you click on the play-axis, the bubbles only move up and down the y-axis.
Date on X-Axis and Play Axis
So to solve the problem, the solution is to put the date on both the x-axis and the play axis. Yet, the x-axis only allows numeric values – i.e. you cannot put a date into it. So how do you around this limitation?
What you can do is create a new calculated column:
DaysToZero = -1*(max([date]) – [date])
What this does is to calculate the number of days differing between the max([date]) within the [date] column as noted below.
As you can see, the max([date]) is 7/30/2012 and the [DaysToZero] column has the value of datediff(dd, [Date], max([Date]))
Once you have created the [DaysToZero] column, you can then place this column onto the x-axis of your Scatter Chart. Below is the scatter chart configuration.
With this configuration, you can see events occur over time when running the play axis as noted in the screenshots below.
Enjoy!
Denny Lee 
Very cool! Thanks for sharing.
Very cool, and will make viewing the data over time that much more interesting…
[…] Lee (@dennylee) posted a Power View Tip: Scatter Chart over Time on the X-Axis and Play Axis post on 7/24/2012: As you have seen in many Power View demos, you can run the Scatter Chart over […]
[…] demos. I wanted to play around a bit more with Data Visualizations in Excel (specifically, the Scatter Chart over Time feature in Power View), and thought that Canadian population statistics would be a good place to […]
My name is Javiera and I am a fan of what you’re doing with your page and your work with Power View 🙂
I try to experiment with Power View, but I ended with certain problems in the management of the program, so I write to you with the hope that you please could help me in my problem or maybe give me some advises .
The question is this: I have a line chart with several series that are selected via a filter.
The purpose of the chart, is select the series so they can appear separately and jointly (showing overlapping), according to the selection filter that handles the user.
The problem is that only some series appear when the filter selection is made.
All appear correctly when run separately but when is choose more than one, Power View don’t show the lines of the series in the chart corresponding to the selection.
All the lines appear together when is made the selection for all series.
I hope that you could know what is happening or maybe could orient me in a way to do it
Thanks you so much for your atencion.
I give you my e-mail: javiera177@gmail.com
So sorry for the delay Javiera – any luck on tackling this issue. It’s a little tricky to visualize the issue – sorry for the pun 😉