Google Sheets: How to make a stacked/aggregate chart

Question

I have made a bar chart which aggregates my data, but is there any way I can split each bar based on the data it is aggregating - similar to how a stacked bar chart would look?

Here is a bad artists impression (thick blue lines mine). The idea is that it's important to know from looking at the graph if I sold 5 at £1, or 1 at £5.

Ideally this would work even if the price for each item is variable, but that is not essential (eg: if there is a 'hack' with hardcoding Apple = 3, I can live with that.)

I'm also fine inputting helper columns etc, within reason, but I would want to be able to easily continue to add things to the list on the left without having to add new helper columns each time (calculated ones are fine, of course.)

Thanks in advance.

UPDATE: With thanks to Kin Siang below, I ended up implementing a slightly modified version of their solution, which I am posting here for completeness.

I added a very large (but finite) number of helper columns to the right, with a formula in each cell which would look for the nth occurrence of the item in the main list (wrapped in an iferror to make the unused cells blank).

=iferror(index(FILTER($A:$B,$A:$A=$D2),E$1,2))

Theoretically it could run out of space one day, but I have made it suitably large that this should not be an issue. It has the advantage over the other solution that I do not need to sort or otherwise manipulate the input range and can continue trickling in data to the main list and have the chart automatically update.

Answer 1

Add a transaction identifier column to your data so you know you sold five (5) bananas separately for £1 each. Most real data will have an OrderNumber or SaleID or whatever associated for each line item.

You can read my steps below or watch this silent low-quality video:

Then utilize a pivot table. Arrange the OrderNumber in rows, Item in columns, and Price as value. Don't worry that the pivot table SUMs the price, because your OrderNumber prevents transaction(5678) from being summed with transaction(5679).

Now make a chart from the PivotTable. Warning: gSheets is overly fickle regarding stacked column charts. Perhaps others have found a better way, but the only way I’ve found to make such a stacked column chart is:

1. Select only the data. Not the top header (row1) or Grand Totals. So for the data depicted below the data range is E2:H10
2. CRUCIAL: OrderNumber must be the first column in the data being charted!
3. Insert the chart
4. Avoid the temptation to change the chart type to Stacked Column. Calm your intuitions and instead scroll down all the way to the bottom…
5. Check Switch rows/columns. (Don’t ask why they placed that at the bottom, beyond sight.)
6. Now scroll up and change the chart type to Stacked Column

Even though the X-axis is OrderNumber you need the “series” to display all your OrderNumbers. Sometimes gSheets won’t plot all your data even though the stacked column is arranged correctly. My method of madness (above) is less maddening than manually adding each series (gSheets term, not mine).

Image of results that would look really great in post but StackOverflow won't show because I'm a newcomer

Answer 2

Yes, it is possible to display the chart in your case, however need some data transpose in order to do so, let me show you the example with dataset

Assuming this is your original data:

First sort the data by alphabet, and enter this formula in new column

=if(G39="",1,if(G40=G39,I39+1,if(G40<>G39,1)))

Next add new column for categorical purpose, by using concatenate function

="Price"&I40

In the transform data for chart purpose, enter this formula to split all price into different row, different column for different product

=sumifs($H$40:$H$47,$G$40:$G$47,$A41,$J$40:$J$47,B$40)

After that i select stack bar chart and ensure the price in under series, in case in 23 will have some problem to set price at series correctly, you can use 33 data create stack bar chart and update the data range again, it will work also

Here is the cute chart you expected, accept if help :)

*When certain fruit has less price record, it is advised to fill in 0, as the data table need in same column (see the orange price 3), although I didnot test if blank