Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Galleries
- Quick Measures Gallery
- De-Seasonalized Correlation Coefficient

01-13-2018 08:46 AM - last edited 06-27-2018 13:21 PM

Greg_Deckler

Super User

De-Seasonalized Correlation Coefficient

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-13-2018
08:46 AM

This builds off of my original article here:

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:

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

Proud to be a Datanaut!