Starting Point and Goals
In this first blog post I want to share my work on this week’s #MakeoverMonday challenge. The Community took a look at US per capita consumption of poultry and livestock and the original visualization was a nice and clean (static) line chart (find the data here).
I decided to focus also on pork, beef and chicken as the main meat types and wanted to give some context to the overall meat consumption trend in addition to the lower level trends.
This is my final viz as uploaded earlier this week on twitter and tableau public (click on the viz to switch to the interactive version on tablau public).
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
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.
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.
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!