KNOWLEDGE BASE

## How to Calculate the Pearson Correlation Coefficient in Tableau Prep

Published: 02 Jun 2023

### Question

How to calculate the Pearson correlation coefficient in Tableau Prep, when advanced aggregregate calculations such as CORR are not available.

### Environment

Tableau Prep Builder

The Pearson Correlation Coefficient can be calculated in Tableau Prep, but because many aggregate functions that are available in Tableau Desktop are not available in Tableau Prep, each calculation and aggregation needs to be done in a separate Calculated Field.
Refer to the attached example flow, which calculates the Pearson Coefficient of Sales and Profit:

1. [Mean Sales] and [Mean Profit] aggregate the average of [Sales] and [Profit].

Calculated Field
[Mean Sales]
{FIXED: AVG([Sales])}

Calculated Field
[Mean Profit]
{FIXED: AVG([Profit])}

2. [Sales diff] and [Profit diff] calculate the difference between the individual values and the means.

Calculated Field
[Sales diff]
[Sales] - [Mean Sales]

Calculated Field
[Profit diff]
[Profit] - [Mean Profit]

3. [Covariance numerator] is the product of [Sales diff] and [Profit diff].

Calculated Field
[Covariance numerator]
{FIXED: SUM([Sales diff]*[Profit diff])}

4. [Covariance denominator] is the count of transactions, here assuming that each transaction has a value for [Sales].

Calculated Field
[Covariance denominator]
{FIXED: COUNT([Sales])}

5. [Covariance] calculates the covariance from the previous numerator and denominator.

Calculated Field
[Covariance]
[Covariance numerator]/[Covariance denominator]

6. [Sales Stdevp] and [Profit Stdevp] calculate the standard deviations of [Sales] and [Profit] using the existing STDEVP function.

Calculated Field
[Sales Stdevp]
{FIXED: STDEVP([Sales])}

Calculated Field
[Profit Stdevp]
{FIXED: STDEVP([Profit])}

7. Finally, [Correlation Coefficient] calculates the correlation coefficient by dividing the covariance by the product of the standard deviations.

Calculated Field
[Correlation Coefficient]
[Covariance]/([Sales Stdevp]*[Profit Stdevp])

You can also check the sample workbook attached with Tableau Desktop example using the calculated fields.