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]) * (DATA[Annual Interest]) /12),"")

can anyone help please?

 

In Excel the statement would be

=IF(Z3="Status)",U3*(V3/12),"")

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

9 Replies

  • 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?

  • adudani's avatar
    adudani
    Memorable Member
    2 years ago

    Hi sbarker_11 ,

     

    1st approach : 

    Try this measure :

    Measure = 

    CALCULATE(

    SUMX (DATA, DATA[Deposit Amount]) * (DATA[Annual Interest]) /12),

    DATA[LOAN TYPE] = "STATUS"

    )

     

    Another approach:

    If you

     

    1. create a column in the table data named IsLoanType_Status where IF ( Data[Loan Type] = "Status", 1 ,0 )

     

    Then, 

    2. Create a measure: 

    SUMX (DATA, DATA[Deposit Amount]) * (DATA[Annual Interest]) /12)

     

    3. On the visual containing the measure above, add the IsLoanType_Status is 1. 

     

    Please share sample data removing any sensitive information in case this doesn't resolve the issue.

    • sbarker_11's avatar
      sbarker_11
      Helper I
      2 years ago

      Hi, this makes sense but when I have tried approach 1, i get the following error, maybe it needs summing? :
      "A single value for column 'Annual Interest' in table 'DATA' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."