How to include an additional element in a chart data series?

Hello - I have had to develop a data input workbook for employee rewards for a particular division of a large, far-flung corporation (example attached).

Each employee reward (one per row on the "Input Sheet" tab) carries the company division ("L0", far right columns), and then the organization sub groups down to 3 levels ("L1", "L2", "L3")

As each level is selected from the data validation lists, I get the number of employees from a conglomeration of pivot tables going against a data table of headcounts ("Org Levels" tab) and calculate the percentage each award represents for the number of employees at that given level ("L1emps", "L1pct"; "L2emps", "L2pct"; "L3emps", "L3pct")

Then I have to provide a bar chart for each level that shows the percentages by the group. You can see how they progress; the L1 chart filters by the quarter; the L2 chart filters by the quarter and L1; the L3 chart filters by the quarter, L1 and L2.

If that were the end of it, it would be great. But the client also wants each chart to show the total percentage of the parent group, so the managers can filter to their group, and see not only the percentage participation of each group that reports to them, but the participation of their group as a whole.

For example, on the "L1 Chart" tab, the bar chart should display the 6 "L1pct" values's, but also include the total percentages for L0, "L0pct". The L2 Chart should display the L2 groups for the L1 selected in the filter, AND the L1pct total.

How can I get the "parent" pct into the chart for the "child" pct's?

Thanks very much

(The 2nd series on the L1 Chart is another client requirement that, when filtering by the quarter, a red line is displayed for 5%, and when no quarter filter is selected (year-to-date), the red line appears at 20%. That's a whole separate issue; I don't think it affects the main issue here)
Who is Participating?
Zack BarresseConnect With a Mentor CEOCommented:
Hi there,

This looks like a prime suspect for Power Pivot. Not sure if you've thought about it, or if it's even doable in your situation.

As for the VBA on 'L1 Chart' sheet, you don't really need it. You can use a cell with a formula and just point the chart title to it. Using any blank cell, like F1, enter this formula...
="Award Percentages for "&IF(B1="(All)",YEAR(TODAY()),IF(B1="(Multiple Items)","multiple quarters in "&YEAR(TODAY()),CHOOSE(--RIGHT(B1,1),"1st Quarter","2nd Quarter","3rd Quarter","4th Quarter")&" "&YEAR(TODAY())))

Open in new window

Then select the chart title, put the cursor in the formula bar (enter edit mode) and type "=F1" (w/o quotes obviously) and press Enter. Now it will update accordingly without the need for the PivotTableUpdate event. Besides, no need to select the chart anyway.

As far as getting the 'L0 pct' field on the chart in 'L1 Chart' chart object, wouldn't you just add the "Lo pct' field into the Values area of the PivotTable? You can make it a secondary axis, or even change the series type to a line and have it overlay your data.

Is there a reason you're not using slicers here? I assume it's to only allow a single selection on a filter only, because select multiple items is not checked. There is a fairly easy workaround for allowing a single slicer selection only, which Bob Phillips shows on his blog:

Your 5%/20% rule should easily be accomplished with a helper column in your data source, looking at what has been selected as the filter and returning an appropriate value afterwards, then using that field in your PivotChart.

And as far as parent percentages go, if you don't go the Power Pivot route, I'd be looking at a calculated field perhaps. It would help here if you gave an example. I think I understand but I'm not completely sure. Let me see if I have this right...

L0 Chart
Has no parents

L1 Chart
Has parent but no parent percent

L2 Chart
Has parent of L1, parent percent should be 'L1pct' field

L3 Chart
Has parent of L2, parent percent should be 'L2pct' field

Is that right? And if the PivotTables will be filtered for their respective parent, why can't you just add the parent percent field to the PivotTable/Chart? It would be filtered for whatever value you filter for anyway. Perhaps an idea of what you expect as your final output would help.

Zack Barresse
mlagrangeAuthor Commented:
Hello, Zack - thanks very much for your responses. I am sorry to be so late in getting back here, but I had to demo this thing yesterday morning, and I was thrashing right up until the end.

Power Pivot - this is in wide use here among the spreadsheet intelligencia, but I don't know anything about it. I have wasted so much time on various techniques just in Excel, that turned out to have some fatal flaw for the requirement of the moment, that I didn't want to take/risk the time on a whole new tool.

The formula for the chart title - your solution is VERY slick! Unfortunately, this workbook now has 8 charts, some with 4 filters, that should be phrased appropriately for the filtering. They needed to be consistent, the client has already changed her mind twice about how they should be arranged (subject/time period/Lx Name, or subject/Lx Name/time period, or... you get it). I am very proud of myself for anticipating that, and I have since added a single routine to pass back the title in the style of the moment, called from the Worksheet_PivotTableUpdate event.

Adding the L0 pct field - they want to see weighted percentages by the populations of the groups; ie: the sum of the L0 pct's right beside the sums of the pct's for each L1 groups. I tried a calculated item of AVERAGE(L1Apct, L1Bpct, L1Cpct...), but that was not the "true weighted average" (THEY said... I'm no math guy; it was 19.54, vs. 19.14, holy crap...). And it wouldn't have worked for all the specific L1 to L2's, or L2 to L3's.

I eventually remembered an old programming joke: when the programming gets too complicated, rearrange the data! I just made three alternate data sources (see L1Table, L2Table, L3Table)

Slicers - I should probably look at that. But I'm so sick of M$ and their "new features". I'm still pissed off about the Ribbon Bar...

5%/20% line - that's what another Expert suggested as well; works like a charm.

Thanks again
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.