I would love to see a solution to this question. I have exactly the same requirement with some of my M/M data from Google Analytics.
So if your data source looks like:
year sales avg sales PMPM units 2011 $172,587.91 $279.27 $0.01 618 2012 $229,513.48 $378.11 $0.02 607
One option to get the results requested is you could create a scaffold data source like:
Column Measure % of Diff sales % of Diff avg sales % of Diff PMPM % of Diff units 2011 sales 2011 avg sales 2011 PMPM 2011 units 2012 sales 2012 avg sales 2012 PMPM 2012 units
as your primary data source in Tableau and then add the values you want, formated you want, and where you want
See the attached for an example that uses a custom text label with a calculated field for each label format.
If you have other constraints, this route can be adjusted to accommodate, or there are other routes that may be better, such as custom SQL. Please provide more details on your situation if you would like to explore other options.
How I hear your request is you want a table of marks formated like:
$0.00 $0.00 % $0.00 $0.00 % $0.000 $0.000 % # # %
Measure Names/Measure values will not be able to help in this situation because each cell needs a different format. Measure Names/Values works when the entire column or entire row is formatted the same. So if we doe not have the unpivot feature from Measure Names/Values, we need to unpivot the data prior to Tableau, and in this case you requested 3 columns and 4 rows, that is 12 marks, so the scaffold data source is 12 records, to enable 12 marks that can be individually tested for in calculated fields and formatted differently.
There are other routes to get the same results, but they would all generate at least 12 records from the query results that Tableau gets back from the data source.
The key takeaway here is that your data structure sets the boundaries for what view you can create in Tableau.
I would create the % Diff column as a second worksheet that is aligned with the first worksheet on a dashboard. I believe you will need the same values in the second worksheet but you will want to hide them. This is how I have solved this problem in other BI applications.
Understood Chris and appreciate your comments. But in my opinion too much effort for such a simple calculation. Just tells me the negatives of Tableau and is behind the competition.
Limited solution. How do you then scroll down if the data doesn't all fit in the viz window.
What 'competition' are you referring to?
It can be confusing when things that are easy to do in other software are not intuitive or easy in Tableau.
In a situation like this, Tableau can be very frustrating when it does not meet our expectations. You are welcome to email me if you would like to talk about Tableau and other perspectives on this situation.
I was just trying to light a fire. I know Tableau is more of a visualization program versus trying to completing setup crosstab reports that are done in excel. But to me this one is a vital need and heard from numerous users that need this.
Understood Bill but the workaround I described above was needed in other tools, enterprise BI tools such as OBIEE and Business Objects, which are supposed to have more functionality than Tableau. As long as the user doesn't need to scroll you should be able to align the second view which shows the variance with the first view, which shows the current and prior period amounts.
I'm able to accomplish this in OBIEE by creating a calculated item, which isn't possible in Tableau. For example,
Measures Year 2012 2013 2012-2013 (calculated item) Sales 1m 0.5m 0.5m Quantity 300k 500k -200k
We are doing the duplicate sheet that aligns with the first sheet, but it's quite silly and doesn't align perfectly.