If you want to do this all in one sheet, you will not be able to use Measure Names / Measure Values to make it happen. You will have to bring in a scaffolding data source.
The scaffolding can be as simple as a 1, 2, 3, 4 (however many rows in your chart). It doesn't even have to be numbers; any unique row contents will do.
You'll use the scaffolding on your Rows shelf, and from there, you can use INDEX() to identify which row you're on and come up with the correct values to use for that row.
Here's how I put something together:
1. Create your scaffold data source. I just jammed some rows into Excel and then copy-pasted into Tableau to create a new data source in the workbook.
2. Create calculated fields [INDEX.Actual] and [INDEX.Opportunity] to conditionally capture the correct values, based on which row of the sheet we're on.
3. Create a similar calculated field [INDEX.Label] to use as your row labels.
4. Lay out your view as shown. Note that the scaffolding is the primary data source, and your data is brought in as secondary.
5. Looking good so far. Now you can set up the view as a chart rather than a table, filter out any unused rows in your scaffold (I had a scaffold of 4 but only 3 types of data I'm displaying), and make it look pretty.
My quick-and-dirty example assumes that you do not need to incorporate any additional filtering/segmentation from the main data. If you do, the blending approach shown here will interfere. You will need to bring your data sources together some other way, such as a Tableau JOIN (but be wary of data replication).
I think the root of the complexity is due to the fact that [Measure Names] and [Measure Values], being "meta" fields, are not accessible in formulas. Applications such as this would be greatly simplified if you could write a calculated field that changes based on [Measure Names]. You may want to upvote the following ideas that are related to this concept:
Finally, I have attached a workbook showing my work, but it's in version 10.1.3, so you may not be able to view it. Sorry.
calulation_JC-10.1.3.twbx 31.1 KB
Thankyou for the quick resolution.
I do have a question. We will be populating the excel data into a SQL table once the ETL Jobs are completed. Also we have Filters added to this dashboard according to which the Data in the charts should be updated. In that case will this approach work.?
If you have an ETL process in your workflow, I suggest you leverage that to pivot your data so that your actual/opportunity columns become individual rows in your output. This will allow you to construct a view similar to how you would construct it with [Measure Names] / [Measure Values], but without the limitations that come with using "pseudo-fields".
Beyond that... the example solution I provided should work fine with an SQL data source. As for filters, you may run into problems because your main data is the secondary data source in my construction, and it is blended with the scaffolding based on an empty Data Relationship (no fields are associated to the scaffolding). Usually, Tableau will insist that any filters/dimensions you try to incorporate from the secondary data source have some sort of Data Relationship to the primary data source.
As I mentioned, you can also build this out as a cross-database JOIN (if you are using Tableau 10.x). Cross-database JOINs mitigate the disadvantages of blending in this scenario, but you must be extra careful to avoid data replication pitfalls.
Finally, if your data is moving into an SQL data source, you can mimic the blending or JOIN solution in Tableau using a custom query. A custom query can be used to incorporate scaffolding or help reshape your data (if your ETL pipeline is not reshaping it the way you need).
At the end of the day, you'll need to take all this into account and decide just how badly you want to build your report as one sheet rather than several sheets on a dashboard. There's a definite tradeoff in terms of data/report complexity, and you may find that doing the dashboard approach is the easier solution.