Power View design tips

Here are some tips for working in Power View in Excel and in SharePoint, to help you create interactive views of data from data models in Excel workbooks or tabular models deployed to SQL Server 2012 Service Pack 1 (SP1) Analysis Services (SSAS) instances.

Create a Power View report

Power View in Excel

It's easy to start Power View in Microsoft Excel 2013.

In Excel on the Insert tab > Power View.

Read more about starting Power View in Excel 2013.

Power View in SharePoint

Launch Power View from a data model in an Excel (XLSX), shared data source (RSDS), or BISM connection (BISM) file in SharePoint Server 2013.

Models can be in a standard SharePoint Server 2013 document library or in a Power Pivot Gallery. The Power View design environment opens, and you see the view on which to place your visualizations.

Top of Page

Fit a Power View report to the window

The Power View report sheet in Microsoft Excel (or view in SharePoint) is a fixed size, similar to a Microsoft Office PowerPoint slide, unlike other Excel worksheets.

Power View Resize

By default, the sheet fits to the window and resizes as you open more panes. For example, if you show the Filters Area, the sheet shrinks to fit the remaining window. You can turn this off.

To stop the sheet from resizing to fit the window, clear the Fit to Window button.

Now you can scroll to view the different parts of the sheet.

Top of Page

Why are chart icons grayed out?

When an item on the ribbon is disabled (grayed out), it means that action isn't available for whatever you have selected in the sheet or view. For example, if a table has no aggregated numbers such as sums or averages, you cannot convert it to a chart, so all the chart icons will be disabled on the Design tab.

Add a numeric field to the table.

Top of Page

What do the icons in the Field List mean?

The pane to the right of the view is the field list.

The fields section in the top half displays the tables and fields in the data model that your Power View report is based on. These are the fields you have available to put in your report.

The lower half is the layout section. It displays field layout options for the visualization that you have selected in the view.

Fields in the fields section

Click a field and it is added to the view. If a visualization is selected in the view, then the new field is added to that visualization. If not, then the new field starts a new visualization in the view.

Or drag a field to the view. You can start a new table or add it to an existing table.

Table names

Expand the table name in the fields section to see the fields in that table.

Click the table name itself, Power View adds the table's default fields to the view. The default fields are defined in the data model that your report is based on.

Row label fields

∑ Aggregates in a table are the fields marked with a Sigma ∑ symbol. An aggregate is a numeric value that indicates the size, quantity, scale, importance, or extent of something. Aggregates are defined in the model your report is based on. You need at least one aggregate to create a chart visualization.

For more information, see Configure table behavior properties for Power View reports.

You can also use a non-aggregate field as an aggregate. For example, if you have a Product Name field, you could add it to a table and then set it to Count. See Change a sum to an average or other aggregate in Power View.

Power View Calculator icon Calculated fields in a table are marked with a calculator symbol. These are sometimes called calculated measures. Each calculated field has its own formula created with DAX. You can't change the aggregate, for example, if it's a sum, it can only be a sum.

Read more about creating visualizations in Power View.

Top of Page

Layout section

Elements in the layout section vary depending on what kind of visualization you have selected in the view. For example, if you have selected a chart, you'll see fields in the Value, Axis, and Series boxes. Here are some actions you can take in the layout section:

Drag fields from the fields section of the field list to the boxes in the layout section. This adds them to the selected visualization in the view.

Delete fields from the layout section; this does not delete them from the model.

Change an aggregate in the layout section. By default, most aggregates are sums. You can permanently change the default aggregation using Power Pivot. Or, you can change the aggregation on a use-by-use basis by clicking the arrow next to the field name in the Values box, and then selecting Average, Minimum, Maximum, or Count.

Change a field that is not an aggregate into an aggregate. For more information, see Change a sum to an average or other aggregate in Power View.

Create a tile container. For example, if you have selected a chart, you can create tiles by dragging a field to the Tile by box. For more information, see Tiles in Power View.

Create multiples by dragging a field to either the vertical or horizontal multiples boxes in the layout section. For more information, see Power View Multiples Visualizations.

Top of Page

View a Power View report in SharePoint in reading and full-screen modes

In reading mode, the report takes up the whole browser. In full-screen mode, the report takes up the whole screen, as the name suggests, similar to a PowerPoint slide show. This is the mode to use when presenting with Power View.

On the Home tab > Reading Mode or Full Screen.

In both modes, the ribbon and field list are hidden, so the visualizations in the view have much more room. The Filters Area retains the state it had in design mode – if it is visible in design mode, then it is visible in reading or full-screen mode, too.

In reading and full-screen modes, move between the views in your report by using the arrow keys or clicking the multi-view icon in the lower left. You can interact with the report visualizations just as you can in design mode.

However, you can't edit the report in reading and full-screen modes – for example, you cannot move, resize, or delete visualizations, or copy, reorder, or delete views.

Press ESC to exit full-screen mode and go back to the mode you were last in, whether design or reading mode.

Top of Page

Overlapping and inset items on top of each other

Items in the view can overlap or completely cover each other. For example, you could inset a smaller chart on a larger one. To enhance the design experience, you can bring objects forward or all the way to the front, or backward or to the back.

Arrange visualization order in Power View

Select a visualization on the design surface.

On the Design tab >

Bring Forward

Bring to Front

Send Backward

Send to Back

Tip: If one visualization is in front of the upper-right corner of another visualization, it may be hard to select the filter or popout icon for the visualization behind. Bring that visualization to the front to filter and then send it back again.

If you add a new visualization when the view is already full, Power View places it on top of existing items in the view.

Top of Page

Expand a visualization

All the visualizations have a 'pop-out' button Power View Popout icon in the upper-right corner. When you click it, the visualization appears to expand to fill the entire Power View view, or if you're in reading or full-screen mode, to fill the entire mode. This is only temporary. The rest of the report is still underneath, and when you click the pop-out icon again, the visualization returns to its spot in the report.

Note: Tiles don't have a pop-out button.

Resize a visualization

Visualizations resize automatically as you add new fields to the visualization. You can also manually resize them.

Select the visualization.
Hover your mouse pointer over one of the sizing handles of the visualization (a sizing handle appears when you hover over the middle of one of the dotted gray lines that surrounds the visualization). The mouse pointer changes to a double-headed arrow .
Drag the double-headed arrow to the size you want for the visualization.

Note: After you manually resize visualization it no longer resizes automatically to accommodate new fields. Instead, scroll bars appear so you can scroll the contents of the visualization. To return to auto-size mode, double-click on of the sizing handles.

Undo

One of the great things about designing in Power View is that you can undo almost anything you do. This frees you up to experiment and learn because you can always just undo it. You can undo multiple steps, in the order in which you did them.

Power View Undo Redo

Note: In Excel, Power View sheets have their own Undo and Redo buttons. They're the large forward and backward arrows on the Power View tab.

"Sorry, we can't show your data because closely related fields are on different axes"

You see this message in Power View if a visualization has the same field in more than one area—for example, in both the Axis and Legend areas.

If you're creating Power View reports in SharePoint (RDLX files) with a SQL Server Analysis Services multidimensional model, you may see this message even with different fields if they are "closely related" (if they share some key fields). This restriction is due to the addition of support for multidimensional models.

Solution 1: Remove the field

Remove the field from all but one of the areas. For example, if you have a column chart with the same field on both Axis and Legend, remove it from Legend, leaving it on Axis only.

Solution 2: Create a duplicate field in the data model

If you have access to the data model for the Power View report, create a copy of that field. For example, say you have a column chart with the field "Country" on both Axis and Legend. In the data model, create a calculated column with the formula "=[Country]", and name it "Country2". Then, in Power View, drag Country to Axis and Country2 to Legend.

Microsoft Office Tutorials

Monday, July 3, 2017