Forum Discussion

sbarker_11's avatar
sbarker_11
Helper I
2 years ago
Solved

Replicating an IF statement to create Column with multiply and divide within on Power BI

I want to create a column directly in BI but i keep getting errors. ive tried multiple options like the below: Unsecured W Average = IF(DATA[Loan Type]="Status", CALCULATE(SUM(DATA[Deposit Amount])...
Need Help
  • BA_Pete's avatar
    BA_Pete
    2 years ago

     

    Ah, I see. It's because you're trying to escape a number value to a text value.

    For a calculated column, this should work:

     

    Unsecured W Average [column] =
IF(
    DATA[Loan Type] = "Status",
    DATA[Deposit Amount] * (DATA[Annual Interest] / 12)
)

     

     

     

     

    Pete

BA_Pete's avatar
BA_Pete
Super User
2 years ago

Hi sbarker_11 ,

 

I may be reading this incorrectly, but it looks like it should be:

Unsecured W Average =
IF(
    DATA[Loan Type] = "Status",
    SUM(DATA[Deposit Amount]) * (DATA[Annual Interest] / 12),
    ""
)

 

Pete

  • sbarker_11's avatar
    sbarker_11
    Helper I
    2 years ago

    Hi,

    I receive the following error Expressions that yield variant data-type cannot be used to define calculated columns.

    • mlsx4's avatar
      mlsx4
      Memorable Member
      2 years ago

      Hi sbarker_11 

       

      Can you share an example of your data and what you're trying to achieve?

      • sbarker_11's avatar
        sbarker_11
        Helper I
        2 years ago

        I need a calculated column to mirror this excel formula, measures arent working:

        =IF(Z2="Fixed Loan (Unsecured)",U2*(V2/12),"")

         

        if [Loan Status] = "Fixed Loan (Unsecured)" then [Deposit Amt] * (Annual Interest / 12) if not then blank. 

         

        i already have a measure for annual interest/12 which is 

        Monthly Interest =
        DIVIDE(SUM('DATA'[Annual Interest]), 12)
    • BA_Pete's avatar
      BA_Pete
      Super User
      2 years ago

       

      Try creating it as a measure, not a calculated column.

       

      Pete

      • sbarker_11's avatar
        sbarker_11
        Helper I
        2 years ago

        sadly the error message is from trying it as a measure:

         

        "Expressions that yield variant data-type cannot be used to define calculated columns."