Tableau Actuals and Budget as measures

Question

I have a question about Actual vs Budget for financial information.

Right now my dataset has 6 measures; 3 actuals and 3 budget. {A1, A2, A3, B1, B2, B3}

I want to be able to show on a bar chart {A1 B1}, {A2 B2}, {A3 B3} as a bar chart of actuals with budget as a mark on each bar.

How would I be able to show this? Tableau is treating each of the measures separately and I'm unable to combine them to show actual vs budget for my three areas

Answer 1

It would be simpler if you can reshape your data wth 3 columns: Area, Actual, Budget.

In that case, you could select all 3 fields in the data pane (left margin), click on the Show Me panel and select bullet chart to get your bar charts displayed against reference lines and bands.

Tableau would have to take a guess about which field to use for the bar and which for the reference line. If you want to swap the two choices easily, you can right click on the axis and swap the reference line.

Answer 2

The problem with your data is that it's column based rather than record based. So as Alex Blakemore suggested you need to reshape your data, which in your case can be done pretty easily within Tableau.

In the Datasource Tab choose the columns A1 to B3, right click on the header and choose "Pivot". What you have done now is putting it in 2 columns, one with the "code" A1 to B3, the other one with the amount (plus any additional columns you had before). You can make it a bit easier now by creating two calculated fields with LEFT([identifier],1) for the Type and RIGHT([identifier],1) for the Area. Where [identifier] is the column with the A1 to B3 values in it.

Now you have everything to display the Budget and actual side by side by dragging the [area] and [type] in the columns shelf and the [amount] in the rows shelf. If you need to have the budget as a seperate mark on the bars, you will have to create two more calculated fields (and end up with Alex' suggested idea) where you create two columns, one for [actual] and one for [budget] by creating them with if [type] = "a" then [amount] end and if [type] = "b" then [amount] end now you can remove the [type] and [amount] pills and add the [actual] and [budget] ones instead. Now you can format them as you want in the marks pane.