Using DAX for test of statistical significance of differences between two groups in a time series

[TM_Visual]

Summary: Using DAX for significance testing 

Data format:

Group	Population size	Year	Score
Male	164	2019	30
Female	300	2019	59
Male	150	2018	24
Female	321	2018	56
Male	180	2017	23
Female	318	2017	68
Male	167	2016	21
Female	317	2016	67
Male	200	2015	30
Female	345	2015	64
Male	198	2014	34
Female	331	2014	63

A sample of males and females took a test and scored between 1 and 100. I have data on an average score for each for years 2014 - 2019.

I would like to establish the significance of the difference between A and B.

Can Power BI run the statistical test required in DAX (or DAX plus Power Query), or would it have to be prepared in r or otherwise? Ideally I'd use Power BI without a plugin so that the file and technique can be used without additional downloads and adapted in the future.

While I'm trying to read guides to introductory statistics, if someone could also advise on the appropriate test I would be very grateful.

[TeigeGao · Accepted solution]

Hi @TM_Visual ,

According to your description, my understanding is that you are going to calculate the difference between the Male group and Female group in the same year, in this scenario, we can use the following DAX query: 

Difference =
CALCULATE (
    MIN ( 'Table'[Score] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Year] = MIN ( 'Table'[Year] )
            && 'Table'[Group] = "Male"
    )
)
    - CALCULATE (
        MIN ( 'Table'[Score] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Year] = MIN ( 'Table'[Year] )
                && 'Table'[Group] = "Female"
        )
    )

The result will like below:

Best Regards,

Teige