Charting Analysis

[MartinL]

Hi charting friends
I am trying to create a dynamic chart(s) that will allow me to select a percentage of total sales, either in a cell or something 60%, 70%, 80%, 90% or intermediate values that will produce a stacked bar chart.

So below I have a pivot table
product and sales are from the source
Share is % of Total column (sales)

I have no idea how to get Share(Accum) by pivot but its a running total of share and i have just used
=SUM($C$2:C2) copied down


I can create a chart from this but I am having problems in being able to put in a percentage figure and just getting back all products that when combined in sales order, make up this figure.

ie enter 60% will return
X0711
X0715
X1281
X1285

I would really like to show a row of stacked bar graphs in a graph at 10% intervals with a smoothed line running over the top, but this is work in progress, so if anyone has done anything like this I am open to ideas.


Basically these 4 products make up 60% of the sales. Actually 53.51%, so maybe I should use rounding somewhere

N.B. My total list of products is around 400 so I need to be careful

	A	B	C	D
1	Product	Sales	Share	Share (acc)
2	X0711	836969	20.44%	20.44%
3	X0715	234871	12.41%	32.84%
4	X1281	211736	18.18%	44.03%
5	X1285	179631	9.49%	53.51%
6	X1291	160026	8.45%	61.97%
7	X0712	98920	5.22%	67.19%
8	X1295	93760	9.96%	72.14%
9	X0611	86884	4.59%	76.73%
10	X0713	80139	4.23%	80.97%
11	X0714	68396	3.61%	84.58%
12	X1301	65348	3.45%	88.03%
13	X1806	63941	3.38%	91.41%
14	X1282	57269	3.04%	95.45%
15	X0891	53715	2.84%	97.29%
16	X1283	51364	2.71%	100.00%
17	Total	1893328	100.00%

<tbody>
</tbody>

Thanks

Martin

 

[Andrew Poulsom]

Can't you use the pivot table's Top 10 feature?

 

[Chris Mack]

The way I've done this before is by defining a dynamic range using the name manager.

I created a chart based on your data, and had 3 colours on the graph for "Sales", "Share" and "Share (acc)".

As an example, the data ("series values") for "Sales" was defined by "Sheet1!$B$2:$B$16".

I changed this to "Sheet1!Sales".

This was after having defined the name "Sales" in the name manager as:

=OFFSET(Sheet1!B2,0,0,COUNTIF(Sheet1!D2:D16,"<="&0.6),1)

So the range starts at B2, and the height is defined as the number of values in D2:D16 that are less than or equal to 60%.

Once I'd done this for the other two ranges, the chart adjusted itself accordingly, showing me only four columns instead of fifteen.

Hope this helps,

Chris.

 

[MartinL]

Can't you use the pivot table's Top 10 feature?

Hi Andrew

I probably could, but how can I get the accumulative figure to work in a pivot table?

Martin

***Suddenly hit me how to do it and the pivot now works, now I just feel stupid, but thanks