I have data that looks like...
id, field, value
A,X,1.2
A,Y,1.3
A,Z,0.4
B,X,2.1
C,X,2.2
C,Y,2.3
D,X,1.3
D,Z,0.5
I want to, in English:
For each ID (A,B,C,D), calculate MIN(X.value, Y.value) - Z.value (note X,Y,Z may not be present)
Return the average of this value across A,B,C,D.
The result should return a single value, 1.525
This has been seemingly quite tricky to express in Tableau. I have several solutions that "almost" work but don't correctly reduce the row count in the final average. Assume I don't have much control over the format of the data and want to avoid the increased complexity of something like Tableau prep to transpose the data.
Happy to rename my question to help others if there's a better name for this.
Thank you for your reply Mavis!
I've attached the dataset.
That's correct - but with the twist that I want the average across all IDs.
A = 0.8
B = 2.1
C = 2.3
D = 0.9
mean(A,B,C,D) = 1.525
