What Chart Type can show 3 levels or data sets?

I can't envision your data from the descriptions in this thread, but I imagine you could use one of these chart types:

Clustered-Stacked Column Chart

http://peltiertech.com/WordPress/clustered-stacked-column-charts/

Marimekko Chart

http://peltiertech.com/WordPress/marimekko-charts/

• Jon

Jon Peltier, Microsoft Excel MVP

Peltier Technical Services, Inc.

Peltier Tech Blog

Apolagies if this is poorly worded as I fear I am not using the correct terms to describe this but what I'm trying to find out is if there is a chart that can show more then 2 data sets; more then the standard X & Y axis. 

For example What I have is a count of items that are grouped at 3 levels like this :

Type - Subtype - Category X

And so X is the number of items with the same Type + Subtype + Category value.  The order of the association of these 3 descriptions is hierachical with each Type containing 1 or more Subtypes and each Subtype containing 1 or more Categorys and each item being counted having only 1 category. 

If I were to show this in a spreadsheet I would place the Subtype as colums with the Category as rows and then I set up a table of these (the Subtype + the Category) for each TYPE.  

I have 3 TYPEs then my spreadsheet has 3 tables or grids and each table or grid has the Subtypes listed in columns with the Categories listed as rows and the intersection of the Subtype & Category is a count of the items.

I am trying to show in a chart these 3 levels of data and I'm not sure what Chart type if any can do it. 

Please let me know if I have failed to properly articulate my scenario and what I am trying to chart.

 

Thankks in advance for any help.

You have a couple of choices.  Both require you to reorganize the data.

1. Put the data in a table with the columns: Type, SubType, Category, X.  Now, create a PivotTable and/or PivotChart.  Experiment with the Type, SubType, and Category as Page, Row, Column Fields.
2. Summarize the data so that you have the same layout as 1) except that you enter only unqiue values for each column.  So, Type-1 will be in row 1 and then subsequent rows will have no value in that column until you get to Type-2.  The same applies to Sub-Type. Now, select this entire table and create a column chart.  Excel will create a hierarchical x axis.

---

Tushar Mehta (Technology and Operations Consulting)

www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)

Microsoft MVP Excel 2000-Present

Tushar,

 

I don't believe option 1 will work because, if I understand correctly, the PivotTable can not show all 4 data sets at the same time.  I need a chart that can show all in one glance so that if could be printed out whcih you can;t do with a Pivot Table, at least not and see all the results in one display.  Can option 2 you have suggested do this?

 

Thanks

 

Well, you can either decide it cannot be done or you can try it and see how it actually works! {grin}  Put the Type and Subtype as row fields, the Category as the column field and X as the data field.

Tushar Mehta (Technology and Operations Consulting)

www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)

Microsoft MVP Excel 2000-Present

The arrangement of your data (worksheet or a subtable: Type, columns: Subtypes, rows: Categories, values: Counts) seem to suggest the application of 3-D bar charts; for each type a separate one. Certainly the values of Counts are so independent that any patching into 3-D surface chart would seem improper. Unfortunately, 4-D chart is extra vires of poor Excel, so there is at hand primarily the proposed variant of separate Type charts or, alternatively, grouping of Subtypes into Types (differentiated by colors) in the same chart; it would probably request a rearrangement of data.

 

Regards

 

Petr Bezucha

Petr,

I had a fear that something like this would be the case, that there was no one chart or graph that could display this kind of sceanrio.  I can isualize in miy mind how to do thsi if I were free hand drawing a chart but I don;t know how to do it using a provided set of charts/graphs in Excel or any other application that has charts and graphs like Crystal Reports.  If there were some way to do A piec Chart with slices where each slice could be sub-sliced then I believe one could do something like this.  Obviously for a large number of varied values at each level this would be impratcical but for our scenario each level from Type to Sub-type to Category has but a few unique values and so slices of a slice in a pie would bot be impractical.

Thanks for replying but I'm going to hold off a little longer just in case another reply just happens toc come up with a work-a-round.

Thanks again

Apolagies if this is poorly worded as I fear I am not using the correct terms to describe this but what I'm trying to find out is if there is a chart that can show more then 2 data sets; more then the standard X & Y axis. 

For example What I have is a count of items that are grouped at 3 levels like this :

Type - Subtype - Category X

And so X is the number of items with the same Type + Subtype + Category value.  The order of the association of these 3 descriptions is hierachical with each Type containing 1 or more Subtypes and each Subtype containing 1 or more Categorys and each item being counted having only 1 category. 

If I were to show this in a spreadsheet I would place the Subtype as colums with the Category as rows and then I set up a table of these (the Subtype + the Category) for each TYPE.  

I have 3 TYPEs then my spreadsheet has 3 tables or grids and each table or grid has the Subtypes listed in columns with the Categories listed as rows and the intersection of the Subtype & Category is a count of the items.

I am trying to show in a chart these 3 levels of data and I'm not sure what Chart type if any can do it. 

Please let me know if I have failed to properly articulate my scenario and what I am trying to chart.

 

Thankks in advance for any help.

You have a couple of choices.  Both require you to reorganize the data.

1. Put the data in a table with the columns: Type, SubType, Category, X.  Now, create a PivotTable and/or PivotChart.  Experiment with the Type, SubType, and Category as Page, Row, Column Fields.
2. Summarize the data so that you have the same layout as 1) except that you enter only unqiue values for each column.  So, Type-1 will be in row 1 and then subsequent rows will have no value in that column until you get to Type-2.  The same applies to Sub-Type. Now, select this entire table and create a column chart.  Excel will create a hierarchical x axis.

Tushar Mehta (Technology and Operations Consulting)

www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)

Microsoft MVP Excel 2000-Present

The arrangement of your data (worksheet or a subtable: Type, columns: Subtypes, rows: Categories, values: Counts) seem to suggest the application of 3-D bar charts; for each type a separate one. Certainly the values of Counts are so independent that any patching into 3-D surface chart would seem improper. Unfortunately, 4-D chart is extra vires of poor Excel, so there is at hand primarily the proposed variant of separate Type charts or, alternatively, grouping of Subtypes into Types (differentiated by colors) in the same chart; it would probably request a rearrangement of data.

Regards

Petr Bezucha