Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Nyx
Frequent Visitor

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, R3R4 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 
           
 1A2A3A4A5A
Jan-2044132713171335131113
Feb-2021145014161446142014
Mar-208154815381528153815
Apr-20016241620164216516
May-2022173117371722173317
Jun-2031182618321850181318
Jul-204193919201931194419
Aug-202620142062045202620
Sep-202021372141215212821
Oct-20222382249224822722
Nov-201223423462315233723
Dec-204924262426249241724

 

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

2 REPLIES 2
daXtreme
Solution Sage
Solution 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.

Nyx
Frequent Visitor

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.