A/B Testing in Power BI

Introduction

In this blog I will go over on the 5 steps required to perform A/B testing in Power BI.

For the use case I will be using an example health care dataset. The dataset contains records of 479 patients with history of substance abuse and mental health conditions which have been split into two different programs:

Usual care: 336 patients (70%)

Intervention: 143 patients (30%)

Data Model

Steps

Step 1: Identify a goal

The goal at the end of the study is to understand is there a statistical difference between both the programs.

The study will be using a DLA-Assessment score which will be measured before the program and after. An increase in DLA-Assessment score means the patient has increase their general health post the program.

Finally we will measure the mean difference between both the programs to see which one is more effective.

Step 2: Develop two different segments (Test group & control group)

The patients will be split into two different types of programs

Test group

Patients will be put in an Intervention program: 143 patients (30%)

Control group

No change in the Patients care program: 336 patients (70%)

Step 3:  Set up Hypothesis (null hypothesis & alternative hypothesis)

H0 = There is no difference between both the programs

Ha = There is a difference between both the programs

Step 4:  Analyze results (Difference in means between both methods)

Calculating means

Mean of DLA assessment 1 before patients going on the program:

Average DLA base = AVERAGEX( 'fct_Substance Abuse Program' , 
                             'fct_Substance Abuse Program'[Daily Living Activity Tes 1]
                z           )

Mean of DLA assessment 2 post patients going on the program:

Average DLA followup = AVERAGEX( 'fct_Substance Abuse Program' , 
                                 'fct_Substance Abuse Program'[Daily Living Activity Test 2]
                               )

DLA Assessment Improvement:

AVERAGEX( 'fct_Substance Abuse Program',
          [Daily Living Activity Test 2] - [Daily Living Activity Tes 1] 
        )

μ0  = Mean DLA Assessment Improvement for Intervention Program

CALCULATE( [Difference in Means], 
           FILTER( dim_Program, dim_Program[Program Key] = 1)
         )

μ1 = Mean DLA Assessment Improvement for Usual Care program

CALCULATE( [Difference in Means], 
           FILTER( dim_Program, dim_Program[Program Key] = 2)
         )

μ0 - μ1 = Difference in means

[Average DLA Followup Internvention] - [Average DLA Followup UsualCare]

Step 5:  Calculate the statistical significance of the results (eliminate change by random chance)

Once we have calculated the difference in means between both programs, we can then check this result to see if the change is statistically significant.

For this example we will be using 2 tailed Z-test with a 95% confidence interval.

Calculating standard deviation

σ0 = STDEVX.P( ADDCOLUMNS( VALUES( 'fct_Substance Abuse Program'[Patient ID]) , "_Value" , [Average DLA followup] ), [_Value] )

σ1 = STDEVX.P( ADDCOLUMNS( VALUES( 'fct_Substance Abuse Program'[Patient ID]) , "_Value" , [Average DLA base] ), [_Value] )

Calculating standard error

Var SDUsualCare = POWER(CALCULATE( [SD DLA followup] , dim_Program[Program Key] = 2) , 2)

Var SDIntervention = POWER(CALCULATE( [SD DLA followup] , dim_Program[Program Key] = 1) , 2)

Return

SQRT( (DIVIDE( SDUsualCare , [Patient Count Usual Care] , 0) + (DIVIDE( SDIntervention , [Patient Count Intervention] , 0)

)))

Calculating Z-Score

DIVIDE( ([Difference in Means Follow up] - 0) , [Standard Error] , 0 )

Calculating Confidence Interval

Confidence Interval 1 = [Difference in Means Follow up] - (1.96 * [Standard Error] )

Confidence Interval 2 = [Difference in Means Follow up] + (1.96 * [Standard Error] )

Step 6:  Interpreting the Results

The Z-score result reflects how many standard deviations away Difference in means between the two programs.

For a 95% confidence interval the critical value of the Z-score is either <-1.96  or >1.96 is considered statistically significant. This signifies that the difference in means between the two programs is unlikely due to just chance.