Chart Source Data Query

[M10Ant]

I have a source table that shows a list of stocks and the total amount invested in each (specified in both dollars and percentages relative to the entire portfolio)

I'd like to display the percentage figures of the stocks in a doughnut chart, displaying those with a percentage figure of 5% or more as individual "slices" and any with a figure of less than this to all be combined in a single slice named "Other".

Ideally I'd like to achieve this by using an amended version of the source data that has already summarized these small percentage figures together (e.g. as a single row).

The table on the left is the original source data. My attempt at the "modified" source data to feed the chart is shown on the far right.

I'm using a helper column of [Combine as Single Pie Slice?] in the original source table to determine which stocks have a percentage figure below 5%, and those that have a figure above it. This is then used to help construct the 'modified' source data shown on the right.

In the modified source data (which isn't actually a table), the formula for [Amount Invested] and [Relative % in Portfolio] cells of the "Other" row can be seen in the Xl2bb output.

Below this is a filtered list of the stocks that should display individually in the chart. That list is a dynamic spill array (formula is in first cell of that array, where "AMZN" resides).

Collectively, all these rows (except the blank row between them) form the actual data I want the chart to use (specifically the [Relative % in Portfolio] column for the data, and [Stock] for the data labels)

Currently this isn't working brilliantly as the chart is giving two doughnut rings rather than just the one and there are are also blank entries in the chart's Legend which is a bit off. I suspect this all could be due to the chart's source data being a combination of a standalone row of data - the "Other" row - plus the dynamic spill array. If I get rid of the empty row between the "Other" row and the dynamic spill array, this still give two doughnut rings in the chart. I only need the one!

I think I just need some way to construct the modified source data so that the chart sees it all as one (and ideally as a single table).

Pie Chart Data.xlsx
	A	B	C	D	E	F	G	H
1	Max. [Relative % in Portfolio] value for combining assets as single "other" pie slice:					5
2
3	Original Source Data					Modified Source Data for Pie Chart
4
5	Stock	Amount Invested	Relative % in Portfolio	Combine as Single Pie Slice?		Stock	Amount Invested	Relative % in Portfolio
6	APPL	$2,500.00	3.29	Y		Other	16,000.00	21.05
7	AMZN	$10,000.00	13.16	N
8	COST	$2,500.00	3.29	Y		AMZN	10,000.00	13.16
9	DIS	$2,500.00	3.29	Y		GOOG	10,000.00	13.16
10	GOOG	$10,000.00	13.16	N		MA	10,000.00	13.16
11	HD	$2,500.00	3.29	Y		MSFT	10,000.00	13.16
12	JNJ	$1,000.00	1.32	Y		TSLA	10,000.00	13.16
13	KO	$1,000.00	1.32	Y		V	10,000.00	13.16
14	MA	$10,000.00	13.16	N
15	META	$2,500.00	3.29	Y
16	MSFT	$10,000.00	13.16	N
17	PG	$500.00	0.66	Y
18	TSLA	$10,000.00	13.16	N
19	UNH	$1,000.00	1.32	Y
20	V	$10,000.00	13.16	N
21	Total	$76,000.00	100
Sheet1

Cell Formulas
Range	Formula
C6:C20	C6	=IFERROR( ([@[Amount Invested]]/tblOrigSourceData[[#Totals],[Amount Invested]])*100,"")
D6:D20	D6	=IF([@[Relative % in Portfolio]]<$F$1,"Y","N")
G6	G6	=SUMIFS(tblOrigSourceData[Amount Invested],tblOrigSourceData[Combine as Single Pie Slice?],"=Y")
H6	H6	=SUMIFS(tblOrigSourceData[Relative % in Portfolio],tblOrigSourceData[Combine as Single Pie Slice?],"=Y")
F8:H13	F8	=FILTER(FILTER(tblOrigSourceData,(tblOrigSourceData[Combine as Single Pie Slice?]="N")),{1,1,1,0})
B21	B21	=SUBTOTAL(109,[Amount Invested])
C21	C21	=SUBTOTAL(109,[Relative % in Portfolio])
Dynamic array formulas.

Chart: