Significance testing

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 weigh...

Need Help

Anonymous
3 years ago
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

Anonymous

Not applicable

3 years ago

Hi Anonymous ,

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.