De-Seasonalized Correlation Coefficient 01-13-2018 08:46 AM - last edited 06-27-2018 13:21 PM

[Greg_Deckler]

This builds off of my original article here:

https://community.powerbi.com/t5/Community-Blog/Correlation-Seasonality-and-Forecasting-with-Power-B...

and @Daniil's take on that approach here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Correlation-coefficient/m-p/196274

To introduce the concept of deseasonalizing the data prior to calculating the correlation. I did not use the new Correlation Coefficient Quick Measure as a basis for this because, in my opinion, it has serious deficiencies as posted here:

https://community.powerbi.com/t5/Desktop/Serious-Issue-with-New-Correlation-Coefficient-Quick-Measur...

The formula is this:

Correlation Coefficient with Seasonality =
VAR Seasonal_Table =
FILTER (
ADDCOLUMNS (
VALUES ( {CATEGORY} ),
"Quarter", CALCULATE( {QUARTER} ),
"Value_Xs", CALCULATE ( {MEASURE1} ),
"Value_Y", CALCULATE ( {MEASURE2} )
),
AND (
NOT ( ISBLANK ( [Value_Xs] ) ),
NOT ( ISBLANK ( [Value_Y] ) )
)
)

VAR SeasonQuarter1Average = AVERAGEX(FILTER(Seasonal_Table,[Quarter]=1),[Value_Xs])
VAR SeasonQuarter2Average = AVERAGEX(FILTER(Seasonal_Table,[Quarter]=2),[Value_Xs])
VAR SeasonQuarter3Average = AVERAGEX(FILTER(Seasonal_Table,[Quarter]=3),[Value_Xs])
VAR SeasonQuarter4Average = AVERAGEX(FILTER(Seasonal_Table,[Quarter]=4),[Value_Xs])
VAR SeasonAverage = AVERAGEX(Seasonal_Table,[Value_Xs])

VAR SeasonQ1SI = SeasonQuarter1Average/SeasonAverage
VAR SeasonQ2SI = SeasonQuarter2Average/SeasonAverage
VAR SeasonQ3SI = SeasonQuarter3Average/SeasonAverage
VAR SeasonQ4SI = SeasonQuarter4Average/SeasonAverage

VAR Correlation_Table =
ADDCOLUMNS(Seasonal_Table,
"Value_X",SWITCH([Quarter],
1,CALCULATE ( AVERAGE(forecasting[Sales])/SeasonQ1SI ),
2,CALCULATE ( AVERAGE(forecasting[Sales])/SeasonQ2SI ),
3,CALCULATE ( AVERAGE(forecasting[Sales])/SeasonQ3SI ),
4,CALCULATE ( AVERAGE(forecasting[Sales])/SeasonQ4SI )
)
)

VAR Count_Items =
COUNTROWS ( Correlation_Table )
VAR Sum_X =
SUMX ( Correlation_Table, [Value_X] )
VAR Sum_X2 =
SUMX ( Correlation_Table, [Value_X] ^ 2 )
VAR Sum_Y =
SUMX ( Correlation_Table, [Value_Y] )
VAR Sum_Y2 =
SUMX ( Correlation_Table, [Value_Y] ^ 2 )
VAR Sum_XY =
SUMX ( Correlation_Table, [Value_X] * [Value_Y] )
VAR Pearson_Numerator =
Count_Items * Sum_XY - Sum_X * Sum_Y
VAR Pearson_Denominator_X =
Count_Items * Sum_X2 - Sum_X ^ 2
VAR Pearson_Denominator_Y =
Count_Items * Sum_Y2 - Sum_Y ^ 2
VAR Pearson_Denominator =
SQRT ( Pearson_Denominator_X * Pearson_Denominator_Y )
RETURN
DIVIDE ( Pearson_Numerator, Pearson_Denominator )

eyJrIjoiZGNhMjcwODctNDNiNy00YTEzLWI3ZjItNmE2ZjJkZDlkYmU5IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9