I have a pivot table connected to a power pivot data model. I have products on rows and the products are set up in a hierarchy in the data model. The net result is that I have expand/collapse buttons on rows (see image)
The problem I have got is how to reset the expand collapse buttons back to "All Collapsed". It is easy to get the top level to collapse, but when I expand it again, all the previous expanded levels return to the expanded state. This is not what I want. I want a way to reset the pivot table so that all levels are collapsed back to the fully collapsed level so the user can start drilling into the data again. I assume there is a way to reset this but I can't find it in Excel and I can't find it on the web.
Any ideas?
Help to Reset Pivot Table
-
- Posts: 1150
- Joined: Sun May 04, 2014 4:01 pm
- Location: Sydney, Australia
Help to Reset Pivot Table
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training
Power BI Training
Re: Help to Reset Pivot Table
Hi Matt,
The Rows have what is called the Active Field. Whichever item in the Rows you have selected will be the Active field. Collapse Field works on the active field. If you collapse each field from the bottom one up in turn, I believe you will get the behaviour you require. That is the manual way.
I suspect you would be able to have a macro that will collapse each one in turn up to the parent level. The code looks something like this - which looks like you can add a command for the different levels in your hierarchy. Perhaps someone will have a more elegant way of looping through the levels.
Cheers
Phil
The Rows have what is called the Active Field. Whichever item in the Rows you have selected will be the Active field. Collapse Field works on the active field. If you collapse each field from the bottom one up in turn, I believe you will get the behaviour you require. That is the manual way.
I suspect you would be able to have a macro that will collapse each one in turn up to the parent level. The code looks something like this - which looks like you can add a command for the different levels in your hierarchy. Perhaps someone will have a more elegant way of looping through the levels.
{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[SMTLines].[SMT Layout].[SMTLevel1]").DrilledDown = False
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[SMTLines].[SMT Layout].[SMTLevel2]").DrilledDown = False
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[SMTLines].[SMT Layout].[SMTLevel3]").DrilledDown = False
Cheers
Phil
Re: Help to Reset Pivot Table
I know this is a redundant step, but try it:
Click the PivotChart.
On the Analyze tab, in the Data group, click Clear, and then click Clear All. The Clear All command resets your PivotTable but does not delete it. The data connection, placement of the PivotTable, and PivotTable cache remain the same.
I hope this helps!
Click the PivotChart.
On the Analyze tab, in the Data group, click Clear, and then click Clear All. The Clear All command resets your PivotTable but does not delete it. The data connection, placement of the PivotTable, and PivotTable cache remain the same.
I hope this helps!
-
- Posts: 1
- Joined: Tue Jul 07, 2020 6:16 pm
Re: Help to Reset Pivot Table
[quote="MattAllington"]I have a pivot table connected to a power pivot data model. I have products on rows and the products are set up in a hierarchy in the data model. The net result is that I have expand/collapse buttons on rows (see image)
hierarchy.png
The problem I have got is how to reset the expand collapse buttons back to "All Collapsed". It is easy to get the top level to collapse, but when I expand it again, all the previous expanded levels return to the expanded state. This is not what I want. I want a way to reset the pivot table so that all levels are collapsed back to the fully collapsed level so the user can start drilling into the data again. I assume there is a way to reset this but I can't find it in Excel and I can't find it on the web.
Any ideas?[/quote]
You have in the settings marked "open all"
hierarchy.png
The problem I have got is how to reset the expand collapse buttons back to "All Collapsed". It is easy to get the top level to collapse, but when I expand it again, all the previous expanded levels return to the expanded state. This is not what I want. I want a way to reset the pivot table so that all levels are collapsed back to the fully collapsed level so the user can start drilling into the data again. I assume there is a way to reset this but I can't find it in Excel and I can't find it on the web.
Any ideas?[/quote]
You have in the settings marked "open all"
Re: Help to Reset Pivot Table
I know this is a redundant step, but try it:
Click the PivotChart.
On the Analyze tab, in the Data group, click Clear, and then click Clear All. The Clear All command resets your PivotTable but does not delete it. The data connection, placement of the PivotTable, and PivotTable cache remain the same.
I hope this helps!
It really helped, I didn't expect it.
jokaroom-casino.com is the best choice for gamblers in Australia
Who is online
Users browsing this forum: No registered users and 1 guest