cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

Multiple operation on matrix cell

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

3 REPLIES 3
Community Support Team

Re: Multiple operation on matrix cell

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Frequent Visitor

Re: Multiple operation on matrix cell

Hello @v-shex-msft 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

Community Support Team

Re: Multiple operation on matrix cell

HI @kpratik,

Can you please share this pbix file to test?

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |