Dynamic Pearson Correlation Coefficient

[Anonymous]

I am attempting to build a dashboard which would allow a user to see how similar their skill level is to a list of other occupations based on estimated skill levels from previous occupations they have worked. Essentially how it would work is a user can select occupations from a filter that they have worked in the past and the highest score for each skill would be collected and used in the correlation coefficient score to compare to the other occupations. The dataset looks like this: 

Occupation	Skill	Score
Chief Executives and Managing Directors	Reading	8
Chief Executives and Managing Directors	Learning	8
Chief Executives and Managing Directors	Problem solving	8
Chief Executives and Managing Directors	Oral communication	8
Chief Executives and Managing Directors	Planning and organising	9
Chief Executives and Managing Directors	Initiative and innovation	9
Chief Executives and Managing Directors	Numeracy	6
Chief Executives and Managing Directors	Digital engagement	6
Chief Executives and Managing Directors	Teamwork	7
Chief Executives and Managing Directors	Writing	7
General Managers	Planning and organising	8
General Managers	Initiative and innovation	8
General Managers	Numeracy	5
General Managers	Digital engagement	6
General Managers	Teamwork	7
General Managers	Writing	7
General Managers	Reading	7
General Managers	Learning	6
General Managers	Problem solving	6
General Managers	Oral communication	7
Livestock Farmers	Planning and organising	9
Livestock Farmers	Initiative and innovation	8
Livestock Farmers	Numeracy	6
Livestock Farmers	Digital engagement	4
Livestock Farmers	Teamwork	5
Livestock Farmers	Writing	6
Livestock Farmers	Reading	6
Livestock Farmers	Learning	6
Livestock Farmers	Problem solving	6
Livestock Farmers	Oral communication	6

So for example if a user selected a Livestock farmer using the filter which has a writing score of 6 and a chief executive officer which has a writing score of 7 then the writing score would be 7 and would be the one used in the correlation calculation. Once the calculation is done I am trying to have a table of all occupations with the correlation coefficient scores showing how similar the users skills are to that occupation, something like this: 

Occupation	Correlation Coefficient
Payroll Clerk	0.706156854
Purchasing Officer	0.702705206
Contract Administrator	0.690257461
Corporate Services Manager	0.687506663
Management Accountant	0.669461421
Financial Investment Manager	0.664373231

I've been trying to use the following measure but nothing is being displayed in my table: 

CorrelationCoefficient = 
VAR SelectedOccupation = SELECTEDVALUE('SelectedOccupations'[Occupation])
VAR SelectedOccupationScores =
    VALUES('Core competencies'[Score])
VAR AllOccupations = VALUES('Core competencies'[Occupation])

RETURN
CALCULATE(
    AVERAGEX(
        FILTER(AllOccupations, NOT 'Core competencies'[Occupation] = SelectedOccupation),
        DIVIDE(
            SUMX(
                INTERSECT(
                    VALUES('Core competencies'[Skill]),
                    VALUES('Core competencies'[Skill])
                ),
                CALCULATE(
                    SUMX(
                        FILTER('Core competencies', 'Core competencies'[Occupation] = SelectedOccupation),
                        'Core competencies'[Score]
                    ) *
                    CALCULATE(
                        SUMX(
                            FILTER('Core competencies', 'Core competencies'[Occupation] = 'Core competencies'[Occupation]),
                            'Core competencies'[Score]
                        )
                    )
                )
            ),
            SQRT(
                SUMX(
                    SelectedOccupationScores,
                    'Core competencies'[Score]^2
                ) *
                SUMX(
                    VALUES('Core competencies'[Skill]),
                    CALCULATE(
                        SUMX(
                            FILTER('Core competencies', 'Core competencies'[Occupation] = VALUES('Core competencies'[Occupation])),
                            'Core competencies'[Score]^2
                        )
                    )
                )
            )
        )
    ),
    ALL('Core competencies'[Occupation])
)

Does anyone know how I can modify this measure so that I can get a visualisation similar to the example I provided? Apologies for my ignorance on this topic, I am fairly new to DAX. 

Thank you in advance for any help.

[lbendlin]

There are numerous articles on that topic

power bi pearson correlation at DuckDuckGo

There is even a Quick Measure that implements a (slightly quirky) version.