This week, Kyle set the challenge of recreating area charts combined with a tile map / small multiple. He’d been inspired by a viz he uses at work, which he then realised had actually been inspired by a previous #WorkoutWednesday challenge from 2020 which I actually blogged about here.

So some of the steps for this solution guide, I’ll lift from my existing blog :-), but there are no table calcs in this instance.

Building the basic tile map

As per Kyle’s instructions, I started by building a new field that I could then format to millions

Pop

[Population (Population)] * 1000

I formatted this to be a custom number with 2 dp and displayed with Millions unit.

Put this into a table where State and Date on Rows and Pop on Text, just so we can validate what we’re up to…

We need to standardise/normalise the display, so the population for each State is ranging from 0 to 1. For this we need to determine

Min Pop Per State

{FIXED [State]:MIN([Pop])}

Max Pop Per State

{FIXED [State]:MAX([Pop])}

and we can then work out

Standardised Pop

(SUM([Pop]) – SUM([Min Pop per State]))/(SUM([Max Pop per State]) – SUM([Min Pop per State]))

Format this to display as a number with 2 dp. Add all 3 fields to the table. For each state the Standardised Pop should have a year when then value is 0 (equivalent to when the population is lowest for the state) and a year when the value is 1 (equivalent to when the population is highest for the state).

For the tile map, we also need fields Rows and Cols which are calculations that map each State to a number (and can be copied straight from the challenge sheet).

On a new sheet, add Rows to Rows (as a discrete dimension) and Cols to Columns (also as a discrete dimension). Add State to Detail. Your initial ‘map’ layout should start to take shape.

Add Date to Columns and set to be a the continuous Year level (green pill) and add Standardised Pop to Rows. Set the display to Entire View, so all the tiles are visible.

Change mark type to Area and add Pop to the Tooltip. Update the Tooltip as required.

Adding the State label and max value

For this we’re going to plot a single point that will be at the centre of the Date axis and slightly higher than 1. For this we need

Centre Date

DATE(DATEADD(‘year’,
FLOOR((YEAR({FIXED:MAX([Date])}) – YEAR({FIXED:MIN([Date])}))/2),
{FIXED:MIN([Date])}
))

This looks a bit complex, so I’ll break it down. What we’re doing is finding the number of years between the minimum year in the whole data set (1900) and the maximum year in the data set (2023). This is

YEAR({FIXED:MAX([Date])}) – YEAR({FIXED:MIN([Date])})

We’re then halving this value ( /2) and rounding it down to the nearest whole number (FLOOR).

We then add this number of years to the minimum date in the data set (DATEADD), to get our central year – 1961.

Now we need a point where we can plot a mark against that date. It needs to be above the maximum value in the area chart (which is 1). Based on what I did before, I decided 1.75 worked

Plot State Label

IF YEAR([Date]) = YEAR([Centre Date]) THEN 1.75 END

Add Plot State Label to Rows between Rows and Standardised Pop. Change the mark type of this axis to shape and use a transparent shape (see here for info). Note you can use a circle and reduce size to smallest and opacity to 0% if you wish. However, this will show a small dot when hovering, which you don’t get with a transparent shape.

Add Max Pop per State to Label and change State from Detail to Label. Adjust the Label accordingly

Remove all text from the Tooltip dialog of the Plot State Label marks card, and hide the Nulls indicator label. Make the chart dual axis and synchronise the axis. Remove Measure Names from the All marks card.

Then remove all gridlines, zero lines, axis lines and row/column dividers. Hide all the axis and the Cols and Rows pills (uncheck show header).

Building the bar chart

Create a new field

Latest Pop per State

IF [Date] = {FIXED [State]:MAX([Date])} THEN [Pop] END

If the date is the maximum date for the state, then get the population. Format this to be 2dp in Millions.

Add State to Rows and Latest Pop for State to Columns and sort descending. Adjust the colour to suit. Show mark labels, and remove all gridlines etc and row/column dividers and hide the axis. Adjust the Tooltip.

Creating the highlight action

Add the two sheets to a dashboard. Add a dashboard highlight action

Highlight State

On hover of the bar chart, target the map, via the State field only.

And that should be it! My published viz is here.

Happy vizzin’!

Donna