Excel: Power View

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

There are four ways to filter in Power View.

The first method is a slicer. Drag the slicer field to a blank spot on the canvas. A new table element is created. On the Design tab, click the Slicer icon and the table is converted to a slicer. Note that this slicer does not look anything like an Excel slicer.

The second way to filter is to select any column in any chart on the report. All of the other charts are filtered to that category. The other categories appear, but they are greyed out.

The third way is to use the View option in the Filters pane. The View filter goes back to the original data set and filters records out of that data set. For example, if I set up a view filter to only see records with revenue greater than $1000, the report would filter out 80% of the records in the Power Pivot model and recalculate.

Contrast the View filter with the Chart or Table filter. If you ask for items with over $1,000,000 in revenue, the filter will be applied at the aggregate level. Any products that had a total over $1,000,000 will appear in the report.

For more resources for Microsoft Excel