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 (%)...
Need Help
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