Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Correlation

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

08-11-2016 08:54 PM

Technique to do correlation in DAX with thanks to @konstantinos for teaching me about VAR statement. This creates a correlation between forecasting table and wages tables:

- In forecasting, create the following custom column:

CorrelateX2 = // Gregory J Deckler - 2/18/2016 // Create a new column that subtracts the Sales from the average of Sales // // First, calculate the average of Sales, making sure to remove any filters on forecasting table VAR AverageX2 = AVERAGEX(ALL(forecasting),[Sales]) // Return the Sales from the current row minus the average of all Sales RETURN ( [Sales] - AverageX2)

- In wages, create the following custom column:

CorrelateY2 = // Gregory J Deckler - 2/18/2016 // Create a new column that subtracts the Wages from the average of Wages // // First, calculate the average of Wages, making sure to remove any filters on wages table VAR AverageY2 = AVERAGEX(ALL(wages),[Wages]) // Return the Wages from the current row minus the average of all Wages RETURN ([Wages] - AverageY2)

- Create the following measure:

Correlation2 = // Gregory J Deckler - 2/18/2016 // This measure calculates the correlation between Sales and Wages using previously created custom columns // [CorrelationX2] (Sales) and [CorrelationY2) (Wages) // [CorrelationX2] and [CorrelationY2] calculate the difference of the current Sales or Wages from the average // // First, sum the product of the difference in averages for Sales squared VAR SumCorrelateX2SumCorrelateX2 = SUMX(ALL(forecasting),[CorrelateX2]*[CorrelateX2]) // Next, sum the product of the difference in averages for Sales multiplied by the related difference in averages for Wages VAR SumCorrelateX2SumCorrelateY2 = SUMX(ALL(forecasting),[CorrelateX] * RELATED(wages[CorrelateY])) // Then, sum the product of the difference in averages for Wages squared VAR SumCorrelateYCorrelateY = SUMX(ALL(forecasting),RELATED(wages[CorrelateY]) * RELATED(wages[CorrelateY])) // Return the correlation calculated using the formula: // Sum the product of the difference in averages for Sales multiplied by the related difference in averages for Wages // divided by the square root of the product // the sum of the product of the difference in averages for Sales squared multiplied by // the sum of the product of the difference in averages for Wages squared RETURN ( SumCorrelateX2SumCorrelateY2 / SQRT(SumCorrelateX2SumCorrelateX2*SumCorrelateYCorrelateY) )