I am having quite a lot of trouble with a calculation in tableau. I have two classes in my data, each containing two different sets of data with about 700 sub groups per set. Each sub group has at least two members. You could call the classes A and B and the subgroups S1-S700. Each sub group as at least two members, say M1 and M2, and each class contains two sets of data, say A1 A2 and B1 B2.
The data is formatted with the sample names as column headers (so A1,A2,B1,B2). The sub groups form the rows, with a identifier for each member (M1,M2,M3,M1,M2,M1,M2,M3,....). So it looks something like this
Group MemberTag A1 A2 B1 B2
1 M1 # # # #
1 M2 # # # #
1 M3 # # # #
3 M1 # # # #
3 M2 # # # #
4 M1 # # # #
4 M2 # # # #
The calculation I am trying to make is comparing M1_A1 with M2_A2 in each sub group (I also want to make the same calculation for class B). Specifically, I want to know what % M2_A2 comprises of the two values in question (ie. M2_A2/(M2_A2+M1_A1)*100). I then want to display this percentage for each group as a bar chart.
I initially tried using LOOKUP(<value>,1) but found that it did not produce accurate results because it was view dependent. So when I only displayed one group on my graph it worked correctly, but when I filtered the graph so it only showed the calculation for M1 members, the calculations where all off (since the LOOKUP was returning the next M1 in the view and NOT the next entry (M2) in the source data).
Here is my current code in psuedo form: LOOKUP(MIN([A2]),1)/(LOOKUP(min([A2]),1)+MIN([A1]))*100
Any help would be greatly appreciated. Ideally, I would like a calculation that is based on the global LOD (ie. source data order) that is not view dependent.
Note, in the attached workbook, the member tags are 12C, 13C_6, 13C_12 instead of M1, M2, M3.
Calculation Help.twbx 275.1 KB