you're not going to create this in one sheet like that. Not easily, anyway. Your stab in lowercase-measures sheet is a start toward doing it that way, but you'll have to abandon the MeasureNames/MeasureValues construct.
On my copy of the measures sheet I switched to a technique whereby each measure is its own column. I use SUM(0) as a bogus axis, and then I plunked SUM(Current Term) on labels. (I did the same for Current Term-2. You'll do the same for each measure.) You can use different values in the bogus measures. I just go with SUM(0) as my own preference.
The Current column is what it looks like in raw format. I have formatted [Current Term-2] as follows:
I selected white as the color for the bars that tableau creates for the zero value. This hides the blue mark from user view.
I centered the label so that your values show up in the center of the column. (You can do all sorts for formatting things here, but that will be a matter of user preference.)
I right-clicked on the axis and did edit axis. I changed the label from "0" to "Current Term". then I clicked the Tick Marks tab and set the ticks to NONE. This eliminates the axis values on the ticks.
I clicked in one of the numbers in the column and chose FORMAT. Then select the 5th icon at the top of edit format to change line settings. Set Zero Line to None. that takes the faint zero line off the chart.
Next, to set individual colors. You can't just make a red-green calc. That will govern the color of the underlying bar, not the text. (I made a calc called 900? to demonstrate this. On the first SUM(0) mark, drag that to the colors shelf. You'll just see those zero bars get set.) So here is what you need to do.
Create two calcs. I did [Current Term -2 900] and [Current Term -2 less than 900]. Red if less than 900, and italicized. Green if greater than 900, and bold. Look at the two calcs. They are mutually exclusive. For each mark, only one will be set, and the other null. I put both on LABELS, and edited the labels editor to select colors and formats and I placed them side-by-side. Only one will display. It works as you see in the measures(3) sheet.
You'll have to do this for each measure. It's tedious, but it's a way to make Tableau behave in an excel-like manner. (You'll see all over this board that Tableau is not excel. It doesn't have a cell-oriented structure. It's dimension-oriented. So to make excel in tableau can be complicated.)
percent diff_v2018.1.twbx 7.9 MB
Thanks Joe. I would have assumed there was a much simpler solution to this, and I appreciate you detailing the workaround. Establishing the dummy x-axis really opened things up for me. I successfully re-engineered this table per your instructions and applied a dynamic color filter to the % difference columns. I cleaned things up a bit by adding dual axis for each column to put the headers on top. Attached is my finished table.
updated.twbx 7.9 MB
Joe Geglio wrote:
... I cleaned things up a bit by adding dual axis for each column to put the headers on top. ...
Yup. That's often the next question when someone sees this technique. And dual axis is the way to address it.
It's not easy. Well it's not difficult once you get it. It's just tedious and awkward. (And I hope your user doesn't ever try to download a crosstab of this sheet! All those zeros will litter the download.) But it also gives you the capability to mix text and shapes and bars all in one chart.