Pearson Coefficient correlation for Performance trend

[Dimitri70]

Hello,

Hello,

I'm working to build a dashboard for my team to measure the delivery perfromance and trend of my supplier based on last year reccord.

I have some supplier where the On-time trend (%) trend is negative as below and would like to retrasncript tthis into a coefficient of correlation

Month Count(On time) Count(Total) On-time Rate

Jan	90	100	90%
Feb	95	120	79%
Mar	100	150	67%

• Performance trend is negative (on-time rate is decreasing).

Do you kow how to do? 

I tried quick measure in PBI but result is not matching reality.

If you have an idea to suggest?

Thank you

[Dimitri70]

I think I could fix it. thank you

[Dimitri70]

Hello, thank you for your help and for sharing the link.

I was able to copy the code and adapt it to my table, and the results appear satisfactory. However, I am facing an issue. My objective is to analyze the delivery performance trend of my suppliers over the last 12 months. In some cases, certain suppliers have only delivered for 3 months, achieving a 100% delivery performance. Despite this, the coefficient of correlation is showing -0.89. I suspect this may be due to the months without deliveries being counted as 0. Is there a way to exclude the months with no deliveries from the analysis?

Month	Count on time	Count total	DP
5/1/2024
6/1/2024
7/1/2024
8/1/2024
9/1/2024
########
########	2	2	100%
########	4	4	100%
1/1/2025	4	4	100%
2/1/2025
3/1/2025
4/1/2025
5/1/2025

here my Dax formula:

DP_Correlation =

VAR Correlation_Table =

    FILTER (

        ADDCOLUMNS (

            VALUES ( 'DP_Upturn'[Cal.year/Month] ),

            "Value_X",

                RANKX (

                    ALL ( 'DP_Upturn'[Cal.year/Month] ),

                    'DP_Upturn'[Cal.year/Month],

                    ,

                    ASC,

                    Dense

                ),

            "Value_Y",

                DIVIDE (

                    CALCULATE ( SUM ( 'DP_Upturn'[Count(On time)] ) ),

                    CALCULATE ( SUM ( 'DP_Upturn'[Count(Total)] ) )

                )

        ),

        NOT ( ISBLANK ( [Value_X] ) ) && NOT ( ISBLANK ( [Value_Y] ) )

    )

VAR Count_Items = COUNTROWS (Correlation_Table)

VAR Sum_X = SUMX (Correlation_Table, [Value_X])

VAR Sum_X2 = SUMX (Correlation_Table, [Value_X] ^ 2)

VAR Sum_Y = SUMX (Correlation_Table, [Value_Y])

VAR Sum_Y2 = SUMX (Correlation_Table, [Value_Y] ^ 2)

VAR Sum_XY = SUMX (Correlation_Table, [Value_X] * [Value_Y])

VAR Pearson_Numerator = Count_Items * Sum_XY - Sum_X * Sum_Y

VAR Pearson_Denominator_X = Count_Items * Sum_X2 - Sum_X ^ 2

VAR Pearson_Denominator_Y = Count_Items * Sum_Y2 - Sum_Y ^ 2

VAR Pearson_Denominator = SQRT (Pearson_Denominator_X * Pearson_Denominator_Y)

RETURN

    DIVIDE (Pearson_Numerator, Pearson_Denominator)

Thank you for any support you cam provide

[v-sgandrathi]

Hi @Dimitri70.,

Thank you for bringing up this question.

I would like to reinforce the valuable recommendation shared by @mark_endicott . The referenced blog provides a clear explanation and a practical DAX implementation of the Pearson correlation coefficient, which is highly effective for analyzing trends such as the negative pattern in your on-time rate.

Using this method allows you to statistically measure the correlation between time (month index) and your performance metric (on-time rate), supporting clearer visualization and communication of delivery performance trends within your dashboard.

A value near -1 indicates a strong negative correlation, which aligns with your observations.

I hope this helped! Feel free to ask any further questions. If this resolved your issue, please mark it as "Accept as Solution" and give us Kudos to assist others.

Thank you and Continue using Microsoft Fabric Communtiy Forum.

[mark_endicott · Accepted solution]

@Dimitri70 - Have you tried the measure mentioned in this blog?

https://xxlbi.com/blog/pearson-correlation-coefficient-in-dax/

It's from a very skilled Microsoft MVP.

Hopefully this helps you achieve the answer you are looking for. 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!