You say it's not too practical to reformat, but that might be your only option.
I'd like to see a working example of this so that I can mess with it with you and see if we can hack something up.
Create an excel file of bogus data that demonstrates what you are facing. Probably only need 4 or 5 rows in it, really. Then hack up a simple sheet based on that, and we can work together from there. Let me know what version of Tableau you are doing this on so that I can work on the same version.
One question I would want to know: How would you determine that the [Target] for "Name1" should be a percent, and the [Target] for "Name2" should be dollars? (And the same for all the other measures.)
Hi Joe, and thanks for your help. Here's a screenshot of some fake unformatted data (didn't see a spot to upload a file--did I miss it?)
In general, I have a number of programs (AAA, BBB, and CCC), each of which needs to be evaluated across a number of separate measures (Measure1, Measure2, and Measure3). As you can see, Measure1 is a financial measure and the others are not.
For each measure, I need to take a numerator (either a number of units or a dollar amount, depending on the measure) and a denominator (always a number of units). Then, the resulting quotient ('actual') is either the profit-per (a $$ field) or percentage 'success (a percent).
That then needs to be compared to a target, also either a percentage or currency.
The actual and target are then compared to produce a % achievement, which is of course always a percent.
Ideally I can wrangle this into a dynamic table (using location and FYQtr as filters) which can allow a user to combine multiple locations and/or quarters or single them out as he/she sees fit. In reality I have a lot more locations and quarters to include, as well as additional programs and measures. However, I think this screenshot shows the data format issue pretty well.
When you are writing your reply, look on the upper right corner of the edit box. "Advanced editor and add an attachment". Click that.
When you are in THAT editor, in the lower right corner is "Attach". click that. That's how you attach an actual file.
So create an excel file of bogus data. Screen shot doesn't help me. About all I can do with that is manually type it all into excel and try to recreate what you are doing... Create a workbook based on that excel file. Make it look somewhat like your actual sheet. THAT's what I need to have.
I want an actual working packaged workbook I can use. Get me as close to where you want to go as you can, and then I'll be able to take it up from there. Remember, I have my own job to do. I can't spend a lot of time trying to recreate what I am guessing you are doing. Get me there, and I can start doing something with that.