There are a couple of different ways to do this, but both are going to take some work. Essentially, we're going to want a separate measure for each different metric.
The first option is to use Tableau Prep. You'd first pivot the data in Prep, as you've done in Tableau Desktop, in order to turn your date columns into rows. Some good news is that Prep now supports wildcard pivots, so you'll be able to pivot those fields automatically even if you add new months. After doing that pivot, then you'd use a new Prep feature which allows you to pivot rows to columns, so you'd then pivot each of your metrics into columns to create individual measures. If you're interested in this method, I'm happy to show you how to do it.
A second method is to use calculated fields within Tableau Desktop to create your separate measures:
IF [F1]="Metric 1" THEN
[Pivot Field Values]
You'd have to create one of these for each separate metric.
Then you'd build a view leveraging Measure Names and Measure Values like this:
Because each measure is separate, you can then format them each individually.
See attached workbook.
sample (2).twbx 45.9 KB
Are you using the MeasureNames/MeasureValues layout on the sheet?
In the attached 10.5 example that's what I did. The default formats for each measure prevail in the display.
Superstore -- Different formats.twbx 368.5 KB
I Thought about creating multiple metrics as well and then format each one to suit the values it holds , but the problem Is that I have 50 diff metrics. and they keep adding more
I would be very interested in the data prep method. Thanks so much for the quick reply.
Hi Joe ,
Thanks for your reply.
Measure name and values are not used. one column can have multiple formats dependent on the metric type.
Okay. Let me work on that and get back to you.
Here's how to build this in Tableau Prep. Note: This uses features of the latest version, so be sure you have 2019.1.
1) Bring in your source.
2) Create a pivot step and choose a wildcard pivot. You can create the wildcard on "-" since each month field seems to contain that.
3) Next create another pivot step, but change the type to "Rows to Columns". Drag F1 to the Pivoted Fields section and Pivot 1 Values to the fields to aggregate section.
4) Finally, output the results to a file. I'm choosing to output to Hyper.
Run it then open it in Tableau. The data will come in like this, which should be exactly what you're looking for.
I've attached the Prep workflow.
Pivots.tfl 3.5 KB