Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Chi Square Test in DAX - Getting wrong p-values

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)

1 Reply