Multiple operation on matrix cell

[kpratik]

My Data Model is somthing similar to this:

QUESTION 1,ANSWER 1,QUESTION 2,ANSWER 2,AUDIENCE,COUNT

QUESTION 1, QUESTION 2 and AUDIENCE are filters and I draw a matrix using following on Power BI:
Rows: ANSWER 1
Columns: ANSWER 2
Values: Count

I basically want to find Chi Square of the matrix, could not find any straight way of doing this (Able to do in R but unable to publish R graph to web).

So I am trying to find Chi Square in brute force manner for which I need to do following for each cell of matrix:
Find Row Total
Find Column Total
Find Grand Total of matrix
For each cell find -- 1. Expected = (Row Tota)*(Column Total)/(Grand Total) 2. (Actual-Expected)^2 3. Final Expected = (Actual-Expected)^2/Expected
And finally sum of final expected for each cell

Wanted to understand if this will be possible in any way in Power BI?

I tried changing my Data Model and adding Row Total, Column Total Grand Total during data preparation and then in Power BI added new column to achieve this. This works for Single Audience Filter but multi audience filter does not work and I feel best way of doing it would be in generated matrix.

Any pointer would be really helpful.

Thanks

[Anonymous]

HI @kpratik,

>>So I am trying to find Chi Square in brute force manner for which I need to do following for each cell of matrix

I don't think it is possible, power bi not contains row index and column index, you can't manually find out specific cell.

>>Wanted to understand if this will be possible in any way in Power BI?

This is possible, but it should be more complicated than you think.

For example, you have to manually calculate with specific filter to achieve what you mentioned, the use if statement with condition to control which place shows these result.

If you can please provide the pbix file with expected result to help us clarify your table structure and coding formula.

Notice: please do mask on sensitive data.

Regards,

Xiaoxin Sheng

[kpratik]

Hello @Anonymous Thank you for your reply.

I was able to do what I have mentioned in little different way, but some how basic arithmetic calculation done by DAX function is not giving correct value. Let me explain.

I have transformed my matrix (mentioned in the problem) as a table which looks something like this:

Column expcol,powercol,finalexpcol are different measure which I am using and looks like following:

expcol = DIVIDE((cross_tab_file[ROW_TOTAL]*cross_tab_file[COL_TOAL]),cross_tab_file[GRAND_TOTAL])

powercol = POWER(ABS(cross_tab_file[COUNT]-cross_tab_file[expcol]),2)


finalexpcol = DIVIDE(cross_tab_file[powercol],cross_tab_file[expcol])

However the calculation which are done by DAX is not matching with actual calculator.

Consider 1st row in the image

expcol should be (40*29)/113 which in calculator gives 10.2654867257 while DAX result is 10.266719118...

Further powercol should be (Mod(9-10.266719118))^2 which again in calculator gives as 1.60457732391 while in DAX returns as 1.18915639..

Again finalexpcol should be 1.18915639/10.266719118 which in calcultor is 0.11582632935 and in DAX is 0.177456..

Am i doing something wrong?? Any pointer would be very helpful.

Thanks

[Anonymous]

HI @kpratik,

Can you please share this pbix file to test?

Regards,

Xiaoxin Sheng