Having demonstrated a number of different presentations of Budgets Vs Actuals in my previous post, this post now demonstrated how to handle the data requirements for these visualisations. I make the assumption that you don’t simply have the budgets being provided in a nice clean data set, and that you have the following problem:
“My actuals are coming from a database, as we collect them using our online billing system, but I have put the budgets together in a spreadsheet”
OH NO!! A data problem. How do I draw a visualisation which shows how I’m doing against targets. Luckily, Tableau 6.0 was released today, so the addition of DATA BLENDING (perhaps the coolest new feature? alright alright, combination charts has it) makes this data problem easy to resolve.
BUT FIRST – you’ll need some budget information.
Since I plan to use Superstore sales data to represent my actuals (one of the sample data sets included with the Tableau download), I want a data set which represents budgets which can be compared against this data. Here’s one for you.
At this point its important to realise what we actually have in this budget file.
This data is one row per year, per region, per customer segment – thus this data can be AGGREGATED to the entire company budget, but cannot be broken to a lower level. We do not have budgets by product category for example, so we cannot draw visualisations which show this.
Its REALLY important to understand the aggregation level that your budgets are at, and how this compares to the actuals.
OK, now break out Tableau 6.0
Make a data connection to the superstore sales database and also to the Excel file I have provided above. Your dtaa connections should look something like this – although the colours of the ticks might vary (I’m not sure what they mean yet – answers on a postcard please).
Now we need to make a RELATIONSHIP between the two data sources – at ALL the appropriate levels.
- Right click either of the data sources and select ‘Relationships’
- Choose the Superstore sales data connection as the primary data source
- And then select CUSTOM
Now we need to define the relationships using the add button – you should choose relationships which end up looking like this:
And finally, lets draw a bullet chart
Start with the sum of sales against the region and customer segment
Then add the budget FROM THE SECONDARY DATA SOURCE to the Level of detail shelf:
Then use the show me button and select a bullet chart
You’re done - Budgets Vs Actuals – 6.0 Style.
See Budgets Vs Actuals Part I for more ideas on how to present this data