cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Super User

## Correlation

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)
)```

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
0 REPLIES 0

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors