Simple Row Toggle/Slicer for Pivot Table using PowerPivot

dmarq

New Member
Joined
Jul 28, 2006
Messages
7
All,

I tend to prefer lurking over posting questions, I do enjoy the challenge of finding answers, however this one has me stumped and can use the help.

Situation:

I have two tables that are joined by a single common key, in which one table has a Hierarchy of [Collection Leader], [Team Leader], and [Collector] in side by side hierarchical columns. The other table is transactional information, that is linked based on the [Collector]'s ID.

Since this report will be used by many people in different levels within my business, I have been asked to put in a 'toggle' (can be a slicer or Option Radial Button(s)) in which a single Row dimension is controlled and gives the viewer the option to see the pivot table by either [Collection Leader], [Team Leader], or [Collector] columns, with the default at Collection Leader and only one option can be chosen.

I also need to use PowerPivot, (which is still a little new for me) based on anticipated size of data and some of the advanced formulas such as using Percentiles.

Any links to articles or hints on how to approach greatly appreciated.

Thanks in advance!

Dmarq
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Was waiting to see if anyone else would reply, as I don't have a definitive solution.

Sounds like what you want is a slicer that determines which column is used in the rows / columns part of a visualisation, rather than the values part. To my understanding this is not directly possible in Power BI. My attempted solution to this issue would depend on whether you have access to bi-directional filters. Assuming you don't, as you mention Power Pivot, I would do the following:

1. Convert all of the data into one table via a merge (or join)
2. Create 3 copies of the table
3. In first table, delete Team Leader and Collector columns, rename Collection Leader to Slicer. Add a new column called Hierarchy that just says Collection Leader for every item.
4. Repeat step 3 on the other two tables, but for the Team Leader and Collector columns
5. Append the 3 tables. You now have one big table that has all the data in their 3 times, with Slicer column and Hierarchy column

At this point the hierarchy column has three options in it and if you filter by one then, say Collection Leader, you will only have the rows of the table where Slicer is the Collection Leaders.

Using this approach you will need to be careful with your measures, perhaps using HASONEVALUE ( Table[Hierarchy] ), so you don't inadvertently return 3x the value you would expect to.

Gary
 
Upvote 0
Gary,

I did some more research .. and found that you can use VBA and a lookup table to change the row, column, or values within a PowerPivot Pivot table.

I found a few articles that helped me figure out :: https://sites.google.com/site/e90e5...lue-fields-for-your-pivot-table-or-powerpivot

Although this describes on how to change the measures within the Values group, I did something similar and created the following VBA to change the xlRowField, based on my original scenario using the above linked web article as a guide:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Selection")) Is Nothing Then

Dim Pvt As PivotTable
Dim Cf As CubeField
Dim Sh As Worksheet

Set Sh = ActiveSheet
Set Pvt = Sh.PivotTables("PivotTable1")

If Range("Selection") = Range("Hierarchy.Type") Then
Exit Sub
Else
For Each Cf In Pvt.CubeFields
Select Case Range("Selection.Number")
Case 1
'first need to remove the current xlRowField before adding the new one
Sh.PivotTables("PivotTable1").CubeFields("[dimHierarchy].[TEAM LEADER]").Orientation = xlHidden
Sh.PivotTables("PivotTable1").CubeFields("[dimHierarchy].[COLLECTIONS LEADER]").Orientation = xlHidden

'then add the appropriate row field ** Realize that there is a difference between CubeField and CubeFields
With Sh.PivotTables("PivotTable1").CubeFields( _
"[dimHierarchy].[COLLECTOR]")
.Orientation = xlRowField
.Position = 1
End With


Case 2
'first need to remove the current xlRowField before adding the new one
Sh.PivotTables("PivotTable1").CubeFields("[dimHierarchy].[COLLECTOR]").Orientation = xlHidden
Sh.PivotTables("PivotTable1").CubeFields("[dimHierarchy].[COLLECTIONS LEADER]").Orientation = xlHidden

'then add the appropriate row field
With Sh.PivotTables("PivotTable1").CubeFields( _
"[dimHierarchy].[TEAM LEADER]")
.Orientation = xlRowField
.Position = 1
End With


Case 3
'first need to remove the current xlRowField before adding the new one
Sh.PivotTables("PivotTable1").CubeFields("[dimHierarchy].[COLLECTOR]").Orientation = xlHidden
Sh.PivotTables("PivotTable1").CubeFields("[dimHierarchy].[TEAM LEADER]").Orientation = xlHidden


'then add the appropriate row field
With Sh.PivotTables("PivotTable1").CubeFields( _
"[dimHierarchy].[COLLECTIONS LEADER]")
.Orientation = xlRowField
.Position = 1
End With


End Select
Next Cf
End If 'For Case
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top