cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

In Excel the statement would be

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

1 ACCEPTED SOLUTION
Super User

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

9 REPLIES 9
Super User

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.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash
Helper I

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

Super User

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Helper I

Hi,

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

Super User

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

Pete

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Helper I

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

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

Super User

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Super User

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

Helper I

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)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.