Forum Discussion
Using the two columns of data with the highest correlation in/for other calculations.
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.
2 Replies
daXtremeSolution Sage
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.
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.
