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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Product
[/TD]
[TD]Sales
[/TD]
[TD]Share
[/TD]
[TD]Share (acc)
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]X0711
[/TD]
[TD]836969
[/TD]
[TD]20.44%
[/TD]
[TD]20.44%
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]X0715
[/TD]
[TD]234871
[/TD]
[TD]12.41%
[/TD]
[TD]32.84%
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]X1281
[/TD]
[TD]211736
[/TD]
[TD]18.18%
[/TD]
[TD]44.03%
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]X1285
[/TD]
[TD]179631
[/TD]
[TD]9.49%
[/TD]
[TD]53.51%
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]X1291
[/TD]
[TD]160026
[/TD]
[TD]8.45%
[/TD]
[TD]61.97%
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]X0712
[/TD]
[TD]98920
[/TD]
[TD]5.22%
[/TD]
[TD]67.19%
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]X1295
[/TD]
[TD]93760
[/TD]
[TD]9.96%
[/TD]
[TD]72.14%
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]X0611
[/TD]
[TD]86884
[/TD]
[TD]4.59%
[/TD]
[TD]76.73%
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]X0713
[/TD]
[TD]80139
[/TD]
[TD]4.23%
[/TD]
[TD]80.97%
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]X0714
[/TD]
[TD]68396
[/TD]
[TD]3.61%
[/TD]
[TD]84.58%
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]X1301
[/TD]
[TD]65348
[/TD]
[TD]3.45%
[/TD]
[TD]88.03%
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]X1806
[/TD]
[TD]63941
[/TD]
[TD]3.38%
[/TD]
[TD]91.41%
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]X1282
[/TD]
[TD]57269
[/TD]
[TD]3.04%
[/TD]
[TD]95.45%
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]X0891
[/TD]
[TD]53715
[/TD]
[TD]2.84%
[/TD]
[TD]97.29%
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]X1283
[/TD]
[TD]51364
[/TD]
[TD]2.71%
[/TD]
[TD]100.00%
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]Total
[/TD]
[TD]1893328
[/TD]
[TD]100.00%
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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