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)
GRR.xlsm
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)
GRR.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
R-R-tool.xlsm