Map Chart - Multiple Series

%3CLINGO-SUB%20id%3D%22lingo-sub-124186%22%20slang%3D%22en-US%22%3EMap%20Chart%20-%20Multiple%20Series%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-124186%22%20slang%3D%22en-US%22%3E%3CP%3EFor%20filled%20map%20we%20naturally%20can't%20show%20multiple%20series%20at%20once%2C%20here%20is%20one%20of%20the%20ways%20how%20we%20may%20shift%20from%20one%20to%20another.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20took%20first%20figures%20I%20found%2C%20internet%20usage%20in%20EU%20countries%2C%20and%20limit%20them%20by%20three%20countries%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MapCharts01.JPG%22%20style%3D%22width%3A%20235px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F23517iBEE344804AEB4A37%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22MapCharts01.JPG%22%20alt%3D%22MapCharts01.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ENext%20we%20'unpivot'%20these%20data%20copying%20each%20series%20one%20under%20another%2C%20add%20one%20more%20column%20to%20the%20right%20(Category)%20with%20the%20name%20of%20the%20series%2C%20convert%20all%20together%20into%20the%20table%20(Ctrl%2BT)%20and%20insert%20Category%20slicer%20for%20it%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MapCharts02.JPG%22%20style%3D%22width%3A%20392px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F23518i98A0B4368B3C7909%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22MapCharts02.JPG%22%20alt%3D%22MapCharts02.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ESelect%20first%20two%20columns%20of%20the%20table%20as%20above%20and%20insert%20the%20chart%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MapCharts03.JPG%22%20style%3D%22width%3A%20295px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F23519i38601DA916BCDEFD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22MapCharts03.JPG%22%20alt%3D%22MapCharts03.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ESince%20the%20chart%20will%20be%20placed%20against%20filtered%20rows%20not%20to%20forget%20to%20fix%20its%20position%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MapCharts04.JPG%22%20style%3D%22width%3A%20232px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F23520iC607A3ABE295390C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22MapCharts04.JPG%22%20alt%3D%22MapCharts04.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EDesirably%20to%20have%20the%20name%20of%20filtered%20category%20as%20separate%20value.%20Usual%20approaches%26nbsp%3Bare%20to%20have%20filter%20value%20from%20PivotTable%20(but%20we%20don't%20need%20it)%20or%20use%20SUBTOTAL%20%2F%20AGGREGATE%20which%20can%20work%20with%20hided%20cells%20(or%20VBA%20of%20course).%20Details%20are%20out%20of%20this%20topic%2C%20let%26nbsp%3Badd%20array%20formula%20(that's%20not%20the%20only%20variant%20of%20it)%3C%2FP%3E%3CPRE%3E%3DINDEX(tMaps%5BCategory%5D%2CMIN(IF(SUBTOTAL(3%2COFFSET(C2%2CROW(tMaps%5BCategory%5D)-ROW(C2)%2C0))%2CROW(tMaps%5BCategory%5D)-ROW(C2)%2B1)))%3C%2FPRE%3E%3CP%3Ewhere%20tMaps%20is%20the%20name%20of%20our%20table%2C%20into%20the%20cell%20F2%20(will%20be%20under%20our%20chart).%20It%20returns%20first%20unfiltered%20value%20for%20Category%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%20hide%20Category%20column%20and%20design%20the%20Slicer%20a%20bit%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MapCharts05.JPG%22%20style%3D%22width%3A%20520px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F23521i0E676F8E539F8108%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22MapCharts05.JPG%22%20alt%3D%22MapCharts05.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ETo%20show%20selected%20series%20on%20the%20chart%20do%20Select%20Data%20for%20it%20and%20assign%20series%20name%20on%20cell%20F2%20was%20defined%20above%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MapCharts06.JPG%22%20style%3D%22width%3A%20314px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F23522i8FF53D874A4B191F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22MapCharts06.JPG%22%20alt%3D%22MapCharts06.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EPractically%20that's%20all%2C%20result%20looks%20like%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MapCharts07.JPG%22%20style%3D%22width%3A%20520px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F23523iAB417F3BD67E58D6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22MapCharts07.JPG%22%20alt%3D%22MapCharts07.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MapCharts08.JPG%22%20style%3D%22width%3A%20495px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F23524iD3F48733F9507C05%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22MapCharts08.JPG%22%20alt%3D%22MapCharts08.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-124186%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharts%20%26amp%3B%20Visualizing%20Data%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETips%20and%20Tricks%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
MVP

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:

MapCharts01.JPG

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:

MapCharts02.JPG

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

MapCharts03.JPG

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

MapCharts04.JPG

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:

MapCharts05.JPG

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

MapCharts06.JPG

Practically that's all, result looks like

MapCharts07.JPG

MapCharts08.JPG

 

0 Replies