One of my favorite features in PowerPivot are slicers and sometimes there is a need to have more than one slicer of the same kind. I think best example is a comparison of a measure for 2 different periods and you want to select both of the periods that you want to compare.
In a pivot this seems to be difficult. At least I could not find an easy solution to do that . But CUBE Formulas have a big advantage vs. a pivot. You can link each formula to a different slicer !
Here is how it could look like
I know colors have potential for improvement but it should show what slicer is linked to what part of the table and chart.
With the blue slicer you can change what you see in the left bar with the purple slicer the right side, green works for both.
To build this report is very easy.
1) Start with a Pivot that includes the measures and whatever you want to show in rows (in this case Headcount/OPEX/CAPEX).
2) Add all the slicer (Period Type / Year /Quarter / Region /Location).
3) Convert the Pivot in CUBE Formulas and you have the left part of the table.
4) Copy the CUBEVALUE formulas to the right to get the right part of the table. At this point in time it shows the same data as the left part.
5) Now create a new pivot, no need for measures- just add the slicer you need twice (Period Type / Year /Quarter).
6) Copy the slicer on the same tab with the first part of your table. If you check the slicer properties you can see that all slicer from second pivot have a number at the end like Period_Type1.
7) Now go into the CUBEVALUE Formulas on the right part of the table and change the link to the slicer from Period_Type to Period_Type1 and the same for Year and Quarter.
8) If you want a header next to a table or chart that includes the info what has been selected use CUBERANKEDMEMBER.
9) Add the Excel stuff like Delta % and charts
Hope this is useful
In a pivot this seems to be difficult. At least I could not find an easy solution to do that . But CUBE Formulas have a big advantage vs. a pivot. You can link each formula to a different slicer !
Here is how it could look like
I know colors have potential for improvement but it should show what slicer is linked to what part of the table and chart.
With the blue slicer you can change what you see in the left bar with the purple slicer the right side, green works for both.
To build this report is very easy.
1) Start with a Pivot that includes the measures and whatever you want to show in rows (in this case Headcount/OPEX/CAPEX).
2) Add all the slicer (Period Type / Year /Quarter / Region /Location).
3) Convert the Pivot in CUBE Formulas and you have the left part of the table.
4) Copy the CUBEVALUE formulas to the right to get the right part of the table. At this point in time it shows the same data as the left part.
5) Now create a new pivot, no need for measures- just add the slicer you need twice (Period Type / Year /Quarter).
6) Copy the slicer on the same tab with the first part of your table. If you check the slicer properties you can see that all slicer from second pivot have a number at the end like Period_Type1.
7) Now go into the CUBEVALUE Formulas on the right part of the table and change the link to the slicer from Period_Type to Period_Type1 and the same for Year and Quarter.
8) If you want a header next to a table or chart that includes the info what has been selected use CUBERANKEDMEMBER.
9) Add the Excel stuff like Delta % and charts
Hope this is useful
