Have you ever looked at a dashboard someone made and went, “man, I like that, but there’s too many steps for me to remember”? Or maybe wanted to have a way to play around with your data in a safe space so you don’t mess it up?
Power View is a new add-in for Excel 2013 that consumes the Data Model. For those who are avid readers of our blog, you will remember Diego did a post on the Data Model we’ve integrated into Excel. If you don’t remember that post (because not all of us are perfect), the gist is that there’s a new way to have lots of data in Excel and not slow it down while also having handy things like relationships (to make your handy dandy new in-workbook cube relational). Another thing to note is that the Power View add-in comes installed by default in Professional Plus versions of Office.
The point of Power View is to make it very easy to create pretty, interactive data presentations (or “reports” as the experts call them) that will make your boss go “Wow, how did you do that? You’re a genius!” Well, OK, maybe not that far but you’ll definitely look super smart. Plus, it also can be used to explore your data visually without worrying about messing anything up and even make reports on your own, without IT help.
Some of you may be quizzical and asking yourselves, “But… Doesn’t Excel already have graphs and charts and visualizations?” To which I would say, “Yes!!” and probably also add that with Power View, you get automatic cross-filtering of visualizations, new visualizations that aren’t in Excel (such as a “play” axis, maps, and cards), a free-form canvas that doesn’t have the restriction of gridlines (no more messing up alignment because you inserted a new row!), unique filtering options (e.g. sliders), and support for images. Plus, if you’re a data head, you’ll be excited to know that Power View sheets in Excel can also be hooked up to external data models (i.e. Analysis Services Tabular Models).
For this post, I’ll be stealing borrowing the workbook Diego made for his house hunt. So, to *help* Diego find a new house, I *borrowed* his already-modeled workbook and booted up Excel. Then, I inserted a Power View sheet…
Insert a Power View sheet
1. Click on the “Insert” tab
2. In the middle of the ribbon, click on the “Power View” button in the Reports section (to the right of the Charts section)
3. If this is the first time you have used Power View, you’ll get a dialog asking you to enable the add-in. Go ahead and hit Enable so we can get started.
If you hit cancel, you’ll have to go to step 1 and we’ll be here all day.
4. If you don’t have Silverlight installed, you’ll have to install it. Power View needs it to render all those pretty visualizations you’re going to make. Good thing we have this nice bar that comes up to give you a handy link:
5. After you’re done installing Silverlight, go ahead and hit the “Reload” button on the bar there. You should now get a blank Power View. Whoohoo! Your first Power View. I’m so proud!
Add a Title
First things first: titles.
1. On your Power View sheet, click in the title text box at the top of the sheet and type “Where to buy a house?” Titles are important, people! Don’t skip this part, it’s definitely essential to the whole shebang.
Add a map
Second things second: we are going to put the data on a map so we don’t have to stare at it in table form, scratching our heads, wondering where this “Ravenna” place is and whether it has views of Puget Sound. (hint: it doesn’t)
1. In the field list on the right side of the screen, from Table1, check the checkboxes next to LIST PRICE and IS SHORT SALE. You should now have a table on the Power View canvas. Voilà!
What, a drag-n-drop table isn’t cool enough for you?
2. I want to visualize this data on a map (as people like to have something to look at to see what’s what), so I click on the table, then click the “Map” button on the Design ribbon tab.
3. Now, I have a map but I forgot to add location information, so there’s nothing on it. Don’t fear, it’s easy to fix! Just click on “All” in the field list (look at the tippy top) and drag “GeographyID” (aka the zip codes) from the demog1 table to the “Locations” well. The map zooms in to your locations! Fun.
4. Next, check LONGITUDE and LATITUDE from Table1. They should populate into the “Longitude” and “Latitude” well.
5. Next, change the aggregation function for LIST PRICE to average (as seeing a sum for an area is really not helpful. To do this, click the arrow next to the LIST PRICE field in the “Size” well and then click on “Average” in the drop down menu that appears:
6. Finally, drag ADDRESS from Table1 in the field list below GeographyID in the “Details” well. This will give us a “hierarchy” of locations; this means we can double click on a bubble on the graph and it will change the view to the next field level down in the Locations well. In our case, it will go from Zip Codes (demog1’s GeographyID) to the address level for houses (Table1’s ADDRESS)
7. Now, you should have a map that can tell you there are a lot of short sales going on in certain Zipcodes. What’s up 98122? You should have something like this:
Remember, double clicking on any point will bring you to the address level-view for those homes. To get back to the GeographyID or Zipcode level, there’ll be an Arrow icon next to the filter one. Nifty, huh?
Add a Bubble Chart
Since I’m happy with the map, let’s move on to something else that’s just as cool: Bubble charts! (no soap required)
1. To put a new visualization on the sheet, first click on empty canvas space (anything blank on the Power View sheet).
2. In the field list, check the checkboxes next to demog1’s UnemploymentRate, MedianAge2010, TotalHouseholds2010, and GeographyID. From Table1, check Location.
(NB: you’re going to see the “Relationships may be needed” warning, but we already have the relationships we need for this example. You’re seeing that because Location isn’t in the primary table, but since GeographyID is in the primary table, it can be used by Power View to differentiate each row)
3. In the Design tab, click on the “Other Chart” button and select “Scatter”. Move LOCATION to above GeographyID from the “Color” well in the field list.
4. Change the aggregation functions for the fields in the “X Value”, “Y Value”, and “Size” wells to Average.
5. Next, drag the Beds and Baths fields from Table1 and drop them on the Filters pane (to the right of the canvas, left of the field list).
6. Move the slider for Beds to “Greater than three.”
7. Click the switch filter mode button twice to get to the Advanced Filter Mode and type in “2” to the text box and hit “apply filter.”
8. Now, Diego can decide from all the 3 bed, 2 bath houses in the Seattle area. Here’s the Power View sheet we end up with:
Looks to me like he should look for a house in Fremont, as it’s got a nice median age and a low unemployment rate, as well as no short sales! I feel so helpful now. Wait, isn’t that the same conclusion Diego came up with last time? Well, now Diego can easily justify his decision with a beautiful, interactive report.
Also, I just put together something that’s fun to click around on and explore. Try clicking on a neighborhood in the bubble chart and watch the map filter! Maybe I’ll start looking for a house now that I have such an awesome way to dig around in the data.
In this post, we did a few things: insert a Power View sheet, create a map visualization, create a bubble chart, and add filtering to the whole sheet. I made something that looks pretty complicated in way fewer steps than you’d think.
For a more detailed demo of Power View, check out Sean Boon’s Olympic series here.
For more info on using Power View, check out these articles on Power View and What’s New in Power View for Excel 2013.
— Allie”
About Advanced Network Consulting:
If you current IT consultant is unresponsive to your business needs, takes too long to return messages, or cannot provide on-site service, consider giving Advanced Network Consulting a call. We provide excellent IT support and service for Southern California small and medium size businesses. Our certified technicians are friendly, personable, and professional. Our clients concentrate on what they do – grow their business while we secure and maintain theirs. Our complimentary on-site evaluation gives you a chance to get to know our technicians, quality of work, and level of commitment to your business. To schedule a meeting, call 562.903.3992. We look forward to helping your business prosper.