Using the two columns of data with the highest correlation in/for other calculations.

[Nyx]

Is there a way that I can:

Have 5 sets of 2 columns. (Separate tables or not, doesn't matter)

Calculate the R value for each pair of columns.

Automatically find which is the highest R value and use the related data (the two columns used to calculate the highest Rvalue) in another calculation? Perhaps save it to a measure or something?

For example, with reference to the dummy table below:

Columns 1 & A correlation is calculated to be R1, correlation for columns 2 & A is R2 etc.

Auto find the highest R value out of the 5 calculated R values (R1, R2, R3, R4 or R5).

Use only the respective numbered column (1,2,3,4 or 5) and column A in other measures or calculations etc.

Such as plot them to a graph, find max value or divide one by the other, this last part doesn't matter and will change with my needs, I do not require assistance with this part. If there is a measure or dax code that can do what I need just leave this open ended or put any calculation in there please, such as divide column 1 (or 2,3,4,5 if they have the highest correlation with column A) by column A.

Thank you in advance.

	R1		R2		R3		R4		R5
	1	A	2	A	3	A	4	A	5	A
Jan-20	44	13	27	13	17	13	35	13	11	13
Feb-20	21	14	50	14	16	14	46	14	20	14
Mar-20	8	15	48	15	38	15	28	15	38	15
Apr-20	0	16	24	16	20	16	42	16	5	16
May-20	22	17	31	17	37	17	22	17	33	17
Jun-20	31	18	26	18	32	18	50	18	13	18
Jul-20	4	19	39	19	20	19	31	19	44	19
Aug-20	26	20	14	20	6	20	45	20	26	20
Sep-20	20	21	37	21	41	21	5	21	28	21
Oct-20	2	22	38	22	49	22	48	22	7	22
Nov-20	12	23	4	23	46	23	15	23	37	23
Dec-20	49	24	26	24	26	24	9	24	17	24

 The R values are not actually at the top of this data, they are just shown here to show their corresponding columns.

[daXtreme]

If you don't get answers for a long time... it means your question is not clear. Since you're a new member, please familiarize yourself with this first: How to Get Your Question Answered Quickly - Microsoft Power BI Community

And then rephrase your question to be understandable. Thanks.

[Nyx]

Thanks, I did read this beforehand, I have tried to rephrase, unfortunately I am struggling with the explanation and have no other data or formulas to provide.

I can calculate all of this in excel quite easily but need it to be dynamic in Power bi as the columns of data can change. Hopefully my rewording is better.