Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Dynamic Correlation function

Hi all   I have 2 time series I wish to compare and I've been used to using the correl() function in Qlikview. Unfortunately I've not seen one that is dynamic on the PowerBI forums or perhaps I've ...
General Comment
Need Help
Tips and Tricks
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi Anonymous,

     

    Here is a good article about Correlation, Seasonality and Forecasting with Power BI. Could you go to check if it helps in your scenario?

     

    If you still have any questions, could you post your table structures with some sample/mock data, and the expected result? So that we can better assist on this issue. :smileyhappy:

     

    Regards

  • Anonymous's avatar
    Anonymous
    8 years ago

    Thanks I will have a look.

     

    I've attached a file with two time series (X and Y) across a number of months. In excel if you do a correl( ) function which I've done in cell D2 then you get 0.61

     

    I was doing stuff like this on a dynamic KPI tile in Qlikview and Qlik Sense. I wanted to determine how to do this same thing with PowerBI. So I'm not after a correlation matrix file, simply the measure that will dynamically create this value for me when I filter different fields and different time periods

     

    thanks

     

    Google Drive link to XLS file

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi Anonymous,

 

Here is a good article about Correlation, Seasonality and Forecasting with Power BI. Could you go to check if it helps in your scenario?

 

If you still have any questions, could you post your table structures with some sample/mock data, and the expected result? So that we can better assist on this issue. :smileyhappy:

 

Regards

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Thanks I will have a look.

 

I've attached a file with two time series (X and Y) across a number of months. In excel if you do a correl( ) function which I've done in cell D2 then you get 0.61

 

I was doing stuff like this on a dynamic KPI tile in Qlikview and Qlik Sense. I wanted to determine how to do this same thing with PowerBI. So I'm not after a correlation matrix file, simply the measure that will dynamically create this value for me when I filter different fields and different time periods

 

thanks

 

Google Drive link to XLS file

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    I just checked that link and it was the closest of the solutions I've found on here so far. I got it working on my data but it calculates things undynamically... I tried making it all work based on measures instead of columns but either way the results were static and did not change based on filtering fields or dates.

     

    I will try it again though as I tweaked a few things smoupre wrote and thought I was getting somewhere but ran out of time.

     

    will advise if I solve this

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      v-ljerr-msft solution led me down the right path with this... the link had its own link to  http://www.mathsisfun.com/data/correlation.html which worked. As a reminder, the Greg_Deckler link was fine but this set the correlation to be non-dynamic when you changed periods. Recalculating it all to be based on measures worked perfectly though. Best approach for anyone new to this is to create all the measures in their separate table, eg Table A and Table B and to test as you go along, then try to consolidate all the code into smaller or even a single measure if you want (for neatness purposes that is).