cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
markymarklondon Regular Visitor
Regular Visitor

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 misinterpreted ones I've tried out. I'm not after a correlation matrix but merely an ability to have two time series and a correlation that changes as the period/date changes on them too. I wanted to display this correlation measure as a dynamic kpi. 

 

Has anyone done something like this?

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Dynamic Correlation function

Hi @markymarklondon,

 

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. Smiley Happy

 

Regards

View solution in original post

markymarklondon Regular Visitor
Regular Visitor

Re: Dynamic Correlation function

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

View solution in original post

4 REPLIES 4
v-ljerr-msft Super Contributor
Super Contributor

Re: Dynamic Correlation function

Hi @markymarklondon,

 

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. Smiley Happy

 

Regards

View solution in original post

markymarklondon Regular Visitor
Regular Visitor

Re: Dynamic Correlation function

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

View solution in original post

Highlighted
markymarklondon Regular Visitor
Regular Visitor

Re: Dynamic Correlation function

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

markymarklondon Regular Visitor
Regular Visitor

Re: Dynamic Correlation function

@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).

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 331 members 3,004 guests
Please welcome our newest community members: