(Note to self: V9.2 workbook)
You may need to reshape this data.
I can show you some ways to do some conditional formatting, but I think you have too many moving conditions to juggle to get it all like you want.
In sheet 1 I created two calcs, DollarSign and PercentSign. Take a look at them.
I added them to the text shelf and positioned them appropriately in the text editor. If the metricname is a certain value, it gets populated, else it's null. So it shows up in some columns, but not others.
But [Last Year] and [Prior Year] don't care what the metricname is. If you set the format to show decimals (so that the percents show up as percents) you'll get decimals on all the columns.
You can make separate calcs. See sheet 2. I did 2 of them. Now you can set separate formats, but you'll have to do a lot of juggling to get last year and this year to behave in separate columns. (Right now I'm just pumping out two LAst-Year values for an example.)
See Sheet 3. You can add multiple fields on the test shelf, and put them side by side. And when the metric name is one thing, the other calcs are null, so they don't show anything. (Again, trying to get separate columns for LAST and THIS year will be awkward.)
At least this shows you some ways to conditionally display (and format!) different things. (You can format the different fields with different formats, so if one value displays, it's bold and green and italicized, and another can be huge font and underlined. Etc.)
You may be able to string together some of these techniques to get what you want.
If it were me, I would consider some additional dimensions, like [Which year?] (values = "last" or "this") Or separate measure columns for the different measures. then you could format each individually.
Here's a thought. When metric is the percent value, enter it as 3 (for 3%) and 25 (for 25%), and then maybe the sheet1 solution would get you where you need to go.
What you've shown me is actually very eye opening. Thank you!
Option 1 is very straightforward and I wasn't aware that could even be done...
Option 2 won't work this time since I won't be able to group measures by type and can't format the table to show lines in between metric names. But its very good to know that I can easily split my Metribname dimension into individual measures.
Option 3 I don't know if I fully understand why some metrics show and others don't.
Overall I think your final thought is probably the way that best suits my needs. I'm doing this as a large data warehousing project with 10+ data sources and want to maintain the bulk of processing outside Tableau so the dashboard loads as fast as possible. I'm thinking I can move that formatting of writing the numbers in decimal or thousands to the data gathering stage like you said, and then apply the signs in Tableau as you showed in option 1.
Thanks again Joe very helpful!
In option #3 I’m essentially breaking into separate calcs. All the rows get set when = “Conv”, (else the value is null for rows that are not “Conv”.) Likewise . In the TEXT editor I placed these two calcs side by side. So when you have a sheet that breaks out columns by , when tableau is displaying the “Conv” stuff, is NULL for all those rows and nothing displays there, and SUM[Last Year CONV] has values, and shows the sum of those rows that have =”Conv”. And I formatted the two calcs to have % or $ respectively, so that’s how the non-null values will display.
Hey Joe I have another question if I choose the Option 1 route.
Is there a way to create a new measure out of the Metricname dimension? For example if I wanted to create Average Order Value out of Demand and Orders...
I have to somehow find a way to create a new dimension for AOV and divide This Year of Demand over This Year of Orders... or create a new dimension that includes all the existing metric names, plus the new one I want to create... not sure if this is achievable...
The data is there already to do what you need to do. It's just that juggling formats and columns might get complicated.
I took a stab at creating last year's AOV for you. See sheet 4.
Scroll sheet 4 so you are looking at the DEMAND column of stuff. I did a "Last Year Sum" in two different ways. One using LOD, and one using a table calc. Either way is acceptable. You get a single number for the total of all weeks (if I understood your requirement here.) (Note: If you only want to display it one time, we can monkey with that.) If you add up the 4 "Last Year" numbers for Demand, you get 5500.
Expanding on that, I did another measure to get last year's orders. (And t shows up in it's respective location in the Orders columns.) Using last year orders and last year demand, I used the LOD method and created an AOV. Your total demand is 5500. Your total orders is 55. Divide them and you get 100.
You don't really have to display all the intermediate measures. (And you don't actually have to create them outright. They can be stacked up inside the logic of the AOV calc. And likewise other such calcs you want to use for analysis.) I just put them here for demonstration purposes.