Error in Correlation Coefficient

[v_mark]

I was trying to use the quick measure to get the Correlation Coefficient but getting this error below.

category is STDID

X-Axis  is AttendanceRate

Y-Axis is GWA

Error Message:
MdxScript(Model) (230, 3) Calculation error in measure 'Correlation_Coefficient ( Including 0 )'[Average of STRATTENDANCERATE and Average of strFinalGWA correlation for FullName]: An argument of function 'SQRT' has the wrong data type or the result is too large or too small.

My Initial conclusion (which not totally sure) is data type mis match since my GWA is example. 1.5, 1.25, 1.33 etc. 

and my attendance rate is 98.00, 87.00, 99.44 

and bunch of blanks both in GWA and AttendanceRate

Any workaround to resolve the error?

Should I remove those blanks affecting both GWA and AttendanceRate? 

Appreciate the help a lot!

My Table shows like this: 

Sample Data 

[TheoC · Accepted solution]

Hi @v_mark yes, you're correct in your theory. The error looks to be driven by blanks / 0s and is causing that. If you are able to remove blanks / 0s either in the raw data, using Power Query, or other dax methods, I'd recommend doing this.

Also, I definitely recommend taking a look at this link that is likely to assist as well: https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/dax-query-language-for-...

And one other great little resource: https://community.powerbi.com/t5/Quick-Measures-Gallery/Correlation-coefficient/m-p/196274

Hope this helps mate.

Theo