Excel 2013 - Help adding a third data series that custom fills the bars in a combo chart

Forgive the potentially dumb question in advance, total charting novice here.

I am successfully tracking two series of data for a company food drive using a combo column chart and line chart. The bars track the donation amount per department and the line markers track the per person amount in each department.

Capture.GIF

What I'd like to do is add another set of related participation percentage data and have the bars fill to show the total participation in each department. For example, if the tallest column (73.75) had 50% participation, half of it would show blue and the other half would show yellow. The source (Amount, Per Person and Participating) data shown below (sans departments).

Capture2.GIF

If I'm barking up the wrong tree and there is a better way to do something like this, please point me in the right direction.

Thanks for any help!

Hi brhend.

I think you need to change up your chart types to make this make sense.

Here is the sample file:
Response for Friday-Challenge-How-would-you-chart-it-Food-Donation-Data.xlsx

Also, you can see how to build these charts from the video tutorial below:
friday-challenge-answers-food-donation-excel-dashboard-chart

Hope these solutions help.

Steve=True

Wow! Way more than I expected. Thanks so much for taking an interest, Steve. I had given up on the question thinking it must not have been a good one.

The video was extremely helpful. Looking forward to going through it thoroughly later to understand how to accomplish it. I liked both solutions but preferred the Steve=True solution because it more clearly shows the values for the three measures.

One thing to note, the attached excel file is displaying [CELL RANGE] in the bars right now. I'm thinking I will figure out how to fix that when I dig into it, but thought I'd let you know.

Thank you again. I appreciate the time you guys spent and will definitely be using what I learned.

Bruce