If you’ve dug around the product
and/or the online documentation, you must have encountered the filtering and
segmentation features Prism offers. If not, this is a good time to get acquainted
with this powerful feature here.
The first advantage of a Prism filter
is that it allows you to filter your dimensions to focus only on the members
that interest you, such as your best customers in terms of sales, products with
a positive selling growth, the 20% of customers that account for 80% of your
revenue, etc.
These filters are saved in your repository
(under the corresponding dimension’s Filters node in the data browser) and
always return the members that fit the filter’s definition at any specific
point in time. Requesting results for
the “Best Customers in terms of Sales” today, may not yield the same results
tomorrow because the best customers changed during the night.
So, what can you do with these filters once you create them? This short article will show you how you can use the same filter to achieve multiple business goals, without having to put in any extra work.
1. Calculate Filter Member Values or - "Show
me the total sales for each one of my best customers"
Placing the filter on the Rows (or Columns) panel with one or measures, will calculate the values of the measure(s) for each of the members returned by the filter. The filter returns Customers B, C, H, L and T which are the best 5 customers in terms of sales. The following pivot calculates the Sales measure for each of these customers.
2.
Calculate values in the context of a filter or - "Show me how much
sales my best customers produced in each of my store cities"
Placing the filter on the background panel will present the pivot values for the filter members only. The following pivot will show the total sales just for customers belonging to the filter for each store city. Note that since the filter is placed on the background, it is calculated for all stores, not for a specific store.
3.
Calculate filter for members individually or - "Show me the best customers
for each one of my store cities, and their sales"
The same 'Best 5 Customers' filter we created can be used to calculate the best 5 customers for each city individually. If you place this filter immediately after the Store City dimension, Prism will behave like this automatically. The following pivot shows the best 5 customers in terms of sales for each store city individually. You will notice that this filter returns different customers for each city.
4.
Using a Filter to Scope a Measure or - "Show me the total sales of each
store city's best customers, without showing me the list of customers"
My Excel file has a 'Sales' measure. I would like to create a new Measure that is called 'Sales for Best Customers' that I can use again and again in my reports. If you have yet to explore Prism's custom measures capabilities, now would be a good time to learn.
Prism introduces a concept called 'Measured Values'. In simple terms, they are expressions that include a measure (or custom measure) and a scope in which to calculate. Measured Values contain a single measure and (at most) a single representative from each available dimension. They are defined by enclosing the list of measures and representatives in parentheses while separating each Measured Value element with commas.
The following formula is created by dropping the 'Sales' measure and 'Best 5 Customers (Sales)' filter onto the formula editor, and adding parentheses and a comma. This tells Prism to calculate the Sales measure in the context of the Best 5 Customers filter. We'll call this custom measure 'Sales Of Best Customers'.
Placing this new formula on a pivot with the Store City dimension will yield the total sales of best customers for each store individually. This is because the filter within the formula still works within the context of the current members on the pivot. If you add subtotals to the pivot in section 3 you will notice that the values of this new pivot match the values of the subtotals.
5.
Aggregating a Filter or - "Show me the average sales for each store city's
best customers"
Filters can also be aggregated over a measure using one of the defined aggregation functions - Sum, Avg, Min, Max, Count, DupCount. What aggregation functions do is calculate the value of a measure for each member in the filter and then aggregates all the values according to the specified function.
The following formula calculates the average sales to 'Best 5 Customers (Sales)':
Placing this formula on a pivot with the Store City dimension will yield the average sales to the best 5 customers in each city.
