Forum Discussion

maqsud's avatar
maqsud
Helper III
6 years ago
Solved

Assign measure value in Calculated Column

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 )
Need Help
Show and Tell
  • AntrikshSharma's avatar
    AntrikshSharma
    6 years ago

    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 )

6 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    6 years ago

    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 )
      • AntrikshSharma's avatar
        AntrikshSharma
        Community Champion
        6 years ago
        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.
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      maqsud
      Helper III
      6 years ago

      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's avatar
        maqsud
        Helper III
        6 years ago

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