• 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.

How to create clustered column graph with no 0 values

Status
Not open for further replies.

Dianiec

New Member
Hi, I need to create a graph for scrap where you have different areas and scrap reasons, amount and month. I want to see on the graph only reasons which has a value. I have around 50 reasons however we usually using only 5 so I would like graph to show those 5 and when I add next month and let say there will be 6 reasons with value I want them to appear on graph. I am not sure if I explained that correctly. It could be different chart but the most important there needs to be months, area and reason for the area and value for each reason but no 0 value. Any help please?
 
Please find attached. I need chart where horizontal it is by month and in each month by area and reason but only if there is any value above 0.
 

Attachments

  • Book1.xlsx
    11.4 KB · Views: 5
Please find attached. I need chart where horizontal it is by month and in each month by area and reason but only if there is any value above 0.
There is code for each reason but I think that it would be too much to add to the graph
 

Dianiec

If Your datas layout is as You've given then outputs could be something like below:
Screenshot 2024-01-20 at 19.53.32.png
Screenshot 2024-01-20 at 19.53.24.png
You wrote about 50 reasons. How do You add ... choose those?
Do You need monthly chart or how?
You could add there 'any number' of rows and columns.
If You could give more details, then I could offer something different.
 

Attachments

  • Dianiec.xlsb
    28.6 KB · Views: 0
In the attached, Pivot table and chart after transforming data with Power Query. You can adjust how the x-axis appears by dragging the Date/Reason/Area to a different order within the Rows section of the Pivot table & Chart:

1705683525497.png
See attached workbook.
 

Attachments

  • Chandoo55895Book1.xlsx
    29.6 KB · Views: 1
In the attached, Pivot table and chart after transforming data with Power Query. You can adjust how the x-axis appears by dragging the Date/Reason/Area to a different order within the Rows section of the Pivot table & Chart:

View attachment 86240
See attached workbook.
I think this is what I want to look like, the only problem I have is Power Query does not work on my work computer. I wanted to do it in Power Query, but when I do, the window is blank.
 

Yes, there is around 50 reasons, and they are added at the end of the month. I need the chart to be for a year but data to show only for the months I have data. I have added a random number, for example, attached as I was not sure how to show what I meant. What I wanted to create is as you did in the first chart is everything by month and in each month I wanted to have each area (where there is a data above 0) and in each area the reason (if there is data above 0), My biggest problem was to create a graph without showing data where the value is 0
 
In the attached, Pivot table and chart after transforming data with Power Query. You can adjust how the x-axis appears by dragging the Date/Reason/Area to a different order within the Rows section of the Pivot table & Chart:

View attachment 86240
See attached workbook.
Thank you. I work with what you sent, and this is exactly what I wanted. I just need to make power query work in my workplace. thank you :)
 
In the attached, Pivot table and chart after transforming data with Power Query. You can adjust how the x-axis appears by dragging the Date/Reason/Area to a different order within the Rows section of the Pivot table & Chart:

View attachment 86240
See attached workbook.
It is probably silly question to ask but is it possible to have different colour for each area in chart? like all the reasons for each area can have the same colour but each area has different colour assigned?
 
What version of Excel is it at the workplace?

It depends… could you attach the workbook with the Date/Reason/Area arranged in the Rows area of the pivot as you finally want them?
Sorry for long reply. Regarding the spreadsheet I was okay with what I had. Thank you for you help. Now I have different issue but dont know if I have to create new post or I can carry on here. What I am after is to create “add & clear button with macro on my sheet. So I want to add things to to one column then I want this to be added to the next column and clear the column I have put number in the first place
 

Dianiec

As written.
Start a new post every time you ask a question, even if the theme is similar.
in
 
Status
Not open for further replies.
Back
Top