In this series, I intend to document common questions asked by Power users about Excel connected to SSAS cubes (or data warehouse) after they go beyond the basic stage of understanding & using Row labels, column labels, report filter & values in Pivot Tables. This post is #4 of N:
a. How to Filter Measure Values?
Report Filters are a great way to put constraints on the data that is displayed. So once a user gets the concept of selecting dimensions as report filters & slicers, they might start wondering how to filter measure values? How do they show a list of customers that have ordered less than $100,000 worth of products? They might try to drag a measure to the report filter section but that won’t work & Excel will throw an error. So how can users filter measure values? it’s a feature of Pivot Table called “Value Filters” and that would be helpful here. Here are the steps:
1. Select the Pivot Table.
2. click on a drop down menu besides “row labels”
3. After that, go to Value Filters. You can see that you can filter by applying different rules like Top 10, less than, greater than, etc. Let me demo “Less than or equal to”
4. On the next dialog box, you can select the measure, rule & the filter value:
After you’ve set them up, click on ok and the Pivot Table will filter by measure values.
Note: Be careful when sharing the files that have value filters with other users, let them know that you’ve value filters set on the report as the value filters are not clearly visible if you’re looking at the file for first time.
b. How to move a Pivot Table?
If a user is trying to create a dashboard using excel, they’ll have more than one Pivot Table in a sheet. In that case, they’ll need to move the Pivot Tables around. here are the steps:
1. select the Pivot Table.
2. In the menu bar, go to Pivot table Tools > Move Pivot Table:
3. select your NEW destination and click on OK.
In this post, we saw how to filter measure values & how to move Pivot Tables.
In earlier articles we saw:
#1: How to sort data? How to add slicers? How to change Pivot table Layout
#2: How to add calculated measures?
#3: how to remove grand totals & subtotals and, how to configure the automatic data refresh.