Set Analysis show data even though the filter excludes the item
I am producing a pivot table of 5 "named" items and I want to see it sales progression by month for two years. So I created a pivot table by year, month and item. For the expression, I used set analysis to show only the 5 "named" items for the last two years. When I look at all the data (no filters) everything is perfect. When I filter to a region/territory some products drop off the chart due to no sales data. However, I still want to see all 5 items, just with zero's in the expression. I fixed the months by doing show all values for the month in the dimension, however, if I do show all values for the items, I get all the items instead of the 5 items I have in the set analysis. Any suggestions?
Here's the scenario I have a certain set of products that I always want to show, even if I have no sales. However, if I filter the data by a sales territory, I want the number to change for that territory but still keep all the products. I tried using the identifies but it was giving me the sales for all territories even though I filtered to one of them. I have attached a sample, where product "4" has sales for Territory B but not A. So when you filter for territory A, product "4" drops off the chart.
