skip to Main Content

I’ve been getting this question a lot lately:  How does Power View relate to PowerPivot?  Is PV a replacement for PP?   (And why does PV have a space in it while PP does not?)

(For more information on the various “Power *” offerings from Microsoft, see also the newer posts What is Power BI? and The Three Big Lies of Data).

First:  Understand that PowerPivot is Kinda Two Things

Let’s rewind all the way back to Office 2010, a world in which PV does not exist.  (For most of you, we call this time Today.  And for those stuck on 2007 or 2003, you may refer to this as Tomorrow.  Or maybe even the Day After Tomorrow).

In that world, which is where this blog largely lives, it’s helpful to reflect that PowerPivot has two parts:  the PowerPivot window, and the Excel window.  They have the following relationship:

PowerPivot's Relationship to Excel


Yes, in 2010 we like to write our measures in Excel, using the field list.  So we’re writing part of the model while looking at the visualization layer (Excel), rather than the PowerPivot window.  The lines blur a bit there, yes, but essentially we still have the Model (data, tables, relationships, calc columns, measures, etc.) and we have the reports/visualizations (pivots, charts, etc.) in Excel.

Power View is Purely an Alternative to the Excel Visualization Layer

PowerPivot is the Source of Data and Underlying Formulas/Relationships (aka the Model) that Can Be Exposed via Excel or Power View

Example:  The User Group Maps

You know those maps of PowerPivot/Excel user group interest I’ve been posting the past couple of days?  Well those are Power View visualizations in Excel 2013:

A Power View Visualization in Excel 2013.  Note that Power View Gets its Own New Sheet Type.

A Power View Visualization in Excel 2013.
Note that Power View Gets its Own New Sheet Type.

Now…  could I have used a PivotTable for this?  Maybe. 

But here’s the trick:  locations don’t always match in the data even though they match in the real world.



Two Problems:  “GA” is the Same as “Georgia,” and Alpharetta is Part of Metropolitan Atlanta
They Should All Be One User Group – I Need All of Those to Be Considered the Same Place!

So in some sense you can think of this Map visualization in PV as a smart “grouping” feature, since it uses the Bing Maps engine to plop all of those dots in the same place:

Power View Displays Geographically Close Locations as a Cluster

Power View Geographically Charts All of Those Rows as a Cluster On the Map, Just Like I Need!

Compare that to a pivot, which treats them as 3 separate locations:

Geographic Locations in a Pivot - If They are "Close," the Pivot Doesn't Know

A Pivot Will Treat Those Locations Just as Distinctly as it Does Athens Greece Smile

In Both Cases Though…

Both the pivot and the PV map connect to the same PowerPivot model and use the same measures (like Response Count).  It’s just that in 2013 we get to choose different visualizations for different purposes.

In other words, ideally we would all view Power View as just a new type of Excel Chart, whereas today it admittedly feels like an alien transplanted into the Excel box.  (I’m providing a lot of feedback to MS along those lines and so far it has been warmly received, which always feels nice).

We also now have GeoFlow, which honestly I’ve yet to experiment with, but will soon.  In true MS fashion we now have two competing map-charts hosted in the same product!  Woo hoo!  Let’s hope they unify those two, and THEN unify both of those with the rest of Excel Smile

Power View Offers More than Maps

There’s also a “Card View” (pictured below) and everyone’s favorite, the animated bubble chart.

More on these later.  It is not my intent to taunt people with things they can’t use, and honestly I don’t enjoy working with 2013 that much due to a number of cosmetic issues.  So I’m keeping the blog firmly rooted in 2010 for the forseeable future.

Power View's Card View

Power View’s Card View

Rob Collie

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 16 Comments
  1. “I don’t enjoy working with 2013 that much due to a number of cosmetic issues”

    I hear you on that! I had 2013 installed on my office computer for a short while, and, my God it is ugly! There is no contrast between anything, even on the “dark grey” theme setting.

    We Had to revert back to 2010 for technical reasons*, and I wasn’t too upset.

    * PowerPivot in 2013 does not work on our machines for some reason, giving a similar error that the early releases of the SQL 2012 based versions of PowerPivot gave – which had something to do with local policy rights.

  2. Nice simple overview of these three components and their relations. Useful for introducing new users.

  3. Excel 2013 lost some Powerpivot functionality:

    1) The SWITCH() and IF() functions now treat numbers and dates as different data types and won’t allow both in the same formula

    2) Lost the ability to query the Powerpivot metadata (e.g., SELECT DISTINCT [Table], [Object_Type], [Object], [Expression] FROM $system.discover_calc_dependency where Object_Type = ‘MEASURE’ or Object_Type = ‘CALC_COLUMN’)

  4. fresh perspective on powerview being just another excel chart. Was muddled about when to say powerview and powerpivot. – using them interchangeably until now. thanks! I would be interested if you have anything comparing SSRS with PowerView. One of my favorite features of Powerview is that you can elegantly offer users detailed view of any part of a dataset represented a chart- side by side. In excel you do this by double clicking into a field in the Pivot table resulting in another tab opening up listing details of records behind the data. In PowerView both chart and details can be viewed at same time. Is PowerView superior to SSRS in this functionality? Would love to know.

  5. So i never thought of this question until i stumbled at this post…Why does PV have a space in it while PP does not? so do we have any answer for that. Sounds silly though 🙂

  6. Someone in my office is looking to do what’s expressed in a world map with bubbles as in power view in Excel 2010. Is this possible at all only with Excel 2010 with Power Pivot added. Is it right that Power View is only available with Excel 2013. A quick answer will be appreciated. Thanks.

    Very Distressed

  7. Well I don’t know… Bought your books “Dax formulas”, “Alchemy” as well as Kasper’s Dashboarding, Worked on 2010 and now working on 2013; maybe because I lack your experience, I have no problem using 2013. I find Power View quite cool although sharing with non Microsoft user isn’t really clear to me as of this moment. This said all end users are quite thrilled by the map system (as we rely on data emanating from several production sites geographical scattered in Asia). Great blog by the way, discovered PP here.

  8. Is mapping features part of Power Pivot in 2010, or do I need to upgrade to Power View and Excel 2013 to get the mapping features? Can you tell me more about the differences between Excel 2010 and Excel 2013 BI features and capabilities? I’m new to Power Pivot as our company just upgraded from 2003 and 2007 Excel…

  9. Hi Rob,
    Thanks for posting! I created a Power View visualization (map) in excel and I want to put it into an Excel Sheet. I have two auto-populating data tables that accompany the visualization. Right now they are in two separate tabs. I want to put them all into one tab. That way I can show the stakeholders what the data means clearly because all the information will be in one spot. Is it possible to do this? Thanks for your help!

  10. its just me. I use excel everyday since 2005. I dont want to upgrade to anything beyond Excel 2007. Why? I am like most of the Excel users a keyboard guy. I dont use trackpad or mouse. The flat design post excel 2007 is not something that cosmetically appealing. Flat design is for touch screens right? Keyboard is not flat so the inherent connection I make with the excel when i am using keyboard should not be flat. I love excel and I love data. Just for the love of excel, I went on to get MCAS (Microsoft Certified App Specialist) in excel 2007.
    Just me again. But I am still happy that you have not changed any of my key comibinations for shorcuts 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *