Afternoon,
I'm trying to find the correlation between the [close] column values of the 'StockbarDataExample' table for different companies in the 'StockSymbolExchangeCode' column.
I'm not sure how to compare the two time series given they share the same column for values.
Pbix & Source Link: https://1drv.ms/f/s!At8Q-ZbRnAj8iF5lSSIdn2oi2tGj
Solved! Go to Solution.
In this sort of "pairwise comparison" scenario, where you have multiple entities in the same table (in this case distinguished by StockSymbolCurrency) I would normally follow the below steps.
My modifed copy of your pbix is here:
https://www.dropbox.com/s/y9h2ncitj46ee6a/PowerBiForumExample2%20Owen%20edit.pbix?dl=0
Average Close = AVERAGE ( StockBarDatExample[close] )
Average Close Comparison = CALCULATE ( [Average Close], ALL ( ReferenceTable ), USERELATIONSHIP ( StockBarDatExample[StockSymbolCurrency], ReferenceTableComparison[StockSymbolCurrency] ) )
Pearson Correlation Coefficient = VAR DateTimes = // Create a table of date/times where both stocks have a Close value FILTER ( SUMMARIZE ( StockBarDatExample, DateTable[DateKey], TimeTable[Column1] ), // Date & Time columns AND ( NOT ( ISBLANK ( [Average Close] ) ), NOT ( ISBLANK ( [Average Close Comparison] ) ) ) ) // Construct table of pairs of Close values VAR Known = SELECTCOLUMNS ( DateTimes, "Known[X]", [Average Close], "Known[Y]", [Average Close Comparison] ) // Calculate correlation coefficient VAR Count_Items = COUNTROWS ( Known ) VAR Average_X = AVERAGEX ( Known, Known[X] ) VAR Average_X2 = AVERAGEX ( Known, Known[X] ^ 2 ) VAR Average_Y = AVERAGEX ( Known, Known[Y] ) VAR Average_Y2 = AVERAGEX ( Known, Known[Y] ^ 2 ) VAR Average_XY = AVERAGEX ( Known, Known[X] * Known[Y] ) VAR CorrelationCoefficient = DIVIDE ( Average_XY - Average_X * Average_Y, SQRT ( ( Average_X2 - Average_X ^ 2 ) * ( Average_Y2 - Average_Y ^ 2 ) ) ) RETURN CorrelationCoefficient
The test report page in the above pbix looks like this:
Hopefully that helps. 🙂
Regards,
Owen
HI @ElliotP,
You can use below formula to get diff between two legend:
Diff = VAR c1 = FIRSTNONBLANK ( ALL ( ReferenceTable[CompanyName] ), [CompanyName] ) VAR c2 = LASTNONBLANK ( ALL ( ReferenceTable[CompanyName] ), [CompanyName] ) RETURN ABS ( CALCULATE ( SUM ( StockBarDatExample[close] ), ReferenceTable[CompanyName] = c1 ) - CALCULATE ( SUM ( StockBarDatExample[close] ), ReferenceTable[CompanyName] = c2 ) )
AFAIK, current line chart not support use multiple value field and legend at same time, I'd like to suggest you use 'line and clustered column chart' to instead.
Regards,
Xiaoxin Sheng
@v-shex-msftThanks for the response.
I'm not trying to calculate the difference between the different company's closes, but I think the principles might be able to work. I would like to be able to calculate the correlation between the time series data for each company; but I'm unable to at the moment as both time series values are in the same column ([close]), but they two time series are distinguishable by the 'StockSymbolCurrency' column string value.
The idea of using variables to seperate the time series values before returning a correlation figure is interesting, but I'm not sure how to get each variable to filter over subsequent different string values in the 'StockSymbolCurrency' column.
In this sort of "pairwise comparison" scenario, where you have multiple entities in the same table (in this case distinguished by StockSymbolCurrency) I would normally follow the below steps.
My modifed copy of your pbix is here:
https://www.dropbox.com/s/y9h2ncitj46ee6a/PowerBiForumExample2%20Owen%20edit.pbix?dl=0
Average Close = AVERAGE ( StockBarDatExample[close] )
Average Close Comparison = CALCULATE ( [Average Close], ALL ( ReferenceTable ), USERELATIONSHIP ( StockBarDatExample[StockSymbolCurrency], ReferenceTableComparison[StockSymbolCurrency] ) )
Pearson Correlation Coefficient = VAR DateTimes = // Create a table of date/times where both stocks have a Close value FILTER ( SUMMARIZE ( StockBarDatExample, DateTable[DateKey], TimeTable[Column1] ), // Date & Time columns AND ( NOT ( ISBLANK ( [Average Close] ) ), NOT ( ISBLANK ( [Average Close Comparison] ) ) ) ) // Construct table of pairs of Close values VAR Known = SELECTCOLUMNS ( DateTimes, "Known[X]", [Average Close], "Known[Y]", [Average Close Comparison] ) // Calculate correlation coefficient VAR Count_Items = COUNTROWS ( Known ) VAR Average_X = AVERAGEX ( Known, Known[X] ) VAR Average_X2 = AVERAGEX ( Known, Known[X] ^ 2 ) VAR Average_Y = AVERAGEX ( Known, Known[Y] ) VAR Average_Y2 = AVERAGEX ( Known, Known[Y] ^ 2 ) VAR Average_XY = AVERAGEX ( Known, Known[X] * Known[Y] ) VAR CorrelationCoefficient = DIVIDE ( Average_XY - Average_X * Average_Y, SQRT ( ( Average_X2 - Average_X ^ 2 ) * ( Average_Y2 - Average_Y ^ 2 ) ) ) RETURN CorrelationCoefficient
The test report page in the above pbix looks like this:
Hopefully that helps. 🙂
Regards,
Owen
@OwenAugerThank you so much, that's amazing.
As an extension, from an idea, would it be possible to do this for a large number of comparisions? I get the feeling that might be better done with python and then exploring the resultant table and data from that instead of trying to use a tabular model to achieve that?
You're welcome 🙂
There's nothing to stop you having an arbitrary number of stocks in your source table...and you could use a matrix visual to show the correlation of every combination, or use that Correlation Plot custom visual. Or create your own R visual I guess
Perhaps performance might be better if you prepare the data with python (or R?) rather than computing on the fly - though don't have much experience with that myself.
User | Count |
---|---|
408 | |
90 | |
79 | |
55 | |
54 |
User | Count |
---|---|
387 | |
102 | |
80 | |
64 | |
48 |