In Tableau, how do I use two parts of a pivotted column for x and y values on a graph?

Question

I'm trying to plot some data (standard curves for analytical chemistry) where the x axis is the mass of a compound I added to a solution, and the y axis is the signal recorded from an instrument (peak height on a mass spectrometer). I'd like Tableau to color code the data by compound (compound A, compound B, compound C, etc.), so that I'd wind up with a graph that looks something like this:

The original structure of my data was like this:

 SampleID   | Mass A | Mass B | ... | Signal A | Signal B | ...  
 standard 0 |       0|       0| ... |         0|         0| ...
 standard 5 |   2.535|   2.555| ... |     0.494|     1.240| ... 
 standard 25|  12.675|  12.775| ... |     2.426|     7.235| ...

I know how to make graphs one compound at a time with these original data, but for the purposes of other analyses I'm doing with these data and because I want multiple compounds on the same graph, I've pivotted them so that the structure is now like this:

 SampleID   | Compound | Parameter | Value 
 standard 0 | A        | Mass      |     0
 standard 0 | A        | Signal    |     0
 standard 5 | A        | Mass      | 2.535

etc.

How do I make a graph where the mass is on the x axis, the signal is on the y axis, and the points are colored by compound? I don't see a good way to do it when my data are in this format. I've tried making new calculated variables where the value = NULL if the parameter is not equal to "Mass" and another calculated variable where the value = NULL if the parameter is not equal to "Signal" and then putting those pills on the columns and rows, but that's not working. Is there a way to do this in Tableau with data structured like this pivotted form?

Alternatively, is there a way to spread my pivotted data so that the new structure is like this:

 SampleID   | Compound | Mass  |  Signal 
 standard 0 | A        |      0|       0
 standard 5 | A        |  2.535|   0.494
 standard 25| A        | 12.675|   2.426
 standard 0 | B        |      0|       0

etc.

and would that work better?

(For R users, that last bit would be the equivalent of the tidyr package gather and spread functions.)

Which data source is your input into tableau the original or your first pivoted version? — smb

xkavator xkavator · Accepted Answer · 2017-11-17T08:43:43

If I had to deal with this, I'd prefer to pre-process the data so that it has the format "SampleID | Compound | Mass | Signal", that would make Tableau chart straightforward.

I think there's a way to achieve the same with the data structure you have, but it's more tricky. So, if I understand correctly, you have the data it this form:

SampleId    Compound    Parameter   Value
standard 5  A           Mass        2.535
standard 5  A           Signal      0.494
standard 5  B           Mass        2.555
standard 5  B           Signal      1.24
standard 25 A           Mass        12.675
standard 25 A           Signal      2.426
standard 25 B           Mass        12.775
standard 25 B           Signal      7.235

1) You can create calculated fields for Mass and Signal using level of detail expressions, that exclude the Parameter granularity:

Mass
{exclude [Parameter] : min(if [Parameter] = 'Mass' then [Value] else NULL end)}

Signal
{exclude [Parameter] : min(if [Parameter] = 'Signal' then [Value] else NULL end)}

That will "collapse" nulls in case Parameter is not included in the view.

2) Using the Scatter Plot visualization, you can pull Mass to columns and Signal to rows, add Compound to Color pane and SampleId to Detail pane. The plot will look like this:

In Tableau, how do I use two parts of a pivotted column for x and y values on a graph?

2 Answers