Hello. I am trying to figure out what the best structure is for our data to make it the most flexible and nimble for use in Tableau. I have attached a spreadsheet with two potential formats. The first is our current data format, the second is a proposed new format. I have read the knowledge base articles on this subject (http://kb.tableausoftware.com/articles/knowledgebase/denormalize-data and http://kb.tableausoftware.com/articles/knowledgebase/preparing-excel-files-analysis), which seem to suggest that the current data format would be more flexible, since it is closer to a "list" format. However, I have been running into limitations in how I can use that format in Tableau. I will attach a Tableau packaged workbook to illustrate the issue.
The first view was relatively easy to create with the data in the proposed format, because each metric is split out and can be added to the view (or used in a calculated field) separately. The list format seems to be more complicated. For instance, I can't figure out a way to have the projected and goal units be a shape while keeping the actuals as a bar. And I cannot figure out what table calculation would replicate the "units percent to goal" field I created. With the proposed format, where the metrics are split out, this was simple: sum([Units Actual])/sum([Units Goal]).
Is there a way to create the view using the current data format? Are there limitations to the data in the "proposed data format" that I am missing? Which format would you prefer if given the choice?
This decision is going to affect the way we capture and store data from here on out, so I want to make sure we get it right.
Thank you in advance for any advice or assistance.