To paraphrase you, you are looking to get a line representing the un-split total?
So line1+line2 = line3
Could you add whatever Measure (green pill) you used in the Control-Test Calculated field that you created onto the Rows Shelf and then use dual axes and sychronize them?
Yes, but I want to have 3 lines, not just combine the two that I have into a third line. The "TPValueTotal" contains the data that is being displaying in the graph. All that the ControlTest dimention does is split that into two lines.
What would be really nice is if in the calculated field I could return one record as two different records (just return twice)
IF [A_Dim] == [Var_1] AND [B_Dim] == [Var_2] THEN
'Test' AND 'Baseline'
ELSEIF [A_Dim] == [Var_2] AND [B_Dim] == [Var_1] THEN
'Test' AND 'Baseline'
'Control' AND 'Baseline'
But I don't think the AND syntax there is allowed.
2 of 2 people found this helpful
Hmmm... another thought would be to create 3 measures instead of using a dimension to try to split 1 measure as you're describing.
IF "Control Criteria" THEN [TPValueTotal] ELSE 0 END
Measure 2: "Test"
IF "Test Criteria" THEN [TPValueTotal] ELSE 0 END
Measure 3: "TPValueTotal"
Then add the Measure Values to the Columns Shelf and color by the Measure Names dimension (and filter the Measure Names to show only those 3 Measures).
I will give that a try, I think that will work. The only problem is that I am calculating percentiles in memory, so having three separate percentile calculations will be significantly slower. I will let you know how it goes though.
Edit: When I attempt that (creating the first measure) I received an error that "cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions. Any ideas?
Is it possible to create these measures prior to aggregating TPValueTotal? i.e. create filtered measures on the underlying data for TPValueTotal, say TPRaw. Then you'd have TPRaw, TPRawTest and TPRawControl and could aggregate each of these in the same way (TPValueTotal, TPValueControlTotal and TPValueTestTotal).
Mixing aggregated with non-aggregated measures can create a many to one relationship, Tableau plays it safe in these situations by sending you an error. If there is no ambiguity, or you're a risk taker, you can wrap token aggregation around the items being compared, i.e min(A_Dim)==min(Var_1).
Looking at Mark's first post, it seems like that might also give the result you what you're looking for. If you create a synchronized dual axis chart with 2 copies of TPValueTotal then select multiple mark types and remove Control-Test-baseline from the marks card for one of the copies of TPValueTotal. This will give 3 lines.
I don't know if you are still reachable by this 2012 posting but I wanted to ask you for the formula structure you used to get two separate values out of a single calculated field.
I'd post a new thread in the forum to ask your specific question, with details about your scenario and a packaged workbook. With those details, someone on the forum will help you out.