Forum Discussion

SergeiBaklan's avatar
Nov 04, 2017

Map Chart - Multiple Series

For filled map we naturally can't show multiple series at once, here is one of the ways how we may shift from one to another.

 

I took first figures I found, internet usage in EU countries, and limit them by three countries:

Next we 'unpivot' these data copying each series one under another, add one more column to the right (Category) with the name of the series, convert all together into the table (Ctrl+T) and insert Category slicer for it:

Select first two columns of the table as above and insert the chart

Since the chart will be placed against filtered rows not to forget to fix its position

Desirably to have the name of filtered category as separate value. Usual approaches are to have filter value from PivotTable (but we don't need it) or use SUBTOTAL / AGGREGATE which can work with hided cells (or VBA of course). Details are out of this topic, let add array formula (that's not the only variant of it)

=INDEX(tMaps[Category],MIN(IF(SUBTOTAL(3,OFFSET(C2,ROW(tMaps[Category])-ROW(C2),0)),ROW(tMaps[Category])-ROW(C2)+1)))

where tMaps is the name of our table, into the cell F2 (will be under our chart). It returns first unfiltered value for Category column.

 

Next hide Category column and design the Slicer a bit:

To show selected series on the chart do Select Data for it and assign series name on cell F2 was defined above

Practically that's all, result looks like

 

No RepliesBe the first to reply

Resources