Pivot Summary based on base sheet hierarchy

Jared Davis

I have a running list of rows (touch points) that have a status associated with them. I want to make some charts that aggregate the responses under the hierarchy. For example, The top level hierarchy is "Quality". The child is "Mock-ups". There are 4 questions related to mock ups, each having a status. I want to chart status for Quality, and mock ups (under quality). When I run a pivot for the status, to get the lines into columns, I lose the hierarchy of the data. I want users to be able to add rows and additional hierarchy and have that new info populate into the pivot sheet and subsequent chart.

Jared Davis

I think I figured it out. Let me know if you have any ideas to simplify the workflow below. Again, the idea is to have a checklist that a project team can add items to, and that new content is populated to a chart under that parent hierarchy:

Step 1: I set up Division and Type Columns. The "Division" column is the Level 1 Hierarchy. The "type" is the Parent Hierarchy of the individual item. (note: the formulas below are based on an added "A" and "C" column that helps me run auto formatting like THIS)

Step 2: Set up a pivot based on the Type Column

Step 3: I pulled the Division Column into the Pivot sheet using INDEX/MATCH. The issue here is if there is if the hierarchy is named the same I will have issues... but what can you do...

Step 4: I then ran a report and filtered for the "Division" that I want to show in my chart.

Step 5: Used that Report to generate my Chart

Here are two examples of how this will work now:

Example 1: An item is added under the "Design Phase" Type, under the "Quality" Division. The stats for the new item will be added to the Design phase bar in the above chart.

Example 2: I add new Hierarchy under the Quality Division. This will add a new bar to the above chart called "New Section" All Automatically!!!

The only outstanding issue is that I am only grabbing the highest hierarchy in the "Division" and the lowest Hierarchy in the "Type". In other words the chart is not reflecting the fact that the stats are from the "Quality Kick-Off" Process. But I am good with the way it is.

Genevieve P.

Hi @Jared Davis

It sounds like you just need a way to bring the Parent row details down into the Child rows so you can use this data in your Pivot, or in a Report with Summary, etc.

Try something like this:

=IF(COUNT(ANCESTORS([Primary Column]@row)) > 0, PARENT([Primary Column]@row), [Primary Column]@row)

It will create a helper column like this:

That way your Mockups will be associated with the appropriate Parent title. Does that make sense?

Let me know if this works for you!

Cheers,

Genevieve

Jared Davis

@Genevieve P. So good. Thank you! I was messing around with this previously, but have a few more questions.

1. I have mulitple layers of hierarchy. I originally used the =PARENT([Primary Column]@row) but the pivot returns a bunch of weird blanks...
2. Lets say I get the pivot to work, and then want to make my chart showing the progress for mockups. I would make a report filtering for only quality and make the chart reference only the Mock ups lines. Correct?
3. If that works, the issue then becomes what happens when a project team adds a line under mock ups? I want that content to automatically be added to the mock up chart.

Again, super helpful, I appreciate your work!

Genevieve P.

Hi @Jared Davis

1. Your top-level won't have a PARENT so then those cells will be blank with that specific formula, does that make sense?
2. Yes, I think so? Would you be able to provide a screen capture of your source sheet set-up (blocking out sensitive data), and perhaps a mock couple of data points to show what the desired output would be? There may be a way to gather this information without a Pivot, just using formulas and a Report.
3. A Report would auto-add rows, depending on how we filter/set it up. Again, it would be really helpful to see your set-up if you have multiple hierarchies to try and picture how this would work.

Thanks!

Genevieve

Jared Davis

I think I figured it out. Let me know if you have any ideas to simplify the workflow below. Again, the idea is to have a checklist that a project team can add items to, and that new content is populated to a chart under that parent hierarchy:

Step 1: I set up Division and Type Columns. The "Division" column is the Level 1 Hierarchy. The "type" is the Parent Hierarchy of the individual item. (note: the formulas below are based on an added "A" and "C" column that helps me run auto formatting like THIS)

Step 2: Set up a pivot based on the Type Column

Step 3: I pulled the Division Column into the Pivot sheet using INDEX/MATCH. The issue here is if there is if the hierarchy is named the same I will have issues... but what can you do...

Step 4: I then ran a report and filtered for the "Division" that I want to show in my chart.

Step 5: Used that Report to generate my Chart

Here are two examples of how this will work now:

Example 1: An item is added under the "Design Phase" Type, under the "Quality" Division. The stats for the new item will be added to the Design phase bar in the above chart.

Example 2: I add new Hierarchy under the Quality Division. This will add a new bar to the above chart called "New Section" All Automatically!!!

The only outstanding issue is that I am only grabbing the highest hierarchy in the "Division" and the lowest Hierarchy in the "Type". In other words the chart is not reflecting the fact that the stats are from the "Quality Kick-Off" Process. But I am good with the way it is.

Genevieve P.

Wow, Thank you so much for your detailed explanation @Jared Davis!

I can see that there are quite a few steps here to get to your end goal, but actually, I think this is great! I'm so glad this is all automated for you and the end chart looks great.

Thanks again for following up!