Pareto Chart

Hi,

I have created a Pareto chart (see attached file).

Now I need to include the Country in the chart. So what (I think) I'm looking for is a Stacked column chart which shows one column for each SalesPerson, which will be stacked showing the breakdown per country. I still need to have the Cumulative % displayed as in the attached file as a Line chart.

Any ideas on how this can be done?

Any ideas? Anyone!?

Thanks, MrShorty.

I'd really prefer the Pivot Table to be able to handle this but it's not looking like it can be done.

I'll have a play around with your suggestion and see if I can get somewhere...

I don't understand how you're including the country data.

Like, your sales people are all either in one country or the other; they're not posting sales in both.

More importantly, since Pareto charts require that the horizontal axis be sorted according to their value, it's not obvious to me how you can visualize the US and UK sales as part of the same chart.

I don't really understand what OP is asking for.

Originally Posted by ben_hensel

I don't understand how you're including the country data.

Like, your sales people are all either in one country or the other; they're not posting sales in both.

More importantly, since Pareto charts require that the horizontal axis be sorted according to their value, it's not obvious to me how you can visualize the US and UK sales as part of the same chart.

I don't really understand what OP is asking for.

Hi Ben,

Thanks for your reply.

The data I used for the sample is not the data I'm actually working on. So maybe I didn't choose the best example. I've amended it to make it more like what I'm dealing with. Sorry for the confusion.

What I'm essentially asking is: Can a Stacked column chart, that is based on Pivot Table data, be used in a Pareto chart? If you look at the revised example I've attached, I'd like one line chart to get it's values from the Total Cumulative % column G but the nearest I can get is the two lines I've inserted which obviously this isn't what I'm looking for...

Originally Posted by romperstomper

The problem is that pivot charts never plot the Grand Totals. That means that you with a regular pivot table you'd have to create a new calculated item that adds UK and US together - but you can't do that because you're using the same field twice in the data area.

If you have PowerPivot, you can create a new measure that shows the total orders for all countries, but it will be shown in both UK and US fields (you can hide one using Sets but the legend will still show the country name in the chart).

You could alternatively create two calculated columns (either in PP or in your source data) that calculate the UK and US orders for every row, and then simply use these fields in the pivot (see attached). That however assumes your real data only has two countries (or a few at most).

Thank you SO much. The last option you mentioned will work perfectly for me. There will only be 3 possible 'countries' so adding those new columns to the source data won't be a problem. I had tried to use a Calculated Item but as you say, I ran into the wall you described above. Never thought of amending the source data!

Thanks again!