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

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