Log In
Register
Home
December 5, 2016
Offline
Hi,
I have a question about how to create a bar / stacked bar chart in a pivot chart. I'm having problems so i'm sure it has something to do with adding a different series or the way I have my data prepared. I'm not a complete beginner at charts, but don't have a lot of experience either.
Basically I want to compare a quantity of an item that was scheduled to be worked on in the 1st Quarter (Oct - Dec) of 2015 as a single solid bar, and compare that to what was actually worked on. I would like to display what was actually worked on as a stacked bar chart (planned / unplanned) compared to the scheduled solid bar.
I am calling it planned if it was scheduled to be worked on and was actually worked on in that QTR, and unplanned if it was not scheduled to be worked on in that QTR but was worked.
I'd like to figure out the best way to display this data in order to use a pivot chart and slicers.
Basically it would be 2 bars for each QTR in the year. One bar for the plan, and one stacked bar for the execution (planned / unplanned)
I tried to upload an image example, but don't have permissions.
Appreciate any help you can offer!
Mike
See Answer
July 16, 2010
Offline
Hi Mike,
I haven't tested as it's tricky without your data, but I don't think you can create this as a combo Pivot chart. Plus Pivot charts are buggy when you make lots of changes to their default format in that they tend to lose then changes on refresh.
You're better off creating a regular chart from the PivotTable as described here: https://www.myonlinetraininghu.....ivottables
Kind regards,
Mynda
VIP
Trusted Members
June 25, 2016
Offline
Hi Mike
To further add to Mynda's reply, the combo chart you wanted (a stacked bar next to a normal bar) will require that the chart's data source be arranged in a special way. Pivot Table will not be able to do that.
Sunny
VIP
Trusted Members
June 25, 2016
Offline
Hi Mike
See if this is what you are looking for.
Although the data is not from a Pivot Table, you will need to ensure that your chart's source data is arranged like this.
Unless you are able to get the Pivot Table to give you this arrangement, I would suggest you use formulas to extract the data and then create a normal chart from it.
Hope this helps.
Sunny
Answers Post
December 5, 2016
Offline
Mynda / SunnyKow,
Thank you for the replies. Thank you for the detailed example SunnyKow! That is exactly what I was trying to do.
I appreciate the help!
Michael
February 17, 2022
Offline
Hello!
After five years, maybe a way has been found? What Mike wants is exactly what I need, too. SunnyKow, your solution works, and I’ve found one that works with filler columns. But neither out of a pivot table. But the chart must come from a pivot table since that is where my data is.
Could Power Query be the answer? But I have no idea!
Have a great day, all.
July 16, 2010
Offline
Hi Steve,
Welcome to our forum! Power Query won't be the answer. Please share a sample file containing a subset of your data including a PivotTable so we can help you further.
Mynda
VIP
Trusted Members
June 25, 2016
Offline
Hi Steve
Frankly I can't recall how I created this chart 5 years ago (sign of old age I guess
)
I was able to recreate the chart via a Pivot Table (using Excel 2019) although it is not that elegant.
I guess it will again depend on your data.
Like Mynda said, do attach a sample of your data and pivot table.
It will help us understand your requirements better.
Hope this helps.
Sunny
February 17, 2022
Offline
Thank you Mynda, thank you Sunny.
I will try to prepare something to clear up what I need.
This will, however take some time because I am not able to work on that all the time and I may have to "translate" from Excel 2016, German version.
Also, I must go to hospital for a few days next month (nothing very serious, I hope).
Have a great weekend!
Steve
July 16, 2010
Offline
Hi Steve,
You shouldn't need to translate your file as Excel will automatically convert formulas to whatever language settings we have. It might be helpful to put your column labels in English so it's easier for us to follow.
All the best for a speedy recovery.
Mynda
February 17, 2022
Offline
Hello Mynda, hello SunnyKow!
I hope I am giving you something you can work with.
In the attachment, “Original Data” is basically what I get from our Software.
I’ve trimmed it down a lot, there are around 100 Objects and more accounts to each object.
Then I would unpivot it and build a pivot table, “PQ unpivoted” and “pivot table” respectively.
“chart” is what I would like to achieve. (OK, it could be improved!) As you can see, there is no connection from “chart” to the rest.
And that is my problem! How can I build a chart like that from that pivot table? And also use slicers to change the object shown in the chart. All costs are of course negative, I’ve multiplied them by -1 so the primary and secondary axis are the same, but is that necessary?
I hope you have enough information. I’ll try to provide should anything be missing.
Thank you for your help!
Steve
February 17, 2022
Offline
Sorry, I'll try again
Where is the start button?
February 17, 2022
Offline
once again, I'm a dunce
July 16, 2010
Offline
Hi Steve,
Thanks for sharing your file and example chart. Unfortunately, Pivot Charts are not flexible enough to enable you to build this chart. If you want your chart connected to a PivotTable so you can use Slicers etc. then you can build a regular chart from a PivotTable.
Hope that points you in the right direction.
Mynda
VIP
Trusted Members
June 25, 2016
Offline
Hi Steve
I managed to create the Pivot Chart and included a Slicer as well (using Excel 2019).
Please note that I have to use a dummy series (using the Profit or Loss values) to create the effect for the gap between the column and stacked chart.
The dummy Profit or Loss column chart color is set to No Fill.
Hope this is what you are looking for.
Regards
Sunny
February 17, 2022
Offline
Hello Sunny!
At first glance, this seems to be exactly what I wanted!
I opened it in Excel 2016 and the selecting the slicers also changed the chart as should be.
Now I just have to figure out just exactly what you did
I guess the dummy series you used replaces the "Filler" columns in my PVT.
Thank you!
I'll keep you posted.
Steve
July 16, 2010
Offline
Sneaky trick, Sunny 😉
The only problem I see is that the primary and secondary vertical axis aren't to the same scale for Riviera school and Franklin school, so the column heights are misleading. e.g. for Riviera school if you take the sum of Jan's labor, material and overheads it comes to 795. Whereas the total revenue is only 740, yet it's column is higher than the costs column.
You'd have to manually set the axis heights, but this wouldn't be ideal for all schools.
Mynda
VIP
Trusted Members
June 25, 2016
Offline
Hi Mynda
The total revenue column (740) is lower than the total overhead (795) on my chart.
Am I missing something?
Sunny
July 16, 2010
Offline
Interesting...when you display both vertical axes the scale for the two series changes so that the 740 column is higher than the 795 column. I turned the axis labels on so I could check they were plotting data on the same scale. I guess they change to share a common scale when only one or no axes are displayed to avoid this issue, but I've never noticed this before. Probably because I typically plot percentages on one axis and whole numbers on another, so having different scales is the primary reason for using the secondary axis. Anyhow, crisis averted, great job.
VIP
Trusted Members
June 25, 2016
Offline
Hi Mynda
You are correct. If one of the axis is deleted it will follow the scale of the other axis (common scale).
In this case I deleted the secondary axis first so it uses the primary axis.
I then deleted the primary axis.
1 Guest(s)
