Dynamic rows controlled by slicers - to be possible or not to be possible?

[Pivvv]

Below you can see 2 very basic Power Pivot tables:
The first table is a disconnected table with 1 column "Category" wich contains only 2 values:

• Products
• Subproducts

This column is used as a slicer to filter on "products" or "subproducts".

The other table is a very basic Salestable which contains the sales by products and subproducts.



Here's what I can't achieve:

In the screenshot below I select "Products" in the slicer. What happens is that the pivottable shows sales by Product:

Now I select "Subproducts" in the slicer and what I want to happen is this:
The rows have to show sales by subproducts instead of product.
Obviously, what I did here is changing the rowlabel from "Product" to "Subproduct" but the real question is this:
Is it possible to create a rowlabel (calculated column) that changes the values of that column by the selection of the slicer?
I want the rows to show OR the products OR the subproducts, based on the slicerselection.

I started out with creating a measure to catch the slicer selection but even after thinking and trying for several hours, I still didn't make any progress from there. Is it simply impossible or is there a way out?

 

[scottsen]

Hmmm....

It feels vaguely possible (you could certainly have a measure that returned the text associated with product/sub-product description, based on the slice selection), but... I don't get what you would put "On Rows" of your pivot.

I really think you might have a better time going with Cube Formulas here?

 

[Tianbas]

I do not really see the use case for that but if you really need it this way you could try the following. Create 2 Sum of Sales measures, one that ignores the subtotals and one that only shows the subtotals and than use a 3rd measure to switch between those measures depending on the slicer selcetion. In rows you would need products and subproducts


formula could be something like
sumproducts =if(countrows(values(Table1[Subproduct]))>1,sum(Table1[Sales]),blank())
sumsubproducts = if(countrows(values(Table1[Subproduct]))=1,sum(Table1[Sales]),blank())

 

[Tianbas]

you should check the latest post on powerpivotpro.com. This might be the solution you are looking for.

Dynamic Charting In Power Pivot « PowerPivotPro

 

[Pivvv]

Thank you all so much for the replies.
At this moment I've used the technique last mentioned. This is realy amazing! I'm hoping to learn much more of this.