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

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)