By Avichal Singh

You read about my Power Pivot journey in my first blog post and in my subsequent blog post I elaborated on migration to Analysis Services Tabular Model (SSAS Tabular). I realize now though, that I did things out of order and need to address that in some way. As my journey outlined, before we switched over to SSAS Tabular, we moved our Power Pivot workbooks to SharePoint and started using Power View Reports. And Power View has been a key element of our success. For this post I’ll go back to the future and speak about

– Our success with Power View

– All the settings in Power Pivot related to Power View

p.s.: When I refer to Power View I am referring to Power View on SharePoint. I am not referring to Power View functionality built in to Excel 2013, since that is a fairly different experience than Power View on SharePoint.

Power View Success Story

I love Power View, except when I don’t. It can feel limiting at times and frustrating, especially to an excel user (which is all of us Smile). After demonstrating a really slick Power View report with all the bells and whistles (check out a sample from Microsoft BI at Power View Demo. Mine don’t look as good as this), the first question I often get from the user is, “Great, now how can I export this to Excel?” And my answer is – you can’t Sad smile
“Export to Excel is the third most common button in data/BI apps…after Ok and Cancel” (click for a real fun post!), and Power View does not have it. Yet! If the powers that be are reading, I think it’s feasible that an icon appears when you hover over Power View report elements, to export the underlying data in excel in a simple table format. Please consider that for the next release. Now that I am in begging mode might as well ask for – ability to re-label measures/column names in Power View Report and show numbers as Percentage of Total (like in Excel Pivots). The latter is doable using DAX but not easily so.

Okay, now let’s move on to some love ©©©

  • Power View reports are easy to build, maintain and use
  • Shared Power View report give you a Single Version of Truth (kinda)

    Power View reports are easy to build, maintain and use

    Build: Regardless of comments earlier, the best thing you can do is approach Power View with a fresh set of eyes and leave your Excel goggles behind. I had done many a report in Excel and struggled initially with Power View. But once you accept it for what it is, it’s a joy to build reports. I love building reports for our users on the fly. I would schedule a 30 minute meeting with them – in person or share my screen if they are remote. And build the report right in front of their eyes as they guide me. This is radically different than the traditional BI world (gather requirements, spec out design…blah blah) – and our users love it! There are many sophisticated features in Power View – Vertical/Horizontal Multiples, Scatter Chart Play Axis, Tiles – to name a few and many more in Excel 2013 and SharePoint 2013. However, most of our reports use the simplest elements – table, bar chart, line chart and slicers. I’m a fan of Stephen Few and his data visualization books, see his Graph Selection Matrix or better yet his book on Information Dashboard Design. In essence he makes a case for keeping things simple and that works for us.

    The fact that building reports is so simple, liberates you and the BI team to focus on other elements; the ones that really matter. Spending time with users to really understand their business not just their “requirements” (read post The Ideal PowerPivot Professional), focus on your Tabular Model and measures (read book The Data Warehouse Toolkit) and bridge the last mile to make sure the reports are really being used and provide users exactly what they need.

    Maintain: How many times have you opened an Excel Dashboard that you created a year or even a month ago and struggled to understand all the links, formulas and dependencies? You will leave all that behind with Power View. Another of my favorite parts is that the Power View Reports refresh automatically, no user action or excel macros involved! (As long as you schedule a refresh of the underlying tabular model, which is easily done)

    Use: Users like being able to load a Power View report in the browser. They can easily open multiple copies and compare different filters on each. It feels more light weight than opening Excel. With Excel Services you can view an Excel file in your browser, but it does not feel quite the same, plus it comes with it’s limitations. Same with Excel 2013 where you can embed Power View in an Excel sheet, but it just feels clunky. Eventually it may come down to personal preference. Of course you do not have to pick one, you can pick both. We still create a few Excel reports besides all our Power View reports.

    Shared Power View report give you a Single Version of Truth (kinda)

    Single Version of Truth – is one the biggest clichés in BI. And I do not believe in it. It does not exist. Just like in real life, we all have our versions of reality (IMHO, nowhere does that apply more, than in marriage; but that’s a whole different blog post Smile).

    In our group, we cannot even agree whether sales figures are up or down (okay, I am exaggerating…a little). Working with users I have often found that they each have either different sets of filters or use a different context. Some user may filter out some product or product categories. Some may look at amount including shipping and taxes while other might exclude that. Users may have legitimate need to view data in a specific way for their own use. You want to retain this flexibility, while also providing a common view that the team or organization can agree upon.

    We do this simply by creating just a handful of standard reports that apply to the whole organization. For team meetings and such, these are the ones that are used. The filters and context for these reports have been agreed upon by all stakeholders.

    Then we have unlimited customized reports for different teams, even for specific users. Users also have the ability to edit and save their own reports. These are tailored to the filters and context required by users for their own use. But if they need to have a discussion with a different team they need to use the standard reports instead of the custom ones – to make sure they speak the same language.

    It is possible to approach this with Excel alone, but you would not get the ease and accessibility of Power View; which is a powerful combination.

    Power View Settings in Power Pivot

    There are tutorials abound for Power View and it’s fairly easy to just learn by doing it yourself. There is the HelloWorldPicnic Tutorial that I use in this post. MicrosoftBI TryIt page is also a good resource as it lets you explore sample Power View reports or create your own based on models provided. (However, if you do feel that a Power View 101 video tutorial would be helpful, then please leave a comment to let me know.)

    Instead I will focus on helping you understand all the various settings in Power Pivot and how they relate to Power View. This I found, was much harder to piece together from all the material interspersed on the web. HelloWorldPicnic Tutorial prerequisite section would let you download the files I use.

    I will cover settings in the Home, Design and Advanced tabs on the Power Pivot ribbon.
    Power Pivot Home, Design and Advanced ribbon tabs

    For each setting I would show you how you change the setting in Power Pivot and then show you side-by-side views of how Power View behaves Without and With the setting:-

    Power View behavior Without <setting>

    Power View behavior With <setting>



    Let me start by covering a general setting which can provide extremely useful documentation to the model and help your users in creating their own Power View reports.


    You can provide Description for a Table, Column or Measure; simply by right clicking the element and selecting ‘’Description”.
    Power Pivot right click to provide Description

    Power View behavior Without Description

    Power View behavior With Description

    Mouse hover merely shows you the name again
    Power View behavior without Description

    Mouse hover shows you the Description provided in Power Pivot

    Table Description
    Power View behavior with Description

    Column Description
    Power View behavior with Description

    Measure Description
     Power View behavior with Description 

    Let us go ahead and cover the Home, Design and Advanced tabs.


    Sort By Column
    Power Pivot Sort By Column:

    In an Excel Pivot you have a lot of flexibility as to how to sort your labels, from simple options to complex ones using custom lists. Power View (or really Power Pivot) at one point only offered alphabetical sorting. That changed with Sort By Column feature in Power Pivot v2. Simply it lets you sort a column based on ordered values specified in another column. Typical scenario would be sort MonthName (January, February,…) using a Sort By Column MonthNumber (1,2,3…).

    Power Pivot Sort By Column

    Power View Without Sort By Column

    Power View With Sort By Column

    Probably not the way you want to Sort Month Name



    Mark as Date Table
    Power Pivot Mark as Date Table

    This turns on the “Date Filters” in Excel Pivots. You also need this in order to use Time Intelligence DAX Functions. This does not have a direct impact on Power View but is an important setting and is mentioned here for completeness. You need to have at least one field of Date Data Type to be able to use this setting for a table.

    Excel Without Mark as Date Table

    Excel With Mark as Date Table




    Image URL
    Power Pivot Image URL

    This can be used to display an image fetched from a URL, in your Power View Reports. This certainly adds some visual pizzaz, but we haven’t found many scenarios where we could effectively use this. You can also use an Image URL field as Tiles or Slicer.

    Power View Without Image URL

    Power View With Image URL

    The URL itself is displayed…ugh!

    As Table


    As Slicer


    As Tiles

    Default Field Set
    Power Pivot Default Field Set

    You can use this to specify the most important fields in a table so that when users click on the Table Name in Power View these fields automatically get dropped on the report. Could save you and your users quite some mouse clicks and can also be useful to guide users to the more important fields.

    Power Pivot Default Field Set


    Power View Without Default Field Set

    Power View With Default Field Set

    If you left click on the Table name (Items in our Example) => Nothing happens

    When you left click on Table name Items
    The default field set is dropped on the canvas

    Table Behavior
    Power Pivot Table Behavior

    See image below for an example of how you can specify these settings. Detailed description follows.

    Power Pivot Table Behavior

    For the columns selected in Table Behavior, Power View displays a special icon next to the column name. I am not sure if this is helpful or confusing to the users. It would have been more useful perhaps to display such an icon next to each column in the Default Field Set, thereby giving user a visual hint as to the more important columns.

    Power View Without Table Behavior columns selected

    Power View With Table Behavior columns selected



    Let us discuss each setting in further detail.

    Row Identifier: You can set this to the column that has a unique value for each row and is never blank or null. In database terms this would be the Primary Key for the table. As far as I understand this setting in itself does not affect anything discernable, besides enabling you to make a selection in “Keep Unique Rows”.

    Keep Unique Rows: Best explained by an example. In our Items table, we have ItemID 13 and 14 which both have the Name as ‘apple’, one with Color=green and the other with Color=red.



    Power View Without Keep Unique Rows

    Power View With Keep Unique Rows

    Only one row is shown which includes the total for ItemId 13 and 14 (Name = apple)

    Notice the two Items are shown separately. This alerts you that there are different items with the same name.

    Another typical example of keeping unique rows would be the Customer Name. You would not want to club together the data of two distinct customers just because they have the same name. To achieve this, after you specify a Row Identifier, you can select the Customer Name column for Keep Unique Rows.

    Default Label:

    Power View Without Default Label

    Power View With Default Label

    When displayed as card all fields have similar display

    Power View emphasizes the field designated as ‘Default Label’. It also does not show the column name (Name in this case) giving a cleaner look.

    Default Image:

    Power View Without Default Image

    Power View With Default Image


    Image is displayed slightly larger and without the column name resulting in a cleaner look.

    Summarize By (for numeric fields)
    Power Pivot Summarize By

    I explain it below, but per advice from a higher voice, this setting is dead to me, as are implicit measures in general Smile. For more, see ‘Implicit Measures’ at bottom of post When to Use Measures vs. Calc Columns or DAX Formulas for Power Pivot (Chapter 6 Introduction to DAX Measures, “Implicit” versus “Explicit” Measures).

    Note that the setting you chose in Power Pivot can be over ridden from inside Power View by clicking on the field and changing the Summarize By.

    Power View Without Summarize By (Do Not Summarize) setting in Power Pivot

    Power View With Summarize By (Sum) setting in Power Pivot

    No icon next to the field.

    When you drop the field it is not summarized.


    This would likely produce a very unexpected result.

    Columns where you have explicitly specified a Summarize By appear with a å icon.
    Note that measures appear with the calculator icon.

    When you drop the field it is summarized as chosen in Power Pivot.

    Note below that you can still change the behavior of Summarize By as you wish on the Power View sheet.


    Qty Served is now showing the Sum Total for each row

    If you have not been using Power View, hopefully this article would give you some inspiration to give it a try. If you have been using Power View, you should now have a better understanding of how all the settings in Power Pivot affect Power View.

    Disclaimer: The opinions and views expressed in this post are those of the author and do not necessarily state or reflect those of Microsoft