Correlation Coefficient -- filter in R script?

[agraffeo21]

Hello,

I am attempting to find correlation coefficients to display in my P BI desktop. 

Is there a simple way to do this with DAX ? I know i can find the coefficients in Excel, but I'd like to be able to easily show them in P Bi, and performing the calculations in excel then transferring them to P Bi is near impossible.

OR, is there a way with R script I can easily display correlation coefficients? I know R can display them, but i need a specific FILTER for these values. all of my values are in one column under the name 'value,' so I am wondering in R script if there is a way to filter these values by the next column, 'value type,' (i have done this with DAX before to calculate measurements.)

Thanks in advance.

[Eric_Zhang]

@agraffeo21

I don't know about Correlation Coefficient. Based on googling, it seems a specific formula and I've found some links to implement the formular in DAX. Try to check the below links.

Correlation Coefficient DAX Formula

Calculating Pearson Correlation Coefficient using DAX

If you still have problem in applying them in your case, please post the tables with data in your case.

[gabriela]

Hello,

I have a follow up question on the topic. Is there any easier/faster way to get the correlation coefficient in Power BI?

I use a lot of scatter plots in my reports. I'd like to add information about correlation coefficient (e.g., Person's r) but this is rather bothersome to write a DAX formula for for every single correlation or calculating it outside Power BI.

Is there any other option to deal with the problem?

Thanks,

Gabriela

[Anonymous]

@gabriela,

Only filtered data will get passed to an R visual.  Would a correlation plot suit, with all correlations between your (filtered) data in one place (with a visual, as well as the numbers)?  

See previous discussion in this post: https://community.powerbi.com/t5/Desktop/Custom-correlations-or-visuals/td-p/84392

[gabriela]

@Anonymous,

thank you for you reply. I'll definitely check the option you've suggested.

If may I ask, what do you mean by 'filtered data'? 

[Anonymous]

You mentioned that "I filter them by a separate column, designated value type." so I assume you have a slicer to filter down your dataset.  If so, the R visual for your corrplot etc. will be updated each time you change your slicer to show results just for that filtered data.

[agraffeo21]

@Eric_Zhang Thank you for the help. I attempted to use those and other formulas I found online with DAX but it was unsuccessful. I resorted to finding the coefficients in Excel then making a column for each one.

The problem with these formulas for me is that they distinguish values between various columns, whereas every single one of my values is in one column together, and I filter them by a separate column, designated value type.