Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
sbarker_11
Helper I
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),"")

can anyone help please?

 

In Excel the statement would be

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

1 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



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

Proud to be a Datanaut!




View solution in original post

9 REPLIES 9
adudani
Super User
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

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
Super User
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!




Hi,

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

 

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!




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

 

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

 

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!




Hi @sbarker_11 

 

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

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)

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors