Hi,
I'm trying to create a chart to show actual, forecast and target sales. My data is in below format:
Product Category | Q1 | Q2 | Q3 | Q4 | Sales | Quarter | Type |
A | 100 | Q1 | Actual | ||||
B | 50 | Q1 | Actual | ||||
C | 70 | Q2 | Actual | ||||
D | 120 | Q2 | Actual | ||||
A | 30 | Target | |||||
B | 20 | 10 | 70 | Target | |||
C | 30 | Target | |||||
D | 60 | 5 | 50 | Target | |||
A | Target | ||||||
B | 80 | Forecast | |||||
C | 50 | 60 | Forecast | ||||
D | Forecast | ||||||
A | 40 | Forecast |
When Type = "Actual", we compute actual sales. The sales value is the sum of the column Sales and we look at it across quarters (Q1 sum of sales, Q2 sum of sales).
When the Type is "Target" we compute the sum of values under Q1, Q2, Q3, Q4. These would give me the target sales values across each quarter.
In a similar way, when Type is "Forecast" we we compute the sum of values under Q1, Q2, Q3, Q4. These would give me the forecast sales values across each quarter.
(Note: When ever the "Type" is Actual, the values under Q1, Q2, Q3, Q4 are blank).
In Tableau, I want to have a single chart, which can show me for each quarter what is my actual, target and forecast sales. Preferably, I would like to have my actual sales in bar chart and the target and forecast sales in line chart.
Can someone help me solve this? I am not sure how I can segment the sales values across quarters for the three categories (actual, target & forecast) to show in a chart since the data is in a very different format. What would be a good calculated field equation?
I think you don't need the "Sales" column. Also you don't need individual columns for each Quarter. You can arrange your data under the following Dimensions:
Product Type (A, B, C,D)
Type (this would have values Actual, Target and Forecast)
Quarter (this would have values Q1, Q2, Q3, Q4)
And a single Measure:
Sales (this will contain the values, irrespective of Actual, Target and Forecast)
Then you can easily create the view you are looking for.