Forum Discussion
SergeiBaklan
Nov 04, 2017MVP
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