Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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) )
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
22 | |
7 | |
6 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |