The screenshot in my dashboard above is actually multiple sheets There is one sheet that contains the rate for the control and the test (all the dark gray numbers) and then 4 individual sheets displaying the % lift of the test group over the control group that are positioned under the main sheet. I structured it this way mainly because of formatting reasons.
Here are the calculations I'm using in the lift calculation:
- Open Rate-Test = sum(case when [Group]='Test' then [Opens] end) / sum(case when [Group]='Test' then [Delivered] end)
- Open Rate-Control = sum(case when [Group]='Control' then [Opens] end) / sum(case when [Group]='Control' then [Delivered] end)
- % Lift = ( [Open Rate-Test] - [Open Rate-Control] ) / [Open Rate-Control]
Hope that helps!