Question
How to calculate the Pearson correlation coefficient in Tableau Prep, when advanced aggregregate calculations such as CORR are not available.
Last Modified Date: 08 Jun 2023
Environment
Tableau Prep BuilderAnswer
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.
Additional Information
Finding the Pearson CorrelationThank you for providing your feedback on the effectiveness of the article.
Open new Case
Continue Searching
Knowledge Base
Community
Product Help
Training and Tutorials