Solved

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

Posted on 2014-03-18
2
337 Views
Last Modified: 2014-03-21
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
0
Comment
Question by:mlagrange
2 Comments
 
LVL 14

Accepted Solution

by:
Zack Barresse earned 500 total points
ID: 39938680
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: http://wessexbi.wordpress.com/2014/03/17/just-one-slice-please/

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.

Regards,
Zack Barresse
0
 

Author Comment

by:mlagrange
ID: 39946145
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
R-R-tool.xlsm
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now