Forum Discussion

Dimitri70's avatar
Dimitri70
Helper I
1 year ago
Solved

Pearson Coefficient correlation for Performance trend

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

Jan9010090%
Feb9512079%
Mar10015067%
  • 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

Need Help

4 Replies

  • v-sgandrathi's avatar
    v-sgandrathi
    Community Support
    1 year ago

    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.

  • Dimitri70's avatar
    Dimitri70
    Helper I
    1 year ago

    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?

    MonthCount on timeCount totalDP
    5/1/2024   
    6/1/2024   
    7/1/2024   
    8/1/2024   
    9/1/2024   
    ########   
    ########22100%
    ########44100%
    1/1/202544100%
    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