Showing Change Over Time
For this challenge, I looked at change over time of fruit consumption in the U.S. I was happy to find a data set that when viewed as an average, was pretty uneventful, but if you looked into it, there were clear divergences of consumption, both by fruit, and over time.
The data came from the USDA’s Fruit and Tree Nuts Yearbook Tables. There is a ton of information here, and I was able to find tables containing the per capita consumption of different kinds of fruits in pounds per year, dating back to 1980. My first task was to bring the data into Numbers and clean the tables.
Once I had my data in place, I wanted to determine what the combined fruit consumption was per year. To do this, I set up a sum column that added up the pounds of all fruits per capita for that year. (example: SUM(B3:O3))
This sum data allowed me to chart the combined fruit consumption per capita per year, which looked like this:
It showed a steady uptick in fruit consumption. For reasons I don’t have a scientific basis for, it’s what I expected—It generally feels like awareness around healthy eating and the importance of fruit and veggies has increased since I was a kid.
I then made a chart showing each individual fruit, and it’s consumption per capita, per pound, over time.
This made it a little more interesting, as we had some obvious favorites. People in the U.S. love bananas and apples!
Something I wanted to try was setting the data in an index chart. This would display the percentage of change from the initial amount in 1980. For a great explanation of index charts, check out this video by Robert Kosara.
To do this, I returned to my original table, and created an index column for each fruit. This calculated the change from the initial 1980 data point. As the data changed each year, the percentage change was calculated against 1 (the starting point for all of the fruits).
When I inserted the formula, it was useful to add the “$” symbol. This made it an absolute value, so when I copy/pasted, the dividend automatically populated with the subsequent row, but the divisor stayed the same.
The chart had a surprising result. Bananas and apples were popular, but their rate of popularity was relatively consistent over time. Limes and blueberries, however, were shot out of a cannon! (Maybe literally—look out!). They had an enormous increase in popularity over time.
Now that I had all of my charts, I set them up in a dashboard, and added some headers to tell the story. I also inserted larger numbers to display some of the data points. I was able to do this by creating a separate chart that housed the number in its title. This way I could place it over the chart to highlight key amounts and percentages.
Here is the completed dashboard!
It can also be viewed on Tableau Public here.
View this challenge on Storytelling With Data: explain the change
I hope this was helpful. Please contact me if you have any questions!