Calculate correlation between multiple columns

[maqsud]

I have table name Data imported in PowerBI

ID	IMPTEXT1	IMPTEXT2	A
1	4	5	4.5
2	5	4	4.3
3	4	4	4.7

I want Table in PowerBI which can calculate correlation between IMPTEXT1 with A and IMPTEXT2 with A.

Some Column Name	A
IMPTEXT1	0.78 (some correlation value)
IMPTEXT2	0.85 (some correlation value)

I tried following but this measure gives me single correlation value. And i am looking for Table in PowerBI which can be calculated through some DAX.

Correlation Coefficient = 
VAR Correlation_Table =
    FILTER (
        ADDCOLUMNS (
            VALUES ( Data[ID] ),
            "Value_X", CALCULATE ( SUM ( Data[IMPTEXT1] ) ),
            "Value_Y", CALCULATE ( SUM ( Data[IMPTEXT2] ) )
        ),
        AND (
            NOT ( ISBLANK ( [Value_X] ) ),
            NOT ( ISBLANK ( [Value_Y] ) )
        )
    )
VAR Count_Items =
    COUNTROWS ( Correlation_Table )
VAR Sum_X =
    SUMX ( Correlation_Table, [Value_X] )
VAR Sum_X2 =
    SUMX ( Correlation_Table, [Value_X] ^ 2 )
VAR Sum_Y =
    SUMX ( Correlation_Table, [Value_Y] )
VAR Sum_Y2 =
    SUMX ( Correlation_Table, [Value_Y] ^ 2 )
VAR Sum_XY =
    SUMX ( Correlation_Table, [Value_X] * [Value_Y] )
VAR Pearson_Numerator =
    Count_Items * Sum_XY - Sum_X * Sum_Y
VAR Pearson_Denominator_X =
    Count_Items * Sum_X2 - Sum_X ^ 2
VAR Pearson_Denominator_Y =
    Count_Items * Sum_Y2 - Sum_Y ^ 2
VAR Pearson_Denominator =
    SQRT ( Pearson_Denominator_X * Pearson_Denominator_Y )
RETURN
    DIVIDE ( Pearson_Numerator, Pearson_Denominator )

Thank you for time and consideration.

[v-easonf-msft · Accepted solution]

Hi , @maqsud 

The Dax you provided is  just to   calculate correlation between IMPTEXT1 and IMPTEXT2 , so it gives you single correlation value.

I think what you want is to calculate  the correlation between IMPTEXT1 and A 

Correlation Coefficient_IMPTEXT1 = 
VAR Correlation_Table =
    FILTER (
        ADDCOLUMNS (
            VALUES ( Data[ID] ),
            "Value_X", CALCULATE ( SUM ( Data[IMPTEXT1] ) ),
            "Value_Y", CALCULATE ( SUM ( Data[A] ) )
        ),
        AND (
            NOT ( ISBLANK ( [Value_X] ) ),
            NOT ( ISBLANK ( [Value_Y] ) )
        )
    )
VAR Count_Items =
    COUNTROWS ( Correlation_Table )
VAR Sum_X =
    SUMX ( Correlation_Table, [Value_X] )
VAR Sum_X2 =
    SUMX ( Correlation_Table, [Value_X] ^ 2 )
VAR Sum_Y =
    SUMX ( Correlation_Table, [Value_Y] )
VAR Sum_Y2 =
    SUMX ( Correlation_Table, [Value_Y] ^ 2 )
VAR Sum_XY =
    SUMX ( Correlation_Table, [Value_X] * [Value_Y] )
VAR Pearson_Numerator =
    Count_Items * Sum_XY - Sum_X * Sum_Y
VAR Pearson_Denominator_X =
    Count_Items * Sum_X2 - Sum_X ^ 2
VAR Pearson_Denominator_Y =
    Count_Items * Sum_Y2 - Sum_Y ^ 2
VAR Pearson_Denominator =
    SQRT (ABS( Pearson_Denominator_X * Pearson_Denominator_Y ))
RETURN
    DIVIDE ( Pearson_Numerator, Pearson_Denominator )

and the correlation between IMPTEXT2 and A .

Correlation Coefficient_IMPTEXT2 = 
VAR Correlation_Table =
    FILTER (
        ADDCOLUMNS (
            VALUES ( Data[ID] ),
            "Value_X", CALCULATE ( SUM ( Data[IMPTEXT2] ) ),
            "Value_Y", CALCULATE ( SUM ( Data[A] ) )
        ),
        AND (
            NOT ( ISBLANK ( [Value_X] ) ),
            NOT ( ISBLANK ( [Value_Y] ) )
        )
    )
VAR Count_Items =
    COUNTROWS ( Correlation_Table )
VAR Sum_X =
    SUMX ( Correlation_Table, [Value_X] )
VAR Sum_X2 =
    SUMX ( Correlation_Table, [Value_X] ^ 2 )
VAR Sum_Y =
    SUMX ( Correlation_Table, [Value_Y] )
VAR Sum_Y2 =
    SUMX ( Correlation_Table, [Value_Y] ^ 2 )
VAR Sum_XY =
    SUMX ( Correlation_Table, [Value_X] * [Value_Y] )
VAR Pearson_Numerator =
    Count_Items * Sum_XY - Sum_X * Sum_Y
VAR Pearson_Denominator_X =
    Count_Items * Sum_X2 - Sum_X ^ 2
VAR Pearson_Denominator_Y =
    Count_Items * Sum_Y2 - Sum_Y ^ 2
VAR Pearson_Denominator =
    SQRT ( ABS(Pearson_Denominator_X * Pearson_Denominator_Y ))
RETURN
    DIVIDE ( Pearson_Numerator, Pearson_Denominator )

If I misunderstand what you mean ,please feel free to let me know.

Best Regards,
Community Support Team _ Eason

[amitchandak]

@maqsud , refer if these can help

http://breaking-bi.blogspot.com/2018/10/data-science-in-power-bi-correlations.html

https://www.sqlshack.com/use-advanced-analytics-power-bi/

[harshnathani]

Hi @maqsud ,

Please check if this helps.

https://www.youtube.com/watch?v=xtRXakHARZg

Regards,

Harsh Nathani