I want my chart to display like the one below. However, giving the second dimension(storage location) colors all the bars instead of showing the storage location names as a second dimension. How do I achieve this?
I think given the number of potential bars that you have in this chart, Settu's solution works well. I've made a minor change and added some colours, which you may, or may not, think help...they are defined in sheet 2 of the Excel workbook, so you can change them to a different colour scheme.
p.s. Obviously I'm not aware of your actual requirements, but be careful using SUM(DISTINCT...) you may not get the results you expect if the same value occurs more than once.