-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Slicer to Control Axis Fields (Categories) or Legend Fields (Series)?
- Thread starter joeshu26
- Start date
Have a look here: 5 Interactive Chart Techniques Come Together « PowerPivotPro
Upvote
0
Thanks Imke, I was trying to reference that blog post for what I am trying to do but wasn't able to. Basically I would like a slicer that changes the Axis Fields (Categories) section of the PowerPivot Field List.
So I might have Region in the Axis Fields (Categories) section and my PivotTable or Chart will have data displayed by region. I would Like to have a slicer that lets you choose Region, Sales Rep, or Country, etc. and then the PivotTable or Chart will update to reflect the selection.
Thoughts?
So I might have Region in the Axis Fields (Categories) section and my PivotTable or Chart will have data displayed by region. I would Like to have a slicer that lets you choose Region, Sales Rep, or Country, etc. and then the PivotTable or Chart will update to reflect the selection.
Thoughts?
Upvote
0
Is my underständig right?:
You want the slicer to trigger a change in the field, that sits in the row section (Pivot table) ie Axis Fields (Categories) for Pivot Charts. This is different of what slicers are normally used (only filtering on existing fields).
What has been described in the above mentioned article is how to change the value-fields instead. So I can see quite some familiarites here (although the solution is more complex for his case).
Please confirm if my understanding of your need is correct - then we can move on
You want the slicer to trigger a change in the field, that sits in the row section (Pivot table) ie Axis Fields (Categories) for Pivot Charts. This is different of what slicers are normally used (only filtering on existing fields).
What has been described in the above mentioned article is how to change the value-fields instead. So I can see quite some familiarites here (although the solution is more complex for his case).
Please confirm if my understanding of your need is correct - then we can move on
Upvote
0
Yes, you have effectively described what I was attempting to describe.
"You want the slicer to trigger a change in the field, that sits in the row section (Pivot table) ie Axis Fields (Categories) for Pivot Charts" is exactly what I'm looking for.
And I would like to apply the same logic to the change the field that sits in the Legends Fields (series) as well, if possible!
"You want the slicer to trigger a change in the field, that sits in the row section (Pivot table) ie Axis Fields (Categories) for Pivot Charts" is exactly what I'm looking for.
And I would like to apply the same logic to the change the field that sits in the Legends Fields (series) as well, if possible!
Upvote
0
Great.
You seem to have some experience with cubeformulae - I make it short. Just say if you need any detailled explanation on any of it:
1) You're going to create a table as a source for your charts that is made of Cubeformulae.
2) The row and columns headings will be: CUBERANKEDMEMBER(..Cubeset, 1..n)
3) The Cubeset in there will dynamically be filled with the desired attributes by your slicer selections. Therefore you create a table (for each slicer) that contains all the names of the attributes that shall be displayed. You "harvest" the selction of the like you've learned in your previous post
4) Then you pass that value on to the Cubeset, use ["&SlicerSelection&"]. This way your colums will show the different attributes that have been selected in your slicer.
5) Frame your Cuberankedmember-formulae in an IFERROR, to show blanks because your selection will most likely bring back different amounts of attributes (no of rows and columns)
Easiest way to achieve this is to create a Pivot from scratch, copy it and convert it to cubeformulae. There you have all the syntax that just needs to be replaced.
Here's some other usefull links on that Topic:
Pulling back the curtain: Intro to Cube Formulas « PowerPivotPro
Better Way to “Catch” Multiple Slicer Selections in a Formula «
hth, Imke
You seem to have some experience with cubeformulae - I make it short. Just say if you need any detailled explanation on any of it:
1) You're going to create a table as a source for your charts that is made of Cubeformulae.
2) The row and columns headings will be: CUBERANKEDMEMBER(..Cubeset, 1..n)
3) The Cubeset in there will dynamically be filled with the desired attributes by your slicer selections. Therefore you create a table (for each slicer) that contains all the names of the attributes that shall be displayed. You "harvest" the selction of the like you've learned in your previous post
4) Then you pass that value on to the Cubeset, use ["&SlicerSelection&"]. This way your colums will show the different attributes that have been selected in your slicer.
5) Frame your Cuberankedmember-formulae in an IFERROR, to show blanks because your selection will most likely bring back different amounts of attributes (no of rows and columns)
Easiest way to achieve this is to create a Pivot from scratch, copy it and convert it to cubeformulae. There you have all the syntax that just needs to be replaced.
Here's some other usefull links on that Topic:
Pulling back the curtain: Intro to Cube Formulas « PowerPivotPro
Better Way to “Catch” Multiple Slicer Selections in a Formula «
hth, Imke
Upvote
0
Thought this was worth a blogpost (sorry, our site is in German, but the online translations aren't too bad, I think): Dynamische Charts in Excel | Blog | sqlXpert GmbH
There you can also find a file with an example which is quite simple and might speak for itself
Imke
There you can also find a file with an example which is quite simple and might speak for itself
Imke
Upvote
0
Similar threads
- Question
- Question
