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”
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.