Correlation Factor on Bar Chart

[Schwami]

I have the following simplified table. 

I can put this on a scatter plot to show some correlation. 

And I can create a correlation measure (quick measure or not). And that all works fine. 

Where I'm running intoo issues is the following. I would like to plot this correlation coefficient onto a bar chart with my department category, but that doesn't seem to be working. 

What am I overlooking here? 

Can anybody assist. 

[v-yueyunzh-msft · Accepted solution]

Hi , @Schwami 

According to your description and dax code , the _mX and _mY  also return the current Label Department's value, so after the calculation it also return 0 .

var _mX = CALCULATE( AVERAGE('Test 2'[Number of Employees]))

var _mY = CALCULATE( AVERAGE('Test 2'[Number of Employees left]))

You can try to use this dax code to get the total average of the current Label Department.

Correlation Coefficient =
var _mX = CALCULATE( AVERAGE('Test 2'[Number of Employees]),ALL('Test 2'[Label Department]))
var _mY = CALCULATE( AVERAGE('Test 2'[Number of Employees left]),ALL('Test 2'[Label Department]))
var _Num = SUMX('Test 2',('Test 2'[Number of Employees]-_mX)* ([Number of Employees left]-_mY))
var _test =SUMX('Test 2',([Number of Employees] - _mX)^2)*SUMX('Test 2',([Number of Employees left]-_mY)^2)
var _Den =SQRT(
SUMX('Test 2',([Number of Employees] - _mX)^2)*SUMX('Test 2',([Number of Employees left]-_mY)^2))
return
DIVIDE(_Num,_Den)

You can also put this measure on the Table visual and update the return value to test , like this:
return
_Num

So you can find which parameter return the wrong value in your measure.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly