It’s the fourth day of dashboard week! The task for today is about the American Personal Consumption Expenditure. Based on requirements, we need to

  1. Download the data
  2. Clean & prepare data using Alteryx
  3. Find supplement data
  4. Make a visualization


Data Preparation


Because the data has 8 categories hierarchy it is a little bit challenging to define if the data is duplicated and which levels I want to analyze. I spent most of the time getting familiar with the data. David, our coach, suggested us to normalize the data to a monthly level. In that case, we can compare the consumption proportion by month without worrying too much about the inflation problems.  Here is my workflow for cleaning the original data. The container below is the supplement data set about the population and personal income in the US.  I thought it might be interesting to compare with personal income as well.




There are 8-level categories in the data set. It took me a while to figure out which level better represents my concept and which aspect I want to analyze. I narrowed the first level down to goods and services. I decided to compare the food consumption in these two aspects, therefore, I can show if customers spending behavior has shifted from buying nondurable goods to paying for food services. Here is the final dashboard.



From the first chart, it very clear that in 1987, food service surpassed the food consumption in households. I made a parameter hidden in the ‘hamburger’ which can drill down to different levels. If drill down to level 8, we can find that from that year a new type of business came out, which is ‘meals at limited service eating places’, and it increase the consumption expenditure in food service.

From the second chart, if we choose different timeline, the dots showed the spending in a more granular level. As time change, people spending less in beef and bakery in household expenditure but spend more in hotels and eating out.



Ivy Yin
Author: Ivy Yin