cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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!:
The Definitive Guide to Power Query (M)

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors