So if I understand this correctly, you have a long data format and you want to simulate a wide format by creating calculated fields.
Someone correct me if my perception is wrong - but how I see it, that method isn't a 'hacky' solution, it is the intended feature.
Obviously if your data is pivoted, you can unpivot it, but you would run into a lot more problems that way.
You're saying the only solution is to create 15 calculated fields for this dashboard?
I was hoping someone would know how to solve this with level of detail expressions or some other clever way.
Well to be clear you said 4...
You can just make 2 calculations only, then create 2 parameters (by right-clicking on 'Experimental group' -> Create... -> Parameter). Call these Param 1 and Param 2.
Then your 2 calculations would be:
IF [experimental group] = [Param 1] THEN [energy savings] ELSE NULL END
IF [experimental group] = [Param 2] THEN [energy savings] ELSE NULL END
Then right-click on the two parameters and click 'Show parameters'. Choose from these parameter controls which two groups you want to calculate the percent difference between.
Or you could duplicate the Experimental Group field, place the two Experimental Groups on Row and Energy Savings on Column. Create single-select quick filters out of the two Experimental Group fields, and then choose which two groups you want to calculate the percent difference between. Right-click on Energy Savings -> Table calculation -> click 'Percent difference.'
Now you can just duplicate this worksheet many times and on each worksheet, choose different pairs of groups to compare. And then put it all together on a dashboard.
Edit: "% difference" is under Table Calcs.
I am really just swinging in the air with these instructions, if you have a workbook to attach it would be much clearer.
OK just trying to understand. Is the parameters solution so that I can change which two experimental groups I'm comparing?
For clarification, I only have two experimental groups, "test" and "control". The 15 calculated fields comes from the fact that, under this solution, I am creating two fields for every metric I want to compare, and then a third field to actually compare them.
1. IF [experimental group] = 'test' THEN [energy savings] ELSE NULL END
2. IF [experimental group] = 'control' THEN [energy savings] ELSE NULL END
3. (SUM(Calc 1) - SUM(Calc 2))/SUM(Calc 1)
4. IF [experimental group] = 'test' THEN [cost savings] ELSE NULL END
5. IF [experimental group] = 'control' THEN [cost savings] ELSE NULL END
6. (SUM(Calc 4) - SUM(Calc 5))/SUM(Calc 4)
7. IF [experimental group] = 'test' THEN [GHG savings] ELSE NULL END
So you can see how this is not an ideal solution.
Eesh that changes everything about my answer.
1. Pivot all the 5 'Savings' fields.
2. Have just the 3 calculations, with [Pivot Field Names] instead of [energy savings], [cost saving], etc.
3. Right-click [Pivot Field Names] -> Show filter.
4. Change the filter view to 'Single value (list)'.
This data is contained in a database whose structure I can't manipulate. How would I go about getting a dataset that is pivoted?
You would have to do it outside of Tableau.