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

[sbarker_11]

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),"")

[adudani]

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]

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."

[BA_Pete]

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]

Hi,

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

[BA_Pete]

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

Pete

[sbarker_11]

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

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

[BA_Pete · Accepted solution]

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

[mlsx4]

Hi @sbarker_11 

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

[sbarker_11]

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)