Hi Brendan, I'm afraid you've run up against a pretty serious wall in Tableau's capabilities. There's no way to reference Measure Names or Measure Values in calculations, and additionally, fields used in MN/MV can only be numeric. You won't be able to add rows of characters like in Metric 3 without using workarounds.
What I would recommend here is that you split this into at least three sheets, one for each of the A/B/C headers. You would choose the appropriate fields for each header and then stack these on top of each other on a dashboard. Hide the headers for the lower two and it'll look close to this. To get the actual A/B/C header, you can just write in the letter on the rows shelf.
You almost have it. Your thinking is on the right track. Try looking at your formula this way:
IF [Period] = "Metric 1" THEN "A"
ELSEIF [Period] = "Metric 6" THEN "B"
ELSEIF [Period] = "Metric 11" THEN "C"
Try it out and let us know if this helps.
This is not the best way of going around this... but here is the "solution".
I added your sheet 1 to the data source window and created this:
Although i got this to "work". Sheet 1 on the excel sheet is something that Tableau hates.
Although you mentioned not wanting to use pivoting in your post, I truly think that's the answer. This will cause you much less headache, trust me. If you believe it will cause problems down the road, just post your questions again
I hope this helps!
Don, I just thought I should mention that the Metrics are fields, not keys in the [Period] dimension, so he won't be able to do that, unfortunately.
I like Matthew's answer. He and I had the same thought, though I was only going off of your example image above.
There really isn't any other great way to do this.
Lol, thanks David. I realized that when I had opened up his workbook...which was on my other computer.
Thanks for the suggestion here!
Regarding your comments about pivoting the data- it definitely is easier to categorize by A/B/C in this format, but I had a couple of concerns:
- Formatting the data row by row in the table. Couldn't figure out how to do this when all of the numbers were stored in the same. I pivoted all the data so that one column was "Metric" (ex: Metric 1), and one column was "Metric Value" (ex: 10.2). the problem was that everything in the metric value column had to be a number, and had to be formatted in the same way
- For now, all of the metrics are coming from the same data source. Soon, about 30% will come from one database, 30% from another database, 30% in excel, etc. I'm worried that pivoting the data in this way would cause issues once the metrics each came from different systems. Thoughts here?
Thanks again for your response!
I tried this, but ran into another wall- the tables require side scrolling, and I found no way to synchronize scrolling between separate sheets, so when you moved one, the headers no longer lined up with the other two. Is there a way around this that I'm not aware of? Or was this not what you were thinking of?
Here is another solution using your Excel data set on Sheet 4.
You can use Tableau's own pivoting function. (This will not work on calculated field's that have been created in Tableau).
Using Sheet 4 of your Excel data set, in Tableau's data source tab, highlight your measure 1,2,4,5, & 6.
Click on the drop down arrow on the last measure (Metric 6), then select 'Pivot'. (As show in the image below)
Now you will have a new Dimension (Pivot Field Names) made up of your 5 measures.
From here you can create a calculation that will break your measures into groups (A,B,C,etc)
Place your pills as they are in the image below, and there you have it.
Hope this helps.
PS Almost forgot to attach the packaged workbook (v10.1)
Grouped Measures.twbx 18.0 KB
Ah, yes, Brendan, the scrolling is indeed an issue with my approach. Sadly, there's no way to sync the scrolling, but there may be some other options depending on how your users will consume this data. Do they use Tableau Desktop, Reader, Server, Public, etc?
Thanks Don Barnetson, this was the approach I initially tried, but my concerns were:
How to handle Metric 3 with letters, since I can't pivot it with the others? Will this pivot method work correctly when half of the data comes from excel, and half comes from another database format?
1 of 1 people found this helpful
Actually, you can pivot Metric 3, even though I didn't do it in my example.
Dealing with Metric 3 being letters, you could always convert your numbers to STR() within the calculation I had provided. (I'll try it out myself as soon as I get the chance)
If you decide to go with David's suggestion, there is a technique that I have used in the past that may help you scroll through separate sheets that may work for you. Check out the link below:
Hope this helps