Jon Sanderson: using 'Weatherbug Historical
Observations' via Windows Azure Marketplace DataMarket
Jon writes:
"I pulled the
Windows Azure Marketplace DataMarket - Weatherbug
Historical Observations to get the elevation of 13,324
records of Weatherbug locations that track current weather
conditions. I then put together a table of the 50 states
and which basketball conference that "state" team belongs.
All of the SEC schools for example, have state schools in
their conference. I made high level assumptions on states
like Louisiana (LSU), Pennsylvania (Penn St over Penn), Ohio
St, etc. I wanted to see the average elevation by
conference, and also pulled into the time zone to sort west
to east to see if the logic made sense. I feel it turned
out very well, while these type of calculations aren't
necessarily real world, I felt like that was the outside of
the box thinking you were looking for.
Screenshots of Jon's
creation
Tushar Mehta: Sent in several entries using PowerPivot
and Windows Azure Marketplace DataMarket
Tushar writes:
Entry
#1
This dashboard adapted a
visualization technique I created some time ago to color
shapes in an Excel object so that the net result is a visual
dashboard that conveys information in a natural context – in
this case a map of the U.S.
I integrated several
Excel components with my software to create this solution.
Data from the Azure Data Market, together with USPS web data
were added to a PowerPivot database, which was the source
for a PivotTable (PT) in Excel. Slicers filtered the
PivotTable and downstream analysis updated a map of the U.S.
Entry
#2
This dashboard shows
U.S. crime statistics on a state level in their ‘natural’
context, i.e., a U.S. map. At the same time, a more
traditional bar chart shows the data in a different by
equally relevant layout.
In the snapshot below I
have intentionally left visible elements of the Excel
environment. While the row and column headers and gridlines
serve no useful purpose in the dashboard they do remind the
viewer that this is an Excel workbook.
Entry
#3
This dashboard shows
U.S. crime statistics on a state level in their ‘natural’
context, i.e., a U.S. map. At the same time, a more
traditional bar chart shows the data in a different by
equally relevant layout.
The use of the map of
the U.S. with appropriately located bubbles lets one see
data in their natural context, something that may reveal
patterns that might otherwise not be obvious – at least not
without more work – a lot more work in some cases. The map
I used did not include Alaska and Hawaii. So, I located
their bubbles in their general direction.
The charts are
synchronized and controlled by the 2 slicers, one for the
year and the other for the type of crime.
The first chart ( REF
_Ref294975583 \h Figure 1)
uses the same color scheme for all data values.
Consequently, it relies strictly on the size of the bubble
to convey information about the relative number of crimes of
interest.
Figure
SEQ Figure \* ARABIC 1 –
Murder statistics for 2008 across U.S. states.
The data are adjusted to represent the crime rate per 1,000
of the state’s population
The second chart uses
‘conditional’ colors to show three ranges of values. The
conditional colors are created by plotting each data point
in one, and only one, of three series. The three series are
then colored differently.
Figure SEQ Figure
\* ARABIC 2 – Murder statistics for U.S. states for 2008.
The data are adjusted to represent the crime rate per 1,000
of the state’s population
Congratulations to our winners!!
-The MrExcel Team
* Terms and Conditions are subject to change -
but that probably won't happen.
