Multi-level nested pie chart in google sheets

user_807574 0
Viewed 1378

I need a multi-level nested pie chart in google sheets. To show number sums in categories and sub-categories. Can use code or just formulas.

Or if a system similar to google sheets already has a easy configurable nested pie-chart I can consider using it.

Here is a sample sheet with 2 charts I want to combine: https://docs.google.com/spreadsheets/d/1TvOf_tRNFS4zqbYR8EMGfTH1krI_iX9resrQ_AwmGZ8/edit?usp=sharing

data chart1 chart2

It can be similar to the one here, but with sums instead of counts:

Three Google Sheets' data graphs (pie charts) in one graph

Or a D3 Sunburst Partition.

google-sheets google-visualization charts
2 Answers

E2:

=QUERY({A2:C}, "select Col1,sum(Col3) where Col1 is not null group by Col1 label sum(Col3)''")

G2:

={FILTER(B2:C, A2:A=E2); "", SUM(FILTER(B2:C, A2:A<>E2))}

I2:

={"", SUM(FILTER(C2:C, A2:A=E2)); FILTER(B2:C, A2:A=E3); "", SUM(FILTER(B2:C, A2:A=E4))}

K2:

={"", SUM(FILTER(B2:C, A2:A<>E4)); FILTER(B2:C, A2:A=E4)}

enter image description here

demo sheet

user_56326290

dynamic fx:

=ARRAYFORMULA({TRANSPOSE(QUERY({A1:C}, "select sum(Col3),Col1 where Col1 is not null group by Col1 label sum(Col3)'Base Pie'"));
 {"♀", IFERROR(1/(1/TRANSPOSE(MMULT(TRANSPOSE(IF(SEQUENCE(COUNTUNIQUE(A2:A))<SEQUENCE(1, COUNTUNIQUE(A2:A)), 
 QUERY({A2:C}, "select sum(Col3) where Col1 is not null group by Col1 label sum(Col3)''"), )*1), SEQUENCE(COUNTUNIQUE(A2:A), 1, 1, )))));
 QUERY(QUERY({A2:C, ROW(A2:A)}, "select Col2,max(Col3) where Col3 is not null group by Col4,Col2 pivot Col1"), "offset 1", )};
 {"♂", IFERROR(1/(1/TRANSPOSE(MMULT(TRANSPOSE(IF(SEQUENCE(COUNTUNIQUE(A2:A))>SEQUENCE(1, COUNTUNIQUE(A2:A)), 
 QUERY({A2:C}, "select sum(Col3) where Col1 is not null group by Col1 label sum(Col3)''"), )*1), SEQUENCE(COUNTUNIQUE(A2:A), 1, 1, )))))}})

  • the formula takes data in A:C range and creates all necessary steps, columns and rows that will feed pie charts.

  • rows labeled ♀♂ serve as pie chart ratio correctors

  • column E can be used as a subcategory labeling system if needed

  • in the same manner row 3 can be used as labels for base pie

  • first, we create a base pie chart (from the black row) which will serve as background:

enter image description here

  • next, for each colored column we create a separate pie chart that we overlay on top of the base pie chart. note that colors and sub-colors needs to be selected manually for each slice by double-clicking a given slice and selecting either color or none in the case of ♀♂ slices

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

and repeat for the next column...

enter image description here

demo spreadsheet

user_56326290
Related