• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Plotting multiple columns on a chart

sparky2205

New Member
Hi folks,
with the attached data I am trying to create a pivot table with slicers and pivot chart(s) that will allow me to filter by Group at the higher level and then by Node at the next level.
I need to be able to plot this on a pivot chart (preferably) or charts (if needed) so that when I filter by group, on the chart I will see the total for all groups plotted by month.
When I filter by node I will see the same except plotted by month for each node.
I have been attempting to achieve this using a pivot table and slicers but I haven't been able to successfully get there.
I'm not great with pivot tables so I'm hoping this is achievable.
By the way it doesn't have to be pivot tables. That's just the way my thinking brought me.
If further clarification is required please let me know.
 

Attachments

sparky2205

New Member
Hi vletm,
humblest apologies for not responding sooner. I got called on to do some other stuff. Thanks for your input above.
I agree a visual representation of what I'm looking for would be ideal. I did try to come up with that before posting. However I found this impossible to achieve without actually producing the outcome I am looking for.
I'll try to make things clearer.
In a simple 2D line chart format on a pivot chart:
• Values on the Y axis
• Months on the X Axis
The ability to switch between the values for the Groups and values for the individual Nodes.
If this needs to be on 2 separate charts that's no problem.

A bit of background:
The data represents the power usage for different types of equipment (Groups).
Within each Group there are numerous individual pieces of equipment (Nodes).
I want to be able to monitor each Group over a period of months which will be added to as the year progresses.
If I see an increasing trend of power usage within a group I want to be able to drill down to the node level to see if the increase is being caused by a particular node.
So, at either the Group or Node level, I want to be able to see a simple line on the chart which displays the trend for the Group or Node.
I hope this is clearer but please let me know if you require further information.
 

sparky2205

New Member
Attached, on Sheet 2, is the kind of thing I'm trying to get to, visually that is.
It's still not correct. It looks like the X axis is still plotting by Group rather than month even though I don't have the option of selecting by Group.
 

Attachments

vletm

Excel Ninja
sparky2205
You're using terms: impossible, simple and hope ( what would be those? )
as well as finally You give visual sample which is not correct.
I modified my layouts to 2D-lines ... those views could f control many ways.
So far, that's what I could offer.
 

Attachments

sparky2205

New Member
You're using terms: impossible, simple and hope ( what would be those? ).
I don't understand what you mean by this. It's not relevant to the question.

as well as finally You give visual sample which is not correct.
I specified in the post that the visual sample wasn't correct. I was trying to give a visual representation, which you had requested, that gave some idea of what I was looking for. It was never going to be fully correct.

The piece that I can't get is plotting the months on the X axis. I need to be able to see the values at group level and at node level plotted by month on the X axis.
Instead of seeing each node or each group on the X axis I need to see January, February, March etc......
I don't know if this is possible with the data structured as it currently is but I don't know how to structure the data differently in order to achieve this.
 

vletm

Excel Ninja
sparky2205
However I found this impossible to achieve without actually producing the outcome I am looking for...
In a simple 2D line chart format on a pivot chart:...
I hope this is clearer...


I modified those as possible 2D line charts which shows something as You've written ... nodes, groups & months
You could do eg manual drawing ... what - if those do not match?
If only mismatch is that months should be in other axis, then You could swap those axes.

Still - many things are possible -
If You should able to explain - what do really would like to get?
 

sparky2205

New Member
I have tried to mock up a visual representation of what I'm looking for.
It's very rough and just contains a few examples. I don't know if you will be able to understand this but I'll try to explain.
The attached graph is just for the Groups but I would need the same for Nodes.
On the mocked up graph:
The Y axis holds the scale for the groups i.e. the sum of the values for each node within each group
The X axis plots the months
The three plots I have put in (freehand) are: Cleanroom AHU, Water Chiller & FFU
These are just drawn roughly to give an idea of what the chart should look like. I haven't drawn it accurately.
Whereever there is a blank in the data this must get represented on the chart as a zero.
 

Attachments

sparky2205

New Member
Hi vletm,
thank you so much, this is what I'm looking for.
Now the hard part, can you explain to me how you got that. I think I mentioned earlier I'm not very good with charts.
In the Values area of the chart you have the months. If I drag the months to this area I get Sum of each month. Are these still the Sum of the months, just renamed? Or is this something else?
How did you get the Values field in the Axis (Categories) area? Is this some form of calculated field?
When I try to recreate your pivot table and chart I just get a long horizontal pivot table rather than your very neat vertical one.
If I can understand how this works then I can try to create the Nodes one myself.
Thanks for your patience. I know I haven't explained myself very well all along.
 

vletm

Excel Ninja
sparky2205
Why do You continue writing negative term? - You are as ... good ... as You're
... and You could learn more!
You could use few seconds and start to learning.
Have You used Pivot-tables ... ever? Do some basic Pivot-table and study?
Have You eg read and done:
Didn't You asked to get sums of months? What is Your point?
Hint: If You do something this-way-or-that-way ... then You should able to verify results other way, especially if You are not 99% sure.
Both axes-values should be numbers.
Do some study ... then You would learn something.
You could even do same Pivot-table Yourself step-by-step and compare it to mine.
If You would get always something ready and after that You'll copy&paste then You'll miss a lot!
 

sparky2205

New Member
Hi vletm,
I am constantly trying to learn new things. I always seek to understand what is posted here, not just use it.
Before I posted here I spent hours trying to do this myself, without success.
Before I wrote yesterday's post I did exactly what you suggested above, I tried to recreate your chart, also without success.
I cannot see how you get the Values column in the pivot table. I have spent a lot of time with this but I cannot reproduce it.
That's why I posted yesterday asking for an explanation.
 

vletm

Excel Ninja
sparky2205
You're are counting ... hours ... I'm counting years.
Did You studied that link? It would be a shortcut.


Q: how you get the Values column in the pivot table.
1) Pivot Table Analyse > Field List activated
2) From FIELD NAME select Groups to Columns
3) From FIELD NAME select all those Jan ... Dec one by one to Values
4) If needed then modify expected Summarise by -option ( I used Sum )
 

sparky2205

New Member
My specific problem was I hadn't seen the Values field appear in the Columns section after I had added the first two fields to the Values section.
Once I noticed this and moved that Value field to the Rows section everything fell into place.

Thanks very much for your help. I'm sure it will be of use not just now but in the future as well.
 

jerry_maguire

New Member
Try this:
  • Open your spreadsheet in Microsoft Excel and list your data into columns. The first column should contain the broad categories and the subsequent columns should contain the subdivided data. As an example, you might list season titles in cells "A1" through "A4" and specific product sales totals for each of these seasons in columns "B" through "E."
  • Click and drag your mouse to select all the data. In the example, you would select cells "A1" through "E4."
  • Click the "Insert" tab, then "Column" from the Charts group and "Cluster Column" from the drop-down menu. The Cluster Column option is the left-most option of each of the column types, such as 2-D, 3-D, or Cylinder. The cluster column chart is automatically created by Excel on the same page as your data.
Regards,
Jerry M.
 
Top