Correlation coefficient

The objective of this example is to create a DAX measure which executes the the Pearson correlation coefficient formula.

• A fact table is available at the grain of product category by month with both marketing expense and sales amount 

• The values in the active filter context for the Marketing Amt and Sales columns will provide the X and Y arrays

1. Create the correlation numerator measure

Correl Numerator Marketing-Sales = 
 SUMX('Marketing Expense', 
('Marketing Expense'[Marketing Amt]-AVERAGE('Marketing Expense'[Marketing Amt]))*
('Marketing Expense'[Sales]-AVERAGE('Marketing Expense'[Sales]))) 

1. Create the correlation denominator measure.

Correl Denominator Marketing-Sales = 
 VAR Marketing = SUMX('Marketing Expense',
  ('Marketing Expense'[Marketing Amt]-AVERAGE('Marketing Expense'[Marketing Amt]))^2)
 VAR Sales = SUMX('Marketing Expense',
  ('Marketing Expense'[Sales]-AVERAGE('Marketing Expense'[Sales]))^2)
RETURN SQRT(Marketing*Sales)

• The sum of the squared differences from the mean for each variable's active row are multiplied prior to the square root.

1. Create the correlation coefficient measure
   • The correlation measure is a trivial DIVIDE() of the numerator and denominator measures
   • The measure name Correlation Marketing-Sales is used to avoid confusion with other correlation measures 

1. Visualize the Correlation Measure

• The measure indicates a much stronger marketing-to-sales relationship for bikes than other other categories. 
  • Additionally, the bikes correlation is weakening, potentially due to brand awareness and repeat customers.
  • Accessories and Clothing categories were not available for sale in 2015-2016