Have you tried making a dashboard?
With some patience you should be able to make the dashboard look how you want and the calculated fields can only affect the relevant data.
To achieve what you are after I think you will need to use the Highlight Table which requires a single dimension and a single measure. With a little tweaking of the data source and some custom formatting it is doable though. I'm still relatively new to using Tableau, but I enjoy a challenge. It's possible that someone else will be able to simplify the steps below...
Step 1. Pivot the source data so that you have a column for Date, Pivot Field Names, and Pivot Field Values. I'm not sure which version of Tableau you are using, but I think the feature became available in v9. This way you have one dimension that contains the name of all of your measures rather than a separate measure for each. If you have an earlier version of Tableau then you would need to do this in excel or wherever you are getting your data. (You might be able to work around the pivot with a couple of calculated fields, but pivoting is an easier option if it's available.) UPDATE: I've attached an excel file that shows the data after the pivot to give you a better idea of what it would look like.
Step 2. Create a calculated variable that is null if the value is a percent ([Value %]). Luckily because of how you named things, I was able to use this formula to do it:
if left([Pivot field names],1)=" " then float([Pivot field values]) else null end
Step 3. Create a calculated variable that is null if the value is not a percent ([Value #]). Again, because of naming, it made things a little easier:
if left([Pivot field names],1)<>" " then float([Pivot field values]) else null end
Now, you might notice we have 2 different measures, which seems like a step backwards but it was the only way I could get things to work a little further down. So, now we can combine these two measures into a single measure.
Step 4. Create a 3rd calculated field ([Value]) with this formula:
if left([Pivot field names],1)=" " then
-float([Pivot field values])
float([Pivot field values])
Notice that this value sets those values which are percentages to a negative value and other values are positive. This is the single measure that we'll use in the highlight table.
Step 5. Create a calculated field that determines the direction of change ([Change]), but only for the % values. Since these calculations make use of table calculations, you have to use aggregate functions. This is why we needed the other values before...
if isnull(sum([Value %])) then 0
if (ZN(SUM([Value %])) - LOOKUP(ZN(SUM([Value %])), -1)) < 0 then 10
Notice that it assigns the value based on both the type of data (numbers=0) and direction of change (percentage down is 10, percentage up is 20).
Using the drop down for this value, change it to Continuous.
Now we can start to build the highlight table.
Add [Pivot field names] to the rows and [Date] to the columns. Change Year([Date]) to Day.
Add [Value] to table as the label. It should show up as SUM(Value).
You'll notice that the percentage values show up as 0, so we need to change the format for SUM(Value). For numbers, select custom and enter:
The part before the ; is the format for positive values and the part after it is for negative numbers. Remember that percentages are negative because of our calculation, so this format puts them in a percentage format.
Now switch to the Highlight Table type.
At this point, Tableau will add SUM(Value) to the color, which highlights each cell based on the range of values.
Move the calculated value you created for change to the color area.
In the legend for AGG(Change) edit the colors and switch to custom diverging.
Place a check next to "Stepped Color" and change it to 3.
For the left color (0) change it to white. (You might need to use create a custom color palette in order to get specific colors for up and down.)
You can add the [Pivot Field Names] as a quick filter to select which fields you want to show, and you can rearrange those fields to get the order in the table you want.
Looking back, you might be able to skip step 3. This was actually from an earlier approach I took and I'm realizing I don't use the [Value #] anywhere else.
I typed this up in a hurry after figuring it out, so if I wasn't clear on any of the steps, let me know and I can elaborate. And again, since I'm still fairly new to Tableau someone might come along with an easier solution.
Hope this helps
pivotted data.xlsx 10.8 KB
Thanks Marc! All, I was able to create it, as a hack, by using separate sheets for each line and cobbling it together as a dashboard, but that isn't the best solution from a sustaining perspective and can look wonky when resizing between display types. The above suggestion looks like it could be a more elegant solution. I will give it a try and let you (and everyone) know how it works. If I can get it to work, I will publish my sample dashboard with the solution.
Thanks again for the feedback,