The per capita consumption of meat in the US has been on the rise since 1965 due to a massive more consumption of chicken meat.

Creation of the Viz

To create this viz I went through the following 5 steps:

1. Pivoting data

As I wanted to create a dual axis viz and as I would have to bring in the measures for the stacked area chart on a combined axis I first had to pivot the data to bring in the line chart as one measure.  (At least I’m thinking I had to do that because I failed to build two combined axes). Before pivoting the data I built the sum of the three meat types in the data source.

2. Calculating the areas

For the first area I calculated the minimum per capita consumption by year:

The second area is the area between the maximum and the minimum. Therefore I first had to calucate the maximum. I couldn’t do it the same way as I calculated the minimum because the sum was included as data points.

Then I simply built the difference between [max] and [min].

The third area was finally the difference between total weight (max(lbs)), the second area ([max]-[min]) and the first area ([min]) or even simpler the difference of total weight (max(lbs)) and [max].

3. Creating the area chart

I created the area chart by bringing in the three calculated areas on a combined axis. I excluded 1960 because of the missing values between 1960 and 1965 and gave no attention to the ‘actual/forecast’ info because that was meaningless for my main message.

4. Building the line chart

The line chart was simply built by bringing in the pivot measure and putting the meat type on color.

5. Building the dual axis and synchronizing axes

Formatting the viz

1. Colouring

I chose a light shade of yellow to colour the second area and to give attention to the trend of the per capita consumption of the three meat types. What stood out for me was the trend of the chicken meat consumption so I coloured the chicken line in red. The remaining areas and lines I coloured in different shades of grey.

2. Labelling

I labelled the lines at the end and chose as measures the per capita consumption and the difference in % to 1965. At the beginning of the lines I labelled the different meat types by using annotations.

3. Axes

Then I hid the y-axes and chose a lag of 10 years for the x-axis.

Building the dashboard

Finally I built the dashboard by bringing in the sheet and adding some text boxes (for the title, the main message of the viz, references), some lines to connect the topics (also by using textboxes) and the BANs.

Please feel free to rebuild the viz.

And I very much welcome any kind of feedback!