Paired T-Test Using DAX

Hi Anonymous ,

I had to refresh my memory on paired T test, always fun to brush up statistic methods (no sarcasm). However, I am a bit unclear on what you are trying to achieve. Couple of questions:
1. What do you expect the output to be exactly? I mean, we can create a measure that returns N (basically the COUNTROWS() of the dataset). However, for the second part, what does *, **, *** mean, when is "missing data" returned and what p-value is the border for not significant? Typically * = 0.05, ** = 0.01 and *** is 0.001 (at least, when I was in university that was the case if memory serves me well). Are you expecting "Missing Data" to be returned given your dataset with missing data in your second sample? And what does 'Not enough power' mean?
2. Sometimes you start numbering questions but end up asking a bunch under 1. So no second question 😛 

I liked the use case so I made a sample for you. I used a different dataset but the principle remains the same of course. Please find my pbix attached, hope this helps you out. The main DAX is of course the significance measure.

Significance = 
//We first extend our sample data, but filter out missing Score2 data. We add a difference column and a difference^2 column
VAR _tmpTableForT = ADDCOLUMNS(FILTER(PairedT, ISERROR(PairedT[Score2]) = FALSE), 
    "Diff", PairedT[Score1] - PairedT[Score2], 
    "DiffSQ", (PairedT[Score1] - PairedT[Score2])^2)

//We create a few variables that make calculating the T-value easier.
VAR sumDiff = SUMX(_tmpTableForT, [Diff])
VAR sumDiffSQ = SUMX(_tmpTableForT, [DiffSQ])
VAR sumDiffColSQ = sumDiff^2
VAR N = COUNTROWS(_tmpTableForT)

//We calculate the T_Score
VAR T_nominator = sumDiff/N
VAR T_denominator = SQRT((sumDiffSQ - (sumDiffColSQ / N)) / ((N-1)*N))
VAR T_Score = ABS(T_nominator/T_denominator)

//We lookup the T-value of the three significance levels. First we assess the row to compare against based on N-1
VAR deg = SWITCH(TRUE(),
    N-1 <= 30, N-1,
    N-1 <= 45, 30,
    N-1 <= 90, 60,
    N-1 <= 560, 120,
    1000)
VAR T_0_05 = LOOKUPVALUE(T_Table[Sig_0.05], T_Table[DegreesOfFreedom], deg)
VAR T_0_01 = LOOKUPVALUE(T_Table[Sig_0.01], T_Table[DegreesOfFreedom], deg)
VAR T_0_001 = LOOKUPVALUE(T_Table[Sig_0.001], T_Table[DegreesOfFreedom], deg)

//If the T_score  is bigger than the T_sig, then it is at least that significance level. So, if T_score < T_0_05, it is not significant. If T_Score > T_0_001, then significance is  ***
VAR result = SWITCH(TRUE(),
    T_Score > T_0_001, "*** p=0.001",
    T_Score > T_0_01, "** p=0.01",
    T_Score > T_0_05, "* p=0.05",
    "Not significant")
RETURN
result

In this sample I do not account for empty values. If you want to throw a 'missing values', you can adjust the last SWITCH statement by adding a first check like < N < COUNTROWS(PairedT), "Missing data", >. See my PBIX attached, this was a fun question to answer! 🙂 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂