we are trying to find a solution for the following problem statement using hierarchies in Excel Pivot Tables. I hope the excel community can help with solutions or suggestions.
Problem Statement:
Challenge arise while using multiple hierarchies in Pivot Tables from PBI Semantic Model. Please consider below scenario for complete understanding.
Scenario:
Following are the two dimensions with the hierarchies for both.
- GL Account
- Cost Center
Expectation:
Once the GL Account Hierarchy & Cost Center Hierarchy are dragged and dropped into the rows section of the pivot table fields, GL Account and Cost Center are nested within the same column A. Please refer to below screenshot where it puts both of them in one column.
enter image description here
We might switch to the tabular option, but that doesn’t completely fix the problem because it adds more columns based on the levels of the leaf nodes. It becomes more difficult to use the spreadsheet for analysis with so many columns. In fact, there are certain cases where Cost Centers have 7 hierarchy nodes.
The ideal solution is shown in the screenshot below, coming from another reporting tool:
enter image description here
Do you think a similar layout can be developed in a Excel Pivot table? Where separate hierarchies unfold in independently in separate columns?
Please if you need clarifications or extra info to understand better, feel free to post and I will update.
Thanks in advance.
Marcello Canetto is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.