Significance testing

[Loubot3000]

Is there any way to do significance testing in Power BI and have it somehow display on a graph with the raw numbers?

For example, I have the following graph, which is calculated by doing the weighted percentages of people responding with certain answers at certain questions each month.

I want to do significance testing to compare the number of each month with the previous month, and highlight it or possibly have an arrow or something if it significantly different.

Is this possible? How would I do this?

Thanks

[Loubot3000 · Accepted solution]

I think I've figured it out. After a bunch of research, for my case where I'm comparing the population of two weighted independent samples whose sizes are quite large (in the thousands), I think the correct statistical test to use is a weighted two-sample two-tailed Z-test of populations.

Scroll to the bottom to see my explanation of why this test makes sense. Also pease somebody correct me if I'm wrong.

Here is my code:

Buzz significance test =

VAR currentMonthSuccesses = CALCULATE(SUM(Sheet1[Weight]), BUZZ[BUZZ] = "SkyShowtime", BUZZ[Response] = "Yes")

VAR currentMonthPopulation = SUM(Sheet1[Weight])

VAR currentMonthProportion = currentMonthSuccesses / currentMonthPopulation

VAR prevMonthSuccesses = CALCULATE(SUM(Sheet1[Weight]), BUZZ[BUZZ] = "SkyShowtime", BUZZ[Response] = "Yes", PREVIOUSMONTH(CALANDER[Date]))

VAR prevMonthPopulation = CALCULATE(SUM(Sheet1[Weight]), PREVIOUSMONTH(CALANDER[Date]))

VAR prevMonthProportion = prevMonthSuccesses / prevMonthPopulation

VAR pooledProportion =

    (currentMonthSuccesses + prevMonthSuccesses)

    / (currentMonthPopulation + prevMonthPopulation)

VAR Z_score =

    IF(

        NOT(currentMonthPopulation) || NOT(prevMonthPopulation),

        BLANK(),

        (currentMonthProportion - prevMonthProportion)

        / SQRT(pooledProportion * (1 - pooledProportion) * (1/currentMonthPopulation + 1/prevMonthPopulation))

    )

// This is the Z-test statistic, used to compare against the critical Z-score(s) for a given confidence interval. It has to be encased in an IF() statement to make sure it's not calculating the score for empty months, otherwise the SQRT() function breaks.

VAR Z_test =

    IF(Z_score > 1.96, 1, IF(z_score < -1.96, -1, 0))

// +/-1.96 is the critical Z-score for a two-tailed Z-test with a confidence level of 5%.

RETURN Z_test

The weight is accounted for by just summing the weights instead of doing distinct counts to calculate the proportions.

Here you can see the significance for each month illustrated in a table above the graph:

The arrows and +/- 1 show the direct of significance. You can see that it visually makes sense with what the data is doing. I achieved that by right clicking the significance test field in the visualization and doing conditional formatting.

This way of illustrating it (arrows) should work well if added to an actual table of data, however not so much for a line graph. Will have to find another way of doing it.

Why Z-test:

The reason the T-test isn't done is because that assumes that the samples are so small (<30) that the mean/variance can't reliably be calculated. In my case, it's in the thousands. Also one reason to rule out a bunch of other statistical tests is that they test the dependence of one variable on another (e.g. proportion of people answering a question, and month), whereas in this case we just have two samples that happen to be from two consecutive months, but the month variable doesn't really matter.

On my earlier question about the mean/standard deviation of a proportion - I think actually it does make sense to talk about the varience of a proportion. It is a statistic about the normal probability distribution of making a new observation of a proportion. That distribution definitely has a mean and stdev. Another way of thinking about it is that if you have 500 successes out of 1000 people, the stdev is smaller because you're more likely to see new observations of successes centred around 500 (e.g. 501 successes would make no difference to the proportion), however if you had 5 successes out of 10 people, the stdev would be much bigger because the new observations wouldn't be as cented around 5 (6 or 7 people out of 10 would be a much bigger difference to the proportion).

At least that's the way I understand it. Please somebody correct me if I'm wrong.

Here's more on the Z-test of proportions: Z Test: Definition & Two Proportion Z-Test - Statistics How To

[kasapzade]

Hello,

I wanted to check if you could find a way to emplement the test results into charts as colorful arrows or something similar...

[cvs1989]

Have you figured out a way to do this dynamically rather than hard coding? For example, I have data like this: 

	AA	AA	BB	BB	Total	Total
	N	%	N	%	N	%
A	3	30%	7	70%	10	100%
B	4	40%	6	60%	10	100%
C	6	60%	4	40%	10	100%
D	9	90%	1	10%	10	100%
E	1	10%	9	90%	10	100%

I kind of doubt it's possible but in an ideal world I want to know for each row if the difference between AA and total is stat sig and if the difference between BB and total is stat sig.  

[VibenChak]

Hi @Loubot3000, Could you please confirm the above formula is fully correct, were you able to get the desired result?

[Loubot3000]

Pretty sure yeah.

[VibenChak]

Thanks for the confirmation!

[Anonymous]

Hi @Loubot3000 ,

I've changed the formula. I apologize. I may have misunderstood you.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Loubot3000 ,

Please have a try.

Sales Difference =
VAR CurrentMonthSales = [Sales]
VAR PreviousMonthSales =
    CALCULATE ( [Sales], DATEADD ( 'Calendar'[Date], -1, MONTH ) )
RETURN
    CurrentMonthSales - PreviousMonthSales

Please check the CONFIDENCE.T.

I have also found the document, please refer to .

Paired T-test in Power BI using DAX - Ben's Blog (datakuity.com)

More details: Apply conditional table formatting in Power BI - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Loubot3000]

Thanks for the reply.

The confidence level is not really what is needed to perform a t-test.

Also that doesn't really address the main concern:
Are you sure Student's T-test is a suitable test for this scenario? The data is categorical - people can either choose answer A or answer B or answer C. I'm graphing the percentage of people answering C each month, and looking to determine whether it is significantly different between two consecutive months.  Since it is categorical, there's no standard deviation or mean or anything, and therefore you can't do a student's t-test.

Please do correct me if I'm wrong about this though, I don't fully understand all of this.

[Loubot3000]

T.TEST() doesn't seem to be an existing function.

Also, are you sure Student's T-test is a suitable test for this scenario? The data is categorical - people can either choose answer A or answer B or answer C. I'm graphing the percentage of people answering C each month, and looking to determine whether it is significantly different each month.  Since it is categorical, there's no standard deviation or mean or anything, and therefore you can't do a student's t-test.

Please do correct me if I'm wrong about this though, I don't fully understand all of this.