Dax Power BI Chi Square Test- Wrong p-values

[Anonymous]

Year	Question_Number	IC	Organization	Positive
2019	1	Agency	Agency	85
2019	2	Agency	Org1	96
2020	1	Agency	Org2	88
2020	2	Agency	Org3	95
2021	1	Agency	Org4	90
2021	1	Agency	Org5	83

I would like to calculate the chi square p-value between the avarage agency positive rating and a specific organization (e.x. org1) for year 2020. I have question number as a filter.

The table above shows structure of my data.

I have my DAX code that I wrote below, but the p value it is giving me is not correct. I used the same data and conducted a chi square test and it renders different p-value- please advise.

Significance vs Agency =

var _year = 2020

var _question_no = SELECTEDVALUE(Sheet1[Question_Number])

var _actual = CALCULATE(AVERAGE(Sheet1[Positive]),Sheet1[Year] =_year)

var _expected = CALCULATE(AVERAGE(Sheet1[Positive]),

        FILTER(ALL(Sheet1),

        Sheet1[Year]=_year && Sheet1[IC]="Agency"

        && Sheet1[Organization]="Agency"  

        && Sheet1[Question_Number]=_question_no

))

VAR ChiStat = ((_actual-_expected)^2)/_expected

--VAR DegreesFreedom = (COUNT(Sheet1[Positive])-1)

RETURN

    CHISQ.DIST.RT(ChiStat,5)

[lbendlin]

There is no "Agency" organization in 2020 so your _expected variable comes up blank.

Here is a modified measure

Significance vs Agency = 
var _year = 2020
var _question_no = SELECTEDVALUE(Sheet1[Question_Number])
var _actual = CALCULATE(AVERAGE(Sheet1[Positive]),ALL(Sheet1),Sheet1[Year] =_year)
var _expected = CALCULATE(AVERAGE(Sheet1[Positive]),
        ALL(Sheet1),
        Sheet1[Year]=_year,
--        Sheet1[IC]="Agency",
--        Sheet1[Organization]="Agency", 
        Sheet1[Question_Number]=_question_no
)
VAR ChiStat = DIVIDE(((_actual-_expected)^2),_expected,0)
RETURN CHISQ.DIST.RT(ChiStat,5)

Actual minus Expected is  small so your final result may not be very useful.