Assign measure value in Calculated Column

[maqsud]

I want to assign below measure value as Calculated column

While putting the formula of Measure in the Calculated column. I am getting a Blank column.

A_B_ = 
VAR Correlation_Table =
    FILTER (
        ADDCOLUMNS (
            VALUES ( Demo_NumericalData[Access code] ),
            "Value_X", CALCULATE ( SUM ( Demo_NumericalData[A] ) ),
            "Value_Y", CALCULATE ( SUM ( Demo_NumericalData[B] ) )
        ),
        AND (
            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 )

[amitchandak]

@maqsud ,  any measure will not take the default value of the parameter , also Sumx here mean grand total. So if do not need grand total do not use sum/min max etc

maybe like this

A_B_ = 
VAR Correlation_Table =
    FILTER (
        ADDCOLUMNS (
            VALUES ( Demo_NumericalData[Access code] ),
            "Value_X", CALCULATE ( SUM ( Demo_NumericalData[A] ) ),
            "Value_Y", CALCULATE ( SUM ( Demo_NumericalData[B] ) )
        ),
        AND (
            NOT ( ISBLANK ( [Value_X] ) ),
            NOT ( ISBLANK ( [Value_Y] ) )
        )
    )
VAR Count_Items =
    COUNTROWS ( Correlation_Table )
VAR Sum_X =
     ( Correlation_Table, [Value_X] )
VAR Sum_X2 =
     ( Correlation_Table, [Value_X] ^ 2 )
VAR Sum_Y =
     ( Correlation_Table, [Value_Y] )
VAR Sum_Y2 =
     ( Correlation_Table, [Value_Y] ^ 2 )
VAR Sum_XY =
     ( 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 )

[maqsud]

@amitchandak, This is giving me Operator or expression '( )' is not supported in this context error.

And also giving me error as Unexpecteed tuple on line VAR SUM_X, SUM_X2, SUM_Y, SUM_Y2, SUM_XY.

[maqsud]

@amitchandak, Here, I am trying to calculate the Pearson Correlation of Column A and Column B.

[AntrikshSharma · Accepted solution]

Can you upload the file? Also I think you can rewrite the code like this:

A_B_ =
VAR Correlation_Table =
    FILTER (
        ADDCOLUMNS (
            VALUES ( Demo_NumericalData[Access code] ),
            "Value_X", CALCULATE ( SUM ( Demo_NumericalData[A] ) ),
            "Value_Y", CALCULATE ( SUM ( Demo_NumericalData[B] ) )
        ),
        [Value_X] > 0
            && [Value_Y] > 0
    )
VAR Count_Items =
    COUNTROWS ( Correlation_Table )
VAR Sum_X =
    SUMX ( Correlation_Table, [Value_X] )
VAR Sum_X2 = Sum_X ^ 2
VAR Sum_Y =
    SUMX ( Correlation_Table, [Value_Y] )
VAR Sum_Y2 = Sum_Y ^ 2
VAR Sum_XY =
    SUMX ( Correlation_Table, [Value_X] * [Value_Y] )
VAR Pearson_Numerator = Count_Items * Sum_XY - Sum_XY
VAR Pearson_Denominator_X = Count_Items * Sum_X2 - Sum_X2
VAR Pearson_Denominator_Y = Count_Items * Sum_Y2 - Sum_Y2
VAR Pearson_Denominator =
    SQRT ( Pearson_Denominator_X * Pearson_Denominator_Y )
RETURN
    DIVIDE ( Pearson_Numerator, Pearson_Denominator )

[maqsud]

@AntrikshSharma, This is also giving me Blank Column.

[AntrikshSharma]

Yes, the behavior is same as the original measure I just made it less verbose, since a measure is being called in a row context created by the calculated column there is a context transition happening and without looking at the data it is impossible to tell why your measure returns blank.