Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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:
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)
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)
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) )
User | Count |
---|---|
21 | |
19 | |
12 | |
9 | |
7 |
User | Count |
---|---|
30 | |
27 | |
14 | |
13 | |
11 |