Log In
Register
Home
January 14, 2019
Offline
I'd like to show the data label for JUST the max value on a stacked column chart. The example that I've attached is just for three months, for ease of manipulation. Ideally, the finished chart would show 12 months of data.
See Answer
VIP
Trusted Members
June 25, 2016
Offline
Hi Andera
From your screenshot it looks like the chart came from a PivotTable and this can complicate matters.
Furthermore I don't know if the Month is a true date or just plain text.
I have added a couple of helper columns and dummy data.
See if my attachment helps (never done this before).
You may need to adjust the axis scale as well as the sorting order if more months are added.
You can refer here https://www.myonlinetraininghu.....in-and-max for more ideas although it is for normal charts (not Pivot Charts).
Good luck
Sunny
January 14, 2019
Offline
I apologize, as I didn't explain myself entirely, nor did I supply an example workbook (thank you for going to the trouble of recreating data for your solution).
See attached file.
I'm hoping to get the data label to display, directly on the associated segment of the stack, for whatever month in the stack is the highest value (I did it by hand for the purposes of my example in the attachment).
I know that Pivot charts can be challenging, so I was working with data that was pasted as values.
VIP
Trusted Members
June 25, 2016
Offline
Hi Andrea
Creating interactive custom labels is challenging enough and using a stacked bar chart makes it worse. 
Since I was unable to find any ready-made solution, I created my own instead.
It involves some manipulation of helper columns but should give you some ideas on how to proceed from there.
Good luck
Sunny
VIP
Trusted Members
June 25, 2016
Offline
Hi Andrea
I have updated the file with months from Jan-Dec.
Sunny
January 14, 2019
Offline
Oh, wow...very impressive. Would you mind explaining what the dummy column is doing and how it relates to everything else? What is causing the Max value label to appear ONLY on the corresponding month data segment? I see that the values in column S change, as new values are entered in the range E3:P10. And I now kind of understand what OFFSET is doing, but only if I really concentrate.
VIP
Trusted Members
June 25, 2016
Offline
Hi Andrea
What I am trying to do is to make the Max series "float" above the Jan-December series.
For that to happen, I need to determine how much to "float". Let's take Seafood for example:
The max sales is in Mar so I need to add the total for Jan and Feb (Dummy) and stack Mar on top to make it "float".
Column S search for the location (column) of the maximum value (in this case 3) and so I need to add 2 columns starting from column E.
OFFSET will do the adding based on column S. Maybe I should just call Dummy as Float instead.
Both Dummy and Max is plotted on the secondary axis (I deleted the axis) and I added the data label to Max only.
I then adjust their Overlap and Gap Width and set their colors to no-fill to create the effect.
Hope I explained clear enough.
Sunny
Answers Post
1 Guest(s)
