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)