Unable to calculate correlation in a calculated column

[chodgson88]

The data I'm using is based on the World Happiness Report 2019. The original data ('Happiness Data') looks like this:

I created measures to calculate the correlation between "Happiness" and the other factors stated above (GDP, Social Support, Life Expectancy etc.) Basically all the columns except Country, Date, Year, and Continent. The below measure is the DAX used for the correlation between Freedom and Happiness, but has been replicated for all the other factors:

FreeCORR = var FreeX = calculate(average('Happiness Data'[Happiness]))

var FreeY = calculate(average('Happiness Data'[Freedom]))

var FreeNumerator = sumx('Happiness Data',('Happiness Data'[Happiness]-Freex)*('Happiness Data'[Freedom]-FreeY))

var FreeDenominator = sqrt(sumx('Happiness Data',('Happiness Data'[Happiness]-FreeX)^2)*sumx('Happiness Data',('Happiness Data'[Freedom]-FreeY)^2))

return

divide(FreeNumerator,FreeDenominator)

This seemingly works fine. However, I'm wanting to rank these measures in a table visual in descending order, with first being the factor with the highest positive correlation and last being the factor with the lowest negative correlation.

After doing some research, I unpivoted he original data table so the factors I'm interested in correlating are now represented in rows rather than columns, which should make calculating the correlation in a table easier (possible). The new, unpivoted data table ('Happiness Data (correlation') looks like this:

Every factor (attribute) is represented in the "Attribute" column

I then created a "summary" table ('Attributes') that would be used to calculate the correlations:

Below is the DAX used in the "Correlation2" column in the above 'Attributes' table:

Correlation2 = var CX = calculate(average('Happiness Data'[Happiness]))

var CY = AVERAGEX(RELATEDTABLE('Happiness Data (correlation)'),'Happiness Data (correlation)'[Value])

var CN = (sumx('Happiness Data',('Happiness Data'[Happiness]-CX))*(SUMX(RELATEDTABLE('Happiness Data (correlation)'),'Happiness Data (correlation)'[Value]-CY)))

var CD = SQRT(SUMX('Happiness Data',('Happiness Data'[Happiness]-CX)^2)*(SUMX(RELATEDTABLE('Happiness Data (correlation)'),('Happiness Data (correlation)'[Value]-CY)^2)))

return

divide(CN,CD)

I've tried to replicate the DAX code used in the original correlation measures as much as possible but, as you can see based on the correlation values calculated, it clearly isn't working.

I suspect there's a simple solution to this that I'm missing/overlooking as I'm pretty new to Power BI, so your help would be greatly appreciated.

For what it's worth, my data model between these 3 tables looks like this:

If you need anymore information, just let me know.

Thanks

[lbendlin]

I don't think you want to link these two tables together.  Might be easier to create the measures from the data directly.

Can you please provide the sample data in usable form?

[v-yalanwu-msft]

Hi, @chodgson88 ;

If you want to consider a row representation, you might consider a matrix;

If not, can you share the file after removing sensitive information, as well as the results you want to output?

It makes it easier to give you a solution.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.